Previous row value (NOT MEASURE) in vertical fact architecture

Question

Hi OBIEE experts,

I've been looking for my answers for a while but no luck so far :/ .

We have vertical fact architecture in organisation with 11.1.1.7 version.

One of the requirements is to have current and previous (month or quarter) attribute in one line ( not measure ! )

So far my approach was :

a) select data for current and previous month and then use filter function , but ...  - FILTER function requires a measure, not attribute

b) use AGO function - don't like it, but if this is the case , I could finally  use it, but ....- AGO function also requires a measure, not attribute

b) select data for one month + union all select data for previous month , but then I have a data like below

Month               Attr

SEP - 2015      Plain

DEC - 2015      Custom

How can I create 3rd column with previous value, like below ?

Month               Attr          Attr_prev

SEP - 2015      Plain         null

DEC - 2015      Custom     Plain

Is it possible without changing RPD ?

Really appreciate your help !

Thanks

Bart

Thomas Dodds · Answer

Use of information drives your data model design ... perhaps you need a data structure that provides for the use of your information.  The data base can do much better with your data than the BI server which is better than the Presentation server - you want your BI system to perform (IOW: you want to to serve up the answers to business questions in efficient ways).

Simon.D · Answer

I would try the lag function. You would be executing the LAG on the time dimension which should have prior month data for all months regardless of your fact table.

BIGolicz · Answer

Hi Thomas,

This could be an idea as well, however tables store data ONLY for the months, where the value exists.

So for example, if the is no value in NOV, there is no record for this month as well.

So we may have situation, where lag ( one month) will find previous record, but not necessary previous month. ( for DEC it will return OCT if there is no record for NOV  ).

Hope we are on the same page.

Thanks

Bart