If you are like the rest of us, however, your business has developed its own way of looking at its data. Like that famous line about old dogs and new tricks, convincing your users to change the way they manage their data is probably useless. Hence, you’re in a position in which your company has specific reporting buckets that do not naturally exist in SAP.

My team and I developed an architecture in BW 3.5 for creating and reporting on specific reporting buckets. It focuses specifically on the complexities involved in reporting on transitive attributes. See Figure 1 for an illustration of transitive attributes. Combined with the architecture we developed, SAP NetWeaver 2004s provides a new method of reporting based on transitive attributes.

Figure 1

InfoProvider 0123 contains a characteristic X, which has an attribute Y which in turn has an attribute Z

Reporting Hierarchy

At The Washington Post, BW provides data to lots of departments within the newspaper, primarily the advertising department and the accounting department. Both the advertising and accounting departments want to break out their reports by categories specific to the newspaper industry. For instance, the advertising department closely tracks the performance of the classified ads in the newspaper, such as “Car for Sale” or “Apartment for Rent.” It doesn’t want to look at classified on just a summary level. It needs the data broken out.

The advertising department wants to know how classified advertising performs on Sundays versus other days, because traditionally Sunday papers have the most advertising. It also wants to break out classified ads according to the publishing zone in which they appeared. For instance, did this ad run in every paper, or was it zoned to just run in a particular county or state? Additionally, the advertising department wants to look at different sub-categories within classified ads. Was the ad a “Car for Sale” ad, which is part of automotive? Or was it an “Apartment for Rent” ad, which belongs to the real estate category?

I’ve illustrated the reporting hierarchy in Figure 2. I refer to level one as the big bucket. Level two is the small bucket. Level three is called daily or Sunday, and level four is called full run/part run.

Figure 2

Four-level reporting hierarchy

I needed a BW infrastructure that could report data easily within these categories. To complicate matters, I needed to organize data from several disparate systems into these buckets. Advertising data comes from SAP R/3 Media/Advertising Management (M/AM). BW receives general ledger (G/L) revenue from an Oracle Financials system.

Users send budget data in Excel spreadsheets. To do year-over-year comparisons, the BW team compiles data from the legacy system that SAP replaced. Since all this data represents the same business, it has to have the same buckets.

I created a key to serve as a common denominator. The InfoObject is called ZKEY. Each value of ZKEY represents a reporting category. For example, ZKEY 1001 represents classified/real estate/ Sunday/full run. ZKEY 1002 represents classified/automotive/daily/part run. ZKEY is a meaningless identification number with the following master data attributes that represent our reporting categories: ZBIGBUCK (big bucket), ZSMBUCK (small bucket), ZDLYSUN (daily or Sunday), and ZFRPR (full run or part run). See Figure 3 for examples.

Figure 3

Sample values of ZKEY and its attributes

Unfortunately, each row of transactional data from the source system does not contain its reporting bucket. It contains InfoObjects that the BW team can map to its reporting bucket. For example, the team extracts data from the G/L system into BW. Each row contains a G/L account number and its associated revenue. Users provide a spreadsheet that lists all G/L account numbers and the reporting bucket to which they belong (Figure 4). I defined the InfoObject that stores the G/L account number to have an attribute of ZKEY.

Figure 4

Spreadsheet that maps G/L account numbers to ZKEY

Therefore, as the BW team loads G/L data, update rules look up the account number and determine the ZKEY to assign to each row. When BW users specify the requirements for the revenue report, they do not want the report to display the revenue associated with each G/L account number. That would not make for a user-friendly report, nor would displaying each value of ZKEY. Instead, the goal is to display the attribute of ZKEY, the big bucket, rather than ZKEY or the G/L number. In other words, the report should display an attribute of an attribute. To use an analogy from baseball, the BW team was on second base, and wanted to get home without showing third base (Figure 5).

Figure 5

The baseball analogy: Second base brings you to third base and third base brings you home. G/L accounts map to ZKEY and ZKEY maps to big bucket.

The BW users wanted the report to look like the one in Figure 6. This report shows revenue rolled up to the level of big bucket, rather than the level of the G/L account or the level of ZKEY. Using the terminology presented in Figure 1, this report queries InfoProvider 0123 and groups the key figures by attribute Z and omits attribute Y.

Figure 6

The report displays the key figures in the InfoProvider rolled up to the big bucket level (i.e., to the attribute of the attribute)

Reporting Requirements

To understand the architecture that the BW team created to achieve transitive attribute reporting, it helps to understand the requirements. Two metrics are very important in our reports. The first one is the one discussed above, revenue. Users want to know how much money the company makes in each category. The other metric is a measurement of advertising space. For instance, a “Help Wanted” ad may be 10 lines. The BW team converts these 10 lines into the number of square inches of space that the ad occupied in the newspaper, and reports those inches to users. Inch reporting is another way of quantifying the advertising business.

The BW team was charged with producing a report called the content report. The content report displays advertising inches and revenue organized by the big bucket and compares them to last year’s performance and the budget projections from the accounting department.

Figure 7 shows the BW 3.5 architecture. The paragraphs that follow explain how the architecture satisfied the reporting requirements for the content report. Let me explain the naming conventions. All objects that the BW team custom-developed start with a Z. If the object is an InfoCube, its name ends with a C. If it is an InfoSet, it ends with an S.

Figure 7

BW 3.5 architecture

Key Decision Points

Let’s first discuss how the BW team reported inches. The inches statistic comes from advertising orders, and advertising orders come from R/3.

The BW team needed to report inches broken out by the transitive attribute big bucket. No area of R/3 stores the big bucket, however. To derive the value of the big bucket, the team asked the users to identify three InfoObjects on each order to determine the ZKEY (remember that the big bucket is an attribute of ZKEY). The users created a spreadsheet of these mapping rules that listed every combination of the three InfoObjects and the corresponding ZKEY. The spreadsheet looked like Figure 8.

Figure 8

Spreadsheet that maps order attributes to ZKEY

The BW team’s first decision was how to store orders. Long before starting work on the content report, the team had decided to create inbound DataStore objects (formerly ODS objects) for every extractor coming from R/3 and all external data sources. Each inbound DataStore object was a pure load from a source system with minimal data manipulation or logic. The team created an InfoCube that the orders DataStore object would feed to facilitate reporting. Figure 7 shows the orders InfoCube, ZORDERSC.

After storing the orders, the next decision was where to store the ZKEY associated with each order. The mapping rules for ZKEY could change at any time, so the team needed an infrastructure with some flexibility. Reporting based on transitive attributes presented us with two main options.

Option one is to store ZKEY as a characteristic in the ZORDERSC InfoCube. If ZKEY were stored in the InfoCube, then the BW team would have to re-load the entire InfoCube if the mapping rules changed.

Option two is to create an auxiliary InfoCube for reporting. Because BW produced the content report weekly, this auxiliary InfoCube would extract the rows from ZORDERSC with activity dates in the reporting week. In the diagram of the architecture in Figure 7, the InfoCube called ZOAUXC is the auxiliary orders InfoCube. To realize option two, the BW team had to create auxiliary InfoCubes and store data redundantly. It also had to make sure that the process chains to load master data always ended by re-loading the auxiliary InfoCubes.

The update rules assign a ZKEY to each row during the load from ZORDERSC into ZOAUXC. This design eliminates having to re-load the orders InfoCube. If ZKEY changes, the BW team only has to re-load the auxiliary InfoCube.

To assign the ZKEY in the update rules to ZOAUXC, the BW team created an InfoObject to represent the mapping rules. It stored that InfoObject, rather than ZKEY itself, in ZORDERSC. The users mapped each combination of booking unit, price group, and daily/Sunday to a ZKEY (Figure 8), so the BW team created an InfoObject with these characteristics as compounded attributes. This InfoObject was called ZINCHMAP. ZINCHMAP has three compounded attributes: 0ME_BOOKUNT, 0PRICE_GRP, and ZDLYSUN. ZINCHMAP also has one stand-alone attribute, ZKEY. The value of ZINCHMAP itself is blank, because BW only uses its attributes to represent the mapping spreadsheet.

The BW team stored ZINCHMAP in ZORDERSC (Figure 9). It was easy to load ZKEY in ZOAUXC. The update rules into ZOAUXC loaded ZKEY as a master data attribute of ZINCHMAP.

Figure 9

The InfoObject ZINCHMAP represents the spreadsheet that maps order attributes to ZKEY

Why did I choose option two? It wasn’t because I liked it. It was because I disliked it less than the other option. One major problem with option two was that BW stores data redundantly. Option two also complicated the master data loads. Each time the team loaded master data, the last step had to be to re-load ZOAUXC. This was the only way to keep the orders associated with the correct mapping rules. The volume of data made option one impossible to manage. The advantage of option two was that it eliminated massive re-loading of data when mapping rules changed. See Figure 7 to review the placement of auxiliary InfoCubes in the BW 3.5 architecture.

Now BW stored orders with a ZKEY, but the BW team still could not create a query to report inches by the big bucket. The reports could display inches by ZKEY, but not inches via the transitive attribute big bucket. To achieve this, the team created an InfoSet of two InfoObjects called ZINCHS to join ZKEY and ZINCHMAP. ZINCHS linked our mapping rules to the big bucket and the other attributes of ZKEY. This meant that if a MultiProvider could merge the auxiliary orders InfoCube with the InfoSet, BW could report orders via the big bucket.

You may be wondering why I did not use an InfoSet to join the orders InfoCube with ZKEY. I could not do this because BW 3.5 only allows flat structures in InfoSets. No InfoCubes are allowed.

You may also wonder if I could have used an ABAP routine to eliminate ZOAUXC and use ABAP to stop loading ZORDERSC if ZKEY were blank. This solution would address the situation in which ZKEY were not mapped, but it would not address the situation in which ZKEY were mapped incorrectly and then changed to the correct value. This last situation does occur, especially when the system first goes live. Users are still learning the data, so they often change their minds about how to organize and categorize data.

I created a similar structure to the orders InfoCube, ZORDERSC, and the auxiliary InfoCube, ZOAUXC, for legacy data coming from the non-SAP system in place prior to R/3. I created an auxiliary InfoCube, an InfoObject to represent mapping rules, and an InfoSet to join the InfoObject to the ZKEY. Let me explain the object names. A legacy field called ZENDCODE could be mapped one-for-one to ZKEY. Hence, we created an InfoObject called ZENDMAP to represent the mapping from ZENDCODE to ZKEY. We then created the InfoSet ZENDS to join ZKEY with ZENDMAP.

In the case of G/L data, the team re- loaded the InfoCube each week because the volume of data was small (about 25,000 rows). Loading it takes under three minutes, so the team chose option one for the G/L; the update rules loaded ZKEY as a characteristic in the G/L InfoCube.

As I mentioned above, the users create budget data in spreadsheets, and they organize the spreadsheets by ZKEY. As a result, the BW team can load the budget data into a DataStore object, and the data is already organized into the reporting buckets. Because ZKEY is a part of this data, I did not need to create any auxiliary InfoCubes or InfoSets. In fact, queries ran directly against the DataStore object, rather than an InfoCube, because the volume of data was very small (only a couple hundred rows).

The final step was to merge all these objects together in a MultiProvider. I did so in Administrator Workbench (transaction RSA1) by right-clicking on the MultiProvider and choosing the Change option. Using the assignment screen in this Edit MultiProvider screen, I assigned every object in the MultiProvider to set the value of ZKEY. Because ZKEY existed in all of the InfoCubes, the DataStore object, and the InfoSets, the team could use its attributes to slice the data in the InfoCubes, the DataStore objects, and the InfoSets.

Architecture in SAP NetWeaver 2004s

SAP NetWeaver 2004s allows InfoCubes in InfoSets. Therefore, when The Washington Post upgraded to SAP NetWeaver 2004s, I could join InfoCubes with ZKEY in an InfoSet without physically storing ZKEY in the InfoCube. The SAP NetWeaver 2004s architecture is much simpler (Figure 10).

Figure 10

Architecture in SAP NetWeaver 2004s (the auxiliary InfoCubes have been removed and replaced with InfoSets)

I eliminated the two auxiliary InfoCubes and removed ZKEY from the G/L InfoCube. I then created an InfoSet called ZORDMAPS to join the orders InfoCube (ZORDERSC) with the ZINCHMAP InfoObject and the ZKEY InfoObject (Figure 11). This organized the orders data around ZKEY. Because ZKEY contained the big bucket as an attribute, the team could group key figures (e.g., inches) via the big bucket.

Figure 11

The ZORDMAPS InfoSet joins the orders InfoCube (ZORDERSC) with the ZINCHMAP InfoObject and the ZKEY InfoObject

I created an analogous structure for legacy data. As explained earlier, the legacy system had a field called ZENDCODE that the team mapped one-for-one to a ZKEY. So the team created an InfoObject called ZENDMAP to represent the map from ZENDCODE to ZKEY. Then, the team joined ZENDMAP, ZKEY, and the legacy InfoCube in an InfoSet.

The design was the same for G/L data. Three InfoSets joined transactional data with ZKEY and therefore with the big bucket. Since the budget DataStore object contained the ZKEY already, the team did not create an InfoSet for the budget data.

For reporting, the MultiProvider joined the three InfoSets with the DataStore object. Since ZKEY was in all four components of the MultiProvider, I had to assign ZKEY to each of the four components in the MultiProvider assignment screen. To do this, I used transaction RSA1, right-clicked on the MultiProvider, and chose the Change option. Once in the change screen, I assigned characteristics and key figures to an InfoProvider by right-clicking on each item. After performing this assignment, users could slice any of the data in the InfoCubes by any of the attributes of ZKEY.

One advantage of this approach was that it eliminated redundant InfoCubes and simplified master data loads. Perhaps the most important improvement was that it automatically joined transactional data to a reporting bucket, even if the mapping rules changed. The reporting bucket was not stored physically; rather, it was represented in a join. So the rules could change and the data would naturally fall into place.

The Result: Easier Support

In the end, either architecture would have met the requirements, but the BW team was interested in the solution that was easier to support because mapping rules might change. Suppose Sally in advertising emailed Joe about creating new booking units. Joe was supposed to map these new booking units to ZKEY and send the updated mapping to the BW team for uploading, but Joe was on vacation, and he never got Sally’s email. The next time the team loaded orders from R/3 into BW, these new booking units would not be mapped to anything. Therefore, the associated inches would not be categorized properly on the content report.

Then someone on the BW team would probably get a frantic phone call in the middle of the night saying the report had a large unassigned bucket. If I got the call, what would I have to do? With the BW 3.5 architecture, I would have to re-load the master data to update the mapping rules, re-load ZOAUCX, and re- generate the report. Who wants to do all of that while half-asleep? With a highly visible report used by senior management, the BW team did not want a process that could be so complex and time-consuming.

With the SAP NetWeaver 2004s architecture, all I would have to do is re-load the master data. The InfoSet takes care of joining the orders to their proper reporting bucket. And the team can sleep better at night knowing that the phone calls will come, but the solutions will be quick. Just re-load master data, and you can go back to bed.

Sarah-Jane O'Connell

Sarah-Jane O’Connell has been an analytics developer in data warehousing for five years. She is currently a senior developer at The Washington Post. Sarah-Jane graduated from Georgetown University with a degree in computer science and mathematics. She would like to thank JoAnn King who served as a technical consultant on this article.

You may contact the author at SarahJaneHoya@yahoo.com.

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