Two Dimensions Sharing same Parent Table

Hi,

I have a serious problem when I explore my Dw Model. I have one Fact table and Two Dimensions:

Brand Dimension and Department Dimension.

Both Dimensions share at Top Level Company Attribute. The Schema is like this :

Brand Dimension : Brand (Brand Table), Brand Group(Brand Group Table), Company (Company Table)

Department Dimension : Department( Department Table), Department Group(Department Group Table), Company (Company Table)

Almost forgot..The Company attribute is present on all tables because is a key member.

At this point everything was ok. But when I started to explore on AS (Browser) my problems has just started.

I realize that, for AS, the Company attribute is not the same for both Dimensions.

When I filtered by Company from Department Dimension I saw all Companies from Brand Dimension.

Naturally I wish to see the same Companies only.

Any clues

Thanks a lot

Joao

Please..anyone..

Richard,

Thanks a lot for your answer.

The Company Dimension is like the "Master Dimension".

Dimensions:
Company(***)
Brand(***, CompanyFK) - Company attribute on Top Level and Brand attribute on Bottom Level
Department(***, CompanyFK) - Company attribute on Top Level and Department attribute on Bottom Level

Your example is great so.. :)

I drag Brand Group attribute to the rows and some measure to the data area. Now I drag Company Dimension into filter. What happens

I get the list of all Brand Group companies, not only the ones related to Company that I filtered.

An example:

Company Dimension values : Coca-Cola, Pepsi

Brand Dimension values (Company, Brand Group, Brand) :

Coca-Cola , Chocolates, Cocoa ; Coca-Cola , Chocolates, Mix Cocoa

Pepsi, Drinks, Coke Drink ; Pepsi, Drinks, Water Drink

Filter by Company Dimension : Coca-Cola

Drag Brand Group to the rows.

Result : Chocolates ( from Coca-Cola) and Drinks ( from Pepsi). Chocolates with values and Drinks not of course.

Result: expected : Chocolates ( from Coca-Cola) with values

The result that I expect appears when I drag the Company attribute from Brand Dimension into filter otherwise ....

If the Company attribute is the same on all Dimensions why doesnĄ¯t work like a common attribute

For excel users is confusing work like this...

Thanks a lot..

Joao

and when you click down the hierarchy Company (Top Level) -> Brand Group -> Brand (Brand Dimension) you get a list of all companies, not only the ones related to Brand

I believe your issue is due to the fact that dimensions are only really "related" to each other via their relationship to a given measure group (fact table). Thus, when you filter on a given company via one dimension but browse the other dimension, you will see all companies because the second dimension's member list cannot be filtered by the first dimension. Now, if you add measures to the pivot table, if you have the pivot table configured to hide empty rows and columns (the default I believe), then the other companies should disappear from the pivot table as there should be no data for a given company (from either dimension) that is not in the filter.

Would it be possible for you to break company out into its own dimension so that there is only one list of companies The brand and department dimensions would then start with brand group and department group -- which may not be good given you will see all brand groups (across companies) and department groups (across companies) listed together. Would that be a more confusing situation for the end users

Another possibility might be to create a named set in each dimension that only includes groups (brand or department) that are under the current member of the company selected in either of the other dimensions. Just a thought (as I have not tried to mock up anything to try it).

HTH,

Dave Fackler

Dave,

Thanks a lot