OBIEE 11G - Conformed Dimensions and Alias Tables

I figured out that most of the newbies in OBIEE struggle with basic concepts of Data warehousing and how to deal with them. Among them is Conformed dimensions and Alias tables what are they and when to use these concepts.

Real World Problem 1: Conformed Dimension

You need to develop a report with a relationship like :

Products : P1 , P2 

Service Request : Customer calls for the Product P1 and complains issue.

Activities : Go and Fix the Product at Customer Site

Create a report for Open Service Request and details for Activities associated .

Solution using Conformed Dimension:

You will create Physical Model like : 

Here Fact Tables Service Request Fact and Activity Fact will have Product Dimension and Customer Dimension as Conformed Dimensions and same Dimension tables will be joined with joins like :

Activity Fact Model:

ProductDim.Row_wid = ActivityFact.Product_wid ;

CustomerDim.Row_wid = ActivityFact.Customer_wid

 ServiceRequest Fact Model:

ProductDim.Row_wid = ServiceRequestFact.Product_wid ;

CustomerDim.Row_wid = ServiceRequestFact.Customer_wid

 Report will have Columns like:

Customer Name	Service Request #	Activity #	Product Name	Service Request Details
ABC	S1	A1	P1	P1 is not working as expected
ABC	S2	A2	P2	P2 needs a battery replacement
XYZ	S3	A3	P2	P2 needs a battery replacement

Where you will get Activity Number and Service Request Number by Same Product and Customer Name.

So this is how and why you will use Conformed Dimension.

 Real World Problem 2 : Using Alias Tables

You need to develop a report with a relationship like :

Products : P1 , P2 

Service Request : Customer calls for the Product P1 and complains issue.

Activities : Go and Fix the Product at Customer Site

Service Request Open Date

Activity Created Open Date

Create a report for Open Service Request and details for Activities associated and to choose Prompts(Filters) by Service Request Open/Activity Created Date.

Solution using Conformed Dimension/Alias Tables:

You will create Physical Model like : 

Here Fact Tables Service Request Fact and Activity Fact will have Product Dimension and Customer Dimension as Conformed Dimensions and along with that , you will create two Alias Tables from Calendar Dimension Table in RPD Physical Layer and name them as Service Request DateDim and Activity DateDim  Dimension tables will be joined with joins like :

Activity Fact Model:

ProductDim.Row_wid = ActivityFact.Product_wid ;

CustomerDim.Row_wid = ActivityFact.Customer_wid;

ActivityDateDim.Row_wid = ActivityFact.Created_wid; //Where Created_wid is Activity Created Date wid

ServiceRequest Fact Model:

ProductDim.Row_wid = ServiceRequestFact.Product_wid ;

CustomerDim.Row_wid = ServiceRequestFact.Customer_wid;

ServiceRequestDateDim.Row_wid = ServiceRequestFact.Opened_wid; //Where Opened_wid is Service Request Opened Date wid

 Report will have Columns like:

Customer Name Service Request # Service Request Opened Date Activity # Activity Created Date Product Name Service Request Details ABC S1 9/1/2015 A1 9/1/2015 P1 P1 is not working as expected ABC S2 9/2/2015 A2 9/4/2015 P2 P2 needs a battery replacement XYZ S3 9/5/2015 A3 9/6/2015 P2 P2 needs a battery replacement

Where you will get Activity Number and Service Request Number by Same Product and Customer Name but with Service Request Date and Activity Date as separate dimensions.

So this is how and why you will use Alias Dimension.

Creating Alias Tables in RPD: Right Click on the Table you want to create Alias for-> New Object and Click on Alias and then Give this Alias table a name.

Alias Table to remove Circular Join

There is another term use to define Alias tables is they are used in order to prevent Circular Joins. lot of times in OBIEE we come across factless fact tables / or we need to join face table with another fact table, not recommended approach but during data modelling , we come across these scenarios. We use Alias Tables then to remove Circular Joins.

e.g Service Request Fact table consist of Service request Details, and there is another table that consists of Audit History for the Service Request which is again a transnational data and then you join the two transaction tables( Fact Tables ) based on Service Request, but then Audit Table also is used to fetch information to get Employee who worked on the Service Request.

See the picture below to understand how we resolved Circular Join using Alias Tables by Creating two Employee Dimensions.

Alias Table to have Self Join in OBIEE

Alias tables are also used to  implement Self Join in OBIEE as there is no concept of Self Join in OBIEE otherwise.

Alias Tables are used where you want to implement more than one relationship between fact and dimension based on multiple reference keys.

Suppose you have a Service Request with multiple date attributes like Created Date , Opened Date , Closed Date , Last Update date etc all for the same Service Request in Service Request Fact Table then you can create multiple Alias tables for the same Calendar Table to join with different keys.

Post your questions or comments below.