Data warehouses rely on source systems and have their own embedded dependencies. Therefore, a delay in any part of the extraction, transformation, and loading (ETL) process can affect the availability of data. To mitigate this risk, most SAP NetWeaver BI implementers leave an ample buffer between the expected completion of data loads and the agreed-upon Service Level Agreement (SLA) with the user community. (You can download an illustration of this time buffer when you have a variable completion window.)

If data volumes are light, or the ETL process completes without delays, you might have plenty of time to perform incremental maintenance and performance tuning. Unfortunately, such situations are unpredictable. Often you must manually evaluate whether to schedule and perform maintenance.

Using the Decision Between Multiple Alternatives process type, which is new in SAP NetWeaver BI 7.0, I’ll show you how to create a dynamic process chain to maximize sporadic windows in your loading process. A simple example of this would be to include InfoCube compression after loading data. With Decision Between Multiple Alternatives, you can make sure that these performance-tuning tasks can scale according to the time allotted and do not jeopardize SLAs. By incorporating this process type in the method I describe, you can extract the most value from your batch windows.

Let’s assume you have an existing process to load sales orders daily into your SAP NetWeaver BI system and your agreed-upon SLA with your users is that you can report on new data at 8:00 am. When this load actually completes may depend on external factors such as volume of sales orders related to seasonality, day of the week, promotional periods, or how master data loads perform that same day. After sufficient observation, you notice that this load completes anywhere from 4:00 am to 7:30 am on any given day.

With this variability, it does not make sense to schedule compressions or other incremental maintenance at the end of the process chain. Instead, you can use Decision Between Multiple Alternatives to determine whether or not certain activities should take place.

Create a New Process Chain

Let’s start by creating a new process chain to perform these dynamic compressions in transaction RSPC. In the new process chain, add a start process. Then connect that to Decision Between Multiple Alternatives, which you can find under General Services (Figure 1).

Figure 1

Choose the Decision Between Multiple Alternatives process type under General Services

Assign a technical name to the variant (ZDECISION10, in my example). This calls up the Process Maintenance screen where you can include decisions. In this screen, insert rows using the plus icon. In my example, I added three rows, for a total of five rows (Figure 2). The table represents the five possible outcomes when the process chain reaches this step, which depends on what time my sales order data loads complete

Figure 2

Create the different decisions to evaluate and the corresponding event options

• Data load completion before 4:00 am
• Data load completion from 4:00 am to 5:00 am
• Data load completion from 5:00 am to 6:00 am
• Data load completion from 6:00 am to 7:00 am
• Data load completion after 7:00 am

After adding these rows, enter a description for each and assign a different event option for each row using the drop- down menu. In this menu, you can also assign an error event. If you do so, it causes the entire process chain to error. In this example, you do not want your process chain to report an error because all possible system times have been accounted for, and there is no system time that should cause the chain to fail. Therefore, you do not need to assign the error event option.

You then need to define a Boolean formula for each outcome. A Boolean formula is a simple test that returns either a true or false answer. Click on the blank page formula icon in Figure 2 for Before 4am to enter the formula builder screen (Figure 3).

Figure 3

The formula builder screen

If you have used the transformation library, Figure 3 might look familiar. In this screen, you create a Boolean formula to evaluate if the system time is before 4:00 am. On the lower-left side of the screen, you can see system variables that you can use to create the Boolean formula. In this example, select SYST-TIMLO (Local Time). Double-click on the variable and it appears in the formula builder. Click on the < operand, and then click on Constant. In the pop-up screen that appears, enter 04:00:00, which corresponds to 4:00 am. When that’s complete, you should have a formula that looks like the one in Figure 4.

Figure 4

Boolean formula to evaluate if local time is before 4:00 am

Return to the variant maintenance screen in Figure 2 (transaction RSPC) and repeat the process for the other four options, changing the operands and times. After you create all five, save the variant and return to the Process Chain Designer screen.

Create Subsequent Options

The next task is to create the subsequent options that correspond to the Boolean statements previously created. Before moving forward, think about what you want to happen. When the data loads complete before 4:00 am, you have more time in the schedule to perform maintenance. When data loads complete after 7:00 am, you should not perform maintenance to avoid conflicts with the SLA time of 8:00 am. You’re creating dynamic compressions, so you want to compress the most amount of data if the local time is before 4:00 am, the least amount of data when the time is between 6:00 am to 7:00 am, and no action if it’s after 7:00 am.

To accomplish this, I added the Compression of the InfoCube process types compressing requests older than 30 days, 60 days, 120 days, and 180 days. This aligns with Before 4am, 4am - 5am, 5am - 6am, and 6am - 7am, respectively, as shown in Figure 5. This screen automatically appears when you connect a Decision Between Multiple Alternatives process to any other process in transaction RSPC.

Figure 5

Connect the event options to process chain steps

Furthermore, when you connect the compression processes to the Decision Between Multiple Alternatives process step, the system prompts you to choose which option each compression process corresponds to. For no compression, I added the process type EXOR under General Services, which executes without an action or error. You would use this option when your chain is approaching your SLA, or after 7:00 am, in this example. Your process chain is now complete and should resemble the image in Figure 6.

Figure 6

Completed dynamic compression process chain with decision and five possible actions

Call the Process Chain

The last step is to call this process chain at the end of your existing process chain that loads sales orders and execute (Figure 7). You can achieve this by adding the Local Process Chain process type under General Services (Figure 1) to the existing data load process chain. The result should resemble the image in Figure 7.

Figure 7

Connect an existing process chain to the dynamic compression process chain

When the system calls the dynamic compression chain after 7:00 am, the screen in Figure 8 appears. Notice that the system skipped all compressions and executed the EXOR process step. When the system calls the dynamic compression chain between 6:00 am. and 7:00 am, the system compresses the InfoCube for requests older than 180 days (Figure 9).

Figure 8

Process chain log when sales order loads complete after 7:00 am

Figure 9

Process chain log when sales order loads complete between 6:00 am and 7:00 am

The dynamic compressions example I’ve used is one of many that you can create. You can also adjust the time windows and number of days for compression based on your specific process. Other steps that you could add to the example easily are database statistics, index generation, PSA deletion, and information broadcast.

You can also use other system variables, such as local date, to create a process chain that behaves differently during the week versus during the weekend. In Figures 10 and 11, I included the variant and formula to showcase this capability. You might want to consider using this if you have special master data or transaction data loads that behave differently on the weekend than on weekdays. Note that the values of 6 and 7 correspond to Saturday and Sunday of the DATE_WEEKDAY1 function.

Figure 10

Create decisions to determine weekday or weekend

Figure 11

Boolean formula to determine if local date is a weekend