Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 214 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Fact Table Selection for Multiple stars in OBIEE
Hello Experts,
I have the below scenario in my project where we have multiple stars.
1. Implicit Fact column in defined on Fact 3
2. Logical levels are defined on all facts as total for non conformed dimensions and detail for conformed dimensions
Issue Description: I have created with Dim1, Fact1, Dim2. 2 separate queries are generated for this report
a) Dim1 and Fact1
b) Dim2 and Fact2
My requirement is that the second query should hit the Fact3 table and not Fact 2. The implicit fact column comes into play only when there is no fact column. As soon as I add the Fact 1 column and then Dim 2 in my report, it ignores the implicit fact column and takes Fact2.
I hope I was able to explain the scenario well.
Please suggest.
Answers
-
2634558 wrote: I have created with Dim1, Fact1, Dim2. 2 separate queries are generated for this report
a) Dim1 and Fact1b) Dim2 and Fact2My requirement is that the second query should hit the Fact3 table and not Fact 2.So? Both Fact 3 and Fact 2 can fulfill that request so the engine will take whatever seems more optimal. You're not providing the engine with any indication as to WHAT it should use WHY and WHEN. It's software, It does what it's instructed to do, not what one wishes it to do.
Add a dummy column on the fact table which forces the engine to choose the fact you want. Like "Fact 2"."Dummy Measure" = 1
0 -
Hello Christian,
Thank you for the quick response. I completely understand that OBIEE is taking the optimum path, but is it possible to make it choose the Implicit fact table column wherever possible in scenarios where non conformed dimensions are present along with other fact table columns as well.
Example : Dim1, Fact1, Dim2
In our Project, Fact 2 and Fact3, have data for different time periods, so when i apply filter on date in my report and it picks Fact 2, no results is displayed. We expected it to go to fact 3 where data is available for the date filter applied.
As developers, yes we can add dummy measure column in the reports ti pick the correct fact, but we have business users also who will use this subject area and they may not technically understand that they will have to add dummy measure column to be able to view the data.
Ideally, may be this scenario should not be there.please let me know if we can do something in RPD to ensure that always Fact 3 is picked.
We will also try to see if we can change the db design for fact tables to resolve this behavior
0 -
2634558 wrote:As developers, yes we can add dummy measure column in the reports ti pick the correct fact, but we have business users also who will use this subject area and they may not technically understand that they will have to add dummy measure column to be able to view the data. Ideally, may be this scenario should not be there.please let me know if we can do something in RPD to ensure that always Fact 3 is picked.
Well how are your facts built? Is it one logical fact with 3 facts LTSs? Or is it 3 logical facts?
0 -
Hello Chritstian,
Three logical facts are created in RPD. All facts are having different data like Employee Fact, Employee Absence Balance and Employee Payroll Fact. So all are actuall having different measures. So we have not clubbed them into a single logical fact table.
0 -
Hence the need for a distinguishing dummy column as you basically prevent any chance of the system being able to choose the correct path via normal LTS procedures
0 -
Ok..Thank you Christian
We will check if we can modify the DB tables
0 -
What are you talking about?
One logical fact table with 3 LTS. I never mentioned the DB layer a single time.
0 -
Hello Christian,
Even with Single Logical Fact table and 3 LTS. The query generated is still the same, when we dont add any measure from fact 3.
Report Criteria: Dim1, Fact1, Dim2
Actual Query:
a) Dim1 and Fact1
b) Dim2 and Fact2
Exoected Query by us:
a) Dim1 and Fact1
b) Dim2 and Fact3(Implicit Fact Column)
0 -
Hello There,
With Christian's solution, you can use Priority Groups to define the Fact that should be selected for the query.
You'll have Multiple LTSs mapped to conformed dimension columns.
By defining the Priority Groups, you can force OBIEE to select the LTS that you want to query on.
Thinking out aloud, this should work in your scenario.
Hope this helps.
0 -
Hello,
Thank you. This should work. I will try the priority option and will update here.
0