Clarify Insert FAC2 Table and Optimize

[Former Member]

Hi,

Do I need to run Optimize in order to see rows I've added to the tblFAC2Finance table in a report? It seems like the rows will not appear in a report until I run Optimize lite. Once I run Optmize lite once, any changes I make to the tblFAC2Finance table after will appear without running Optimize again.

I also noticed that running the Clear package to remove the data I added, adds a reversing entry to the FAC2 table and displays unexpected values in the report. Again running an incremental optimize resolves the issue.

Can anyone explain the relationship between FAC2 and Optimize beyond Lite copies from WB to FAC2, Incremental moves to Fact and Full moves to Fact and Processes.

Thanks,

Joe

[sorin_radulescu · Accepted solution]

Hi,

First you have to be aware about structure of BPC cubes:

Each cube has 3 partitions:

1. fact - MOLAP

2. fac2 - MOLAP

3. WB - ROLAP

When you insert records into WB table because WB is ROLAP partitions you will see the impact of that insert into cube in Real Time.

If you insert records into any of MOLAP partitions without processin the partition you are not able to see these records into cube.

I think now you have a clear picture about BPC cube and you undertsood diference between MOLAP and ROLAP partitions.

Lite Optimize is necessary just to keep under contrl the number of records from WB table.

For SSAS if a Rolap Partitions has more than 100 000 records retrieve data from that cube it will be very slow if in the same time users are doing insert into WB Table.

So Lite optimize is schedule usually every 15 minutes when number of records is over 20 000.

That's means every 15 minutes this dtsx package check if WB has 20 000 records.

If yes then is running this process

If not then is not doing anything.

LITE Optimize process

It is doing the follow steps:

1. Copy records from wb to fac2 and marlk the records from wb move into fac2

2. Create a temporary partitions and start to process this partition just for these records move from wb table

3. When it is finishing the process of partition then the system is doing in transaction the follow:

- merge partition fac2 with temp partition

- delete the records marked from wb

I hope this it will help for begining.

I suggest to follow one of BPC training available because there for sure it is presented all information

about Optimize Lite, Incremental and Full.

Edited by: Sorin Radulescu on Apr 8, 2010 9:02 AM

[Former Member · Accepted solution]

Thanks Sorin it clears things up quite a bit. Where do I find the BPC training you mentioned? Is this an online resource?

[sorin_radulescu]

Any Admin or technical BPC training will cover this part regarding Optimization and Structure of cubes.

You can find the trainings available into SAP Training site.

Kind Regards

Sorin Radulescu