importance of defining dimensional hierarchies

Dimensional modeling is very important in OBIEE. Many mistakes happen because of incorrectly defined time dimension or other dimensions. This service request below is a demo of how an incorrectly defined key in the beginning can block the development efforts in the future.

Overview:
========

Modified rpd and added a new dimension for Key Accounts (Sales.”Key Account”.”Key Acct”) into the Sales subject area . After this change when building a report with Sales.Brand and drillingdown on Brand to show Brand_SKU ( a different dimension throws an error in Answers.

ERROR
===========-
[nQSError: 14020] None of the fact tables are compatible with the query request Brand.Brand.

STEPS
===========
By following these steps the issue can be reproduced:

1. Add dimension called Sales.”Key Account”.”Key Acct into sales subject area in rpd
2. save rpd and checkin changes
3. create answers report with a different dimension Brand.
4. Drilldown on Brand to show the next level “Brand”.”Brand_SKU”.
5. Get the error mentioned above

EXPECTED BEHAVIOR
===========
The report should work fine because the “Brand”.”Brand_SKU” was not modified at all.

Business Impact
===========
This is a very important issue that is holding up all BI projects. You cannot move forward until this issue is fixed.
Cause

The error thrown when drillingdown from Brand to Brand_SKU is caused because of incorrect definition of level keys in your rpd. The issue is, that by definition, a key is the lowest level of a table. Any higher levels are made up of aggregating the lowest or detail level. Since Brand and Product have the same detail key, there should be some consistency between the detail or lowest level of their respective hierarchies. There is not. The lowest level of the Brand hierarchy has a level key of CHILD_KEY_WID. The lowest level of the Product hierarchy has a level key of ‘SKU Nbr’ which is mapped to LVL6_KEY. It seems that the navigator is expecting the same detail key but is not seeing it. This seems to be the cause of the navigation error.

As confirmed by reviewing your rpd inhouse, the level keys are not properly defined for Brand and Product dimensions.
The levels were not defined according to best practices. The document from Engineering that lists the issues in your rpd that dont meet best practice is attached to the SR.
Solution

Please follow these steps in order to fix the error “[nQSError: 14020] None of the fact tables are compatible with the query request Brand.Brand”

1) Open rpd using Admin tool
2) Select BMM layer and delete the current hierarchy created for Brand
3) Make the following change to Product Dimension

–Dimension –Logical File –Physical File
Product Product HSAMI_SKU_DH and
HSAMI_BRAND_SKU_DH

The Product Dimension has two hierarchies, CMDYGRP and BRAND:
ALL
CMDYGRP
COMMODITY
SKU
BRAND
SKU

So now both hierarchies are constructed in one dimension over
one logical dimension table, with multiple phyiscal source tables.

4) Save changes to rpd
5) Test in Answers by creating report with Brand and Product columns
6) Navigation/Drilldown from brand and product should work fine.