Many organizations that use Business Objects as their front end encounter implementation issues and stumbling points when combining Business Objects with BW. The issues may include misinterpreted results in Business Objects because of different data storage formats, synchronizing object transport to quality and production environments, and maintaining security in Business Objects.

Advantages of combining Business Objects and BW include the unique object relationships that you can define at the universe level (InfoObject to InfoObject, InfoObject to InfoProvider, InfoProvider to InfoProvider). See Figure 1 for a depiction of how Business Objects connects BW objects. In addition, Business Objects and BW offer sophisticated security by using combinations of table synonyms and user profile content stored in special security tables. Business Objects also provides data analysis and reporting capabilities such as slicing and dicing, reporting sections, prompts, list of values, sorting, and ranking.

Figure 1

How a Business Objects universe connects BW objects

I will discuss how to use Business Objects as a front-end solution for SAP BW, the advantages of this approach, and potential implementation dilemmas for developers and functional consultants. I will give you six tips about how to overcome these challenges. I have tested this approach with SAP BW 3.1 and Business Objects 6.5.

1. Solve Transport Problems

In many cases, Business Objects universes group objects that BW does not connect (for example, an ODS object and an InfoCube, or a set of characteristics and an InfoCube). This makes transporting objects in BW challenging, because when you transport an InfoCube, the system does not prompt the developer for objects that Business Objects links but BW does not. Therefore, all the objects required for reporting do not arrive in production. If you do not track all objects in the Business Objects universe, you won’t know what is missing and you must audit the whole subject area for missing objects.

To solve this problem, developers should keep an inventory of objects (or groups of objects) that each universe and subject area uses. You can use a Microsoft Excel inventory tracking form like the one in Figure 2.

Figure 2

Inventory tracking form

2. Define Security at the Universe Level

Business Objects allows you to define a flexible security policy based on filters and database table synonyms. A synonym is another name for a table that you can define with a SQL Navigator tool, for example. If you define the table by a synonym in the universe, then the system generates all queries with the synonym name rather than the table name. This allows administrators to grant and revoke access to certain database tables (BW objects) for particular user groups. You can assign a synonym to each database table in the universe. Depending on the type of user in the universe definition (a technical user or an NT user ID that you define in the database connection parameters of the universe), you can permit any or all users to access tables. Only users with corresponding synonyms listed in their profile can access tables (Figure 3).

Figure 3

Universe A contains a technical user who defines one security profile for all users. Universe B allows different users and groups of users to access different tables, depending on their synonym definition

Another way to restrict access to tables and records for all universe users is to use filters in a universe definition. For instance, you can set up a filter for cost centers (tables BI0SCOSTCENTER and BI0PCOSTCENTER), choosing a list (range) of cost centers that users can access via the universe. In this case, a SQL statement restricts access to the cost centers listed in the filter for every query that the Business Objects front end executes containing cost center names or attributes.

3. Deal with Invalid Date Values

Business Objects offers date selection capabilities that require you to convert original text date values into their date format. In some cases, SAP R/3’s configuration allows invalid date entries. For example, you may enter a blank date as the invoice posting date. This creates a problem when you need to convert values in Business Objects. I suggest that you modify the extractor from R/3 to ensure that R/3 transfers proper dates to BW. You could convert all invalid documents to a certain date, 01/01/1999 for example. Alternatively, you could use an invalid date’s fiscal period to generate a somewhat accurate document date value that corresponds to the first day of the posting period.

The code in Figure 4 verifies if the year on the document date is invalid. If so, it assigns a new value to the document date based on the transaction’s fiscal period. Apply this code directly in the 0FI_GL_4 extractor in R/3 using transaction SE37 for function module EXIT_SAPLRSAP_001. Add the code to include ZXRSAU01 for the respective DataSource to ensure that BW receives corrected date values.

DATA dd like L_S_FIGL4_BIW-BLDAT.
DATA ddper like L_S_FIGL4_BIW-BLDAT.
    dd = L_S_FIGL4_BIW-BLDAT.
    IF dd < '1985' OR dd >'2030'.
       dd = L_S_FIGL4_BIW-FISCPER.
       ddper = dd+5.
       dd+4 = ddper.
       dd+6 = '01'.
       L_S_FIGL4_BIW-BLDAT = dd.
    ENDIF.

4. Resolve Currency Format Problems

It may surprise you that SAP stores certain currency amounts in a distorted way, meaning that the database level does not store the exact correct values. SAP designed its software to accommodate large amounts of foreign currencies, such as Turkish lira or Belgian francs. Front ends such as BEx interpret those amounts properly and display correct values. Some internal BW reports display correct (original) values, while others display distorted values that the system divides by a factor of 10 or 100. The Business Objects front end displays the exact amounts that the database stores, which may be values divided by a factor of 10, depending on the number of decimal points set for the currency.

Some extractors distort the amounts — for example, purchase order (PO) value amounts in the 2LIS_02_ITM extractor (or custom extractor ZPO_PROFORMA). Apply the code in Figure 5 directly in the 2LIS_02_ITM extractor in R/3 using transaction SE37 for function module EXIT_SAPLRSAP_001. Add the code to include ZXRSAU01 for the respective DataSource to make sure that BW receives correct amounts.

n_dec = 2.
      nprice = L_S_ZPO_PROFORMA_BIW-NETPR.
      select single CURRDEC
        into n_dec
        from TCURX
        where CURRKEY = L_S_ZPO_PROFORMA_BIW-WAERS.
      if n_dec = 0.
        nprice = nprice * 100.
      else.
        if n_dec = 1.
          nprice = nprice * 10.
        else.
          if n_dec = 3.
            nprice = nprice / 10.
            else.
          endif.
        endif.
      endif.
      L_S_ZPO_PROFORMA_BIW-NETPR = nprice.

5. Configure Aggregates to Optimize Performance

BW uses aggregates to store the most frequently accessed data from an InfoCube. Tables store aggregated data with the same structure as fact tables and BW rolls up aggregates after each data upload.

To improve reporting performance, you can use BW-generated aggregate-aware key figures (measures) in the Business Objects front end. Whenever users execute a report for such key figures, Business Objects generates a SQL statement to either access a fact table or an aggregate table to fulfill the request, depending on whether the aggregate contains the necessary level of detail.

To make aggregates work properly in Business Objects, you must add aggregate tables to the universe and create a separate context for each aggregate. Each context must combine all the tables that the aggregate uses. When properly configured, Business Objects uses the most appropriate aggregate when generating an optimal SQL statement for a query.

6. Implement BW Hierarchies in Business Objects

Many companies use work breakdown structure (WBS) elements in Project System (PS) to define a project structure and report on project costs according to this structure. BW uses WBS hierarchies to report on project structure and drill down to analyze and report. In many cases, you may want to have multiple hierarchies on top of the same InfoObject. For example, you could analyze a company’s org structure from a corporate point of view or from a local organization’s point of view. You may have this requirement when both corporate headquarters and a local management structure use the same reporting system. The example below shows how you can use Business Objects to directly access a WBS element hierarchy that is time and version independent.

You must use the same data objects in BW and Business Objects to ensure your data is consistent. You can easily add some objects as tables to Business Objects universes and use them in Business Objects reports. However, you cannot directly use some objects like hierarchies in Business Objects Designer because the BW hierarchy tables (BI0Hx, BICHx) store data in a normalized format (one record per hierarchy node), as shown in Figure 6.

Figure 6

A BW hierarchy table content (BI0Hx, BICHx) that stores linked records (normalized format)

Universes must explicitly mention hierarchy levels. You cannot allocate fields directly in the normalized table in Business Objects Designer to appropriate hierarchy level objects because that does not clearly show which record belongs to which hierarchy level.

To access a WBS element hierarchy in Business Objects Designer, you must perform a hierarchy table transformation. You have to create a denormalized structure where one record stores all hierarchy levels separately for each node. The best way to implement this is to create this structure at the time of execution. This way, you do not have to worry about launching special scripts for transforming objects especially for Business Objects. Instead, as soon as you load the hierarchies into BW, Business Objects reports can directly access them. Figure 7 shows the structure that you can directly use in Business Objects because it explicitly identifies values for hierarchy nodes at each level.

Figure 7

Transformed hierarchy table content that you can access in the Business Objects universe

Figure 8 is an example of a custom view that I created for WBS elements on top of the hierarchy table BI0HWBS_ELEMT. This view transforms data from internal BW table format into a denormalized format directly accessible in Business Objects. The code in Figure 8 uses three top levels of the hierarchy in the WBS element structure. You must implement it at the database level using a tool such as SQL Navigator. You may access the view in Business Objects Designer like a relational database table.

(SELECT
/* 1-2-3 */
BI0HWBS_ELEMT3.NODENAME,
BI0HWBS_ELEMT3.tlevel,
BI0HWBS_ELEMT1.NODENAME,
  BI0HWBS_ELEMT2.NODENAME,
  BI0HWBS_ELEMT3.NODENAME,
    '','',''
FROM
  BI0HWBS_ELEMT  BI0HWBS_ELEMT1,
  BI0HWBS_ELEMT  BI0HWBS_ELEMT2,
  BI0HWBS_ELEMT  BI0HWBS_ELEMT3
where
 (  ( BI0HWBS_ELEMT3.TLEVEL='03'  )
  AND  ( BI0HWBS_ELEMT2.TLEVEL='02'  )
  AND  ( BI0HWBS_ELEMT1.TLEVEL='01'  )
  AND  ( BI0HWBS_ELEMT1.NODEID=BI0HWBS_ELEMT2.PARENTID  )
  AND  ( BI0HWBS_ELEMT3.PARENTID=BI0HWBS_ELEMT2.NODEID  ))
)
union
(SELECT
/* 1-2 */
BI0HWBS_ELEMT2.NODENAME,
BI0HWBS_ELEMT2.tlevel,
  BI0HWBS_ELEMT1.NODENAME,
  BI0HWBS_ELEMT2.NODENAME,'','','',''
FROM
  BI0HWBS_ELEMT  BI0HWBS_ELEMT1,
  BI0HWBS_ELEMT  BI0HWBS_ELEMT2
where
( BI0HWBS_ELEMT2.TLEVEL='02'  )
  AND  ( BI0HWBS_ELEMT1.TLEVEL='01'  )
  AND  ( BI0HWBS_ELEMT1.NODEID=BI0HWBS_ELEMT2.PARENTID  )
)

In each case, you must adjust the view using proper hierarchy table names (or synonyms). Also, depending on how many hierarchy levels you require for end-user reporting, adjust the view by adding or deleting values in the SELECT clause and adding or deleting SELECT FROM WHERE clauses using the UNION keyword for each hierarchy level. For time-dependent hierarchies, adjust the view so that each line in the result table contains a node’s validity date range.

Sergei Peleshuk

Sergei Peleshuk has more than 15 years of experience implementing BI technologies for global clients in retail, distribution, fast-moving consumer goods (FMCG), oil, and gas industries. He has helped clients to design robust BI reporting and planning capabilities, leading them through all project phases: from analysis of requirements to building BI roadmaps, technical architecture, and efficient BI teams. Sergei is an expert in modern BI tools and technologies available on the market, including SAP Business Warehouse (SAP BW), SAP HANA, BusinessObjects, and SAP Lumira. Sergei maintains a business intelligence portal at www.biportal.org.

Sergei will be presenting at the upcoming SAPinsider HANA 2017 conference, June 14-16, 2017, in Amsterdam. For information on the event, click here.

You may contact the author at peleshuk@biportal.org.

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