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.
===========-
[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:
===========
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
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.
===========
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
===========
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
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
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
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
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.
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.
5) Test in Answers by creating report with Brand and Product columns
6) Navigation/Drilldown from brand and product should work fine.
Posts
