Partitioning OLAP and OLTP

[Former Member]

Hi,

I'm in a BPC 7.5 MS 3 server environment: DB, MSAS and Web. All running Windows Server 2008 R2 / SQL Server 2008 R2.

We have over 700 million rows in our fact table. Performance was suffering so we addressed OLAP partitions. To reduce each partitions row count to around 20 million we have to slice data by year, dept and sub dept. This introduces 300 partitions.

After creating the 300 partitions processing the Retail application has run for over 72 hours. Prior to this change we had 15 partitions,sliced by dept. Processing would take 8 hours or less.

I have the:

1. Business Planning and Consolidation Performance Tuning for BPC 5.x document
2. SAP BPC 5.0 & 5.1 Performance and Reliability Tips document
3. How to Create Partitions in SSAS of SAP BusinessObjects Planning and Consolidation document from July 2009

I welcome any additional guidance and experience related to the number of partitions, processing performance and partitioning approached with larger sets of data?

Are 300 partitions, too many?

Can I partition the Fact table. Currently there is only 1 long term storage table: tblFact<AppSet>. Can I create tblFact<AppSet>2010, tblFact<AppSet>2011, etc. and manage optimizations and data loads against the appropriate fact table for the data?

[Former Member]

Hi Josef

It sounds like your data slice is to granular and because of all of the partition's it has slowed down your processing. My suggestion is the following:

Check and possibly amend the following OLAP properties:

• ThreadPool\Process\MaxThreads - Typically this property is set to 10 * number of CPU Cores. For example: If your OLAP server has 16 cores, then this property should be 160 (10 * 16)
• ThreadPool\Query\MaxThreads - This typically should be 2 * # of CPU + # of OLAP database with Proactive Caching

Additionally, log an OSS messages to see if FACT Table Partitioning is supported on version 7.5.

• Log an OSS message with SAP Support to find out if you can partition your FACT table in BPC version 7.5

Look at the below link and check your SQL Server to ensure that the following parameters have been set to ensure that you have effective parallel processing on the SQL Server side.

http://technet.microsoft.com/en-us/magazine/dd320292.aspx

There are also other strategies and things that can be done.

Let us know what happens and what is the feedback. 

Kind Regards

Daniel

[former_member186498]

Hi Josef,

the limit of partitiions is 1.000 so it doesn't seems to much but I don't have experience on very large tables.

I can add only some docs:

http://wiki.sdn.sap.com/wiki/display/CPM/Partitioning+SSAS+Cubes+on+BPC+for+Microsoft

http://msdn.microsoft.com/en-us/library/dd758814(v=SQL.100).aspx

http://www.mssqltips.com/sqlservertip/1914/sql-server-database-partitioning-myths-and-truths/

http://www.mssqltips.com/sqlservertip/2439/optimize-a-sql-server-analysis-services-measure-group-par...

and about SQL Server Best Practices:
http://technet.microsoft.com/en-us/sqlserver/bb671430
http://technet.microsoft.com/en-us/library/dd578580(v=SQL.100).aspx
http://support.microsoft.com/kb/2023571
http://technet.microsoft.com/en-us/library/cc966399.aspx

Kind regards

     Roberto