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.
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.
In Data we trust 
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?
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.
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 !..
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.
Thanks a lot Saeed,
It works!
khosh bashi 🙂
you’re most welcome mate, shoma niz. 🙂
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.
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.