# OBIEE - Densification / Dimensions Preservation Possibilities

### Table of Contents

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

## 2 - Articles Related

## 3 - 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

## 4 - Tips

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