Attribute Hierachies

Hi there. I have a problem that I have been struggling to understand. Here is the situation. I will use a time dimension just as an example.

If I build a time dimension containing 2 Years of dates from 2005 to 2006 with 3 attributes: Year, Month, Date and set my attribute relationships as Date ->Month and Month -> Year. In management studio I can drag over Month and then Year (in that particular order ) and it will give me the correct results. For example if I expand the Month of January 2006 the year column will show 2006. Now If I filter the year column for the year of 2006, you would expect that the month column would only contain the months of 2006, but it doesn't. It still has months for 2005.

Now if I rebuild that dimension setting the attribute relationships to the key. I.e. Date ->Year, Month. And no attribute relation to month. I can rebuild the cube and drag over the same 2 colums, Month and Year (in that order) and then filter on the the year column for 2006 and then the Month column will show only the months for 2006.

Whats going on here?

I would check to make sure you have the total heirarchy structure as part of your key.

IE: Date should have a key of (Year --> Month --> Date).

|||

I'm not quite sure what you mean since date is the key.

My user hierarchy for the first scenario is:

Year

Month

Year (year is the attribute relationship)|||

I was able to recreate this scenario using Adventure Works. Seems to me this should work, but it behaves as you described. Here's what I did:

First, I created a new dimension called Date Test 1 that had three attributes -- Date, Month, Year based on the DimTime dimension table. For this dimension, I left Month and Year related to Date (the default). The Month attribute had members like "January 2002", "January 2003", etc. while the Year attribute had members like "CY 2002", "CY 2003", etc. I included this dimension in the Adventure Works cube, relating it to the Internet Sales measure group (joining from the Date attribute to the OrderDateKey column in the underlying fact table). After deploying and processing the updated project, I browsed the cube. From the Date Test 1 dimension, I put Month on rows and got each month listed. I then added Year to the filter area (not the subcube filter area, but the normal page filter area. I selected only "CY 2003" from the list of years and the list of months was indeed filtered to just those in 2003. Note that this was done without putting any measures in the data area -- just the dimension data. This works as I would expect it to.

Second, I created another new dimension called Date Test 2 that had the same three attributes with the same definitions. However, in this test, I moved the attribute relationship for Year from the Date attribute to the Month attribute. Thus, Date had a single attribute relationship to Month and Month had a single attribute relationship to Year. I again added this new dimension to the Adventure Works cube, relating it in the same manner as Date Test 2. Then I did the same browsing test. In this case, adding Month on rows to begin with gives the same list of all months across years. But, adding Year to the filter area and selecting "CY 2003" did NOT filter the list of months displayed on rows. Again, this was done without any measures in the data area (as was the first test above).

A few interesting observations:

1. I turned on SQL Server Profiler and captured the MDX queries being generated. As far as I can tell, they look exactly the same. They query in the browser window results in a few different queries that look something like this:

CREATE SESSION
SET [Adventure Works].[{39001120-29B1-4733-BBBA-C2BA3B63A05B}Pivot21Axis1Set0] AS
'
{
{ [Date Test X].[Month].[All] },
AddCalculatedMembers([Date Test X].[Month].[Month].MEMBERS)
}
'

SELECT
NON EMPTY [{39001120-29B1-4733-BBBA-C2BA3B63A05B}Pivot21Axis1Set0]
FROM [Adventure Works]
WHERE
(
[Date Test X].[Year].&[2003]
)

2. In the browser window, if I instead add the Year attribute to the subcube filter area (instead of the page filter area in the pivot area itself), things work as expected. For both dimension tests, the list of months displayed on the rows after adding the Month attribute is filtered when a specific year is selected for the Year attribute filter.

I suspect that this has something to do with how the query engine determines what attribute combinations actually exist (ie, the autoexists feature of the engine). It seems to be able to determine that the months of a given year are the only months that exist when both attributes are related to the key attribute for the dimension. But it doesn't seem to be able to determine this in a scenario where the relationship between the attributes is above the key attribute for the dimension.

I would consider this to be a bug myself. Anyone from Microsoft care to comment (Mosha?)

HTH,

Dave Fackler

|||

Thanks for taking the time to go through this. Another thing to note is that the same situation occurs when both attributes are related to the key attribute and then you try to filter the dates by the month.

I suspect that this is a bug as well. Any other comments?

Thanks.