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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vpfaiz
Regular Visitor

Star or Snowflake for datamodel?

If I have a snowflake model in my Datawarehouse, do I need to flatten/denormalize that to star schema before connecting Power BI in, to improve performance or storage? I assume that Power BI is using verti pack/MD storage to store MOLAP data internally. So in that case, will there be a performance difference, storage cost difference, for star schema DW over Snowflake? Which one is the best candidate?

 

I see that Power BI can connect directly to OLTP DBs as well and report agains it. So if I need only operational reports, is there a point in restructuring data to read optimized one?

4 REPLIES 4
greggyb
Resident Rockstar
Resident Rockstar

Power BI utilizes the SSAS Tabular storage and formula engines. This includes very powerful compression based on the columnstore technology utilized by the storage engine.

 

Snowflake vs star isn't too important in terms of storage. You won't see any storage space benefits of snowflake over star like you might in a SQL database, because the column-based compression makes duplicate values in a field nearly costless in terms of storage.

Snowflake sees a slight performance hit compared to star for query run-time speed. This is because joins are expensive. Certainly, they're optimized and quite fast in the Tabular storage engine, but doing N joins will always be faster than >N joins.

Snowflake vs star can be important for user-friendliness, depending on how extreme your snowflaking is. In my experience, non-technical users find a star more approachable and easier to use.

My rule of thumb is to stick to a star schema unless I've got a good reason not to (and there are plenty of good reasons).

 

Direct Query against SQL sources is possible, but you cannot define measures against these sources, which limits a lot of the functionality. The DAX -> SQL translation layer is also not the most efficient, though it is currently undergoing performance enhancements, so this should improve. Using Power BI against a normalized OLTP schema seems a recipe for frustration to me, though it should still be doable.

The use case is more for going against a SQL data warehouse in a dimensional schema, to be able to meet simple reporting needs quickly.

 

**Edit for typoes and clarity.

Totally agree with @greggyb

 

But with tabular model you can not create a relationship on a "non key" dimension attribute and using a another granularity.

And for this reason, sometimes you have to use snowflake modelisation.

Nowadays, I prefer to use snowflake in my sql dwh and flattenize dimensions by using views.

 

On the other hand, Direct Query would be not so bad in term of performance when combining Clustered Column Store Indexes on your SQL Datawarehouse combining "super dax" querying available in SQL Server 2016:

Just waiting for the SQL Server 2016 RTM and the capability in PBI to create calculated measures when using Direct Query mode (please vote Smiley Very Happyhttps://ideas.powerbi.com/forums/265200-power-bi/suggestions/10435572-create-dax-measures-fields-and...

@fbrossard They released the capability to create measures on direct query in the Desktop update today. Blog

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer I know and i've already tweet about that.

 

From my point of view, it's a major step forward, and we can consider Power BI as an SSAS SaaS solution 🙂

 

Just need some another features like :

- Role for security implementation or the avaibility to use windows authent and propagate the credential till our database and then using the Row Level Security 

- Translation

- KPIs

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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