Hello all
Which is best practice; to have dates such as fiscal quarters set up in the source data through Power Query or to have a separate date dimension table and load that into Power Pivot? Also, I was really interested to note that dimension table columns should be used in rows and columns fields when creating pivot tables when you have 2 or more fact tables. On this basis then, should I create a separate date table even though my transaction data of actual spend is the only fact table that contains dates (my budget fact table does not have a date field)?
Thank you
Tim
Hi Tim,
Great questions.
It is more efficient to remove date fields other than the day from your fact table and instead store them in a calendar dimension table. e.g. assuming your fact table has date, year, month, fiscal quarter etc. then remove all these columns except for the date. In your calendar dimension table you'd have date, year, month, fiscal quarter etc. This reduces the amount of data in your model.
If you have fiscal periods that aren't inline with calendar periods then I recommend you create your calendar dimension table in Power Query. Otherwise, you can create it in Power Pivot if it's easier and likely to be static.
Just note, if your budget table doesn't store the data at the date level of detail then you cannot include budget data in any analysis where you also want the date (year, month, quarter etc.).
Hope that points you in the right direction.
Mynda
