I want to create a level based hierarchy that spans multiple dimensions in my business model. Simply put my model looks like this (2 Dimensions and 1 Fact):
Dimensions
- Account
- Product
Fact
- Fact - Transaction Item
Business Model has Account and Product as dimensions off of the Fact - Transaction Item
I create the level based hierarchy as follows:
- Grand Total Level (Total Dollars)
- Product Level qty = 5,000 (Product Dimension) Primary Key = Product Number
- Country Level qty = 20 (Account Dimension) Primary Key = Country
- struggled with this since I know the qty at this level is supposed to be greater than the parent level but the reality is that while drilling down some of the quantities will get smaller and there are only about 20 countries
- State Level qty = 50 (Account Dimension) Primary Key = State
- City Level qty = 200 (Account Dimension) Primary Key = City
- Zip Level qty = 20 (Account Dimension) Primary Key = Zip
- Account Number Level qty = 50 (Account Dimension) Primary Key = Account Number
When I do the consistency check I get the following error message:
Business Model CCCi Transaction Analytics:
[nQSError: 15003] Missing most detailed table for dimension tables: [Designation Denormalized,Source Account Denormalized].
[nQSError: 15001] Could not load navigation space for subject area CCCi Transaction Analytics.
I assumed that the error is related to the fact that I have two different dimensions in my level based hierarchy. I tried to resolve this by going into the Product Level "Preferred Drill Path" and creating a preferred path. Here is what I got:
- From the "Browse" list under Name I selected Country (since Country would be directly below the Product level). I selected that it was added to my Preferred Drill Path list.
I did a consistency check and again got the same error message. My assumption is that because the Product to Account dimensions are connected through the Fact table that it is still missing another step. I also made the assumption that I don't have to add a Preferred Drill Path to the levels below country since they all come from the same dimension as Country. At this point I'm not sue how to resolve this and would appreciate any help.
Thanks...