Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How one Fact attributes will populate correct result with another fact measure
HI all,
I have below requirement for a report to create in OBI,
There is one dimension (Time dim) and 3 different fact table.
the report requirement is to pull year and month column from time dim and quantity measure from other 3 fact table.
Time dim is common dimension for all the fact.
report:
year Month factt1.Quantity1 fact2.Quantity2 fact3.Quantity3
2017 Jun 20
2017 July 21
2017 Aug 40
2018 Jun 31 50
2018 July 30 21 21
Now , we need to include one more column that is under 1st fact table,
when i am pulling the column in the report the the other quantity measure column which is from other fact table became null,
year Month fact1.run_error factt1.Quantity1 fact2.Quantity2 fact3.Quantity3
2017 Jun 05/28-06/03 20
2017 July 07/2-07/09 21
2018 Jun 05/28-06/03 31
2018 July 07/2-07/09 30
please suggest me how i should design my model in RPD to build this report .
Thanks ,
saurav
Answers
-
Hi,
First you need to fix a problem: your fact tables are for facts, not attributes.
I'm quite sure "run_error" is not a fact but an attribute, so model it as it's supposed to be, as a dimension and not a fact table column.
Once you did that set the content level correctly and your analysis (in OBIEE Answers it's called analysis, a report is a BI Publisher report) will works fine.
Your time dimension playing the role of conformed dimension between the 3 fact tables, your "run error" attribute will be available only for rows with a value coming from "fact1.quantiy1" and will be null for others fact tables rows as it's a non-conformed dimension, but the content level settings will tell that to OBIEE, so it will also retrieve info from other fact tables just by setting this "run_error" column to null.
But first is really to have a proper models: facts tables are only for facts, nothing else. Model it as a degenerated dimension.
0 -
+1 to Gianni. Model things correctly. Attributes in facts are only there for RPD-level calculations and NEVER to be exposed, let alone used in analyses!
0 -
Call me a pedant, but can we make that "Attributes in physical facts are only there for RPD-level calculations and NEVER to be exposed except via logical Dimensions" - nothing will out perform a single table that is used as a star schema....
0 -
Robert Angel wrote: nothing will out perform a single table that is used as a star schema....
That statement is quite touchy. Personally a properly modeled dimension table with all descriptive attributes will win over repeating all those attributes over and over and over and over needlessly in "the one table".
Faster. Less storage space used up etc etc
0 -
Agreed on the storage, and agreed you would not want to try it on a massive star schema, but denormalisation is a good strategy to improve performance, joins do slow performance.
0 -
Thanks,
But yes we kept few dimension attributes as well as measure attributes in a single fact table , earlier the intention was to use the table alone but as we got new requirement where i need to include other fact table as well and we don't want to do any ETL changes .
Although we i have figured out the solution .
1. created a logical table "Dim-fact1"2. pulled "run_error" only into the new logical table from "fact1".
3. create logical join between "Dim-fact1" and "fact1"
4. create a level based hierarchy of "Dim - fact1"
5. set "fact1" content to detail for "Dim - fact1"
6. set "fact2" content to Total for "Dim - fact1"
7. set "fact2.Quantity2" levels on Total for "Dim - fact1"
8. set "fact3" content to Total for "Dim - fact1"
9. set "fact3.Quantity3" levels on Total for "Dim - fact1"
10. Pulled run_error into presentation and create ad-hoc record and report is working fine .
Thanks you quick response.
Please let me know if you feel any correction required in this solution, we were using BIP report for the report earlier but due to performance issue we are moving it to OBI again
0 -
Your solution is just what I wrote ... Never mentioned any ETL, OBIEE is about modelling and all the things I talked about where in BMM layer: pure modelling things.
PS: as it isn't Publisher the name is "analysis", so call it analysis or people will keep thinking you talk about a BI Publisher report (OBIEE Answer = analysis, BI Publisher = report)
0 -
Thanks Gianni . yes the pointed " Model it as a degenerated dimension." helped to figured out the solution
0
