OBIEE - Densification with logical Sql


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.

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
       Sales s, 
       Times t, 
       Products p
       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
order by 
       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
 Products."Prod Name" saw_0, 
 Calendar."Calendar Year" saw_1, 
 Fiscal."Fiscal Week Number" saw_2, 
 "Sales Facts"."Amount Sold" saw_3
  (Calendar."Calendar Year" IN (2000, 2001)) 
  AND (Products."Prod Name" = 'Bounce') 
  AND (Fiscal."Fiscal Week Number" BETWEEN 20 AND 30) 
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 
   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


nQSError: 27004 - Unresolved table

Odbc driver returned an error (SQLExecDirectW).
Error Details
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.

Powered by ComboStrap