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.
The preservation of the dimensions is is also well known as a densification .
The densification of the data is performed in Sql in two steps :
More information can be found in this article : Dimensional Data Operation - Data Densification (sparse to dense data) - Preservation of the dimensions
This article show you how to model the repository in order to densify your data :
A typical situation in the sh schema with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001:
SELECT SUBSTR(p.Prod_Name,1,15) Product_Name,
t.Calendar_Year Year,
t.Calendar_Week_Number Week,
SUM(Amount_Sold) Sales
FROM
Sales s,
Times t,
Products p
WHERE
s.Time_id = t.Time_id AND
s.Prod_id = p.Prod_id AND
p.Prod_name IN ('Bounce') AND
t.Calendar_Year IN (2000,2001) AND
t.Calendar_Week_Number BETWEEN 20 AND 30
GROUP BY
p.Prod_Name,
t.Calendar_Year,
t.Calendar_Week_Number
order by
product_name,
year,
week asc
PRODUCT_NAME YEAR WEEK SALES
--------------- ---------- ---------- ----------
Bounce 2000 20 801
Bounce 2000 21 4062.24
Bounce 2000 22 2043.16
Bounce 2000 23 2731.14
Bounce 2000 24 4419.36
---- Miss one row for the week 25
---- Miss one row for the week 26
Bounce 2000 27 2297.29
Bounce 2000 28 1443.13
Bounce 2000 29 1927.38
Bounce 2000 30 1927.38
Bounce 2001 20 1483.3
Bounce 2001 21 4184.49
Bounce 2001 22 2609.19
Bounce 2001 23 1416.95
Bounce 2001 24 3149.62
Bounce 2001 25 2645.98
---- Miss one row for the week 26
Bounce 2001 27 2125.12
---- Miss one row for the week 28
Bounce 2001 29 2467.92
Bounce 2001 30 2620.17
In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001.
In Obiee which have a fact centric modelling, you must use a fact table to link two dimensions. Thus, a solution is to create a dummy fact table with no valid condition and to link it on the other dimensions.
To create the dummy Fact Cross join :
select 'Yes' Preserve from dual
Then we must join this fact table with all the other dimensions :
Then for the Business Model :
Don't forget to drag and drop the logical column “Preserve Dimension” 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, we will choose :
and make this selection :
Obiee fired the SQL below with no relation between the table and ask then a cross join to the database.
select T210.PROD_NAME as c1,
T268.CALENDAR_YEAR as c2,
T268.CALENDAR_WEEK_NUMBER as c3,
max(T4454.Preserve) as c4,
T210.PROD_ID as c5
from
SH.TIMES T268,
SH.PRODUCTS T210,
(select 'Yes' Preserve from dual) T4454
where ( T210.PROD_NAME = 'Bounce' and (T268.CALENDAR_YEAR in (2000, 2001)) and T268.CALENDAR_WEEK_NUMBER between 20 and 30 )
group by T210.PROD_ID, T210.PROD_NAME, T268.CALENDAR_WEEK_NUMBER, T268.CALENDAR_YEAR
order by c1, c2, c3, c5
The fact vertical partitioning occurs when :
Then the BI Server generates two queries for each logical table source and perform a full outer join between them.
In our situation, we have already two fact table :
And we need then just to add the amount measure of the sales tables in our previous report and a dense report appear.
The Sql here is splited in two parts and the Obi Server perform in its own memory the full outer join on the attributes of the dimension but you can push the complete query to the database: OBIEE - How to control the use of the WITH CLAUSE and of STITCH Join ?
select max(T4454.Preserve) as c1,
T210.PROD_NAME as c2,
T268.CALENDAR_YEAR as c3,
T268.CALENDAR_WEEK_NUMBER as c4,
T210.PROD_ID as c5
from
SH.TIMES T268,
SH.PRODUCTS T210,
(select 'Yes' Preserve from dual) T4454
where ( T210.PROD_NAME = 'Bounce' and (T268.CALENDAR_YEAR in (2000, 2001)) and T268.CALENDAR_WEEK_NUMBER between 20 and 30 )
group by T210.PROD_ID, T210.PROD_NAME, T268.CALENDAR_WEEK_NUMBER, T268.CALENDAR_YEAR
order by c5, c3, c4
select sum(T245.AMOUNT_SOLD) as c1,
T210.PROD_NAME as c2,
T268.CALENDAR_YEAR as c3,
T268.CALENDAR_WEEK_NUMBER as c4,
T210.PROD_ID as c5
from
SH.TIMES T268,
SH.PRODUCTS T210,
SH.SALES T245
where ( T210.PROD_ID = T245.PROD_ID and T210.PROD_NAME = 'Bounce' and T245.TIME_ID = T268.TIME_ID and
(T268.CALENDAR_YEAR in (2000, 2001)) and T268.CALENDAR_WEEK_NUMBER between 20 and 30 )
group by T210.PROD_ID, T210.PROD_NAME, T268.CALENDAR_WEEK_NUMBER, T268.CALENDAR_YEAR
order by c5, c3, c4
You have three possibilities :
Preserve Dimension is equal to / is in Yes