User manual BUSINESS OBJECTS UNIVERSAL DRILL THROUGH FOR WINDOWS SERVICE GUIDE

[. . . ] Universal Drill Through Service Guide Universal Drill Through Service Windows 2 Universal Drill Through Service Guide Copyright Copyright © 2004 Business Objects. If you find any problems with this documentation, please report them to Business Objects in writing at documentation@businessobjects. com. Trademarks Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names mentioned herein may be trademarks of their respective owners. Contains ICU libraries (c) 1995-2003 International Business Machines Corporation and others. [. . . ] If you use views in the target database, you do not need to perform as much data migration. Designing the target database 44 Universal Drill Through Guide Designing a star schema database In a star schema, the numeric data (the Accounts dimension in an Essbase/DB2 OLAP cube) is stored in a central `fact' table. The dimension tables (equivalent to the dimensions in an OLAP cube) radiate around this fact table and are joined to it via foreign keys. (See Designing a star schema database on page 44 for more details. ) The example drill through database supplied with UDS Designer is arranged almost entirely in a star schema, although the Product dimension has been split (or `snowflaked') into the PRODUCT and PRODUCT_CLASS tables. Databases in which the dimensions are partly re-normalized into separate tables are known as snowflake-schema databases. Essbase/DB2 member naming restrictions In an Essbase/DB2 OLAP cube, each member name must be unique throughout the entire cube. Essbase/DB2 OLAP cube designers typically work around this restriction by qualifying the name of the member with the names of its parent members. For example, it is not possible to have a member called `Q2' if the cube contains data for more than one Q2 (which is likely). In this situation, the cube designer could qualify the name of the quarter with the name of the year in order to make the member name unique. The member name for Q2 of 2001 would be 2001Q2. You can account for this in the database by adding additional columns where necessary to allow for mapping to uniquely named-members. You can see this in the TIME_PERIOD table of the example database, which contains the columns ess_quarter (uniquely-named quarters) as ess_month (uniquely-named months). NOTE You can also account for unique members at the universe level. See Creating universe objects to map to unique members on page 38 for more details. The Accounts dimension An Essbase/DB2 Accounts dimension might contain data that is not in the relational data source. The Accounts dimension can contain aggregates, while the relational data might contain the lowest-level numerical data only. In this scenario it is not possible to map the Accounts dimension to the database. Designing the Target Universe Universal Drill Through Guide 45 This should not present an issue as the mapping of an Accounts dimension is rarely useful. The purpose of Drill Through is to allow the user to retrieve lowerlevel numerical data from the database than is available in the cube. To do this, Drill Through needs to map dimensions and levels between the cube and database. Numerical data is retrieved based on the dimensions and levels that the user is querying - in itself it is not relevant to drill through context. Designing a star schema database 46 Universal Drill Through Guide Creating the target universe from the database In the target universe, the classes must map to the dimension in the OLAP cube, the objects must map to the levels, and the values of those objects must map to the cube members. The following table shows some example correspondances between the example cube and universe shipped with WebIntelligence. Cube Term Dimension Level Cube Data `Product' Universe Term Universe Data Class `Product' `Brand Name' `Brand Name' Object (dimension or detail) `Cleats' Value Member `Cleats' Designing the Target Universe Building Translation Maps chapter 48 Universal Drill Through Guide Overview This chapter describes UDS translation maps and how to build them using UDS Designer. Translation maps tell the Drill Through Service how to translate the context from an OLAP source cube to a universe (OLAP-to-relational drill through) or OLAP target cube (OLAP-to-OLAP drill through). Drill through currently supports Microsoft SQL Analysis Services and Essbase/DB2 OLAP as OLAP sources. Building Translation Maps Universal Drill Through Guide 49 What is UDS Designer? UDS Designer is a graphical tool that you use to build translation maps. Translation maps are XML files; UDS Designer is a specialized XML editor specifically tailored to building maps. However, you can build translation maps with any XML editing tool. [. . . ] The Universal Drill Through Service applies filters to the reports depending on where the user chooses to drill in the source cube. Making target reports available for drill through You make target reports available by uploading them to the 'Corporate Documents' area of WebIntelligence, then refreshing the list of corporate documents. The reports can then be accessed by users who need to drill through from OLAP cubes. Creating WebIntelligence target reports 82 Universal Drill Through Service Guide Drill through workflow This section describes what happens in WebIntelligence when a user drills through from an OLAP cube to a universe. Where can the user drill in a report? UDS supports single or multiple-cell drill through from both measure and member cells. For example, the user might choose to drill down into sportswear revenues in California in Q1 2000 (measure cell) or drill down into fashion products (member cell). Member cell Measure cell WebIntelligence in Drill Through Universal Drill Through Service Guide 83 Starting drill through To start drill through: 1. The cell is highlighted. Cell selected for drill through Drill through workflow 84 Universal Drill Through Service Guide 2. [. . . ]