Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, a Business Intelligence and Data Warehouse Enthusiast passionate about blogging articles in the BI, Data warehousing, space. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects.

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 73 other followers

  • Blog Hits Since Nov 2010

    • 364,321 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for March, 2011

Degenerate Dimensions

Posted by Santosh Kumar Gidadmani on March 18, 2011

What is a degenerate dimension?

A degenerate dimension is dimension without attributes. It is a transaction-based number which resides in the fact table. There may be more than one degenerate dimension inside a fact table.

According to Ralph Kimball, in a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn’t have its own dimension table. Degenerate dimensions are often used when a fact table’s grain represents transactional level data and one wish to maintain system specific identifiers such as order numbers, invoice numbers (Bill No) and the like without forcing their inclusion in their own dimension.

A degenerate dimension is data that is dimensional in nature but stored in a fact table.

OLTP transaction numbers, such as bill numbers, courier tracking #, order number, invoice number, application received acknowledgement, and ticket number, usually produce dimensions without any attributes and are represented as degenerate dimensions in the fact table.

It would be correct to say that all information that the Bill Number# represents is stored in all other dimensions. Therefore, Bill Number# dimension has no attributes of its own; and therefore it cannot be made a separate dimension.

The Bill Number# should be placed inside the fact table right after the dimensional foreign keys and right before the numeric facts.

The best way to identify a missing or a badly designed degenerate dimension is to review your dimensional design and look for any dimension table that has equal or nearly the same number of rows as the fact table.

In other words, if, for every row that you insert in the fact table you also have to pre-insert another row in any other dimension table, then you have missed a degenerate dimension.

– Santosh

Posted in Dimensional Modeling, Identify Dimensions | Leave a Comment »

Dimension Hierarchies

Posted by Santosh Kumar Gidadmani on March 11, 2011

A hierarchy is a cascaded series of many-to-one relationships and consists of different levels.

Example, a region hierarchy is defined with the levels Region, State, and City.

Types of Hierarchies

1. Balanced hierarchy

2. Unbalanced hierarchy

3. Ragged hierarchy

1. Balanced hierarchy

A balanced hierarchy is one in which all of the dimension branches have the same number of levels. In other words, the branches have a consistent depth. The logical parent of a level is directly above it.

A balanced hierarchy can represent a date where the meaning and depth of each level, such as Year, Quarter, and Month, are consistent.

2. Unbalanced hierarchy

A hierarchy is unbalanced if it has dimension branches containing varying numbers of levels.

An unbalanced hierarchy has levels that have a consistent parent-child relationship, but have a logically inconsistent level. The hierarchy branches also can have inconsistent depths. An unbalanced hierarchy can represent an organization chart.

3. Ragged hierarchy

A ragged dimension contains at least one member whose parent belongs to a hierarchy that is more than one level above the child. Ragged dimensions, therefore, contain branches with varying depths.

A ragged hierarchy is one in which each level has a consistent meaning, but the branches have inconsistent depths.

A ragged hierarchy can represent a geographic hierarchy in which the meaning of each level, such as city or country, is used consistently, but the depth of the hierarchy varies.

– Santosh

Posted in Dimensional Hierarchy, Dimensional Modeling, Identify Dimensions | Leave a Comment »

Aggregate Persistence Wizard

Posted by Santosh Kumar Gidadmani on March 8, 2011

Aggregate tables store pre-computed results that are aggregated measures (typically summed) over a set of dimensional attributes. Using aggregate tables is a typical technique used to improve query response times in decision support systems. This feature came with OBIEE version.

Steps for creating Aggregate tables using Aggregate Persistence Wizard
Create dimensional hierarchies to use in the aggregate persistance wizard.
Ensure there is at least one measure value which is aggregated on the properties, aggregation tab.
Open the Admin tool in online mode
Go to Tools – Utilities
Click on Aggregate Persistence Wizard and execute it

Type the name of the SQL file to be generated and locate anywhere on the system and click next.

Select the Database, Measure on which aggregates have to be created and click next.

Select the dimensional hierarchy and click on surrogate key options. You can also select other hierarchies.

Select the same database, connection pool and click next.

This will create the UDML scrip which is understood by the BI Server only.

Select “I’m done” and click next

Click on Finish, Save the RPD and Close it.

The SQL file will be created, go to the location path and open the file to check the script.

Run this script in the command prompt to create the SQL statement to create the actual tables to use in Admin tool.

We will use NQCMD.exe file to create the aggregates table. This exe file is located in OracleBI -> Server -> BIN

Now, type the below shown command to create the tables, It will take some time to create the tables.

Now, open the Admin tool, you would see the aggregate table created in the physical layer and added to Sales Fact TLS.

Let us take a look at the Year_Sales_Agg table properties column mapping tab, the data is coming from year_sales_agg table which is aggregate table and on the content tab, it is showing at year level.

Save the RPD and let us check the Query.

Login to the Answers and select the below columns in the SH Subject area.

Go to Settings -> Administrations -> Manage Sessions -> click on the View log link

The data is fetching from the Year_Sales_Agg table which is aggregate table.

– Santosh

Posted in Aggregate Wizard, OBIEE 10g, OBIEE 10g RPD | 2 Comments »

%d bloggers like this: