Snowflake Schema to Handle Different Fact Table Grains

[brian_wrench]

Hi There,

Is it bad practice to snowflake a dimension to handle different grains on fact tables? For example, I have two fact tables: fact_sales, and fact_sales_targets. The first is at the day grain, but the second is at the month grain. You could write DAX in your measures to handle that, but I have always created a month dimension, attached it to the date dimension, and related each fact table to it's respective dimension. This works great with no fancy DAX, or at least not much.

Will snowflaking the dimension cause any type of performance problems compared to writing the DAX? I've never noticed problems but may not be dealing with enough data. 

Thanks.

-Brian

[FreemanZ · Accepted solution]

In general, I think it is a good practice. The additional DimMonth table may take some but very limited storage but it saves the DAX code, saving the processing CPU. 

[brian_wrench]

Thank you. I expected this, but every example I could find online had bad examples only for why you should not snowflake the model. This situation was not included.