10 Replies Latest reply on Jun 12, 2015 10:18 AM by asim cholas

# OBIEE Analysis to show Dimensional values without Fact data

Hi All -

In the graph and table levels we would like to show all the dimensional values even though there is no corresponding fact value.

we have followed the following blog but still the data is filtering out by showing only the values which are having values in the fact table.

http://bidirect.blogspot.in/2012/02/data-densification-in-obiee-1011g.html

Thanks!

• ###### 1. Re: OBIEE Analysis to show Dimensional values without Fact data

Is it a date? can you show us what exactly the problem with some screenshots?.. Is it line breaking in chart?

• ###### 2. Re: OBIEE Analysis to show Dimensional values without Fact data

Thanks for the reply,.  I am attaching the document for your review.

• ###### 3. Re: OBIEE Analysis to show Dimensional values without Fact data
 Dimension Table Fact Table Dimension table, is a csv file which we are manually maitaining to update the target information Fact Table has no measure columns defined, we have a Defect ID field and using count(Defect ID) in  the report level to get the count. Primary Key, using for Joining with Fact Table In the Fact table, the period is a calculation derieved field which is created based on the creation date of the defect. Product Period Concat Target Product Version Period Defect ID Concat ABC A+1 ABCA+1 5 ABC abc1.0 A+1 1234 ABCA+1 ABC A+2 ABCA+2 5 ABC abc1.0 A+2 4567 ABCA+2 ABC A+3 ABCA+3 5 ABC abc1.0 A+3 8910 ABCA+3 ABC A+4 ABCA+4 12 ABC abc1.0 A+3 1112 ABCA+3 ABC A+5 ABCA+5 20 DEF def2.0 A+1 1213 DEFA+1 ABC A+6 ABCA+6 22 DEF def2.0 A+1 1415 DEFA+1 ABC A+7 ABCA+7 22 ABC A+8 ABCA+8 22 ABC A+9 ABCA+9 22 ABC A+10 ABCA+10 22 ABC A+11 ABCA+11 22 *** Join Condition = the join condition between the fact and the dimension table is dimension.concat = fact.concat ABC A+12 ABCA+12 22 DEF A+1 DEFA+1 6 Now in the report level we are bringing in the Period from the dimension table, target from the dimension table and count of defect ID from the Fact table. DEF A+2 DEFA+2 6 But in the report normally it will print only A+1, A+2, A+3 periods for the version abc1.0. DEF A+3 DEFA+3 7 But the requirement is to show all the periods, i.e., A+1 - A+12 but the count(defect ID) should be 0 and in the Target we need to show the value from the dimension table. DEF A+4 DEFA+4 12 Even after following the blog, we are not able to show all the periods. DEF A+5 DEFA+5 21 DEF A+6 DEFA+6 24 DEF A+7 DEFA+7 25 DEF A+8 DEFA+8 22 DEF A+9 DEFA+9 22 DEF A+10 DEFA+10 22 DEF A+11 DEFA+11 22 DEF A+12 DEFA+12 22
• ###### 4. Re: OBIEE Analysis to show Dimensional values without Fact data

Did you try by checking the "Include Null Values" checkbox in the analysis properties?

It's supposed to do what you look for without doing strange things with your model (or create cartesian products).

• ###### 5. Re: OBIEE Analysis to show Dimensional values without Fact data

Hi ,

Thanks for the solution.

But we are using 11.1.1.6.8 version and this 'Include Null Values' option is not available.

And also we have the 11.1.1.7 version in our test environment, I have tested the scenario there.

We are able to print the null value related dimensional values in the table, and beside to that we have Threshold values,  but we would like to print the same in the graph, but not possible.

I also tried deriving the graph based on the pivot table, there also no luck.

Attaching the screenshot for your reference.

Thanks!

• ###### 6. Re: OBIEE Analysis to show Dimensional values without Fact data

Can you try the below formula in your column properties -> Data format ->Custom

#,##0;-#,##0;0

Thanks

Aj

• ###### 7. Re: OBIEE Analysis to show Dimensional values without Fact data

Able to print 0 in the null values for Rate, but Threshold is not populating on the graph.

I am using your custom format in 11.1.1.7.

The threshold value is coming from the dimension table.

Period, Threshold is coming from Dimension and Rate is populated based on Fact table.

Thanks,!

• ###### 8. Re: OBIEE Analysis to show Dimensional values without Fact data

For fixing chart, you need to cross join your date column with date dimension. This you can do in RPD and webcat. In RPD it is very easy, Just create an alias for your date dimension , Join it with the fact with join condition as 1=1. Use this date column in answers. you can do this in answers also. But it is not so easy. You need to modify your logical SQL something like Below.

Search for data densification for further info. There is a nice article in Gerard Nico's page.

Your current logical sql will be like below,

Select saw_0,saw_1,Saw_2 from your logicaltables where conditions

Modify it like below

Select saw_0,saw_1,Saw_2, saw_3

From

(

Select saw_0,saw_1,Saw_2 from your logicaltables where conditions

),

(Select Saw_3 from yourdatedimension where datefilter same as main query)

Thanks

Aj

• ###### 9. Re: OBIEE Analysis to show Dimensional values without Fact data

You mean to say I have to create the alias of the dimension table and join as complex join with the fact table?

We don't have a date dimension here.

Thanks!

• ###### 10. Re: OBIEE Analysis to show Dimensional values without Fact data

Not complex join.. You need a cross join in physical layer.. Check the below doc.

Densification in OBIEE 11g using a common Date Dimension - ClearPeaks Blog ClearPeaks Blog

Thanks

Aj