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 :
- how to model a cross join
Sparse data to densify
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.
The Cross Join between the dimensions
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 :
- in the physical layer, select a folder, right-click and choose “New Physical Table”
- name it FactCrossJoin
- select the table type : select and enter this statement :
select 'Yes' Preserve from dual
- in the column tab, create the column “Preserver” as varchar(10) as below
Then we must join this fact table with all the other dimensions :
- in the physical layer, select the FactCrossJoin 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 “Preserve” from the physical layer to the logical fact table.
- rename it to “Preserve Dimension”
- change the aggregation rule to “Max” (Min is also good). The result will return always Yes and remark that the table FactCrossJoin is added in the logical table source list.
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 :
- Prod Name
- Calendar Year
- Calendar Week Number
and make this selection :
- Prod Name = Bounce
- Calendar Year in 2001 and 2002
- Calendar Week Number between 20 and 30
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 outer join with the fact vertical partitioning capabilities
The fact vertical partitioning occurs when :
- you have two logical table source with the same level of content for the same logical table
- minimum one measure of each logical source fact table are included in the query
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
How to hide the preservation column ?
You have three possibilities :
- Application of the hidden column format as the system-wide default for these preservation measure
- or creation of a filter with the condition :
Preserve Dimension is equal to / is in Yes
- 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.