Semantic Layer / Data modeling

[Temoryian]

Hello,

My orginzation currently uses Oracle's OBIEE (Oracle Analytics Server replaced it a few years ago, we just haven't upgraded to OAS).  The org is looking to move in a new direction and we have access to Power BI, Tableau, and some other tools.  I would like to steer the ship to adopting Power BI.  The problem I am running into is that OBIEE provided some flexibility in their data modeling/repository.  We have a data warehouse that when designed many years ago, was built into facts and dimensions but really into a big snowflake with many dimensions connected through dimensions.  I know that Power BI and modern tools work best with star schemas.  The purpose of these data models is to provide them as a source of truth for report creators without them having to recreate joins and measures.  They are very large and I would like to keep the snowflake design.  In OBIEE, you can join tables in the phsycial layer, model them into a star by merging snowflakes in the logical layer, but then also spread stars back out into a snowflake in the presentation layer to keep the data organized in tables the way report authors are used to viewing them.

I'm looking for advice on how I can make the snowflake model work and define not only relationships but joins between tables.  Is anyone out there using a semantic layer between their data warehouse and Power BI?  I feel like this might be the way to go that way if someone wants to use a tool outside of Power BI, they can connect and grab the model and this would make us tool agnostic.  Is there a way for me to model solutions into a star via Power BI Desktop but split some dimensions into multiple tables in the presentation layer?  Thanks for helping me get on the right track!

[j_kee]

I'd say, from my present knowledge, what you describe can't be done.

To me, it is a matter of "how Power BI is designed to work" - the model you build in Power BI Desktop is the model shown in the "presentation layer". You can make that model whatever you like (given restrictions), but abstraction would need to be performed on a lower layer, either through M code (native queries of the objects added to the Power BI Semantic Model) or through objects in the databases / datasources below Power BI.

As far as I know, Power BI Semantic Models are not supporting tool agnosticity. They are the Power BI tool itself. But you can layer them, so they are perceived different from one another. Actually, they are not, they only differ by content.

[Bobp]

Others may have more to say on this ...

I have had the same question; "How to add a semantic layer before Power BI". 

Here are a few options that I have found - I am sure there are others along with many 3rd party tools.

Below is ordered from Small scale to Enterprise level:

1. Data Model in Power BI - publlished to the service. Create a shared pbix dataset that has the Semantic layer details defined and uses the Star Schema. Different people then use this dataset to create their reports.
2. Power BI Data Mart - part of the premium service ☹️. Create the Model in the DataMart and this is then used as the data connection for the user's reports. Again, the Star schema would be pre-defined along with measures and references between tables.
3. Azure Analysis Services - can be expensive ... Create a tabular model in AAS with the connections, references, and business logic. Users use this as their data connection with the measures, column formatting and even Row Level security in place.

Right now, we are using Option1 until our adoption of Power BI matures - eventually hoping to move to the Premium per user model and then implement DataMarts.