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
Administrators 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, thats 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 arent 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 datadata models are key
• Work with your data providersask 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//obie
  e101.blogspot.com
• Business Intelligence Oraclehttp//oraclebizint
  .wordpress.com
• OBIEE Metadata Development (DOC)http//polydata.c
  alpoly.edu/dashboards/obiee_conf/presentations/OBI
  EE_Metadata_Development.doc

40
Questions?
41
Contact

• OBIEE Technical Conferencehttp//polydata.calpol
  y.edu/dashboards/obiee_conf/index.html
• Email polydata_at_calpoly.edu