Showing Dimension Data in absence of fact values (Data Densification)!!

Need:- A lot of time there’s a need for showing the dimension records even if the value are not available in Fact table, we want to show those dimensions either null or with value as zero, indicating specifically those dimension values as being null values.

This article shows you how to model the repository in order to densify your data to achieve the above goal.

The typical situation of data is , we have the number of bridges(Fact Measure) based on the condition of bridges (Dimension) such as Excellent, Good, Fair so on and so forth, now in a given year there is one condition’s data missing in fact table due to which that condition is not getting showed in answers but user want those conditions to be showed with a value as zero.

To achieve the result set of 6 records, we have to perform 2 major tasks.

• Create an outer joins to current dimensions with current fact.
• Create a dummy Fact Table with no valid condition and to link it on the other dimensions.

To create the dummy Fact Cross joins:

• In the physical layer, select a folder, right-click and choose “New Physical Table”
• name it OBIEE_Dummy_tbl
• select the table type : select and enter this statement :

    SELECT ‘1′ as DUMMY_COL FROM dual

Then we must join this fact table with all the other dimensions:

• in the physical layer, select the OBIEE_DUMMY_TBL table and the dimensions
  
• right click, Physical Diagram / Selected Object Only
  
• click on the Complex join icon
  
• And create for each dimension a join with a condition 1 = 1.
  
• 
  

Then for the Business Model :

• Drag and drop the column “DUMMY_COL” from the physical layer to the logical fact table.
  
• Change the aggregation rule to “Max” (Min is also good). The result will return always yes and remark that the table OBIEE_DUMMY_TBL is added in the logical table source list.
  

Don’t forget to drag and drop the logical column “Dummy_Col” to the presentation layer.

In OBIEE Answers, now when you select only this measure column and that you choose some attributes of the dimension, Obiee will perform a cross-join.

In our case

It won’t be a good idea to show this dummy column on answer report as it will always return 1, which is meaningless for users, so to avoid confusion to users we need to hide the column, you can hide it in following manners.

• Going to column format and clicking Hide check box.

• Creation of a filter with the condition 

        DUMMY_COL is equal to / is in 1>

• Add this column as an implicit fact column of the presentation catalog. If you set an implicit fact column this column will be added to a query when it contains columns from two or more dimension tables and no measures. The column is not visible in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives.
   

This entry was posted on 31. August 2011 at 20:08 and is filed under OBIEE. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.