OBIEE - Densification / Dimensions Preservation Possibilities

Bi Server Architecture With Client


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.

Possibilities to densify the data

You have two possibilities in OBIEE to densify the data :

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


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


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

Obiee Dense Analytics By Members


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

Discover More
Obiee Logical Sql Densification
Blog - Obiee 10G - Densification with the OBIEE logical Sql (Sparse to dense data)

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. And it’s also the most problem that you have when you...
Bi Server Architecture With Client
OBIEE - Fact-based vertical partitioning/fragmentation

This technique allow you to : mix two facts table with two different grains (the level based partitioning) perform a densification (to preserve dimension value) : partition vertically your fact...
Obiee Logical Join Bmm
OBIEE - Full outer Join

To perform a , you have two ways : in the repository by using the fact vertical partitioning capabilities or with the Obiee logical sql To know what sort of join is a full outer join, follow this...
Obiee Period To Period Comparison Lag
OBIEE - Period to period comparison (YAGO, MAGO) with the analytical function Lag/Lead

lag and lead are analytical functions that can be used to get the value of a column in a previous/next row. More ... densewith sparse datadense use the Obiee Ago Function or densify your data ...
Bi Server Architecture With Client
OBIEE - Repository Design

... .. A consideration to take when designing a subject area is to pay attention at the final user. Do you design for a user:OBIEE Analytics/Reporting...

Share this page:
Follow us:
Task Runner