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,410 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for the ‘Dimensional Hierarchy’ 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 »

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 »

Dimensional Hierarchy

Posted by Santosh Kumar Gidadmani on November 18, 2010

Dimensional Hierarchy – Preferred Drilldown – Number of elements at this level

A hierarchy is a set of parent child relationships between attributes within a dimension.

Dimension hierarchical levels are used to perform the set up aggregate navigation, configure level-based measure calculations and drill down from one parent to a child level.

Creation of Dimensional hierarchy.

There are two methods of creating dimensional hierarchies.

1. Automatic

2. Manual

Automatic Method:

Right click on the dimension and click on Create Dimension. This will create two levels total and detail level by default. You can change it to any way you want.

Manual Method: We will create Time dimension

1. On offline & online mode, Right click on the business model and select, New Object – Dimension.

2. Right click on the new dimension and select New Object – Logical Level, type as “Time total” for the Name and check the option, Grand Total Level and save it.

3. Right click on the “Time Total” level, and select the option, New Object – Child Level, name as Year and save it and to the same for Month.

4. Right click on the “Month” level, and select the option, New Object – Child Level, name as Time Detail and save it.

5. Drag year from logical table and drop it on Year level, drag month from logical level and drop it on month level and drag date from logical table and drop it on Time detail level.

6. Drag and drop the primary key of the time logical table to the time detail level.

7. Right click on the year logical column properties, on the levels tab, select year and click ok, do the same for month and time detail levels.

8. Select on year, month, time detail levels, select keys and add the same keys (check the option use for drilldown option).

9. Save and check on answers.

For time dimensional hierarchy, it is necessary to check the option “Time Dimension” on the general tab to support for time series calculation.

Preferred Drilldown

You will see a tab in the logical level called Preferred Drilldown

This option is used to force BI to move to any level specified in preferred drill tab. It is most commonly used to drill from one dimension to another.

Number of elements at this level

This number is used by the Oracle BI Server when picking aggregate sources. The number does not have to be exact, but ratios of numbers from one logical level to another should be accurate.

These Level Counts can be automatically known for one or more dimensional hierarchies using Estimate Levels.

Open the RPD in online mode, right click on any dimensional hierarchy or any BMM model, select estimate level, click yes for “Do you want to check them out?”. It will automatically update the level counts in the dimensional hierarchy.

– Santosh

Posted in Dimensional Hierarchy, OBIEE 10g, OBIEE 10g RPD | Tagged: | Leave a Comment »

 
%d bloggers like this: