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 shows the capability of the OBIEE logical sql to densify the data.
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 with the fact-based fragmentation capabilities.
Articles Related
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
Support
nQSError: 27004 - Unresolved table
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P
State: HY000. Code: 2057371920. [NQODBC] [SQL_STATE: HY000]
[nQSError: 10058] A general error has occurred.
[nQSError: 27004] Unresolved table: "(SELECT MyTable.MyColumn saw_100, FILTER(". (HY000)
It seems than when using a SQL formatter a bad newline char (return, …) may come between the SELECT clause and the first column. By suppressing the newline, the problem seems to be resolved.