Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Previous row value (NOT MEASURE) in vertical fact architecture

Received Response
27
Views
5
Comments
BIGolicz
BIGolicz Rank 1 - Community Starter
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

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

Answers

  • BIGolicz
    BIGolicz Rank 1 - Community Starter

    Hi again ,

    One option is related to point C ), where we could use union all and then use evaluate function call LISTAGG and then substr text from the column.

      Month               Attr          listagg (ATTR)            substr(LISTAGG) - after coma

         SEP - 2015      Plain         PLAN,                      null

         DEC - 2015      Custom     CUSTOM, Plain         Plain

    Issue is that source is MS SQL server 2008 R2, and as far as I know - it doesn't have similar function.

    Correct me if I'm wrong

    Thanks

    Bart

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    What about using EVALUATE(LAG(%),....)

    SQL Server Supports that LAG() functionality  https://msdn.microsoft.com/en-us/library/hh231256.aspx

  • BIGolicz
    BIGolicz Rank 1 - Community Starter

    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

  • Simon.D
    Simon.D Rank 4 - Community Specialist

    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.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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).