How one Fact attributes will populate correct result with another fact measure

Saurav Singh chandel

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 

Gianni Ceresa

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.

[Deleted User]

+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!

Robert Angel

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....

[Deleted User]

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

Robert Angel

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.

Saurav Singh chandel

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

Gianni Ceresa

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)

Saurav Singh chandel

Thanks Gianni . yes the pointed " Model it as a degenerated dimension." helped to figured out the solution