In the previous recipe, we created a very basic report with a list of countries. While we are in a development phase, this type of report is useful to control if the attribute forms have been created correctly. Similarly, to check the parent-child settings, you can create a quick report with two or more attributes involved in the relationships; just to be sure that every son has its father and no family is broken.
We are now creating a report on the Time dimension:
- Go to My Personal Objects | My Reports, right-click on the right pane and select New | Report.
- Leave Blank Report selected and click on OK.
- In My Shortcuts pane on the left, click on Attributes, and from the list that appears on the right-click on Year.
- Select Add to Rows.
- Do the same with Month, you'll see that the two attributes appear as columns in the Report View pane.
- Run the report by selecting the View | Grid View menu.
- You can now click on Save and Close and name the report:
02 Calendar.
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?