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!

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 »

ODI Installation 11.1.1.7.0 Steps on Windows 64 bit System

Posted by Santosh Kumar Gidadmani on May 3, 2015

Prerequisites

  1. WebLogic Server 10.3.6.0. Click here for WebLogic upgrade steps.
  2. Install JDK 1.6.0_35
  3. RCU Installations for Repositories. Click here for RCU Steps. (Optional). You can create Master and Work repositories using ODI Studio.

Note:

  1. Download ODI Installation files from the Oracle and unzip both the files in the same directory folder.
  2. Make sure the JDK folder path doesn’t have spaces and JDK location should be on the same drive as the installer.
  3. The java version should be 1.6.0_35+. Java version greater than 1.6 will not work.

Steps:

Click on the disk1 64 bit install file

It will ask for Java path, provide the below path…D:\Java (if you have installed the JDK in D:\Java)

ODI_1

So copy Java path and right click on this window bar -> mark ->paste to paste the path.

ODI_2

Click on Next.

ODI_3

Skip the software updates and click on next.

ODI_4a

Click on all options or required options per requirements. Click on next.

On the next screen, click on next if all checks passed, if you are installing on windows 8, the operation system certification may show error, click on continue and click on next.

ODI_5

Click on Next.

ODI_6

The installation will detect the WLS directory. Click on next.

ODI_7

If you want to configure repository at this stage, click on configure repositories or click on skip repository configuration. I have skipped the configuration as I have already installed the Repository.

On the next screen, provide the agent name and agent port. You can also skip the agent configuration by unchecking the agent option in the select installation type section. I have skipped

ODI_9

Click on install to start installation of the ODI.

ODI_10

Installation in progress.

ODI_11

Click on Next.

ODI_12

Click on finish after successfully installing ODI.

To check the repository connection, open the ODI studio.

Check the repository details using the SQL developer.

ODI_13

And provide below connections to create the work repository.

ODI_14

Select the repositories and click ok.

ODI_15

The studio is connected.

Thanks,

Santosh

Posted in ODI Installation, Oracle Data Integrator | Leave a Comment »

ODI RCU Installation Steps

Posted by Santosh Kumar Gidadmani on May 3, 2015

Download the ODI RCU installation file from Oracle.

Click on D:\OBIEE_Softwares\ODI\rcuHome\BIN

WebLogic14

Click on next

WebLogic13

Click on Create and click next

WebLogic12

Provide the system host name, port number and SID. Provide sys user and password. Click on next.

WebLogic11

Click on Ok

WebLogic10

Click on Next

WebLogic9

Click on Ok

WebLogic8

Provide the password and click on next.

WebLogic7

Provide the above information to create the master repository.

WebLogic6

Click on Next.

WebLogic5

Click on Ok

WebLogic4

Click on Ok

WebLogic3

Click on create the repository.

WebLogic2

The repository is created, click on close.

Check the schema as below.

WebLogic1

Thanks,

Santosh

Posted in ODI Installation, Oracle Data Integrator | Leave a Comment »

 
%d bloggers like this: