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

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.

And it’s also the most problem that you have when you work with time series calculations as the lag/lead function, is that the data are not dense.

This article show the capability of the OBIEE logical sql to densify the data. To know more about the Obiee logical Sql, you can also read the good post of Venkatakrishnan.

The sample example come from this article : Dimensional Data Operation - Data Densification (sparse to dense data) - Preservation of the dimensions and the sql below is a translation in OBIEE logical sql of the densification solution with cross and right outer join.

As the gold rule is to embedded the difficulties in the repository, I highly recommend this solution instead : OBIEE - Densification / Dimensions Preservation Possibilities.

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 and the outer join

select D.saw_0, D.saw_1, D.saw_2, A.saw_3
FROM
(
SELECT
Products."Prod Name" saw_0,
Calendar."Calendar Year" saw_1,
Fiscal."Fiscal Week Number" saw_2,
"Sales Facts"."Amount Sold" saw_3
FROM SH
WHERE
(Calendar."Calendar Year" IN (2000, 2001))
AND (Products."Prod Name" = 'Bounce')
AND (Fiscal."Fiscal Week Number" BETWEEN 20 AND 30)
) A RIGHT OUTER JOIN
(
SELECT C.saw_0 saw_0, B.saw_0 saw_1, B.saw_1 saw_2 FROM
( Select Calendar."Calendar Year" saw_0, Fiscal."Fiscal Week Number" saw_1
FROM SH
WHERE Calendar."Calendar Year" IN (2000, 2001)
AND Fiscal."Fiscal Week Number" BETWEEN 20 AND 30 ) B,
( Select Products."Prod Name" saw_0 FROM SH WHERE Products."Prod Name" = 'Bounce' ) C
) D
ON ( A.saw_0 = D.saw_0 and A.saw_1 = D.saw_1 and A.saw_2 = D.saw_2 )
ORDER BY D.saw_0, D.saw_1, D.saw_2

The result in answer

The result in issue sql

In dashboard, Setting / Administration / Issue Sql