Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
po
Post Prodigy
Post Prodigy

Semantic layer design and row level security with third party access - best practice

Hi,

 

We are looking into the following - building a semantic layer (universe-like structure) to enable standardisation across power bi.

 

Have built semantic layers in other bi tools (e.g business objecst universe) and wondered as to peoples' thoughts/links to useful documents as to best practice in doing similar within power bi.  Have heard but not currently familiar with Sql server analaysis services and wonder if best to use this rather than build the data model within power bi itself.

 

 Worth mentioning our data warehouse is oracle approx 4 billion rows thus not practical to import all such data for adhoc queries 

 

Also within business objects there is a concept of aggreate awareness whereby can set up semantic layer such that query will run against the appropriate fact table depending on the dimensions selected e.g if wanted sales at region level would run against a region summary whereas if wanted sales at customer level woudl access the lower level customer sales fact table.

 

Also wondering as to best practive regrading row level scurity as will potentailly look to expose the semantic layer at later stage to external parties who should only see their own data.

 

Thanks

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @po,

 

I have a little confused about your scenario.

 

If I understand your requirement correctly that you want to know which is the best practice for modeling in Power BI or Sql Server Analaysis Services?

 

If it is, I'm afraid that should be determined by your specific requiremnt. Normally, the data modeling in SSAS will be more complex than that in Power BI. If you're not familiar with SSAS, I'll suggest you use Power BI design and configure the Row level Security.

 

In addition, you also could refer to this video Deep dive into data modeling using Power BI desktop and SQL Server Analysis Services to understand it in more details.

 

Please note if you use SQL Service Analysis Service as data source, you should create RLS in SSAS.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Thanks for reply.

 

Looking for best way to create a company-wide semantic data model which can be used by many people rather than every user create their own.

 

With business objects would use tool called information design tool and at this stage looking for best practice for enterprise-level semantic layer to be used by other users including potentailly external 3rd parties.  The semantic model we will look to epmploy with power bi should satisfy scheduled reports and give users ability to use it as dat amodel fro ad-hoc reporting.

 

Thanks 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.