![]() This simply leads us to the second reason for date dimension Consistency. Next month you’ll bring Inventory fact table, and the story begins with date fields in Inventory fact table. For third question you need to bring fiscal columns, and after a while you will have heaps of date related columns in your fact table!Īnd worst part is that this is only this fact table (let’s say Sales fact table). To answer second question you need to bring Week number of year. ![]() You can answer questions above without a date dimension of course, for example to answer the first question above, you will need to add Weekday Name, because you would need to sort it based on Weekday number, so you have to bring that as well. Here are some fields that you can slice and dice based on that in Date Dimension (and this is just a very short list, a real date dimension might have twice, three times, or 4 times more fields than this! Yes more fields means more power in slicing and dicing) That’s when a date dimension with all of these fields is handy. If you want to step beyond that, then you have to create additional fields to do it. The fact is when you use the simple hierarchy of Year, Quarter, Month, and Day, you are limited to only slice and dice by these fields. What about Fiscal? What is the revenue for Fiscal year?.Which week in the year generates least revenue or most revenue?.Weekday analysis of revenue (by Monday, Tuesday, …., Sunday).This built-in hierarchy is very good help, but what about times that you want to slice and dice date fields based on something other than these 4 fields? for example questions that you cannot answer with this hierarchy are This hierarchy normally adds automatically to date fields. If you have worked with Power BI you know that there is a date hierarchy built-in which gives you fields such as Year, Quarter, Month, and Day. Let’s look at reasons one by one Powerful Slice and Dice Some BI tools extended functions need to work with a Date Dimension.Ability to do analysis based on public holidays (Easter Monday, Good Friday, and etc).Ability to slice and dice by many date attributes such as week number, half year, day of year, and etc.Why Date Dimension?ĭate Dimension is useful for scenarios mentioned below So it shouldn’t be part of your every night ETL or data load process. ![]() Columns will be normally all descriptive information about date, such as Date itself, year, month, quarter, half year, day of month, day of year….ĭate Dimension will be normally loaded once, and used many times after it. here is an example screenshot of a date dimension records ĭate Dimension is not a big dimension as it would be only ~3650 records for 10 years, or even ~36500 rows for 100 years (and you might never want to go beyond that). For every year normally you will have 365 records (one record per year), except leap years with 366 records. For example your date dimension can start from 1st of Jan 1980 to 31st of December of 2030. What is Date Dimension?ĭate Dimension is a table that has one record per each day, no more, no less! Depends on the period used in the business you can define start and end of the date dimension. If you like to learn more about Power BI, read Power BI online book from Rookie to Rock Star. Majority of this post is conceptual and can be used across all BI tools, however part of it is Power BI focused. Having this hierarchy brings the question most of the time that do I need to have a date dimension still? of I can simply use this date hierarchy? In this post I’ll answer this question and explain it with reasons. ![]() As an example Power BI has a built-in date hierarchy that is enabled by default on top of all date fields which gives you a simple hierarchy of year, quarter, month, and day. some years ago, date dimension has been used more, but nowadays less! The main reason is that there are some tools that are using their own built-in date hierarchy. I’ve hear this question many times “Do you think we need a date dimension?”. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |