OBIEE - Densification / Dimensions Preservation Possibilities
About
You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them.
A MOLAP environment give you automatically this feature but in a ROLAP system, you have to perform a SQL densification.
The densification process also known as the preservation of the dimensions.
For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it.
Data densification is the process of converting sparse data into dense form.
An article was written especially and give the various methods to densify the data : Dimensional Data Operation - Data Densification (sparse to dense data) - Preservation of the dimensions
The most important method is to create a cross join between the dimension to have all possible combination and to outer join them with the fact data.
Articles Related
Possibilities to densify the data
You have two possibilities in OBIEE to densify the data :
- by designing the repository : OBIEE - Densification with the fact-based fragmentation capabilities
- or with the OBIEE logical Sql : OBIEE - Densification with logical Sql
As the gold rule is to embedded the difficulties in the repository, I highly recommend the first solution ( OBIEE - Densification with the fact-based fragmentation capabilities). The Obiee logical Sql solution is just there for information purpose
Tips
How to get the dimension values that are already dense in your fact table ?
When you want to search based on filter which dimension value will give you a dense result, you can use the following filter logical SQL snippet.
Suppose that you filter on a week and that you want to know all teams that have a minimum of one transaction by day. In this case, you will filter your answer with this snippet.
sum(max(1 by Date.Day, Organisation.Team) by Organisation.Team) = 7
where:
- the max aggregate will return 1 for each existing combination of Day (Date.Day) and Team (Organisation.Team)
- and the sum aggregate function will sum up the previous result (of the max)
- the equality means that you want all teams with transactions on 7 days.
Where:
- Team 1 shows dense data. You have data for all day
- and Team 2 doesn't.
Even if you add a measure of a fact table in your answer, OBIEE can choose to take an other logical fact table. Be sure then to make only one subject area by domain (fact table with the lowest grain) and to set the implicit fact column.