Warehouse Table Naming Conventions

 

These conventions are based upon Oracle’s BI Applications 7.9.6.3 standards.

The Analytic Data Warehouse (ADW) is an arbitrary name for the performance layer of your data-warehouse. The diagram below shows a typical data warehouse reference architecture with the Performance Layer circled in red.

Table Names

The ADW schema will predominantly hold tables built by ETL and queried by OBIEE

• Fact tables contain columns which can be summed, averaged, etc.
• Dimensional tables contain descriptive columns
• ADW tables to be loosely named in the format business name/grain/type
• Business name is a short business description of the table which is not tied to Northgate or e-Business Suite names
• Grain is optional and describes table granularity (useful for aggregate tables)
  • For the time dimension, the level names DAY, WEEK, MONTH, YEAR should be used
• Type is one of:
  • _A = aggregated fact table
  • _D = dimension table
  • _DH = dimension hierarchy table
  • _F = fact table
  • Staging tables (used during ETL but not by OBIEE) will have S appended to the
    • _DS = dimensional staging table
    • _FS = fact staging table
  • Materialized views should have MV appended to them
    • _DMV = dimensional materialized view
    • _FMV = fact materialized view
• By default use singular names (facts maybe plural?)
• Some examples:
  • TENANCY_D = Tenancy dimension
  • DEBT_DETAIL_F = Debt fact (lowest level detail)
  • DEBT_REGION_A = Debt fact aggregated to a regional level
  • DAY_D = Calendar dimension at day level (lowest level)
  • WEEK_D = Calendar dimension at week level
  • MONTH_D = Calendar dimension at month level
  • PROPERTY_UTILISATION_FMV = Property utilisation fact materialized view

Columns

• Column names should be business relevant, non-technical, and not tied to Northgate or e-Business Suite names
• Column suffixes:
  • WID - Identifier generated by ETL to link dimension and fact tables (except ROW_ID). For example TENANCY_WID would reference TENANCY_D.ROW_ID.
  • _ID - Source system identifier
  • _FLG - Flags Y/N
  • _IND - Indicator e.g. D,W,M
  • _DT - Date
  • _TS – Timestamp
  • _AMT - Amount
  • _CNT - Count
  • _PCT - Percentage
  • _CODE - A coded alphanumeric value that defines a possible value for a data element
  • _NAME - A short name that describes the code
  • _DESC - A long description for the code
• Other conventions:
  • _NO - For descriptive number columns, i.e. TELEPHONE_NO
  • _TXT - For text fields which contain concatenated values etc.
• System columns on each table:
  • ROW_WID - surrogate key to uniquely identify record (generated during ETL), first column in table
  • DATASOURCE_NUM_ID - Unique identifier of source system from which data was extracted. TBD numbers to use, 2nd column in table
  • INTEGRATION_ID - Unique identifier of fact or dimension in source system, 3rd column in table
  • W_INSERT_DT - Date inserted to Warehouse, at the end of table column definitions
  • W_UPDATE_DT - Date updated to Warehouse, at the end of table column definitions
  • ETL_RUN_ID - Identifier of ETL process that loaded, integrated, checked the row
• Optional on each table:
  • CREATED_ON_DT - Date created in source system
  • CHANGED_ON_DT - Date changed in source system
  • DELETED_FLG - For logical deletes of warehouse records
• Type 2 Slowly Changing Dimension columns:
  • EFFECTIVE_FROM_DT – Date that the dimensional record is effective from
  • EFFECTIVE_TO_DT – Date that the dimensional record is effective to
  • CURRENT_FLG – Is record current? Y/N
• All ADW surrogate keys should be integers and generated from a sequence
• Foreign keys should always use warehouse surrogate keys
• The W_ prefix denotes a translated to warehouse code/name/description combination. (the concept of domain values)

Notes on Time Dimensions

• Reporting, financial, and other calendars are all associated (de-normalized) onto each day of the year
• Dimensional aggregates are needed by OBIEE for aggregate navigation. So WEEK_D, MONTH_D, and YEAR_D should also be created.
• ETL updates dimension with reporting flags as necessary
• The surrogate key ROW_WID is an integer representation of the period, of the format YYYYMMDD or YYYYMM or YYYY

Example Physical Table Column Conventions

Domain	Datatype	Notes
AMT	Number (12,2)	Amount
CNT	Integer	Count
CODE	Varchar2 (20)	Code e.g. TUS, TUD
DD	Number (2)	Day of month e.g. 1-31
DDD	Number (3)	Day of year e.g. 1-366
DESC	Varchar2 (200)	Code description
DS	Number (2)	Datasource e.g. 1=EBS
DT	Date	Date and time
FLG	Char (1)	Y/N flag
ID	Varchar2 (12)	Identifier
IND	Varchar2 (3)	Indicator e.g. D,W,M,Q,A
MM	Number (2)	Month of year e.g. 1-12
NAME	Varchar2 (250)	Name
NO	Number (12)	Number
PCT	Number (3,3)	Percentage
QQ	Number (2)	Quarter of year e.g. 1-4
TS	Timestamp	Timestamp
TXT	Varchar2 (200)	Text string e.g. +64 4 1234567
WW	Number (2)	Week of year e.g. 1-53
WID	Number (12)	Warehouse Identifier i.e. rowid
YYYY	Number (4)	Year e.g. 2012

 

Refresher on Slowly Changing Dimensions Types

(from Wikipedia)

OBIEE Naming Conventions, Standards, and Tips

 

Further to my previous blog on modelling the RPD, below is a more detailed explanation.

Before you start, make sure you are familiar with OBIEE modelling techniques. A good place to start is the Introduction chapter in the Admin Tool help. Modelling is done with Admin Tool, and the file modelled is name the Repository or RPD (its extension). The RPD can be opened in online mode when there few developers. Backup the RPD regularly.

Best Practise

Oracle's BI Applications RPD file is available as a best practise reference. The conventions in this document follow many of the BI Applications modelling standards. My initial post also lists the basic RPD modelling steps. 

BI Applications RPD

General Conventions and Tips

• Do not tie any names to source system implementation
• The goal of the logical layer is to simplify a complex physical implementation: thus re-use existing logical tables, do not recreate a logical table for the same business concept
• In online mode, save backups of the online repository before and after every completed unit of work. If needed, use Copy As on the File menu to make an offline copy containing the changes.
• Use the Physical Diagrams in the Administration Tool to verify sources and joins.
• Use business readable names and descriptions in most places (apart from Physical Layer and Variables)
• Enable query logging to test the physical SQL generation

Physical Layer Tips

This is where access to actual resources are defined, be they databases, files, cubes or other. The main task here is to define databases, connections, physical tables, aliases, keys, and joins of an actual implementation.

• Use the Import Metadata feature to import tables to the physical layer
• Always work with aliases not physical objects. This allows for object reuse for certain dimensions (ie. the physical table DAY_D could be reused for multiple date dimensions). This allows you to:
  • Eliminate all physical joins that cross dimensions (inter-dimensional circular joins) by using aliases
  • Eliminate all circular joins (intra-dimensional circular joins) in a logical table source in the Physical Model by creating physical table aliases. For example, say you have a Customer table that can be used to look up ship-to addresses, and using a different join, to look up bill-to addresses. Avoid the circular joins by aliasing the table in the Physical layer so that there is one instance for each purpose, with separate joins.
• Opaque views (a Physical layer table that consists of a SELECT statement) should be used only if there is no other solution to your modeling problem

• Name aliases Dim_ or Fact_ for example the alias Dim_TENANT_D would point to the physical table TENANT_D
• Create joins on the aliases - not the underlying

• Keys are created on dimensional aliases (these are done automatically when you create joins)

• Foreign keys are created on fact aliases (these are done automatically when you create joins)

Business Model and Mapping Layer Conventions and Tips

This layer is often termed the logical layer and is where the physical layer is simplified into a single business representation.

• The business model should ideally resemble a simple star schema in which each fact table is joined directly to its dimensions.

• Never use technical terms on this layer, all terms should be business orientated.

Work under single business model, eg. HNZC

• Name logical tables Dim - [business name] or Fact - [business name]. For example Dim - Tenant, or Fact - Debt.

• Name logical dimensions the same as the logical table names.

• Use the Admin Tool->Tools-Utilities-Rename Wizard to rename en masse logical columns with the following rules:
  • Change each occurence of '_' into a space
  • All text lowercase
  • First letter of each word capital

• Remove columns that are not needed
• Use the description field for logical tables columns, as this is made available to users through tooltips in the Analysis Editor

Tooltip

• Every logical fact table must join to at least one logical dimension table. Note that when the source is a fully denormalized table or flat file, you must map its physical fact columns to one or more logical fact tables, and its physical dimension columns to logical dimension tables.
• Every logical dimension table should have a dimensional hierarchy associated with it. This rule holds true even if the hierarchy has only one level

Set the Content tab for each logical table source to reflect the grain of each fact and dimension

• Typically, logical fact tables should not contain any keys. The only exception is when you need to send Logical SQL queries against the Oracle BI Server from a client that requires keys. In this case, you need to expose those keys in both the logical fact tables, and in the Presentation layer.
• Normally, all columns in logical fact tables are aggregated measures, except for keys required by external clients, or dummy columns used as a divider. Other non-aggregated columns should instead exist in a logical dimension table.
• Aggregate sources should be created as separate logical table sources. For fact aggregates, use the Content tab of the Logical Table Source dialog to assign the correct logical level to each dimension
• Each dimension level in a hierarchy must have a unique level key. Also, each logical dimension table must have a unique primary key. Normally, this key is also used as the level key for the lowest hierarchy level
• Renaming columns in the Business Model and Mapping layer automatically creates aliases (synonyms) for Presentation layer columns that have the property Use Logical Column Name selected
• To prevent problems with aggregate navigation, ensure that each logical level of a dimension hierarchy contains the correct value in the field named Number of elements at this level.
• Model outer joins only when necessary. Outer joins are always defined in the Business Model and Mapping layer. Physical layer joins do not specify inner or outer.

Presentation Layer Convention and Tips

The presentation layer is the exposed layer to ODBC query clients including the OBIEE presentation services component. This is the layer that users will see in the Analysis Editor when they build queries.

• Group subject areas around business relevant functions, such as Debt Management. These relate to logical stars, for analysis.

• Describe each subject area

• Name presentation folders the same as logical tables but remove the Dim - or Fact - prefix.
• In a subject area, sort presentation folders first by dimension then fact, then alphabetically

• So that it is displayed in the Select Subject Area drop down.

• Because there is no automatic way to synchronize all changes between the Business Model and Mapping layer and the Presentation layer, it is best to wait until the Business Model and Mapping layer is relatively stable before adding customizations in the Presentation layer.
• There are many ways to create subject areas, such as dragging and dropping the entire business model, dragging and dropping incremental pieces of the model, or automatically creating subject areas based on logical stars or snowflakes.
• It is a best practice to rename objects in the Business Model and Mapping layer rather than the Presentation layer, for better maintainability. Giving user-friendly names to logical objects rather than presentation objects ensures that the names can be reused in multiple subject areas. Also, it ensures that the names persist even when you need to delete and re-create subject areas to incorporate changes to your business model.
• Be aware that members in a presentation hierarchy are not visible in the Presentation layer. Instead, you can see hierarchy members in Answers.
• You can use the Administration Tool to update Presentation layer metadata to give the appearance of nested folders in Answers.
• When setting up data access security for a large number of objects, consider setting object permissions by role rather than setting permissions for individual columns
• You can show fewer columns than exist in the Business Model and Mapping layer. For example, you can exclude the key columns because they have no business meaning.
• You can set permissions to grant or deny users access to individual subject areas, tables, and columns.
• You can export logical keys to ODBC-based query and reporting tools.
• You can create multiple subject areas for a single business model.
• You can create a list of aliases (synonyms) for presentation objects that can be used in Logical SQL queries. This feature lets you change presentation column names without breaking existing reports.

Initialization Blocks and Variables

• Name all repository variables (static, dynamic, session) in upper case with _ instead of spaces. Ie. for example CURRENT_DATE.

• Name initialize blocks in plain text. Example: Authorization.

How to Create a OBIEE Virtual Machine on a Shoestring

 

It is sometimes useful to have an all in one OBIEE 11g virtual machine for purposes such as training. The following steps show how a bare bones OBIEE 11g and Oracle 11g database was setup on a Windows 2003 SP2 32-bit platform with 3600 MB RAM.

Inventory:

• Vmware Player 5.0
• Windows 2003 SP2 32-Bit
• Oracle 11gR2
• RCU 11.1.1.6.0
• OBIEE 11.1.1.6.0

 

I setup the initial virtual machine with: 3600 MB Ram, 2 CPUs, and 50 GB disk (c: 10 GB, d: 40 GB for OBIEE 11g software, Database software, and database itself).

I setup the network mode to Host Only.

After installing Windows 2003 x32 SP2, copy the software installs to a folder (eg. d:\stage) using a network share.

Then, install the database, there is a minimalist guide to installing Oracle here

ie. Install the db software first, then create a customized, minimalist, database.

Some parameters of note that I used:

Default to standard SID = ORCL

Disabled Enterprise Manager

 

Install only OLAP and Spatial packages

In Standard Database Components, remove APEX.

• 600 MB SGA, Automatic allocation
• ALF32UTF8 character set

Note – do not use 4096 bytes block size, the RCU install will fail. Stick to 8192 bytes.

Create the default listener and local naming entry using Net Configuration Assistant.Install the Microsoft Loopback adaptor on IP address 10.10.10.10

After installing the RCU, do a simple OBIEE 11g Install:

For testing purposes, I install also Firefox and Flash Player.

The results:

I have 4.45 Gb free on my D: drive after all the installations (installer files still present too).

Remove the installers, and this pumps up to circa 22 Gb.

With oracle db, OBIEE running, and Sample App Lite, we have the following memory profile, still 1.5 Gb free:

And we can see that we have about 1.5 Gb of Oracle software memory footprint. (Weblogic, database, BI system components)

How to Copy Sample App Usage Tracking Dashboards & RPD

 

The OBIEE Sample Application has a good set of standard Usage Tracking dashboards. It is fairly easy to graft the dashboards, analyses, and RPD objects into your target environment. This will get you up and running quickly with Usage Tracking. You first need to obtain a copy of the Sample App RPD and web catalog.

There are other system based dashboards that can be copied from the Sample App.

Steps:

• Obtain Sample App v207 RPD & Web Catalog
• Archive Sample App Usage Tracking catalog folder(s) to local file
• Un-archive to target catalog
• Open target RPD and Sample App RPD in two windows
• Copy Physical, Logical, and Presentation objects in order (select, ctrl-c, ctrl-v), at a time
• Modify Physical layer connections and schema names as required
• Copy variables as necessary
• Deploy RPD and catalog

Usage Tracking RPD:

How to Customize OBIEE Portal Look & Feel

 

This post describes the steps required to customize the look and feel of the OBIEE 11g portal. The look and feel is customizable through Custom Style Sheets (.css files), images, and messages (in XML).

The Firebug Add-On for Firefox is useful for inspecting page elements and determining the source image or CSS file for customization if you are a Firefox user. A similar tool exists for Internet Explorer. Chrome comes with a built in inspector tool.

The three elements that you work with in OBIEE for portal customization are:

· Styles – Applied to dashboard pages

· Skins – Applied to the OBIEE portal

· XML Messages – Both in dashboard pages and the portal

An example customization below -

The setup involves (1) copying the default Styles, Skins, and XML Messages to a target directory (2) modifying the copied artefacts as desired (3) configuring your dashboard pages to use the customized Style.

 

 

· Venkat’s blog post on the subject - http://www.rittmanmead.com/2010/12/oracle-bi-ee-11g-styles-skins-custom-xml-messages/

· An Oracle White Paper - http://www.oracle.com/technetwork/middleware/bi/customizing-oracle-biee-11g-176387.pdf

How to Setup Usage Tracking in OBIEE 11g

 

Usage Tracking allows you to track how OBIEE is being used. There is a good set of sample dashboards in the OBIEE Sample Application V207 (http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html).

See below for an example Sample App Usage Tracking dashboard:

(For OBIEE 11.1.1.6.0)

Setup details from manual: http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/usage_track.htm#CIHDEEDG

To set up direct insertion usage tracking using the System MBean Browser:

1. Display the Fusion Middleware Control MBean Browser.

2. Expand Application Defined MBeans, then expand oracle.biee.admin, then expand Domain: bifoundation_domain.

3. Lock for edit

4. Expand BIDomain.BIInstance.ServerConfiguration, then select the BIDomain.BIInstance.ServerConfiguration MBean.

5. Ensure that the UsageTrackingCentrallyManaged attribute is set to true. When UsageTrackingCentrallyManaged is set to false, the following parameters are managed using the NQSConfig.INI file on each Oracle BI Server computer rather than the SystemMBean Browser:

· UsageTrackingConnectionPool => "HNZ Analytic Usage Tracking"."Usage Tracking Writer Connection Pool"

· UsageTrackingDirectInsert => true

· UsageTrackingEnabled => true

· UsageTrackingPhysicalTableName => "HNZ Analytic Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT"

6. After applying your changes, release the lock on the domain, as follows:

1. Return to the BIDomain MBean where group=Service under oracle.biee.admin, Domain:bifoundation_domain, BIDomain.

2. Display the Operations tab.

3. Click one of the commit operations.

7. Go to the Oracle Business Intelligence Overview page and click Restart.

8. Note that NQSConfig.INI is updated