Drill Through from MOLAP to Relational

---

As part of WebI 2.7, released in the first half of 2002, Business Objects introduced a new component for multidimensional analysis: Universal Drill-Through Service (UDS). UDS allows WebI users to explore a MOLAP cube and drill through to details in relational tables. UDS is available for the following MOLAP servers: Essbase, DB2 OLAP, MS Analysis Services, and SAP/BW. This capability is available only for WebI users and not for full-client users who access MOLAP cubes. According to Business Objects, they are considering functionality for which there is no formal time frame.

Figure 11-2 provides a conceptual overview of how drill-through works. A WebI user can drill down and rotate dimensions within an OLAP cube. The OLAP cube contains details down to the month and state. If the user would like details by week and by city, then the user can drill through to the underlying star schema that was used to populate the OLAP cube or to other detailed fact tables that share dimensions with the MOLAP cubes. This concept is quite different from the MOLAP vendor approaches. With Microsoft Analysis Services, users can only drill down to the star schema that built the initial cube and are presented with a 'screen dump' of all the keys and columns for a particular cube intersection. Similarly, Hyperion's Integration Server also drills into a star schema used to populate the Essbase cube.

Figure 11-2: When a WebI user drills through to the detail fact table, the user selects a document; the intersection in the MOLAP cube is passed into the WHERE clause of the query.

Once a WebI user clicks drill-through, the user then selects a predefined target document. This target document contains result columns that a user wants to see upon drill-through. UDS passes the intersection information to the WebI target report in the form of a WHERE clause.

Components of Drill-Through

The following components enable drill-through to function:

UDS Designer In your Windows menu, this is called Map Developer, yet in the menu bar, it is called UDS Designer. This program allows you to define translations between a MOLAP cube and an existing universe, as shown in Figure 11-3. It is similar to Designer in that it is a full-client product.

Figure 11-3: The UDS Designer maps relationships between the MOLAP cube metadata and the universe classes and objects.

UDS Maps Translations between MOLAP cubes and BusinessObjects universes are called UDS Maps.

Originating Data Source This is the MOLAP cube from which users will want to drill through to details. UDS Designer uses metadata information from the MOLAP cube to provide a link to a BusinessObjects universe. In Essbase, the metadata is the outline; in Analysis Services, it is the cube structure.

Target Universe This is the BusinessObjects universe that contains the detailed fact tables to which users will drill through to get details from a summary cube. For MS Analysis Services, cubes must be populated from a star schema so that your Target universe is one that points to tables that were used to build the cube. For Essbase, the cube could have been populated from flat files and multiple relational sources. In this case, you need to build a relational data mart and a corresponding universe before proceeding. For SAP/BW, users may drill from Info Cubes into the operational data store. Otherwise, the target universe can be an existing universe that contains dimensions similar to the ones in the MOLAP cube. The important thing is to ensure that the summary numbers in the cube will correspond (and ideally reconcile with) detail numbers when users run a relational report via a universe.

Target Database The target database contains the relational tables that users will be drilling through to and is the same data source that is referenced by the target universe. The tables can contain more dimensions and details than the MOLAP cubes; however, the common dimensions must contain the same values (see Table 11-2 for more information). When the values are not exactly the same, they must be synchronized in the translation map. For example, if the cube refers to the month of July as Jul and the universe/target database refers to it as July, then you must create a member translation.

Table 11-1: OLAP Dimensions Map to BusinessObjects Classes

Essbase Dimension	BusinessObjects Class
Year	Time
Measures	Measures
Product	Product
Market	Geography Dimension
Scenario	NA
… attribute dimensions	NA

Table 11-2: Translation Types Define How Essbase Members Map to Universe Object Values

Essbase Member	BusinessObjects Object (data value)	Translation Type
Gen3, Year: Jul, Aug, Sep	Month (July, August, September)	Member
	Week (27, 28, 29)	NA
Gen3, Measures: Sales	Sales	NA
Gen3, Product: (100-10, 100-20, 100-30)	Prod Id (100-10,100-20,100-30)	Level
Gen2, Market (East, West…)	Region	Level
Gen3, Market (New York, Florida…)	State	Level
	City (Orlando, Miami)	NA

Target Document These are prebuilt WebI reports that users can select from when drilling through to details. In one sense, these can contain all potential columns in the fact table, or you as a designer can provide users with multiple reports and varying result columns. UDS adds a WHERE clause to the SQL based on the point in the MOLAP cube from which users request drill-through. As shown in Figure 11-3, the UDS Designer links the target document with the translation maps.

Caution 

Target documents cannot be full-client documents, or the user will receive an I/O Exception error when attempting drill-through.

WebI Drill-Through Service (DTS) Manager This is a new service that must be running in addition to WebI Manager. DTS handles the activity when a user drills from a MOLAP cube into a detail report. As you make changes to UDS maps, you must restart DTS for the changes to take effect. To enable drill-through, follow these steps:

1. Understand the data.

2. Build WebI documents.

3. Build UDS maps.

4. Add WebI documents to the map.

5. Restart DTS Manager.

6. Test the drill-through.

Understand the Data

In order to illustrate how UDS works, I will use the Hyperion Essbase Sample Basic application. This database contains revenue information for soft drink sales. A newly created MS Access database will be the Target database. As shown in Table 11-1, the two databases share four common dimensions: Year, Measures, Product, and Market.

As shown in Figure 11-1, a dimension can have multiple levels. The terminologies of MOLAP products and of BusinessObjects universes do not correspond at this point. Normally in BusinessObjects, you think of levels as going from the top down. So Year would be level 1. This is the opposite in Hyperion Essbase. Hyperion refers to level 0 as the lowest level of detail; in Figure 11-1, this would be Day. Hyperion refers to top down as generations. So Generation 1 is Year. Next, in BusinessObjects, you see only the actual data values in the columns when you run a query; otherwise, all you need to know is that the object Year will give you a list of years (2000, 2001, 2002, 2003). MOLAP products refer to these individual data values as members. It is at these two levels of details that you start to see differences in how the MOLAP cube and BusinessObjects universe relate, as shown in Figure 11-4.

Figure 11-4: The Essbase Outline and the BusinessObjects Classes and Objects have common elements that become the Translation Map.

Notice in the Essbase outline at the Year dimension, Generation 3, you see the names of the individual months as Jul, Aug, Sep. In the BusinessObjects universe, you see only the object Month. You do not see the individual data values. As long as the member names and the data values are exactly the same between the MOLAP cube and the relational data source, you can map Essbase's Year,Gen3 directly to the BusinessObjects Month using a level translation. A level translation, which is the default translation, means that the individual member names within a particular generation match exactly the data values for a particular object. When they don't match, you use a member translation. In Table 11-2, the BusinessObjects Month object actually spells out the names of the month. Therefore, you will eventually create a member translation.

Note 

You could create a new object called Month MMM using the SQL LEFT function to return the first three characters and ensure that your Essbase months match the months in the relational source. This is highly recommended, as maintaining UDS Maps is not automatic and can be cumbersome. The month member translation is purely for example purposes and is not a recommended approach.

Recall from Table 11-1 that you do not have any universe classes that correspond to the Scenario dimension and the attribute dimensions. While you, as the UDS designer, may not need to define a specific translation, users must understand the data they are looking at. In this example, the fact table contains only actual sales figures. Other scenarios (budget) and measures (Cost of Goods Sold) are not available in the fact table. You do not define a translation, because there is no corresponding object to pass a WHERE clause to. Likewise, the objects Week and City are lower levels of detail that exist within the Target Universe and Target Data Source but do not require translations because they do not exist in the Original MOLAP source.

Building WebI Documents

In looking at Table 11-2, you can guess that users might have two drill-through reporting needs: 1) sales by product, region, and week or 2) sales by product, city, and month. Week and city are your lower levels of detail that are not available in the MOLAP cube and that must come from the detailed fact table. In order to make these reports available to the UDS Designer, you must first create them in WebI and publish them to Corporate Documents (refer to Chapter 23 for additional instructions). These documents should not contain any additional conditions or prompts, as WebI will replace them with the dimension member names from the MOLAP cube when a user drills through.

1. Log into InfoView.

2. Select Create Documents.

3. Select the target universe, or the universe that contains the star or snowflake schemas tables to which users will drill through.

4. Select the desired Sections, Result Objects.

   

5. You do not need to run the query at this time, and you do not want to store the document in the repository with data. Click Save and Close.

6. From the top menu bar, click Publish.

7. In the Publish As Corporate Document page, shown next, enter a document name: Regional Sales by Week.

   

8. In the description box, you can enter help text; however, it is not displayed to the user during the drill-through process.

9. For drill-through purposes, it is not necessary to assign a category or keyword, as users will automatically be presented with a list of drill-through reports. However, if these reports are used outside of drill-through, you may wish to assign categories and keywords.

10. Select the user group(s) that accesses OLAP data sources and the target universe.

11. Set the Refresh option to Refreshed Manually.

12. Click Publish to store the new document as a Corporate Document in the Document domain of the repository.

Building the UDS Map

Building the UDS map has two main subprocesses: first, to define the MOLAP and Universe sources, and second, to create the translations.

Defining Source Information

1. From your desktop or the MS Windows Start menu, select Map Developer to start UDS Designer. If this option does not appear, you need to run the BusinessObjects Setup to install it as a separate component.

2. From the UDS Designer pull-down menu, select File | New. You will be prompted to provide OLAP Source Information.

   

3. Enter the name of the OLAP Server and use the drop-down box to select the Server Type (Essbase, DB2OLAP, MSOLAP, or SAPBW).

4. In the Username box, enter a valid username for the MOLAP cube.

5. In the Password box, enter a valid password for the MOLAP cube.

6. Click Connect so that the MOLAP server will generate a list of possible DataBases and Cubes. Use the drop-down box to select the desired sources-in this example, Sample Basic.

7. Click LoadCube for UDS Designer to read the MOLAP metadata and fill in the Originating Data Source Information.

8. You will immediately be prompted to log into the BusinessObjects repository so that UDS Designer can import the universe definitions. Enter your BusinessObjects username and password.

9. In the Universe Target Information dialog box, use the drop-down arrow to select the target universe. This is the universe that accesses the detailed data that will be used to generate drill-through reports.

   

10. Click Load Universe. At this point, UDS Designer loads only the classes and objects; joins, contexts, and SQL statements are not needed for the translation and are not loaded.

Creating Translations

With translations, the primary goal is to tell WebI how cube information corresponds to object information in the universe. When there is no correspondence, the translation is disabled. UDS Designer initially displays the Map Model with all translations disabled, shown next. The red X indicates the translation is disabled for each hierarchy or dimension.

1. Under the Map Model pane, expand the Year translations by clicking the + sign. Select Gen3, Year. Recall from Table 11-2 that the month member names in Essbase are three characters, while in the BusinessObjects universe Month object, they are spelled out.

   

2. To enable a translation for this Data Source, in the Object Properties pane, click Translation Enabled (step 1 in the next screen).

   

3. Under the Mapping Parts pane, the options Level Translation and Member Translation appear. Select the Member Translation (step 2), then click the Add To Map arrow (step 3).

4. UDS Designer automatically inserts both the Level Translation and the Member Translation. The Member Translation is set to a generic Source Member Unique Name and a Target Member Unique Name (step 4). In this example, replace the Source Member Unique Name by entering Jul (MOLAP member) in the Member Translation box, as shown in the following screen. Replace the generic Target Unique Value with July by typing this in the translation box.

   

5. Repeat steps 1 through 4 for each member whose object value differs in the target relational database. Note that to add additional member translations, you must first select the translation point (step 1). At this point, it should be clear why you want to avoid member translations!

6. In the Map Model, under Gen3,Year, Level Translation, note that the translation is set to Incomplete. You need to tell the model that the Gen3,Year from the Essbase outline is the same as the Month object from the BusinessObjects universe.

7. Select Incomplete (step 1 in the following screen), then in the Universe Target Information, select the object Month (step 2).

8. Click the Add To Map arrow (step 3). Under Object Properties, UDS Designer fills in the Structure Translation for you. Gen3,Year in the Originating Data Source translates to the Universe Class Time and the Universe Object Month (step 4).

   

9. One final step in the translation to make the month translation work: Any upper-level translations must be enabled, even if the levels don't exist in the detail target database. In this example, set the Year and YearHierarchy Object Properties to Translation Enabled, as shown next.

   

10. Click Save or use the pull-down menu File | Save to save your translation map.

11. Complete the map model for Market, Market Hierarchy as follows:

    

Adding Documents to the Map

These translation maps tell UDS when to pass a WHERE clause to a SQL statement and what member/object data value to pass into the WHERE clause. You now need to tell the UDS which documents users can access for the drill-through.

1. From within UDS Designer, select from the pull-down menu File | Add/Edit Target Docs or use the toolbar to select View/Modify List of Target documents.

2. UDS Designer presents you with a Document Information screen, shown next. Unfortunately, UDS Designer does not read the list of Corporate Documents from the Document domain. You have to remember the name exactly. This is the document you created in the earlier section 'Building WebI Documents,' step 7, Regional Sales by Week. Type in the Document Name: Regional Sales by Week. The Document Caption and Comments are optional and not displayed to the user. If you do enter a Document Caption, the caption, rather than the name, is displayed to the user when drilling through.

   

3. Click Add for the document to appear in the Assigned Documents box.

4. Click OK to close the Document Information screen.

5. Click Save or use File | Save from the pull-down menu.

Restarting DTS Manager

When you save a translation map, it is saved by default to C:\Program Files\BusinessObjects\BusinessObjects 5.0\Data as a .udm file. Once the file is in the data directory, the DTS Manager can read it. However, the changes take effect only when DTS is first started. This is something your server administrator will most likely do when you have completed the map.

1. From the Desktop, select My Computer.

2. Right-click to bring up the pop-up menu. Select Manage.

   

3. Under Services and Applications, double-click Services.

4. Scroll down to WebI DTS Manager, as shown next. Use the buttons on the toolbar to stop and restart the service.

   

Testing the Drill-Through

The testing stage is the first place you find out if you have defined your translations correctly. If you have defined them incorrectly, users will receive either no data or too much data, as the drill context is not passed through to the WHERE clause. Chapter 23 provides additional information on creating documents in WebI.

1. Log into Infoview.

2. Select Create Documents.

3. Select OLAP Data Sources.

4. Select the data source for which you have enabled drill-through.

5. Within WebI, drill to the point in the cube for which you want to test the drill-through and for which you have defined translations. In this example, you want to test the drill-through from State to City detail. Note in the following screen that I have set my member selections to Scenario/Actual and Measures/Sales for members that do not have explicit translations to the BusinessObjects universe. If you want only the State name to get passed through to the WHERE clause, position the cursor on a member value (such as Florida) and click the drill-through button. If you want all the possible intersections to be passed through to the WHERE clause, select the numeric cell (for example, 286.00) to tell DTS to use all available translations. In this example, both the Month and State translations will be used.

   

6. WebI displays a list of drill-through targets, as shown next. These are the WebI reports you added to the translation map. At the time of this writing, the list of available drill-through reports is not context-sensitive-for example, detail reports for Budget, Expense, and Product will also appear here. Select the name of the desired report and click OK.

   

7. WebI runs the query and displays the results. Select Edit, then SQL from the top menu bar to ensure DTS passed the drill context through to the WHERE clause. In this example, I drilled through while on a measure cell (286), so all valid translations were passed through. Notice that the Jul in the Essbase outline was converted to July:

   WHERE   ( Product.Prod_ID=Sales_Fact.Prod_ID  )   AND  ( City.City=Sales_Fact.City  )   AND  ( Time_Lookup.Week_Number=Sales_Fact.Week_Number  )   AND  (   City.State  =  'Florida'   AND  Time_Lookup.Month_Name  =  'July'   AND  Product.Prod_ID  =  '100-10'   )

---