You need to have completed the previous recipes to continue.

We are now creating a report on the Time dimension:

MicroStrategy reads the metadata to get the information needed to produce the SQL and issues the query. The resulting dataset is then displayed and the Parent attribute rows are merged. You only see the Year values appearing once in the grid, with the corresponding Months, this is the default behavior.

Re-run the report and look at the results, isn't it strange? Uh-Oh, year 2005 starts in July. In this particular case, we are using test data and in fact the database only holds dates starting from July 2005. However, in a real-world project, this situation should raise a flag of attention, and we'd better go to the sqlcmd console to check the DimDate table.

Now scroll to the end of the report. Year 2008 also has fewer months, year 2009 is missing, and year 2010 only has November. Definitely not a very good date dimension: can you imagine what would happen if we had sales in 2009 or 2010? Yes, probably they would disappear from the sales reports due to inner JOIN and this is one of the most common mistakes we can do in BI. Sometimes dimension tables are incomplete, long forgotten, or simply not updated; it helps to check them every now and then.

It happened to a project of mine. Back in 2009, I had a Time table with dates until year 2012: three years is a long life span for a report (I thought), so 2012 would be enough. Wrong! That report survived more than expected and in January 2013 it started returning no rows. With a red face I had to modify the Time table and, since bad habits are hard to break, I inserted dates until 2015. Guess what?