How to model the OBIEE RPD (Basic)

 

OBIEE: Modelling an existing EDW Subject Area (Bottom-Up) based upon existing reports.

The following steps summarise the modelling steps. This PDF has diagrams to illustrate the steps.

 

1. Before you start: 1.1. Talk to user and understand business behind report 1.2. Talk to business analyst and get example report, SQLs, existing ER diagrams 1.3. Understand grain, dimensionality of subject area 1.4. Check SQLs, data quality and join conditions via SQL*Developer 1.5. Ensure you are working in the correct OBIEE environment OBIS works with facts at a logical level – understanding fact granularity is key This approach assumes physical tables have been built, and populated and are accessible to OBIS. Key Terms & Concepts Oracle BI Server (OBIS) the heart of OBIEE RPD – Repository file where modelling takes place Physical layer – Where physical source objects are defined Logical layer or Business Model & Mapping layer – Where logical hierarchies, joins, and aggregation rules are set Presentation Layer - the layer that is exposed to query clients such as Oracle BI Presentation Services (OBIPS) LTS – Logical Table Source: a logical table contains one or more LTSes

2. Physical Layer: 2.1. Import Metadata 2.2. Re-use, create, and/or duplicate physical layer Aliases 2.3. Set Physical Joins between Aliases with Physical Diagram tool – physical keys will automatically created Always work with physical Aliases Physical aliases allow a single underlying table to be used for a variety of roles Joins and keys are set on Alias, not on underlying table Column defs are done on underlying table, and are reflected in Alias Physical Diagrams: Star Schemas and Transactional (Snowflaked) schemas can be modelled. Database, Connection Pools, etc.

3. Logical Layer: 3.1. New logical tables: Drag & drop physical aliases to ABC ORG. Core subject area 3.1.1. Rename cols using Rename Wizard according to standard 3.2. Existing logical tables: Drag & drop physical aliases to existing logical table within ABC ORG. Core 3.2.1. Rename cols, remove unwanted cols 3.3. Set description for each logical column, these are available to users in Analysis Editor as tooltips 3.4. Set logical table properties (General->Description, Keys) 3.5. Diagram logical joins with Business Model Diagram, set join cardinality and type (inner, left outer, etc.) 3.6. Set aggregate rules for logical columns which are facts. These should turn yellow denoting logical measure 3.7. Set LTS properties (General, Column Mapping) 3.8. Create logical dimensions (hierarchies) and define properties, levels, level keys, etc. as necessary 3.9. Set Content tab for logical tables sources (Aggregation content, Where Clause, Fragmentation content). Add joins to LTS as necessary. 3.10. Create derived logical columns and level-based measures as necessary 3.11. Check subject area consistency (not global consistency) Always set the LTS Content tab. This is used by OBIS to choose the most economic source. The Business Model Diagram should be a Logical Star for each Subject Area Snapshotted measures are aggregated with a LAST function on the Snapshot Period dimension as the data is already aggregated by period in the tables. The LAST function takes will take the last available period when the period is not in the query. Check consistency often. Always have a logical star. Ensure LTS Content Tab is set. Define logical dimensions (hierarchies).

4. Presentation Layer: 4.1. Either create or re-use existing Subject Area (eg. Assets – Property Snapshot) 4.2. Set Subject Area description, implicit fact column 4.3. Add presentation tables to subject area by either dragging & dropping from logical layer or copy & pasting from other subject areas 4.4. Rename presentation tables in accordance with standards (remove dim – prefix for dimensions). Remove unwanted presentation columns. 4.5. Order presentation tables Snapshot Period first table, then assets/advisor hierarchy, then alphabetical dimensions, then facts. 4.6. Nest tables as necessary with -> in Description 4.7. Set object permissions on subject areas, presentation tables, and presentation columns to restrict access as necessary Make subject areas as simple as possible mapping to a single logical star Strictly follow naming conventions Remove hierarchical columns where no hierarchy exists

5. Sanity test subject area: 5.1. Create a new analysis and examine generated physical SQL, data quality, and response times. 5.2. Test granularity of facts by creating query with an attribute from each dimension 5.3. Test aggregation rules of measures 5.4. Test drill-down of hierarchical columns and attributes with drill When creating Analyses always start with simple requests and progressively add attributes Refresh Metadata often from Analysis Editor to get latest RPD online-mode changes Test bad generated physical SQL in SQL*Developer and compare to original SQLs provided by analyst

Help: The Admin Tool help functionality is comprehensive. There is also online documentation from the Oracle Web Site. Finally, there are some well known bloggers. The BI Applications RPD provides an excellent reference for OBIEE best practise. The OBIEE Sample Application is a great reference point. Develop a set of OBIEE dimensional modelling standards & conventions.