ORACLE BUSINESS INTELLIGENCE WORLD

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

    • 337,456 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for the ‘Dimensional Modeling’ Category

OBIEE11g Configuring Two Facts with Non Confirmed Dimensions.

Posted by Santosh Kumar Gidadmani on October 26, 2015

There will be situations in the business to build a report with multiple facts using confirmed as well as non-confirmed dimensions.

Below is an example to demonstrate the configuration in OBIEE 11g.

twofacts_1

DIM_1 is non-confirmed dimension and DIM_2 is confirmed dimension.

Below are the steps to configure in the OBIEE 11g.

Import all the DIMS and FACTs, apply physical joins as shown in the above picture.

Create a logical model and move a fact which has maximum joins along with corresponding dims, example above move FACT_2, DIM_1 and DIM_2. Rename the fact table like fact.

Add other fact measures from physical layer to BBM layer model (example to fact logical table). This will bring two LTS to the logical fact as shown below.

twofacts_2

Next, create dimension hierarchies and add the aggregation to the measures based on requirements (example sum)

Go to the Amount measure properties and in the content tab set the DIM2 level to Total, as this measure from FACT_1 doesn’t have join to DIM_2,  this will make it level based measure. If you don’t set the level to total, the query will provide null values for amount measure. If you want to avoid nulls, add detail level for all the confirmed dimensions.

twofacts_3

Promote the model to presentation layer and create the report.

twofacts_4

The above report will provide data from two facts with non-confirmed dimension. The report will create two separate physical queries and combine both the results using full outer join. Below are the physical queries.

Physical Query when the level is set to Total.
WITH
SAWITH0 AS (select sum(T758957.DISCOUNT) as c1,
T758947.NAME2 as c2,
T758944.NAME as c3
from
DIM_1 T758944,
DIM_2 T758947,
FACT_2 T758957
where ( T758944.DIM_1_KEY = T758957.DIM_1_KEY and T758947.DIM_2_KEY = T758957.DIM_2_KEY )
group by T758944.NAME, T758947.NAME2),
SAWITH1 AS (select sum(T758950.AMOUNT) as c1,
T758944.NAME as c2
from
DIM_1 T758944,
FACT_1 T758950
where ( T758944.DIM_1_KEY = T758950.DIM_1_KEY )
group by T758944.NAME),
SAWITH2 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
(select 0 as c1,
case when D1.c3 is not null then D1.c3 when D2.c2 is not null then D2.c2 end as c2,
D1.c2 as c3,
D2.c1 as c4,
D1.c1 as c5,
ROW_NUMBER() OVER (PARTITION BY D1.c2, case when D1.c3 is not null then D1.c3 when D2.c2 is not null then D2.c2 end ORDER BY D1.c2 ASC, case when D1.c3 is not null then D1.c3 when D2.c2 is not null then D2.c2 end ASC) as c6
from
SAWITH0 D1 full outer join SAWITH1 D2 On SYS_OP_MAP_NONNULL(D1.c3) = SYS_OP_MAP_NONNULL(D2.c2)
) D1
where ( D1.c6 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
SAWITH2 D1
order by c1, c2, c3 ) D1 where rownum <= 500001

Physical Query when the level is left blank.
WITH
SAWITH0 AS (select sum(T758957.DISCOUNT) as c1,
T758944.NAME as c2,
T758947.NAME2 as c3
from
DIM_1 T758944,
DIM_2 T758947,
FACT_2 T758957
where ( T758944.DIM_1_KEY = T758957.DIM_1_KEY and T758947.DIM_2_KEY = T758957.DIM_2_KEY )
group by T758944.NAME, T758947.NAME2)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select distinct 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
cast(NULL as DOUBLE PRECISION ) as c4,
D1.c1 as c5
from
SAWITH0 D1
order by c2, c3 ) D1 where rownum <= 500001

 

Thanks,

Santosh

Advertisements

Posted in Dimensional Hierarchy, Dimensional Modeling, OBIEE 11g, OBIEE 11g RPD | Leave a Comment »

Fast Changing Dimensions

Posted by Santosh Kumar Gidadmani on April 12, 2011

Fast changing dimensions are those dimensions if one or more of its attributes changes frequently and in many rows. A fast changing dimension can grow very large if we use the Type-2 approach to track numerous changes. These dimensions some time called rapidly changing dimensions.
Examples of fast changing dimensions are
Age
Income
Test score
Rating
Credit history score
Customer account
status
Weight

How to solve fast changing dimensions
After identifying the fast changing dimensions attributes, you have to create a mini dimension table with these attributes joined directly to fact table and not snowflaking.

A mini-dimension is a dimension that usually contains fast changing attributes of a larger dimension table. This is to improve accessibility to data in the fact table.

Before creating a mini dimension table, you have to convert these identified attributes individually into band ranges. The concept behind this method is to take limited discreet values as shown below.

Rows in mini-dimensions will be fewer than rows in large dimension tables because it restricts the rows in mini-dimensions by using the band range value method.

After identifying the fast changing attributes of the primary customer dimension, and determining the band ranges for these attributes, a new mini-dimension is formed called Cust_Mini_Dim as shown below.

– Santosh

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

Slowly Changing Dimensions

Posted by Santosh Kumar Gidadmani on April 10, 2011

Designing a dimensional model that accurately and efficiently handles changes is a critical consideration when building a data warehouse.
Handling changes to dimensional data across time can be difficult, and dimensional attributes rarely remain static. A customer address or name can change, sales representatives come and go, and companies introduce new products to replace older ones.

A slowly changing dimension is a dimension whose attribute or attributes for a record (row) change slowly over time.

Types of SCDs
1. Type-1 (overwriting the history)
2. Type-2 (preserving one or more versions of history)
3. Type-3 (preserving one or more columns of history)
4. Type-4 (preserving one or more table of history)

Type-1 (overwriting the history)
The Type 1 methodology overwrites old data with new data, and therefore does not track historical data at all.
This may be the best approach to use if the attribute change is simple, such as a correction in spelling. And, if the old value was wrong, it may not be critical that history is not maintained.
Advantages:
• It is the easiest and simplest to implement.
• It is extremely effective in those situations requiring the correction of bad data.
• No change is needed to the structure of the dimension table.

Type-2 (preserving the history)
A Type-2 approach adds a new dimension row for the changed attribute, and therefore preserves history. This approach accurately partitions history across time more efficiently than other change handling approaches. However, because the Type-2 approach adds new records for every attribute change, it can significantly increase the database’s size.
Advantages:
• Enables tracking of all historical information accurately and for an infinite number of changes.
Disadvantages:
• Causes the size of the dimension table to grow fast.
• Complicates the ETL process needed to load the dimensional model.

There are three methods to handle type 2 SCD.

Method A – No surrogate key – Use timestamp

When a change happens, a new record is added into the table. All the attributes are copied from the previous record except the changed values. The nature key is copied as well so the timestamps is used to differentiate the records.

When a fact table is joined with the dimension, if you are interested in the historical data, the timestamp will be used as part of the join condition. To ease the join, the record typically use two date columns – the effective start date and the effective end date.

Method B – No surrogate key – Use version number

Instead of using the date column, a version number is used to differentiate the different versions of the records.

This technique requires the fact table store both nature key and the version number to retrive a given version of the dimension date.

Method C – Use a surrogate key

When an attribue is change, a sequence generated key is used, the fact table will also use this key column as the foreign key.

Type-3 (preserving one or more versions of history)
The type-3 approach is typically used only if there is a limited need to preserve and accurately describe history. The Type 3 method tracks changes using separate columns.
Advantages:
• Does not increase the size of the table as compared to the type-2 approach, since new information is updated.

Type-4 (preserving one or more table of history)
The Type-4 method is usually referred to as using “history tables”, where one table keeps the current data, and an additional table is used to keep a record of some or all changes.

– Santosh

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

 
%d bloggers like this: