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.
PS: As an observation, dbo.fac2Finance was not containing any BUDGET category entries.
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).
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.
Clears both real-time and Short-term data storage (WB and FAC2) and moves both to Long-term data storage (FACT).
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:
Clears both real-time and short-term data storage and processes the dimensions.
This option takes the system offline and takes longer to run than the incremental optimization.
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.
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
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?