5 Replies Latest reply: Sep 17, 2009 4:26 PM by Raj Attili RSS

BPC FACT Tables

ankush barapatre
Currently Being Moderated

Dear all,

 

Please tell me what is the purpose of following BPC FACT tables in which data is stored. Also, how they are different from each other.

 

1) dbo.tblfactFinance

2) dbo.tblfac2Finance

3) dbo.tblWBFinance

 

PS: As an observation, dbo.fac2Finance was not containing any BUDGET category entries.

 

Regards,

Ankush

  • Re: BPC FACT Tables
    Andries Van den Berg
    Currently Being Moderated

    As per the tuning doc:

     

    WB u2013 real time data input (ROLAP partition)

    This is data that is the most current data sent to the system. Data sent by BPC for Excel data sends and Investigator browser data sends is placed in real-time storage.

    FAC2 u2013 short term and Data Manager imports (MOLAP partition)

    This is data that is not real-time data, but is also not in long-term storage yet. When you load data via Data Manager (automatic data load from external data sources), it loads the data to short-term storage so that the loaded data does not affect system performance. Only the cube partition associated with this table is processed, so the system is not taken offline.

    Fact u2013 long term history (MOLAP partition)

    This is the main data storage. All data eventually resides in long-term storage. Data that is not accessed very often remains in long-term storage so that the system maintains performance

    This structure allows SAP BPC to maintain the same performance over time even when there is a large increase in data volumes.

    Periodically clearing real-time data greatly optimizes the performance of the system and an u201COptimizationu201D process is required (this could be scheduled automatically based on given parameters like a numbers of records threshold).

     

    Lite Optimization:

    u2014

    Clears Real-time data storage (WRITEBACK) and moves it to short-term data storage (FAC2). This option doesnu2019t take the system offline, and can be scheduled during normal business activity.

     

    Incremental Optimization:

    u2014

    Clears both real-time and Short-term data storage (WB and FAC2) and moves both to Long-term data storage (FACT).

    u2014

    This option should be run when the system is offline, but it will not take the system offline so it should be run during off-peak periods of activity.

     

    Full Process Optimization:

    u2014

    Clears both real-time and short-term data storage and processes the dimensions.

    u2014

    This option takes the system offline and takes longer to run than the incremental optimization.

    u2014

    It is best run scheduled at down-time periods u2013 for example after a month-end close.

    The Compress Database option is available to rationalize the Fact Tables. u201CCompressu201D sums multiple entries for the same CurrentView into one entry so that data storage space is minimized. Compressed databases also process more quickly.

    • Re: BPC FACT Tables
      ankush barapatre
      Currently Being Moderated

      Thanks Gert Andries  !

       

      This has solved the problem....

       

       

      So that means we have to consider entires belonging to all these tables to compare with actuals....as all the entries of these tables are unique...Ain't it?

       

      Bye

      Ankush

      • Re: BPC FACT Tables
        Andries Van den Berg
        Currently Being Moderated

        Hi,

         

        Keep in mind that you have to sum all the tables and join them to get you final value, because that is what BPC does.  See the following what it does in the sql logic when querying the data:

         

        select ACCOUNT,CATEGORY,DATASRC,INTCO,OPERATIONS,TIMEID,SIGNEDDATA
        into #tblTempLogic_731746
        from tblFactFinance
        WHERE  
        insert into #tblTempLogic_731746 (ACCOUNT,CATEGORY,DATASRC,INTCO,OPERATIONS,TIMEID,SIGNEDDATA)
        select ACCOUNT,CATEGORY,DATASRC,INTCO,OPERATIONS,TIMEID,SIGNEDDATA
        from tblFactWBFinance
        WHERE 
        and SOURCE = 0
        insert into #tblTempLogic_731746 (ACCOUNT,CATEGORY,DATASRC,INTCO,OPERATIONS,TIMEID,SIGNEDDATA)
        select ACCOUNT,CATEGORY,DATASRC,INTCO,OPERATIONS,TIMEID,SIGNEDDATA
        from tblFAC2Finance
        WHERE 
        select tmpTable.ACCOUNT,tmpTable.CATEGORY,tmpTable.DATASRC,tmpTable.INTCO,tmpTable.OPERATIONS,tmpTable.TIMEID,sum(SIGNEDDATA) as SIGNEDDATA
        from #tblTempLogic_731746 as tmpTable
        group by tmpTable.ACCOUNT,tmpTable.CATEGORY,tmpTable.DATASRC,tmpTable.INTCO,tmpTable.OPERATIONS,tmpTable.TIMEID

         

        Edited by: Gert Andries van den Berg on Aug 20, 2009 2:59 PM

  • Re: BPC FACT Tables
    J W
    Currently Being Moderated

    I run my BPC report and notice that some data is missing which can find in the fact table, but not in fac2 or WB table, looks like bpc did not join the data from fact, how can we fix this? I guess this because of the fulll optimization ,When we do full process optimization, the system will be take offline, we will not get accurate report duing this time, but how it going to be back to online again? is there anyway to control it?

     

    Thanks,

    • Re: BPC FACT Tables
      Raj Attili
      Currently Being Moderated

      You don't have to do anything to bring it back online. Once the full-optimize is complete, the application should be online. Depending on the amount of data in the back end tables, the downtime could be a blip.

Actions