OBIEE: Multiple joins between same tables (Fact to Dim)

Hi all. I am finally writing a new post after more than 1 year and surprisingly it is not on SQL Server! I must confess that I am not a front-end kinda person and do not particularly enjoy doing dashboards and reports. But I recently started a new job and my first project is going to be on OBIEE.

Since I best learn by doing and diving into getting my hands dirty (instead of reading pages and pages of tutorials), I defined a new project for myself that I am not gonna bore you with. What we need to know for the sake of this post is that I have a fact table with 5 columns:

Buyer_ID

Product_ID

Seller_ID

Sale_Date

Sale_Qty

The twist here is that both Buyer_ID and Seller_ID point to the same dimension table, DimCustomer. Since we can add each table to the Physical layer of Oracle BI Administration Tool only once, I was wondering how I should model my data model in OBIEE.

Basically what we need to do is to tell OBIEE to join the fact table twice to DimCustomer:

  • First on Fact.Buyer_ID = DimCustomer.Customer_ID
  • Then on Fact.Seller_ID = DimCustomer.Customer_ID

And we already know that the physical structure of queries sent to data source is defined by how objects are related and joined to each other in Physical layer of BI Administration Tool. Therefore, we need to find a way to create 2 objects in Physical layer based on DimCustomer. Well, this is very easy: it can be done by creating an Alias based on the original DimCustomer.

 

Screen Shot 2015-02-03 at 1.38.25 pm

 

 

 

 

 

 

 

From here, everything is very straightforward:

  • On General tab, give the new Alias a name and make sure it is based on the physical table you want it to be by checking Source Table right under Name textbox;
  • If this Alias is going to be used as a dimension table, define the primary key of that table on Keys tab. You’ll need to enter a name in Key Name column and then choose the actual column from Columns drop-down to be the primary key of this new Alias table.

And now you are done. You’ll see a new entry in Physical layer as a table that you can use in your physical diagram and use it to define your model properly.

Hope this post will come in handy, cheers.

 

 

Advertisements

About Saeed Barghi

Big Data/DW/ETL Consultant based in Melbourne, Australia View all posts by Saeed Barghi

8 responses to “OBIEE: Multiple joins between same tables (Fact to Dim)

  • Seifolah

    Hi Saeid,
    thanks for your article.
    I have same problem.
    My fact table f_pr have created_date,Authorized_date,close_date.
    I have time dimension.I want to show this report in hierarchy mode

    date | created | authorized | closed
    ————————————————-
    2007-q1 | 50 | 40 | 30
    2007-q2 | 60 | 20 | 10
    2007-q3 | 10 | 14 | 11
    2007-q4 | 67 | 28 | 22
    2008-q1 | 20 | 13 | 8
    2008-q2 | 55 | 25 | 20
    2008-q3 | 75 | 35 | 20
    2008-q4 | 90 | 20 | 2

    have you any idea?

  • Saeed Barghi

    Hi Seifolah,
    What is the first column, date?
    If it is supposed to come from the time dimension, you need to determine which column from your fact table should be used to join to time dim, implement the join, and get the “date” column from dimension table.

    Cheers,
    Saeed.

  • Seifolah

    Thanks Saeed fro your reply.
    The first column is date,and i get it from Time hierarchy !But i’m in doubt about join column!
    Suppose I want to count authorized items in specific duration,so i should to join authorized_date to my time dim.but i also need to count closed items in same time,again it seems i should join on closed_date column.etc..
    I’m in doubt to create multi dims or multi fact that each one contains specified column to join !..

  • Saeed Barghi

    That’s when you create a new alias of dim time table. And then join your fact table once to the actual table that is added to physical layer on “authorized date” and then join fact table to the alias you created on closed time.
    This should do the trick.

  • Rohit

    In the above scenario do we have to drag the alias table to the BMM Layer and then to the Presentation layer or there is no need,If there is no need then it means that we are creating alias table just to define another join, please reply on my understanding as i need to implement an similar scenario, thanks.

  • Saeed Barghi

    Hi Rohit,
    Yes you need to drag them all the way up to presentation layer. Remember, the whole point is to enable querying fact table by those dimensions as required. Without having them in the presentation layer the users won’t have access to them.
    Hope this helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: