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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Post Prodigy
Post Prodigy

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



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.



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,


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.



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.



Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.