This content has been marked as final. Show 15 replies
Its depends s on the Fact1 and Fact2 granularity; and case by case. I'm assuming you are using both facts as LTS, in another case you may not have this issue.
Try this: Try to create dimension hierarchies for all dimensions and set them to both Facts with proper levels
see how it works.
If helps mark
Edited by: Srini VEERAVALLI on Apr 4, 2013 5:15 PM
Srini, logically both are different stars, per one person one row exist in fact1 table, 26 rows in Facts2 table. i have set the logical levels for all dimensions in both the facts.
there is no issue with RPD consistency, still getting error when i run report by selecting cilumns from all the dimension and two facts.
Thanks for your help.
Thank your for your replies,
currently i'm getting 26 rows from Fact2 table with D1 and DA, DB, DC, expecting same 26 rows when PULLING columns from with FACT1 and Fact2, D2, D3, D4, DA,DB, DC as well.
FACT1 - D1
FACT1 - D2
FACT1 - D3
select D1.*,D2.*,D3.*, DA.*,DB.*,DC.*, FACT1.*
getting below error, with any other combinations, apart from combination Fact2 table with D1 and DA, DB, DC
[nQSError: 43119] Query Failed: [nQSError: 14025] No fact table exists at the requested level of detail
Please suggest me the correct approach if my design is wrong.
atleast i'm expecting the report to work when i pull the columns from D1 and FACT1 and FACT2, since D1 is joined with FACT1 and FACT2 and the FACT2 has join with DA, DB, DC
like select D1.*, Fact1.*, Fact2*,DA.*, DB.*, DC.*
FACT1, FACT2, D1, DA, DB, DC
I assume you have created proper dimensional hierarchies for all the dimensions in your subject area. Also, ensure that all of them have a Total Level created.
Assuming the above, since D1 is a conformed dimension, if you create a report having columns from only D1,Fact1 and Fact2, the report should show you proper results.
For creating a report having columns from all tables, ensure the following:
1. For all measure columns from Fact1 to be included in the report, set them to be calculated at the Total Level of dimensions DA, DB and DC
2. For all measure columns from Fact2 to be included in the report, set them to be calculated at the Total Level of dimensions D2 and D3
The physical sql will fire separate queries for the 2 star schemas and then join them using the conformed dimension.
Hope this helps,
Hi Gaurav, i have followed the steps that you have provided, its really helpful but still getting the below error
[nQSError: 43119] Query Failed: [nQSError: 14020] None of the fact tables are compatible with the query request
Fact - HR - Operation (Workforce)_WPA.ASSIGNMENT_ID. (HY000)
are we missing any other steps. Thanks for your patience