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

[po]

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

[v-piga-msft]

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

[po]

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