Perhaps you implemented your SAP NetWeaver BI data warehouse yourself and you’re pretty confident about what’s going on when you upload data to it. Or maybe your consulting partner provides clear, concise, and complete documentation. Even so, you may need more information to optimize your data warehouse. For example, SAP has developed recent enhancements to the upload process. Also, SAP NetWeaver BI contains features that have been available since the early versions of SAP BW but haven’t been fully communicated to the user base. Still other features came into your environment with various Service Packages.

Partitioning is one hidden feature that you can use to help speed up and simplify your data setup. When coupled with tuning performance, partitioning in a MultiProvider can boost performance when you’re running existing reports. I’ll introduce you to the two partitioning methods you can use with SAP NetWeaver BI and SAP BW: time-based partitioning and logical partitioning (i.e., partitioning InfoCubes logically by characteristics). For a refresher on partitioning basics, see the sidebar at the end of this article.

Time-Based Partitioning

To perform time-based partitioning, you need to be on an Oracle, IBM, or Microsoft database. You cannot use this feature when using other databases, such as the free MaxDB, because it is disabled. When the system condenses the data into the E table, it divides the data into separate partitions and only during query access does the query optimizer grab the necessary information from the database. The following steps show you how to create time-based partitioning. To partition a new InfoCube or an InfoCube that does not contain any data in SAP BW 3.x, right-click on the InfoCube and select Extras>Partitioning. In SAP NetWeaver BI 7.0, right-click on the InfoCube and follow menu path Extras>DB Performance>Partitioning.

Either menu path takes you to the Determine Partitioning Condition screen in Figure 1. Here you can define the partitioning characteristic. How you define it depends on whether you slice your data based on the calendar year (January to December) or on a fiscal year based on periods and the fiscal variant. For example, your company may run 13 periods. Your Controlling department can tell you which to use.

Figure 1

Selection criteria for time-based partitioning characteristics

In my example I selected Calendar Year/Month. This leads you to the screen in Figure 2 in which you set the values for the dates and maximum number of partitions. This important step splits your InfoCube data into multiple slices and creates multiple tables in the database in which to store the data. In my example I entered the time range 01.2000 to 01.2010, which forces the system to create a single table for each month of the selection range (in my example 12 months/year multiplied by 10 years = 120 partition tables). To avoid having many tiny partitions and to force several months into one partition (one year), I set the Max. no. partitions to 10. However, you can use any time frame you like.

Figure 2

Entry screen for time range settings and maximum partitions

Whatever partition range you enter, the system automatically adds one extra partition called MAXVALUE to store all data that comes later than the end of your definition. In my example, when I activate the InfoCube with the displayed settings, the system generates 11 tables within the database (i.e., maximum partition selection, plus one MAXVALUE partition).

Check the Partition

You can check the generation of individual partitions easily by using transaction DB02 and selecting Detail info. Enter the table prefix of your InfoCube name — in my example (/BIC/E<YOUR_CUBENAME>*) /BIC/ETESTTCT* — and execute the transaction. (Note that you use /BI0/ for SAP content InfoCubes or /BIC/ for your designed InfoCubes.) Figure 3 shows the results for my InfoCube.

Figure 3

The system generates 11 tables, including the MAXVALUE partition

I’m using partitioning for 10 years in my example. After January 2010 I must repartition the InfoCube to ensure future performance because the system will then store all data in the MAXVALUE partition, causing performance problems.

If you start today with data from 2008, all partitions for 2001 – 2007 are useless. When condensing InfoCube data (i.e., requests) the system automatically forces the data, depending on its date, into the correct tables. Therefore, the system stores data from a specific year, month, or period in its own partition and does not mix it with data from a previous or the next time segment. When running queries using time frame filters, you read only the necessary data and skip data outside the time selection.

Logical Partitioning

Logical partitioning is available for all databases (since SAP BW 3.0 Service Package 30) and is based on characteristics. Most companies started their SAP NetWeaver BI projects by implementing InfoCubes for specific processing areas, such as Sales and Distribution (SD) or Financial Accounting (FI). They used the SAP content InfoCubes to load SD data into a single InfoCube. Over the years, as sales grew and companies became more global or merged, the amount of SD data increased, resulting in reports that took increasingly longer to run.

The amount of data coming from huge subsidiaries or from other countries can delay reporting for small subsidiaries or local branches in your enterprise. Think of an InfoCube containing world-wide sales data on an item level for all countries. Now think of a query displaying only sales in Germany and the same query displaying sales for the US. Even small subsidiaries are influenced by the big subsidiaries in terms of query runtime. To resolve this delay, you can separate the single InfoCube into multiple country InfoCubes. You can divide data coming from SAP ERP or other systems into new InfoCubes by using update routines or other technology. You can then separate US data into the US InfoCube and The Netherlands data into the DE InfoCube.

When you create single reports on those basic InfoCubes, queries only read the individual InfoCube data (e.g., the US InfoCube). When you create queries in basic InfoCubes, you have to create multiple singles — one for each InfoCube, which is a real overhead of work. A better decision is to use a MultiProvider that contains all the single, basic InfoCubes. This shifts reporting from retrieving data from a single InfoCube to harvesting multiple InfoCubes to provide the optimal speed for each subsidiary or country.

However, a problem with the MultiProvider is that query runtime is based on the slowest involved InfoProvider. In my example, shown in Figure 4, the query accesses data from the US InfoCube and the DE InfoCube. In the case of the US InfoCube, a subquery runs for five seconds while a subquery for the DE InfoCube runs for one second. The total runtime is five seconds. This means that without further action, the US data influences the runtime for queries running through the DE filter.

Figure 4

Displaying query access by a MultiProvider

To avoid this, you can filter the technical InfoObject 0INFOPROV so that it becomes 0INFOPROV = ‘DE’. The filter restricts the query so that it only accesses the DE InfoProvider. The problem with this approach is that it requires significant query creation overhead because you have to define a query for each basic InfoCube.

Now I will disclose a little secret. You can cut down the query overhead as well as the overhead in creating filter routines in the update rules or transformations and still use the single InfoCube access with MultiProvider queries. You can do so by logically partitioning the basic InfoCubes. There are many ways to logically partition a data stream. An example method that shows you the basic concept follows.

First, divide the incoming data stream into two InfoCubes without a separation routine. This is a convenient way of overcoming the need for individual update rules or transformations when you want to populate the InfoCubes with only the desired part of the data (e.g., country object 0COUNTRY).

For example, to separate data for Germany (DE) and the United States (US), I divide an incoming data stream into two InfoCubes without using a programming routine to partition them. I only use functionality that is available in all SAP BW 3.x and SAP NetWeaver BI 7.0 systems.

I force the InfoCube to store only the data for the maintained InfoObject by changing its settings. For BW 3.x, right- click on the InfoCube and select Extras>Structure-Specific InfoObject Properties. In SAP NetWeaver BI 7.0, you access the settings from the InfoCube maintenance dialog. Right-click on Dimensions and then select Provider-Specific InfoObject Properties.

Next, indicate which InfoObject or combination you want to use to restrict the InfoCube. In Figure 5 I restrict the first InfoCube to 0COUNTRY = DE by entering the value in the Constant field. The second InfoCube contains 0COUNTRY = everything else. Then, activate the InfoCube, and you are ready to upload data. The system automatically skips all data in the data stream that comes from the US or anywhere else.

Figure 5

Setting the InfoObject 0COUNTRY to DE restricts the data in your InfoCube

Using logically partitioned InfoCubes can save you time during conceptual work or redesign because they improve warehouse management performance and InfoCube population. The indexes for the InfoCubes containing restricted data are perfect, meaning only the desired data resides inside the InfoCube. This also keeps the index small.

Boost the MultiProvider Performance

As I mentioned in my example in Figure 4, the runtime depends on the slowest participating member. Even after you logically partition your InfoCubes, the slowest default access (the US) still governs performance.

In Figure 4 the Basis InfoCube 2 and Basis InfoCube XX don’t know whether the system contains any data for the country selection = DE. The system still examines and reads all InfoCubes to look for the DE data, which slows the process down. Instead of using multiple InfoCubes for reporting, consider organizing them under a MultiProvider, which provides faster access to relevant data.

With the help of some hidden features, you can force the query optimizer to read only a single InfoProvider in a very dynamic way. Instead of running many subqueries on each basic InfoProvider, the system filters the query by using the InfoObject 0INFOPROV. This allows you to use a single query with variables to filter on countries to restrict query access to a single basic InfoCube without any manual work during query runtime.

First, implement SAP Note 911939, “Optimization hint for logical MultiProvider partitioning.” This note explains that you need to maintain the table RRKMULTIPROVHINT (MultiProvider Hint Control) by adding the name of your MultiProvider and the characteristic you used in logical partitioning (e.g., country). In my example, the MultiProvider (MULTIPROV) is ZSDM_C03 and the country (CHANM) is 0COUNTRY (Figure 6).

Figure 6

Maintain the RRKMULTIPROVHINT table

Next, optimize your MultiProvider query definition in BEx Query Designer. No matter which version you use (SAP BW 3.x or SAP NetWeaver BI 7.0) the only additional task for speeding up the performance of your query is to move the technical InfoObject 0INFOPROV to the filter. Drag this from the left side of the screen and drop it into the Filter area on the right side (Figure 7).

Figure 7

Drop 0INFOPROV into the Filter area to restrict 0COUNTRY to the US

In Figure 7 in the Rows section, you can see that I designed my query to filter only United States (US) data. From now on, instead of examining all InfoCubes, the query only accesses Basis InfoCube 1 (Figure 8). The query optimizer skips all the other InfoCubes.

Figure 8

MultiProvider query access when using logical partitioning and the filter value US

Check the Partition

You can verify the result of logical partitioning with a filter by using transaction RSRT and clicking on the Execute + Debug button. In the screen that appears select Multiprovider Explain (Figure 9). After you’ve executed the query, this displays the basic InfoProviders underneath the MultiProvider circled in Figure 10. The description of the basic InfoProviders that the MultiProvider query accessed tells me that the query execution ignored InfoCube ZSD_C_DE (my InfoCube containing German data). Data from this InfoCube did not affect execution time.

Figure 9

Display of debug mode options

Figure 10

Query MultiProvider with the filter for US data

Figure 10 shows that the filter equals US. If you filtered data for 0COUNTRY equals DE, the result in Figure 10 would be the opposite. I used a static filter in my example to explain the situation by figures. When you use query variables instead of the query result, access is identical. This behavior is desired when it comes to analysis with your query data because when you filter dynamically in BEx (Excel or Web-based) reports, only the requested InfoCube answers and transmits data.

Joerg Boeke

Joerg Boeke is an SAP NetWeaver BW solution architect and senior consultant working with BIAnalyst GmbH & Co.KG, with 19 years experience in SAP NetWeaver BW, having worked on it since SAP BW 1.2A. He offers significant expertise in the SAP NetWeaver BW reporting area, including design, data integration, data visualization, performance optimization, and the cleanup of existing SAP NetWeaver BW systems. He is the author of SAP BW 7.x Reporting - Visualize your data.

You may contact the author at Joerg.boeke@bianalyst.de.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.