none
Create Unnatural Hirearchy RRS feed

  • Question

  • Hi I have heard of unnatural hirearchies in SSAS where the attributes displayed in a hirearchy have no relation between them.

    I have a Hirearchy called product which has got product description and another attribute called Customerfrom customer dim table .

    I need to create a hireacrchy where if i drill down on a product i should be able to see all the customers who bought it.How do i go about doing that.


    There is no relation between the product dim and customer dim tables. The only common factro between them is Fact Table.


    Any ideas.

    Thanks,
    Hemanth.
    Tuesday, July 7, 2009 12:20 PM

Answers

  • A fact relationship, or degenerate dimension as they used to be called, is when the dimension exists within the fact table itself. That shouldn't be the case here as the customer dimension would be an independent business actor from the fact table as well as the product dimension. Unnatural hierarchies can be created within the same dimension when no direct relationship between attributes exists but users still wish to be able to analyze data using those combinations of attributes. An example commonly given is a Product attribute with both direct Model and Color attributes. No direct relationship exists between the Color and Model attributes but yet you can create an unnatural hierarchy with Model as the first level and Color as the second level. As each color can have multiple Model parents, each Color can appear multiple times in this user navigable hierarchy.

    In your case, the customer and product dimensions should be completely independent and you cannot create a unnatural hierarchy of them as these two attribute hierarchies are not defined in the same dimension. The problem you've described above is an example of cross drilling rather than drill down. It's accomplished different ways by different tools but all of them support it. The following simple MDX query against Adventure Works gives you all the customers who bought products in 2004.
    SELECT 	{
    		[Measures].[Internet Sales Amount]
    	} ON COLUMNS,
    	NonEmpty(
    		{ [Product].[Product Categories].[Product].Members }
    		* { [Customer].[Customer Geography].[Customer].Members }
    		, ( [Measures].[Internet Sales Amount] )
    	) ON ROWS
    FROM 	[Adventure Works]
    WHERE 	(
    		[Date].[Calendar].[Calendar Year].&[2004]
    	)

    If you're using this in SSRS, you would create a group on Product and add Customers to the Detail section with the display of the Detail toggled by customer.

    HTH,

    Martin

     

     

    Tuesday, July 7, 2009 5:00 PM

All replies

  • I believe you will have to creat a fact relationship

    The following might help you
    http://msdn.microsoft.com/en-us/library/ms167409.aspx
    Tuesday, July 7, 2009 12:37 PM
    Answerer
  • A fact relationship, or degenerate dimension as they used to be called, is when the dimension exists within the fact table itself. That shouldn't be the case here as the customer dimension would be an independent business actor from the fact table as well as the product dimension. Unnatural hierarchies can be created within the same dimension when no direct relationship between attributes exists but users still wish to be able to analyze data using those combinations of attributes. An example commonly given is a Product attribute with both direct Model and Color attributes. No direct relationship exists between the Color and Model attributes but yet you can create an unnatural hierarchy with Model as the first level and Color as the second level. As each color can have multiple Model parents, each Color can appear multiple times in this user navigable hierarchy.

    In your case, the customer and product dimensions should be completely independent and you cannot create a unnatural hierarchy of them as these two attribute hierarchies are not defined in the same dimension. The problem you've described above is an example of cross drilling rather than drill down. It's accomplished different ways by different tools but all of them support it. The following simple MDX query against Adventure Works gives you all the customers who bought products in 2004.
    SELECT 	{
    		[Measures].[Internet Sales Amount]
    	} ON COLUMNS,
    	NonEmpty(
    		{ [Product].[Product Categories].[Product].Members }
    		* { [Customer].[Customer Geography].[Customer].Members }
    		, ( [Measures].[Internet Sales Amount] )
    	) ON ROWS
    FROM 	[Adventure Works]
    WHERE 	(
    		[Date].[Calendar].[Calendar Year].&[2004]
    	)

    If you're using this in SSRS, you would create a group on Product and add Customers to the Detail section with the display of the Detail toggled by customer.

    HTH,

    Martin

     

     

    Tuesday, July 7, 2009 5:00 PM