Lite optimization in BPC MS

[former_member204026]

Introduction

The transaction data cube of standard BPC model (on the analysis services side) consists of three partitions: fact, fac2 and WB.  The fact and fac2 partitions are of MOLAP type (reads the records from the cube storage) and the WB table is of ROLAP type (reads the data from table directly).  Due to this performance of the data retrieval from WB partition is relatively slower than fac2 and fact partitions. Hence, it is recommended to keep the record count of WB table to less than 30,000 records.

The role of lite optimization is to move data from WB to fac2 table and process fac2 partition. The system remains online for the users during lite optimization process. Given that the processing of fac2 partition takes some time, one can’t help but wonder how the data doesn’t double up or go missing during the lite optimize process. Below is the detailed breakdown of steps executed on the SQL Server and analysis server side during lite optimization, the statements executed on the SQL server are in the form of DML and analysis services are in the form of XMLA script (XML for analysis services).

Steps

     Step 1: Copy/Update data from Write-Back table to Fac2 table. Below are the SQL script executed on SQL Server

• Update records of WB table to Source =1

        UPDATE TBLFACTWBPLANNING WITH (TABLOCKX) SET SOURCE=1 WHERE SOURCE=0

• Copy records from WB table to fac2 table by setting the Source=4. Below is the SQL script executed on SQL Server

INSERT INTO TBLFAC2PLANNING WITH (TABLOCKX) ([ACCOUNT],[AUDITTRAIL],[CATEGORY],[ENTITY],[FLOW],[RPTCURRENCY],[SIGNEDDATA],[SOURCE],[TIMEID])

SELECT [ACCOUNT],[AUDITTRAIL],[CATEGORY],[ENTITY],[FLOW],[RPTCURRENCY],[SIGNEDDATA],4 AS [SOURCE],[TIMEID] FROM TBLFACTWBPLANNING(NOLOCK) WHERE SOURCE=1

     Step 2: Begin SSAS transaction

1. Update the Fac2 partition to read fac2 table where Source =4
2. Process Fac2 table with ProcessAdd option. Below is the XMLA script executed on SSAS server

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

        <Bindings>

                <Binding>

                        <DatabaseID>BADSHELL</DatabaseID>

                        <CubeID>Planning</CubeID>

                        <MeasureGroupID>Planning</MeasureGroupID>

                        <PartitionID>Fac2Planning</PartitionID>

                        <Source d8p1:type="QueryBinding" xmlns:d8p1="http://www.w3.org/2001/XMLSchema-instance">

                                <DataSourceID>AppDef</DataSourceID>

                                <QueryDefinition>SELECT ACCOUNT

                                                       ,AUDITTRAIL

                                                       ,CATEGORY

                                                       ,ENTITY

                                                       ,FLOW

                                                       ,RPTCURRENCY

                                                       ,TIMEID

                                                       ,SIGNEDDATA

                                                       ,SOURCE

            FROM [DBO].[tblFac2PLANNING]

            WHERE [DBO].[tblFac2PLANNING].[SOURCE]=4

</QueryDefinition>

                        </Source>

                </Binding>

        </Bindings>

<Parallel>

                <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" …

                        <Object>

                                <DatabaseID>BADSHELL</DatabaseID>

                                <CubeID>Planning</CubeID>

                                <MeasureGroupID>Planning</MeasureGroupID>

                                <PartitionID>Fac2Planning</PartitionID>

                        </Object>

                        <Type>ProcessAdd</Type>

                </Process>

        </Parallel>

</Batch>

     Step 3: Update WB partition to read WB table where source=0.  Below is the XMLA script executed on SSAS server

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

        <Object>

                <DatabaseID>BADSHELL</DatabaseID>

                <CubeID>Planning</CubeID>

                <MeasureGroupID>Planning</MeasureGroupID>

                <PartitionID>WBPlanning</PartitionID>

        </Object>

        <ObjectDefinition>

                <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" …

                        <ID>WBPlanning</ID>

                        <Name>WBPlanning</Name>

                        <Source xsi:type="QueryBinding">

                                <DataSourceID>AppDef</DataSourceID>

                                <QueryDefinition>SELECT ACCOUNT

                                                       ,AUDITTRAIL

                                                       ,CATEGORY

                                                       ,ENTITY

                                                       ,FLOW

                                                       ,RPTCURRENCY

                                                       ,TIMEID

                                                       ,SIGNEDDATA

                                                       ,SOURCE

FROM [DBO].[tblFactWBPlanning] WHERE SOURCE = 0

</QueryDefinition>

                        </Source>

                        <AggregationPrefix>Planning_WBPlanning_</AggregationPrefix>

                        <StorageMode>Rolap</StorageMode>

                        <ProcessingMode>Regular</ProcessingMode>

                        <ProactiveCaching>

                                <SilenceInterval>-PT1S</SilenceInterval>

                                <Latency>PT0S</Latency>

                                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>

                                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>

                                <Enabled>true</Enabled>

                                <AggregationStorage>MolapOnly</AggregationStorage>

                                <Source xsi:type="ProactiveCachingTablesBinding">

                                        <NotificationTechnique>Server</NotificationTechnique>

                                        <TableNotifications>

                                                <TableNotification>

                                                        <DbTableName>tblFactWBPLANNING</DbTableName>

                                                        <DbSchemaName>dbo</DbSchemaName>

                                                </TableNotification>

                                        </TableNotifications>

                                </Source>

                        </ProactiveCaching>

                </Partition>

        </ObjectDefinition>

</Alter>

     Step 4: Commit SSAS transaction

     Step 5: Clean up Write-Back and update Fac2 Source value to 0

• Clean up WB value

    DELETE FROM TBLFACTWBPLANNING WITH (TABLOCKX) WHERE SOURCE=1

• Update Fac2 value (Update source= '0' where source= '4')

                    UPDATE TBLFAC2PLANNING WITH (TABLOCKX) SET SOURCE= 0 WHERE SOURCE=4

     Step 6: Update WB partition to read WB table. Below is the XMLA script executed on SSAS server

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

        <Object>

                <DatabaseID>BADSHELL</DatabaseID>

                <CubeID>Planning</CubeID>

                <MeasureGroupID>Planning</MeasureGroupID>

                <PartitionID>WBPlanning</PartitionID>

        </Object>

        <ObjectDefinition>

                <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" …

                        <ID>WBPlanning</ID>

                        <Name>WBPlanning</Name>

                        <Source xsi:type="TableBinding">

                                <DataSourceID>AppDef</DataSourceID>

                                <DbSchemaName>dbo</DbSchemaName>

                                <DbTableName>tblFactWBPlanning</DbTableName>

                        </Source>

                        <AggregationPrefix>Planning_WBPlanning_</AggregationPrefix>

                        <StorageMode>Rolap</StorageMode>

                        <ProcessingMode>Regular</ProcessingMode>

                        <ProactiveCaching>

                                <SilenceInterval>-PT1S</SilenceInterval>

                                <Latency>PT0S</Latency>

                                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>

                                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>

                                <Enabled>true</Enabled>

                                <AggregationStorage>MolapOnly</AggregationStorage>

                                <Source xsi:type="ProactiveCachingTablesBinding">

                                        <NotificationTechnique>Server</NotificationTechnique>

                                        <TableNotifications>

                                                <TableNotification>

                                                        <DbTableName>tblFactWBPLANNING</DbTableName>

                                                        <DbSchemaName>dbo</DbSchemaName>

                                                </TableNotification>

                                        </TableNotifications>

                                </Source>

                        </ProactiveCaching>

                </Partition>

        </ObjectDefinition>

</Alter>

Impact on Data Retrieval

Step 1: Has no effect for sending & retrieving data because this step is before processing Fac2 partition

Step 2 to Step 4: Process partition is not committed. During this step user will receive unprocessed Fac2 data and real one of WB.

Step 5: User will receive processed Fac2 data and filtered WB data (source=0). No effect for retrieving data because the WB's filter is still source=0 on this Step 6. All steps complete.

Conclusion 

After reviewing the details above, it is easy to see how the lite optimization manages to keep the system online (without doubling or missing records) while copying the records from WB to Fac2 table and processing Fac2 partition.

Additional Note

• The steps mentioned above are based on the observations on the SQL Server and Analysis services side
• Refer to SAP guides and notes for details of these steps
• Schedule the execution of lite optimization periodically to avoid performance issues

Reference Link

SAP Help

1621911 - BPC basic steps of Lite & Incremental & Full Optimization for Microsoft and Netweaver

1289330 - New process of Lite optimization