Hi Guys,

I am pretty much new to SSAS and a way to go. I am having a requirement like below and I need some help down here.

We are having some products. There are basically hirarchical structure (Only 1 level). Say I am having Prod1 as my parent product and it's flag is 1. Under Prod1 there can be saveral products which flag is 0. 

My requirement is something like this while extracing data from the Cube through MDX by default only Prod1 records should come. If they want to see the detailed products (i.e. all the products under Prod1) then they have to use "[Item].[Contained Flag].[All]" explicitly in the MDX. 

I am literally pretty much confused how to achive this. I was referering some blogs and found that we can use scope in the calculated members. Any help will be appreciable. 

T

Hi Sarat, this looks achievable by simply setting the DefaultMember property of the Contained Flag attribute for the Item project dimension (not cube dimension, which could be a problem if the dimension needs to behave another way in other cubes in the same project) to the member 1.

Hi sarat,

In this scenario, since you only have flag for your product and sub product, it's not enough to build the relationship between the product and sub product. Because we can't determine how many flag 0 sub products belong to which flag 1 parent product. So for your requirement, you should have a column in your dimension table which shows the parent product on sub product grain. Then you can build the hierarchy: Product->Sub Product. It's pretty much like the Category and SubCategory of Product dimension in AdventureWorks sample database.

 

Regards,