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
Jo?o
[993 byte] By [
JoaoCruz] at [2007-12-27]
If I understand you correctly you have this layout and problem?
Dimensions:
Company(***)
Brand(***, CompanyFK)
Department(***, CompanyFK)
And you want the hierarchy
Brand -> Brand Group -> Company and
Department -> Dep Group -> Company.
With Company beeing the same table.
So then you go into the cube browser and drag Brand to the rows (for example) and some measure to the data area, and when you click down the hierarchy Brand -> Brand Group -> Company you get a list of all companies, not only the ones related to Brand?
...................
Then you can create two named sets with the Exists() function that contain the companies related to Brand and the companies related to Department and add a filter in the filterpane to that set.
If the problem is that the filter pane shows all companies I think you will have to make two separate Company dimensions.
Here's two links to stuff on the subject:
http://msdn2.microsoft.com/en-us/library/ms166594.aspx
http://msdn2.microsoft.com/en-gb/library/ms144936.aspx
Hope it helps, if not.. well, I learned some stuff... ;)
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..
Jo?o
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
