User manual BUSINESS OBJECTS UNIVERSE BUILDER

[. . . ] BusinessObjects Universe Builder Guide BusinessObjects Universe Builder Guide Copyright © 2008 Business Objects. Business Objects owns the following U. S. patents, which may cover products that are offered and licensed by Business Objects: 5, 555, 403; 5, 857, 205; 6, 289, 352; 6, 247, 008; 6, 490, 593; 6, 578, 027; 6, 831, 668; 6, 768, 986; 6, 772, 409; 6, 882, 998; 7, 139, 766; 7, 299, 419; 7, 194, 465; 7, 222, 130; 7, 181, 440 and 7, 181, 435. Business Objects and the Business Objects logo, BusinessObjects, Business Objects Crystal Vision, Business Process On Demand, BusinessQuery, Crystal Analysis, Crystal Applications, Crystal Decisions, Crystal Enterprise, Crystal Insider, Crystal Reports, Desktop Intelligence, Inxight, the Inxight Logo, LinguistX, Star Tree, Table Lens, ThingFinder, Timewall, Let there be light, Metify, NSite, Rapid Marts, RapidMarts, the Spectrum Design, Web Intelligence, Workmail and Xcelsius are trademarks or registered trademarks in the United States and/or other countries of Business Objects and/or affiliated companies. [. . . ] A IBM DB2 Cube Views dimension object is deduced directly from a BusinessObjects class. Dimensions All the objects within a class determine the attributes of the IBM DB2 Cube Views dimension. The tables inferred by BusinessObjects objects within the class, are detected by parsing the object's Select field. Attributes are not directly deduced from the columns of tables in the universe. Candidate attributes are detected and identified from the following information: · BusinessObjects objects within a class. · BusinessObjects objects referenced in Select field of other BusinessObjects objects by the @Select statement. · Columns involved in a join. Attributes The detail-dimension relationship in a universe is translated Attribute relationto an attribute relationship of type Functional Dependency ships in IBM DB2 Cube Views. Joins Joins and their properties are directly read from universe structure. All classes are searched for measure objects. If a measure is not built on the same fact table, then it is ignored. Measures BusinessObjects Universe Builder Guide 27 5 Exporting a universe to a XML file format Mapping specific SQL expressions Non-matching universe metada- Description ta A hierarchy in DB2 Cube Views is linked to a dimension object and all its levels are members of this same dimension. This is not the case in a universe, where a custom hierarchy can contain levels coming from different Business Objects classes. Hierarchies are treated as follows: · If a universe uses only default hierarchies, then the hierarchies for export to IBM DB2 Cube Views are deduced from the objects, using the object order within each class. · If the universe has custom hierarchies, then they are exported without any modification. Hierarchies Mapping specific SQL expressions Certain SQL expressions are mapped in particular ways by the export process. The following cases of SQL expressions are described in detail: · · · · · SELECT expression for a measure on page 28 @AggregateAware function on page 29 Complex join expressions on page 29 Theta joins on page 29 Shortcut joins on page 29 SELECT expression for a measure The BusinessObjects UMB gets the following information from the SELECT of a measure: · · · Detect tables and columns involved in a measure and map them to sqlExpression:column Identify the aggregation function Determine the formula expression and map it to sqlExpression:template. 28 BusinessObjects Universe Builder Guide Exporting a universe to a XML file format Mapping specific SQL expressions 5 @AggregateAware function When an object contains the @AggregateAware function, only the last parameter of the @AggregateAware function is taken into account. This is the expression that contains the lowest level of aggregation used by the function. For example: A @AggregateAware expression for a measure expression in the universe is as follows: @Aggregate_Aware( sum(AggregatedTable1. Sales_revenue), sum(AggregatedTable2. Sales_revenue), sum(Fact_Table. Amount_sold)) The expression that is mapped to IBM DB2 Cube Views is: sum(Fact_Table. Amount_sold)) Complex join expressions The expression of a complex join in a universe can consist of expressions of type: LeftTable. Column=RightTable. Column In a complex join, these type of expressions can be linked together with the AND operator. The BusinessObjects UMB maps each expression in the complex join to an attribute pair of IBM DB2 Cube Views within the same join. Theta joins A theta join is split into two IBM DB2 Cube Views joins, where the operator BETWEEN is replaced by operators <= and >=. For example: A join in a universe has this expression: Customer. age between Age_group. age_min and Age_group. age_max This join will be split into two joins with the following expressions: Join1: Customer. age >= Age_group. age_min Join2: Customer. age <= Age_group. age_max Shortcut joins Not exported to IBM DB2 Cube Views. Shortcut joins in a universe represent an alternate path to improve performance of queries by not taking into account BusinessObjects Universe Builder Guide 29 5 Exporting a universe to a XML file format How universe structures are mapped to DB2 Cube Views metadata intermediate tables. As shortcut joins create loops within a cube model, they are not exported. How universe structures are mapped to DB2 Cube Views metadata The following universe structures are mapped to corresponding IBM DB2 Cube Views structures. Each section provides detailed descriptions for structures that are mapped from a universe to IBM DB2 Cube Views when a universe is exported to a XML file. · · · · · · · · · Universe to cube model on page 30 Class to dimension on page 31 Fact table to Fact on page 31 Measure to measure on page 32 Dimension and detail object to attribute on page 33 Dimension and detail relationship to attribute relationship on page 33 Default hierarchy to hierarchy on page 34 Custom hierarchy to hierarchy on page 34 Join to join on page 34 Universe to cube model The following describes mapping for a universe to cube model: Universe property Short name (file name) Universe name (long name) Description Fact table List of classes Cube property Cube file name Business name. By default the name is the universe short name (<universe short name>) Comments name factsRef dimensionRef 30 BusinessObjects Universe Builder Guide Exporting a universe to a XML file format How universe structures are mapped to DB2 Cube Views metadata 5 Universe property List of joins involving the fact table. Cube property joinRef Class to dimension The following table describes the mapping for a class to a dimension: Class property Name Description List of dimension and detail objects. Joins between dimension tables inferred by the class. [. . . ] Expand a AW node to display the cubes available to the AW. Right click a cube and select Create Universe from the context menu. How to create a Oracle OLAP universe on page 48 for a description of each option. Click the Generate Universe from View radio button. [. . . ]