BW offers a substantial array of features in its so-called “presentation layer” so you can create versatile, intelligent, and graphically impressive Web and Business Explorer (BEx) front-end tools. Sometimes, however, your reports need more than what the BEx Analyzer alone can provide. A simple remedy to the dilemma is solution workbooks, which combine BEx queries with Microsoft Excel worksheets for business intelligence reporting.

The solution workbook uses BEx Analyzer queries linked via Visual Basic functions to an Excel worksheet used as a front sheet. Its simple design and construction enable the BW developer and more technically able end users to link multiple BEx queries and other sources of data within Excel for BW reporting.

I will help you decide whether BW solution workbooks are right for you and your end users. Then, I’ll walk you through the development process for solution workbooks.

Note that while developing solution workbooks is not complicated, it requires basic knowledge of Visual Basic. In addition, you need to know basic Excel functionality such as using logic to add formulas to a workbook.

Are They Right for You?

Solution workbooks are a great way of generating reports that don’t adhere to the master data and metadata stored in BW. They also provide for reporting via the BEx Analzyer that combines data from multiple ad hoc queries from the same (or different) InfoProviders in a solution that end users can maintain themselves. Other examples of when BW solution workbooks fit the bill are when your ad hoc reporting requires combining BW reporting with off line or other non-BW data sources, or you need more advanced report formatting via Excel.

Consider who will be using the reports. The beauty of adding front ends to your BEx queries using this simple approach is that modifying workbooks is relatively easy. It’s also a process that your more savvy users can undertake with sufficient training.

Typically, solution workbooks are built and used by authors and analysts. This group performs more advanced analysis work using BEx as well as ad hoc explorations and report development. Initially, your development team will be responsible for gathering the data, ensuring that it is translated properly, and building the solution workbook. With training, however, you can pass this development back to the authors and analysts.

Other Reporting Options

I can’t detail the pros and cons of all the various reporting options in this article, but let’s look at some of the more popular ones relative to solution workbooks.

Introduced with the BW 3.1 Content Release, the Analysis Process Designer (APD) allows users to merge data sets and store the merged data in an ODS for subsequent reporting on an InfoSet. However, my experience tells me that these reports, if they don’t follow the BW structures, can change frequently. No such overhead is required in this scenario with the Excel solution, and users can maintain it.

BW 3.5 offers a new precalculated workbook option that should cut down on the overhead and improve performance, but that functionality will only be available in the most recent release of BW — not a solution if you’re not ready to upgrade.

Crystal Reports offers BW users a robust optional reporting tool and shares some of the features of solution workbooks, but this technology requires upfront configuration not needed for solution workbooks. While it offers similar query merging capabilities, it costs extra after your first 10 reports. In addition, Crystal Reports follows the structures held in BW and does not support spot picking specific nodes from a hierarchy, selecting across levels, etc., which you can do with solution workbooks.

Web reports are faster than the Excel-based solution I’m proposing and do not require client- installed software like solution workbooks. However, they often are not as straightforward as solution workbooks. Also, Web-based reports do not offer self-sustainable solutions for users because they require a developer’s input. Web reports also struggle in much the same manner as Crystal Reports to produce reports that do not conform to BW structures.

Required Planning

Once you’ve decided that solutions workbooks are right for you, a certain amount of planning is essential regardless of the flexibility they provide. A clearly defined set of reporting requirements is important, but not necessary. However, failure to spend sufficient time analyzing your business needs can lead to inefficiencies in the workbook design and result in increased maintenance overhead. You run the risk of system performance degradation due to excessive BEx query runtimes as well as failing to create the desired self-sufficient user-supportable model.

I suggest that you create a matrix that details all your requirements, report by report. Some areas of consideration that should be in your matrix are:

• Reporting fields
• Hierarchy view vs. structure attributes
• Time dimensions
• Other non-BW sources of data
• Look and feel of report format

Next, determine the sources of data you’ll require, including which InfoCube and query are needed. This allows you to spot any overlap and gain efficiencies by identifying where the solution workbook presentation pages share data that can be derived from shared queries — eliminating duplication.

InfoProvider Selection

In most cases, you’re not required to select an InfoProvider as a data source for your solution workbooks. If faced with a number of InfoProviders as potential data sources, however, you will have to choose. You would be wise to ask a few standard questions before making your pick.

Look at the volume of data stored in an InfoProvider before you select it and ask if the data is available from one with fewer records. If you are tapping only 5 percent of the data stored in an InfoProvider, you may want to choose a smaller one.

You also need to determine that the data is refreshed frequently enough to satisfy the demands of the reporting. In addition, check to see if any feedback or negotiations are required from other business units and make sure the requirements of your service level agreements (SLAs) are met. SLAs may be in place that determine the number of data refreshes performed during a day, week, month, and modifying the frequency or timing of these refreshes may affect established SLAs.

Also, remember to check that your report performance is optimized. If the report is based on a summarized view of the InfoProvider data, for example, make sure the InfoProvider has aggregates that will be hit by the report. See if other performance-improving features are available such as aggregates on hierarchies, which I’ll detail further next.

Best Queries

Poor query management can substantially hamper the performance of your BW system, and solution workbooks offer you a way to improve this. Managing BEx queries from within solution workbooks puts control of the query engine room in your hands. Adding, removing, and creating views are all actions that the user can take within the solution workbook to create the necessary data source.

Using queries that include unnecessary hierarchies, key figures, etc. can result in solution workbooks that take longer than necessary to refresh. Worse still, this situation can lead to a number of users running poorly designed workbooks in parallel, which can degrade performance. Adding more solution workbooks exacerbates the condition. The unnecessary use of a hierarchy within a solution workbook in isolation may not have much impact on system performance. A suite of solution workbooks, however, all running with the same problem hierarchies may throttle down performance.

The best queries are simply constructed but flexible enough to get the job done. There is no hard and fast rule for query selection, but I can offer a couple tips. Take out any unnecessary key regardless of whether it is displayed because each will be calculated at runtime. When the list of items is relatively small, avoid using exclusions and make “includes” rather than “excludes.” Also, avoid using hierarchies where possible. Hierarchy queries are slower to refresh than queries containing the hierarchy as attributes.

When determining which queries to use, it is important to consider who is responsible for managing the queries. This includes both the ongoing maintenance and any further development. Queries can be used throughout the business by any number of teams, so it is important to identify up front where conflicting requests might affect the query used in the solution workbook. I’ll discuss this in more detail later.

Preparing the Code

As I noted earlier, my approach to building a solution workbook requires a working knowledge of the standard Excel functions in Table 1. You also need to use the additional non-standard function (MATCHMULTILINE), which is available for download at this link.

MATCHMULTILINE returns the absolute column reference matched for two ranges within a workbook. This typically means matching column headers in the front sheet to the column headers in the query range.

Creating Your Solution Workbook

First, copy and paste the MATCHMULTILINE code at the BW Expert Web site into the SAPBEX module using the Visual Basic editor (Figure 1). Next, using the BEx Analyzer, insert the BEx queries you selected as your data sources. Leave the first column free and place one query per worksheet into cell B1, which is the cell in the first row of the second column. Then refresh, filter, and drill down or across to create the necessary view for the workbook’s front sheet to source its data.

Figure 1

Add the custom MATCHMULTILINE code to the SAPBEX module click here to view a larger version of this image

Tip!
If you need to remove a query from a workbook during the build phase or at any point when the workbook is live, make sure to use the Remove Query option in the BW toolbar. Simply deleting the worksheet does not remove the query from the workbook!

With both the code and BEx queries in your solution workbook, you can build the frontend. Use the INDEX function to find the intersection of a column and row reference from a data range. The data cell can move both longitudinally and laterally as a result of the query refresh, and locating the intersecting column and row reference with the INDEX function overcomes this problem. The column reference is the key figures or characteristics you drilled across in the BEx query; and the row reference is the characteristic (Figure 2).

Figure 2

BEx query drill-down reference points click here to view a larger version of this image

Use the CONCATENATE function, which joins several text strings into one string, to bring together each of the keys on the adjacent row from the query result range so the front sheet can derive the row reference. Add two ranges to your BEx query worksheet. The first contains the reference column added in the previous step, which is displayed as DATANAME in Figure 3. The second range represents the entire query result range. The workbook front-sheet code uses this range to locate the intersection of the row reference range DATANAME and the column reference found via the function MATCHMULTILINE. For this example, I’ve called it DATA (Figure 4).

Figure 3

Range DATANAME click here to view a larger version of this image

Figure 4

Range DATA click here to view a larger version of this image

Insert Formulas

The front sheet contains three formulas to reference the rows and columns of the query and uses these intersections to bring back the data from the ranges I mentioned earlier. The row reference formula copies the key based on the concatenation of the query drill-down keys to the first column of the front sheet and provides the row reference. The second formula determines the column reference using the MATCHMULTILINE function to match the column in the query with the header in the front sheet. The third formula indexes the row and column references and searches the range DATA to locate the cell containing the relevant data.

Let’s look a little more closely at each of these three formulas and see how they are used in an example solution workbook that spot picks and reports specific levels or areas within a business structure. This process calls for locating a key based on the concatenation of the drill-down keys from the query and copying it to the first column of the front sheet. The first formula locates the row reference, which is added to discover the row reference/number. The logic for this formula is:

=IF(ISNA(MATCH(A10,INDIRECT($B10&”Name”),0)),0,(MATCH(A10,INDIRECT ($B10&”Name”),0))).

In this example, cell A10 is the concatenated key taken from the BEx query and cell B10 is the range. DATA becomes DATANAME via formula. The second formula is:

=MATCHMULTILINE(Queryworksheet!30:32,’FrontSheet’!J1:J3).

It finds the column reference using the MATCHMULTILINE function and identifies the column in the query that the header in the front sheet matches.

The last formula, which uses the indexing function, is:

=ROUND(INDEX(INDIRECT($B16),$C16,J$5),J$7)/J$6*J$8

It indexes the results of the other two formulas and searches the range DATA to locate the cell containing your data.

Having connected the front sheet to the BEx query via the steps outlined above, you can now bring the front end of the solution workbook to life. It may contain one or more BEx queries brought together in the presentation front end (Figure 5), or you may want to add links to other non-BW data sources, insert graphs, text fields, etc.

Figure 5

A formatted front end with two BEx queries as the data source click here to view a larger version of this image

Simon Ferres

Simon Ferres is a manager on the BW team at Barclays Capital, London, where he has worked since 1999. He is jointly responsible for the ongoing development of the BW installation and providing the bank's BI solution for a number of core business areas including finance, human resources, sales, and investment banking. Prior to working at Barclays Capital, Simon worked as a graduate trainee at Gillette UK.

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