Problem with the way data is stored in tables...

building semantic layer
1

…in the database, the data is stored as points in time…

for example our table which contains Price Changes:

date price

JAN 2009 7.30
JUN 2009 7.50
SEP 2009 8.10
OCT 2009 8.20

When I create a report in Webi which shows the evolution of price changes in 2009, I only retrieve data for January, June, September and October.

But the users want to see all values from JAN - OCT

So I need:

date price

JAN 2009 7.30
FEB 2009 7.30
MAR 2009 7.30
APR 2009 7.30
MAY 2009 7.30
JUN 2009 7.50
JUL 2009 7.50
AUG 2009 7.50
SEP 2009 8.10
OCT 2009 8.20

=> what is the solution? should this be solved in the universe or can I handle it in the report?

MichM3 (BOB member since 2005-06-07)

2

By far the simplest solution is to add a “calendar” or “numbers” table to your universe. This is simply a table that contains a list of dates, or a list of sequential numbers. You can then do an outer join against that table to generate the missing months. Do a Google search for “calendar table” and your particular database for specific advice, as well as some options that other people have used.

If you happen to have a table already in your universe that is guaranteed to have at least one record (with a date field) for each month, you can also create a derived table against that to simulate a calendar table. Given that you only need month values, and not each day, that is viable.

Lugh (BOB member since 2009-07-16)

3

thanks Lugh, but with your solution I guess indeed all moths will appear in the report… but the Prices will remain empty for those months and will only be filled in for jan, jun, sep and oct.

MichM3 (BOB member since 2005-06-07)

4

best done in SQL I think,

this works in SQL Server


create view vfact as 
(
select 200901 as period, 7.3 as val union all
select 200906 as period, 7.5 as val union all
select 200909 as period, 8.1 as val union all
select 200910 as period, 8.2 as val
)
go

create view vcalendar as 
(
select 200901 as period union all
select 200902 as period union all
select 200903 as period union all
select 200904 as period union all
select 200905 as period union all
select 200906 as period union all
select 200907 as period union all
select 200908 as period union all
select 200909 as period union all
select 200910 as period union all
select 200911 as period union all
select 200912 as period
)
go

select 
   vcalendar.period,
   MainFact.period,
   MainFact.val,
   (
   select 
      Fact2.val
   from 
      vFact Fact2
   where
      Fact2.period =
         (
         select 
            max(Fact1.period)
         from
            vFact Fact1
         where 
            vcalendar.period > = Fact1.period
         )
   ) val_calc
from
   vCalendar
      left outer join vFact MainFact
         on vcalendar.period = MainFact.period


drop view vfact
go


drop view vcalendar
go

200901	200901	7.3	7.3
200902	NULL	NULL	7.3
200903	NULL	NULL	7.3
200904	NULL	NULL	7.3
200905	NULL	NULL	7.3
200906	200906	7.5	7.5
200907	NULL	NULL	7.5
200908	NULL	NULL	7.5
200909	200909	8.1	8.1
200910	200910	8.2	8.2
200911	NULL	NULL	8.2
200912	NULL	NULL	8.2

Ottoman :uk: (BOB member since 2002-10-04)

5

so you think it’s best to define a view via SQL and load that view into BO universe in order to achieve this…

no BO functionality available for issues like this… as far as I’m aware, it looks like a problem that others should have faced already :crazy_face:

MichM3 (BOB member since 2005-06-07)

6

You may be able to do something in BO through linking dataproviders but it would be only usable in that report.

I find it is better for complex logic to build it in SQL & put it in the universe.

Ottoman :uk: (BOB member since 2002-10-04)

7

Oh, sure. You’d need to add an extra bit of SQL to fill that in. Or use the Previous() function on the report.

The SQL might look something like:


SELECT cal.DateName, MyTable.Price
FROM MyTable
INNER JOIN
  (SELECT c.DateName, MAX(m.MyDate) as LastDate
   FROM MyCalendar c
   INNER JOIN MyTable m
   ON m.MyDate < c.DateName
   ) cal
ON cal.LastDate = MyTable.MyDate

The variable might look something like:


If(IsNull([Price]);Previous([FillPrice]);[Price])

where FillPrice is the name of the variable.

Lugh (BOB member since 2009-07-16)

8

ok I see 8) … no problem for a developer loke me to do so, but a user just wants to drag and drop an object without having to write a formula.

so I’ll handle it in the database 8) …thnx

MichM3 (BOB member since 2005-06-07)