Monday, October 24, 2011

After playing around with the new sampleapp107 I managed to crash the VM. After a reboot the OID refused to start up. It seemed that because there was still an active status record in the ODS schema, the OPMN couldn’t find anything to start.

Friday, October 21, 2011

One of the new feature of 11g is the AGGREGATE AT function. It uses the hierarchical level to pin the the aggregate. Problem is you can’t select a hierarchy level in the formula editor, so you have to some old school typing:

Example:

Only month 1 to 6 are selected.

Just like the BY statement you can do multiple levels from different Hierarchies:

This is always a “work in progress” and please feel free to make any suggestions!

Catalog management

- use a transport folder.

Don’t allow all developers to place everything in all shared folders. Have them place it first in “transport” folder. Assign a “librarian” for the shared folder who will check everything and place it in the correct shared folder.

- Add Metadata to the folders:

This makes them better searchable.

- Add structure to the portal:

There are a number of different ways that a portal could be structure to improve efficiency to business users. For instance:

Reports could be stored by subject area, such as 'finance', 'sales', 'supply chain'

Reports could be stored in a 'daily', 'weekly', 'monthly' directory structure depending on how often they have been designed to be refreshed.

Reports could be stored by perspective:

Financial — Groups objectives, initiatives, and KPIs that relate to or support the monetary or economic health and development of your organization.

Customer — Groups objectives, initiatives, and KPIs that pertain to or support your client base.

Saturday, October 15, 2011

First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them. I just added my own observations.

The first column you select for your report should always a column from your time or calendar dimension. Time is often the most consistent aggregation splitter. And most database use partitioning along a time-line.

- Dimension Order:

Try to maintain the same order of the dimension across all reports build on a single presentation layer. That way the use of aggregate tables and or query rewrite can be optimally provisioned.

- Move Complex Logic to the rpd:

If you have made a nice “fancy” formula which you probably need to use in a couple of reports, consider moving it to the rpd. That way you only have to maintain it in one place.

- Less is more 1!

If the user is only interested in the “bad” records, then only show hem/her the “bad” records. To make the user scroll trough hundreds of records looking for the ones you flagged with conditional formatting isn’t very efficient.

- Less is more 2!

If a report generates more then a hundred records, changes are big that the user is going to do “download to excel”. Check with the user if he needs the report in this form. Consider using different deliver methods (agents / Bip).

- Less is more 3!

If on opening the report the user already has to scroll or navigate to other pages try opening the report on a “higher” level.

- Avoid multidimensionality on graphs:

If the human eye and brain need to pick up more then 1 dimension on graph it’s easily fooled.

This is always a “work in progress” and please feel free to make any suggestions!

Presentation Layer

- Common dimension

When you have multiple Subject Areas, list the common dimensions in the same order across all the Subject Areas

- Time dimension first:

Since the time/calendar dimension is often the main aggregator make it the first in your presentation layer list.

- No prefixes:

Presentation Table names within each Subject Area must not begin with “Dim – “ or “Fact –“ or “Fact Compound –“. So remove these prefixes if they are present after creating the Subject Area by dragging Logical Tables directly from the Business Model.

- Identify your facts:

The Presentation Table containing your facts should be listed right at the bottom, and the Presentation Table name should contain words like “Measures” or “Facts”

- Ensure logical relationship:

There should be absolutely no possibility whatsoever of a user selecting objects from a Subject Area that have no logical relationship. So, if there are any objects within the same Subject Area that cannot co-exist in the same report, then your Subject Area design is incorrect!

- Split over multiple subject area:

Within OBIEE11g report can be build using multiple presentation layers based on the same business layer:

Consider splitting your presentation layer in “sub” areas.

- Dimension Column Order:

Try to have the column in the same order as your hierarchy: Year > Quarter > Month > Week > Date or Business Line > Brand > Product

- Special characters:

Special HTML characters {< > / } should be avoided in the object names. Not ever browser can render them correctly.

- Metadata dictionary

Have a well maintained metadata dictionary in place:

Remember in OBIEE11G you have to redeploy the metadata dictionary after each RPD deployment

This is always a “work in progress” and please feel free to make any suggestions!

Business Model Layer

- Prefix Logical Tables

All Logical Tables should be prefixed. There are several naming convention's in use:

“Dim – “, “Fact – “ or “Fact Compound –“

“D## name”, “F## name” or “FC## name”

- No “physical” column names

No “physical” column names should ever be seen on the Business Model layer. All naming conventions should be “business oriented”. For example use “$ Revenue” rather than “DOLLARS” .

- No Primary or Surrogate Physical Keys

Physical Primary Keys or Surrogate Keys should not be present on the Business Model layer (unless, for example, you have a Primary Key such as Order Id which will be displayed on reports)

- Logical Keys

Dimension Logical Tables must always have a Logical Key assigned. The Logical Key should be something “business oriented” such as “Employee Login” rather than “EMPLOYEE_PK”

- No Facts in dimensions

Dimension Logical Tables must only contain dimension attributes, they should never contain any measure columns (which have an Aggregate Rule)

- No Logical Keys on facts

Fact Logical Tables should not have a Logical Key assigned.

[UPDATE: ] As far as I know this is because the OBIEE optimizer uses the logical key to determine the "driving" table. No logical keys on fact tables should ensure the "correct" optimizer path. Please correct me if I’m misinformed.

- Aggregation Rules on Facts

Every Logical Column within a Fact Logical Table must be a measure column, and therefore have an Aggregation Rule assigned.

- Only Complex Joins

When defining Logical Joins between Logical Tables, only use “Complex Joins” (and use the default settings – you only ever specify a “Driving Table” when dealing with cross-database joins)

- No Snowflakes

The Business Model should only consist of logical star-schemas, there should not be any snow-flaking

- Hierarchies on Dimensions

Every Dimension Logical Table should have a corresponding Dimension Hierarchy (with “Total” as a Grand Total level, and “Detail” at the lowest level)

- Number of Elements

Each level of a Dimension Hierarchy should have its “Number of Elements” appropriately set (there is a utility in Tools that can do this automatically).

- Content Levels

Every Logical Table Source within every dimension and fact Logical Table should have its “Content Levels” appropriately set. The only time the “Content Level” is not set for a particular dimension is when there is no logical relationship existing

- Multiple facts tables

Do not merge all your measures into a single Fact Logical Table. For example, you should split “Forecast Sales” and “Actual Sales” measures into two Logical Tables e.g. “Fact – Sales” and “Fact – Forecast”

- Description fields

All available description field should have meaningful descriptions with non technical users.

This is always a “work in progress” and please feel free to make any suggestions!

Physical Layer

- Clear the cache check box

Using cache should be a last resource, reconsider your data model and ETL processes first!

- Always use “Foreign Key” joins, not “Complex Joins” on the Physical Layer

If your join looks like D_DATE = TRUNC(S_DATETIME) try add a extra column S_DATE in your DWH. Any matching processing done by the BI-server costs time and you often loose the advantage of an index in your DWH.

- Prefix your tables

When modelling a star-schema data-model, create aliases for all your physical tables (prefixed with either “Dim_”, “Fact_” or “Fact_Agg_” )

- Physical Display Folder

Use Physical Display Folder to organise your stars

- Call Interface

When possible, configure your connection pools to use a “native driver” to connect to your physical databases. For example, use OCI for connecting to an Oracle database rather than ODBC.

- Parameterize your data source name

This way you only have to change it in one place when moving from development to production.

- 3NF in DWH

Try to avoid doing 3NF to Star Schema Modelling in the Physical Layer. Flatten the table if possible during the ETL or in a view on the database.

- Connecting User

The User you use to connect to your data should by default not by the “owner” of the table, but should have only select rights trough a role.

My Site's

Subscribe To

Followers

Disclaimer

Opinions expressed are entirely my own and do not reflect the position of my employer, Oracle or any other corporation. I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog. And Yes I'm human, so I tend to make a misstake every now and then