Posts Tagged ‘date dimension’

MDX #15 – Why do I get Year, Semester, Quarter when I only need Month?

September 10, 2012 Leave a comment

Descendants() function gives me the exact level in the Date hierarchy in SSMS

In MDX #14, I used this query to get the internet sales amount for each month.


The results above are from SSMS, and they are what I was expecting.

In SSRS, the same MDX query will give more than what the Descendants() function is asking

Run this same MDX query in the Query Designer in SSRS, this is what you will get.


We’ve noticed two differences.

  1. Unlike the results from the SSMS where the returns on the Y axis are not given any column names, all attributes from the Date dimension has a column name, and each column name is the same as the level name in the Calendar hierarchy.
  2. Unlike the results from the SSMS where only the Month is returned, the SSRS returned 3 more levels in the calendar hierarchy  that are above the level Month.

Bad feature or good feature?

Having a column name is certainly a good thing in Reporting Services. But getting columns more than what I asked for can be annoying, although I can, on the other hand, also view it as a good feature, in case I need Year, Semester or Quarter for other purposes such as sorting. 

Dimension Role-Playing in Employee Comp Reporting

March 17, 2011 Leave a comment

The employee hierarchy table is just one physical table, with a date range to keep employee’s employment history. This same employee hierarchy table can be used sometimes to show different business functional teams, for example, LAS VS. Underwriters on the same fact table. 

I can simply create two SQL views to handle this, or just use two sub-queries if I don’t want to create too many SQL views.



This is called dimension role-playing. Role-playing in a data warehouse occurs when a single dimension simultaneously appears several times in the same fact table. The underlying dimension exists as a single physical table, but each of the roles should be presented in a separated labeled view.  

Another common example in the employee productivity reporting, is the date dimension. We will just need one single date dimension table, but very often, we need to have a second date, such as application date, besides the primary decision date, on the same fact table. Again we only need to create two different SQL views to handle this, with only one underlying physical date dimension table.

%d bloggers like this: