Forum Stats

  • 3,874,064 Users
  • 2,266,669 Discussions


Understanding Date - Hierachy Dimension and so on

Hello everyone,

I am new to OBIEE. I have about 5 years good practise of Discoverer and now I am going to update my knowlegde to the new OBIEE.
I was able to create some nice repository based on some tables and views from my discoverer schema. This was more helpfull to me than the tutorials in the OBE.
But now I have some trouble with the dates, date hierachies in Discoverer were nice and easy, but in OBIEE I am more than confused.

While searching the web and the tutorials, I found a lot of tipps regarding the "ago-function". That looks nice, and I believe that I will try this out, but does not solve my current issue, because I am simply one or two steps before that. What I also see in the tutorials is a "times-table". A real table on the database, containing every date, and nearly all date/time-function values in seperate lines.

But for what? I mean: thats why I have date/time functions. In my Oracle Database.
Do I really need such a table? Should I copy it ouf of the SH-example into my productive-database? If yes, why?

In discoverer I simply click on "date hierarchy" and then I got my columns for year, month, quarter and day.
I understand how to create a dimension in the repository, based on some columns.
But when I only have one date column, and this might also contain some time information, what is the best solution here?

And then in practise: I have a view containing my sales data: order, item_id, customer_id, qty, amount, and hand full of dates:
- booked date -> when the order was booked
- request date -> when the customer wants to have the stuff
- shipping date -> when we send the stuff
How should I handle this. Should I then join all these dates with the times table?

I will be thankful for any helpfull links and more detailed explaination.
Best Regards


  • Prash11
    Prash11 Member Posts: 386
    It is always good to maintain all the date related fields in one table call it(Time dimension, calender dimension...), the reason for being said like this in obiee is for best modeling purposes. OBIEE best modeling approaches always insists on star schema with proper fact-dimension defined in the layers of the rpd.

    All the time series functions like Ago, To_Date can only be used only when we you have well defined time dimension.

    As you mentioned that you have some bunch of dates in your fact table itself, and I dont know if you can create a time dimension by adding a datewid or some thing to be your unique combination of the date.

    If you cant do that then you have to join your fact table back to your fact table(Self join) itself for you to be able to do any kind of year over year analysis or period over period analysis. (Comparing between base period/year and prev period/year)

    It all depends on your requirement and how we handle it. But for best practises purposes it is always good to have a time dimension to implement time series functions and to create a dimensional hierarchy for drill down purposes

    Hope it helps
  • Thank you for your comment, Prash. I still keep looking for helpful answers.
    Does someone know if there is any helpfull "select" to fill a times table with required information? My example contains only the years 1999 to 2002. But I need 2009+
  • Might anyone have some additional hints for me?
This discussion has been closed.