BPC FACT Tables

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member]

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,

[Former Member]

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.