May 9, 2011 at 3:19 pm
Hi,
I have a business problem where users need different hierrchy combinations like below:
Country --> Supplier --> CommodityType
Country -->Commodity Type--> Supplier
Supplier --> Country --> Commodity Type etc
Currently there are different dimensions for each of the hierrachy. Is there any alternate way to achieve this? I want to design it in such a way where I might be able to set the "Unique Member Key" property true for the leaf level column in the hierarchy. Ultimately I am looking at reducing the processing time.
Please let me know if my question doesn't have sufficient information.
Thanks and Regards,
Praveena
May 9, 2011 at 9:18 pm
Since your hierarchies can appear in any order (or so it seems), I believe you're implying that they are quite independent of each other. In this scenario, the dictum is that independent data points should appear as different (and thus independent) dimensions in a the cube.
For example, a user might say show me:
Gender > Age > Zip code -that is, drill down from the customer's gender to their ages and then for each age in each gender to the zip code they live in.
OR
Age > Zip code > Gender -that is, drill down from the customer's age to the zip code they live in and then what gender they are
etc.....
Logically, this sounds like a single, hierarchical dimension. But these are independent data points - just because you are female, doesn't mean you are 35 and being a 35-year old female doesn't mean you live in 90210.
If these elements are different ways of getting to the SAME data point (like country > state > county > zip>address vesus country > city >zip > address) then you should use different HIERARCHIES within the SAME dimension, because the data points are MUTUALLY EXCLUSIVE and not independent. So 1800 Happy St, LA California 90210 USA can be rolled up by its state, country, city, zip code or even street. If you live in 90210 you MUST live in CA. If you live in CA, you must live in the USA.
So the right thing to do is make 3 separate dimensions and let the user cross analyze the data in a cube tool (like Proclarity, Excel, Panorama, Pyramid etc). This is the entire point of a cube.
I know this can be confusing and I hope this has been helpful.
snap 😎
May 12, 2011 at 9:01 am
Thankyou! I think I got it. I will try to create independent dimensions and see how it works.
May 12, 2011 at 11:57 am
@Snap - keep in mind, with Attribute Hierarchies, these can represent independent views of the same data. To me the question here is, do you *absolutely* have to create drillable hierarchies, or can the user simply create the 'hierarchy' on the fly. if it's the former, then @snap is right, sounds like separate dims. If the latter is ok, then you can still use you existing dim, but the onus is on the user to build the drill path at query time.
Using the example data you've given, assuming a grain on the dim of something like 'address' or 'customer', you can definitely have 3 attribute hierarchies in the single dimension, say Country, Supplier, CommodityType - each of these, being independent of each other, require only an attribute relationship to the grain (ie key) of the dim. Then, when querying, the user can drag in Country, the Commodity Type then Supplier. Alternatively, they could drag in Supplier then Country etc etc. Doing it this way gets you want you want, data-wise. But it may not provide the 'presentation' feel you're users are looking for (in which case, go with the separate dimensions).
Steve.
May 13, 2011 at 4:56 am
Without data to analyze, these can only be suggestions, but there is some chance that supplier and country are related. Commodity type is probably independent, so I think you are possibly looking at two dimensions here. As has been noted, the country would be an attribute of supplier. If the same supplier has multiple countries associated with it, they will need to be in two dimensions.
It's not impossible that commodity type is dependent on supplier if the type belongs exclusively to that supplier, but the more likely case is that the same commodity type can be used across different suppliers.
May 13, 2011 at 7:31 am
Thank you all ! your suggestions really helped.
In my case, same supplier can be in different countries, also different suppliers can use the same Commodity type.
But the data tables are designed a bit different way. Supplier table has a column which contains what country it belongs to. Though the supplier ID is unique the supplier Names are duplicate as one supplier can be in different countries.
Commodity table is also not directly related to the fact table. It is linked via a Cross reference table. Fact table has a LocalCommodityCode column and a cross reference table is used to assign a set of standardized commodity codes to the Local Commodity Codes.
With the suggestions above. It makes more sense to have all three different dimensions.
Thanks again 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply