Repository Metadata: Tips and Tricks

1. Repository Metadata: Tips and Tricks Peggy Rodriguez,Kathy Kimball

2. Repository Metadata — what's it for? • Defines the data sources to the BI server • Prepares data for use in Answers and Dashboard • Creates additional objects (columns or variables) that can be referenced in Answers and Dashboards

3. Administrator’s Desktop

4. What is the tool used to work with the Repository? • Oracle BI Administration Tool, aka "Administration" • Available only in Windows environment • Communicates with non-Windows databases and server environments • One Repository per BI server... a Repository can't be shared by multiple servers

5. Repository Metadata Consists of 3 Layers • Physical • Business Model and Mapping • Presentation

6. Oracle BI Administration Tool

7. And other tools... • Manage Repository variables (session variables, other variables) • Manage server cache • Manage sessions and jobs • Repository Metadata Utilities

8. Manage menu

9. Prepare Ahead ? • Right now, get in there and do stuff! • But plan to do it over... • Until you start to know what you need to know • "Information Needed for BI Repository Metadata Development“ document

10. Information needed for OBI Repository Metadata Development • What is the URL for the model diagrams? • What is the URL for the source to target documents? • What are the questions that this model can answer? This will help us understand the data and formulate queries. • Please provide a complete select * query on the data model, which would theoretically return all data from a given data model. We could use this general query as a starting place for specific queries against the model that replicate the Answers queries. • What is the selection criteria for specific groups of data? • When a table contains multiple records for the same data item (e.g. history rows or outdated rows) what is the best way to identify the most current row? • What are the fact tables? • What are the dimension tables? • What are the specific column joins between tables (required to build the Physical Diagram in OBI)? • If table alias names are used in the model what are their mapping to the specific tables in the warehouse? (e.g. Accounting Date maps to TIME)

11. Information needed for OBI Metadata Development, continued • If alias column names have been used in the model what is their specific mapping to columns in the warehouse? (e.g. Accounting Date.Date maps to TIME.DAYDATE) • If columns have been omitted in the model what is the complete set of specific column names that should be included in the OBI repository metadata? • What name changes should be made to tables or columns for the presentation display? • What hierarchies should be constructed for drill-down? • Are there drill-across relationships (a need to drill from one dimension to another)? • Which columns should have default SUM aggregation? (or other aggregations?) • What calculated fields should be created in the repository metadata? • What columns should not be brought to the presentation layer? (e.g. Extract Date) • Are there any columns that happen to be named "...key" that are not dimension keys? • We can group columns logically to improve readability and access. Within tables, are there sets of columns that should be grouped together? Any groupings that might make sense even across tables? (e.g., create one folder for all of the commonly used Finance chartfields)

12. ...but right now we'll concentrate on the data layers.

13. Minimum Process • Import tables to Physical layer • Create joins in Physical layer • Check in changes as you go • Drag subject area to Business Model and Mapping layer • Drag subject area to Presentation layer • Check in changes as you go

14. Consistency Check Manager • Concurrent administrators proceed with caution!

15. Physical Layer • Connection Pools • Joins • Define column data types • Updates to existing subject areas • Use of denormalized tables

16. Connection Pool • Is created every time you import data from the server • Fully qualified names are required because a proxy user is used to connect to the warehouse • Same TNS name is used for the Data Source Name to easily move among DEV, TEST, PROD environments

17. Joins • Must be created using the GUI tool • Expects a dimensional model...but can work with reporting tables

18. Data Order and Types • Column order is always alphabetic • Data types are problematic • DateTime vs Date • Double vs Integer

19. Updates to existing subject areas • Adding new tables to a subject area is simple • Import into subject area • Create joins on new tables • Remember that changes made in the warehouse must also be made in the repository metadata, e.g. column length

20. Business Model and Mapping Layer • Rename tables and columns • Column order • Group columns • Define hierarchies • Create calculated columns • Join new tables in existing subject areas • Alternate sorting of data • Set default aggregation • Refine relationships among data (e.g., outer joins) • Stuff we don't yet know about(!)

21. Rename tables and columns • Get users involved early • Renaming Wizard • Rename tables and columns in the Presentation and Business Model and Mapping layers • Renaming the presentation layer columns only will reset the “Use Logical Column Name property to false.” It is recommended that you rename the business model layer logical columns instead. • In other words, aliases are created if columns are named at the Presentation layer causing the columns to have different names from the Business Model layer. This may result in problems with Hierarchies and Navigation within Answers.

22. Rename Wizard – order matters!

23. Column order and Grouping • Move common, frequently-used columns to the top • Group columns with dummy columns, e.g. --- Program Plan Begin --- --- Program Plan End --- • Check "Use existing logical column as the source" • Enter the number 1 in the formula box

24. Define hierarchies for drill • Natural hierarchy of related elements • Members of a hierarchy will usually be contained within one dimension (best case) • Can be created from multiple dimensions (worst case)

25. Create calculated columns • Used for • Measures • Concatenated fields • Alternate sort sources • Anything not in the warehouse that will be used a lot

26. Alternate sorting • Source for an alternate sort can be an existing column or a calculated value

27. Set default aggregation • We have used Sum for most measures • We had inconsistent results when aggregation was set in Answers instead of within the Repository Metadata

28. New tables in existing subject areas • Create join in Business Model and Mapping Layer for new tables added to existing subject area

29. Stuff we don't know about yet! • Refine relationships among data (e.g., outer joins) • The Business Layer allows complicated manipulation of data relationships that we have not used yet

30. Presentation Layer • Unique icons for Presentation and Business Model subject areas • Eliminate irrelevant columns (keys) • Custom formatting? Not here, that’s done in Answers. • See the next session: Dashboard Reporting: Design Tips and Tricks

31. Unique icons for Presentation and Business Model subject areas • For convenience, use icons to relate items in Presentation and Business Model layers

32. Repository Documentation Utility • Generates a csv or tab-delimited file • Documents the mapping from the presentation columns to the corresponding logical and physical columns • Eliminated columns (like keys) are not included in this report • Includes conditional expressions associated with the columns

33. Denormalized tables present special problems • Stand-alone tables or views that aren’t part of a dimensional model • But the tool requires that a table be joined to another table to be usable

34. Denormalized tables • Physical layer • Several subject-related denormalized tables may be imported together in a single subject area • Create an alias of each denormalized table • In the Physical Diagram, use a simple join from the original table to the alias • The alias becomes the "fact" table

35. Denormalized tables • Business Layer • If you want to mimic a dimensional model, delete columns from the "fact" table that are not used for measures (all but the join column in most cases)

36. Denormalized tables • Presentation Layer • Delete alias table (assuming it does not contain measures) • Create different folders (subject areas) for different denormalized tables so that people don't try using them together. (While this would not be allowed in Answers, trying to do so returns an error message that is confusing for the user.)

37. Denormalized Table sequence

38. Conclusion • Prepare ahead • Know your data—data models are key • Work with your data providers—ask questions and keep talking • We like being people who work with both the Repository and with the reporting tools

39. Resources • Oracle Metalink 3.0http://metalink3.oracle.com • Oracle Business Intelligence obiee-101http://obiee101.blogspot.com • Business Intelligence – Oraclehttp://oraclebizint.wordpress.com • OBIEE Metadata Development (DOC)http://polydata.calpoly.edu/dashboards/obiee_conf/presentations/OBIEE_Metadata_Development.doc

40. Questions?

41. Contact • OBIEE Technical Conference:http://polydata.calpoly.edu/dashboards/obiee_conf/index.html • Email: polydata@calpoly.edu