OBI EE, Time Dimensions and Time-Series Calculations

April 30th, 2007 by

I’m currently working on a paper on Oracle BI Suite Enterprise Edition (OBI EE) for Discoverer users, for the upcoming ODTUG conference at Daytona Beach, and a tricky area that I’ve come up against recently is getting the metadata layer in a state suitable for performing time-series queries. One of the strengths of Discoverer is it’s support for analytic and time-series (lag, lead, window etc) queries and any customer looking to migrate to Answers and Interactive Dashboards would want to make these new tools have similar capabilities.

Taking a look through the OBI EE documentation, it appears there is some basic support for time-series queries, in the form of AGO and TODATE. AGO gives you, for example, the value of sales one month ago or one quarter ago, whilst TODATE gives you the total of sales month-to-date, or quarter-to-date, or year-to-date. Both of these time-series functions rely on the existence of a time dimension in your business model and mapping layer, with the period you can refer to in the AGO and TODATE functions being determined by what levels you have in this time dimension. I found, however, the time dimension a bit tricky to set up, but now I’ve got it working, I thought I’d jot down some notes on how the process works.

As a quick recap: dimensions in OBI EE define drill paths in your data, and are a prerequisite if you want uses to be able to drill down from say all customers, to region, then warehouse and finally ship to.

Each level has at least one logical key, the top level is marked as the “grand total” level, and you can drag other attributes in to each level and have OBI EE display those, rather than the level key, when users drill down in reports. At present, you have to create these dimensions manually when setting up your business model and mapping layer, although there is talk about automatically creating these when importing Oracle dimensions or OLAP structures, or when using the soon-to-be-delivered Discoverer migration utility. In my case though, I create them all manually which is usually a five or ten minute job at most.

For the migration paper I’m working on for the ODTUG event, I’m bringing across the Videostore example dataset that comes with Discoverer Administrator, on the basis that everyone running Discoverer has got it and most people are familiar with its contents. To keep things simple, I’m initially working with the main sales fact table, SALES_FACT and the three dimension tables it links to, TIMES, PRODUCT and STORE. One additional step I had to carry out though to get everything working was to create two additional views in the VIDEO5 schema:

A view over the TIMES table to add additional month and quarter attributes that are used to display month and quarter names, and to permit their sorting (like alternate sorts in Discoverer)

And a view over the SALES_FACT table to allow it to be joined to the TIMES_VIEW view on the transaction_date column (a date datatype) rather than the TIME_KEY column (a number datatype) – this is because I found I needed to make the primary key for the logical TIMES table a date, rather than a number, to get the time-series offset calculations working:

Theoretically, at least the view over the time dimension shouldn’t be necessary, as you can create virtual (calculated) columns within OBI EE, but when I tried to do this, although the columns created OK I had trouble using them with the AGO and TODATE functions; it may also be the case that you can use non-date columns for the time dimension primary key, but again I had problems getting this working. What I’m showing here is what eventually worked for me, but feel free obviously to try and build things without these views, and let me know if you get it working.

Anyway, once the views are created, I then imported them in to the physical layer of the common enterprise information mode, created a primary key key on the TIMES_VIEW.TRANSACTION_DATE column, and foreign keys on the SALES_FACT_VIEW.PRODUCT_KEY, TRANSACTION_DATE and STORE_KEY columns, linking back to the relevant tables/views. Once this was done, I dragged the whole set of Videostore tables and views over to the business model and mapping view to create a set of logical tables, like this:

After running the rename utility to put all the column and tables names in to sentence case, remove the underscores and so on, it was time to create the time dimension, which I called TimesDim.

Creating a time dimension is initially the same as creating a regular dimension, except on the dimension properties dialog you tick the “Time dimension” tickbox, like this:

Then, I created five levels for the dimension:

  1. All Years
  2. Year
  3. Quarter
  4. Month
  5. Day

and then dragged the TRANSACTION_DATE column from the TIMES_VIEW logical table on to the day level, the MONTH_IDYYYYMM column on to the month level, the QUARTER_ID_YYYYQ on to the quarter level, and left the year level empty as it’s a grand total level. For the day, month and quarter levels, the columns dragged on to them were registered as logical level keys and also as the primary key for each level – the rationale here being that in DATE, YYYYMM and YYYYQ format, column values were sortable which is a requirement for performing rolling-window time-series calculations.

Next, to finish off the dimension, I dragged the MONTH_MON_YYYY column on to the month level, added it as a level key but kept the previous value as the primary key, like this:

I then edited each level key and ensured the “Use for drilldown” checkbox was unchecked for the YYYYMM value, but checked for the second, MON-YYYY value, so that the correct column was used for organizing the level values but users saw friendly looking month names when they drilled into time dimension values.

After doing the same for the Quarter dimension, I then went back to the day, detail level and carried out one more task, and the one that’s essential for a time dimension – marking one of the logical keys as being the Chronological Key. In my case, I marked the key created using the TRANSACTION_DATE column as being the Chronological key, which worked for me.

According to the docs, the chronological key has to be a “monotonically increasing value” which stumped me a bit; I tried using just a number field but I couldn’t get the time-series queries to output the correct values for month ago sales and so on, but this could more be down to my unfamiliarity with the feature – again, if you know better, let me know.

Once everything was done, and I added the other detail day-level attributes into the day level, my dimension looked like this:

with each level containing the following columns:

  1. All Years Level (grand total level) – no keys or columns added
  2. Year Level – YEAR_YYYY column, set as primary key, set as logical key
  3. Quarter Level – QUARTER_ID_YYYYQ set as primary key and logical key but with “use for drilldown” disabled, together with QUARTER_Q_YYYY set as a logical key and enabled for drilldown
  4. Month Level – MONTH_ID_YYYYMM set as primary key and logical key but with “use for drilldown” disabled, together with MONTH_MON_YYYY set as a logical key and enabled for drilldown
  5. Day Level – TRANSACTION_DATE set as primary key and logical key and enabled for drilldown, DAY_OF_WEEK and HOLIDAY_FLAG set as attributes and not used for drilldown.

The next step was to tell OBI EE to sort the MONTH_MON_YYYY column by the MONTH_ID_YYYYMM column, and the QUARTER_Q_YYYY column by the QUARTER_ID_YYYYQ column, as whilst the latter columns were more readable (“Q3 2000”,”Jun-1999” and so on) they don’t sort properly by default (the year would start “Apr-1998”, followed by “Aug-1998” and so on). To do this, I double-clicked on the columns to be sorted and picked the other columns as the one to sort them by, like this:

Now the time dimension and logical table were set up, I could create my time-series functions. To start off, I used the AGO function to calculate the sum of sales one month ago, based on the SALES_FACT.SALES column, using the following formula:

AGO(Videostore."Sales fact view".Sales, Videostore.TimesDim."Month", 1)

…with "Sales Fact View".Sales being the base measure, Videostore.TimesDim.Month indicating that within our time dimension, we’re going to use months as the offset, and 1 indicating that we’re going back 1 month.

So, now it’s time to test it out. After dragging all the logical columns to the presentation layer and saving the changes to the repository, I started up Oracle BI Answers and ran a query showing sales month-by-month with last months sales next to them, like this:

Not bad. Running it at the day level gave similar results.

Using the expression editor, I created another logical column, this time using the TODATE function to create month-to-date values, using the expression:

ToDate("Videostore"."Sales fact view"."Sales" , "Videostore"."TimesDim"."Month" )

with "Sales fact view"."Sales" being the base measure, and "Videostore"."TimesDim","Month" indicating we’re going a month-to-date (as opposed to, say, year-to-date) total.

Then, running the day-level report created a moment ago, along with this new month-to-date attribute, gave the following results:

Looking back, the tricky bits were firstly, adding all the derived month and quarter values for the times logical table, which I tried doing using the EXTRACT and CAST features of BI Server SQL, but in the end found much easier just deriving as part of an Oracle view. The second tricky part was working out that the chronological key was only defined at the bottom (day) level, and that it should be a date datatype – this might be wrong, but it’s the only way I got it working. The final tricky bit was sorting the month and quarter descriptive columns by the YYYYMM and YYYYQ columns – without these, either the months and quarters were listed in the wrong order, or worse, they were in the right order but the time-series calculations were sometimes wrong. Going down the route I’ve outlined in this article made it work for me, but as I say if there’s a simpler way just let me know.

Comments

  1. Peter Scott Says:

    You could define your database view as a “select table” in the physical mapping pain of BIEE administration.

    I constructed a demo for a customer once using other techniques for the YTD and PY functions – (OK, I adapted the PAINT demo!):
    PY was a table alias to my fact table with a date FK to “same date last year” in my calendar table

  2. Peter Scott Says:

    oh, the joy of hitting submit too early :-)
    continued…
    YTD was constructed as SELECT TABLE joining to a range driver in the calendar table.

    Of course, what works best for you is down to circumstances – try before you buy!

  3. Be Ice: Oracle BI Consultancy » Blog Archive » Time Series in OBIEE compared with Discoverer Says:

    [...] just finished reading through Mark Rittman’s post titled OBI EE, Time Dimensions and Time-Series Calculations Here, Mark goes on a wee journey to look at how time-series queries in OBIEE (as the title suggests [...]

  4. Borkur Says:

    Thanks a lot for the writeup, Mark!
    I am about to venture in to the “real implementation phase” for a client after a few weeks of “testing and poking”. I will be keeping this nugget handy :)

    Cheers,
    Borkur

  5. Krishnan Viswanathan Says:

    Creating Logical columns within BI EE for manipulating the Date /Time functions are fairly straight forward using the rich set of Date/time conversation functions available within the Expression Builder UI.
    For Example, to extract a Year from the date column you could use the syntax :

    “Year(transaction_date)” within the expression builder.

    Its also possible to reuse the logical column for additional manipulation. For Example, if the Logical column created above of named “YYYY”, you can use that to define a Quarter as follows :

    ‘Q’ || cast(Quarter_Of_Year(transaction_date ) as char(1) ) || ‘-’ || cast(YYYY as char(4) )

    The above method would not necessitate the need for creating additional views over the dimension table and fact tables as your example suggests.

    A note of caution, for any real life deployment it is highly advisable to extend the Time dimension table in the DB to include these columns and avoid having to deal with complicated SQL’s later during optimization and debugging.

    Coming to the next concept of “Chronological Keys” within the BI EE, the premise for chronological key itself is fairly straight forward.
    As an Administrator, you need to let the BI server know which columns can be sorted in a Chronological order. These columns need not restricted to date columns, but can be numbers or even varchars. Examples would be Year (like 2006, 2007, 2008…) or Year-Qtr (like 2006-Q1, 2006-Q2 …).

    Hope this clarifies some of the issues and concerns you have noted.

    Regards
    -Krishnan

  6. Mark Rittman Says:

    Hi Krishan,

    Thanks for the comments. Just to clarify a couple of points

    - are there any restrictions on using derived columns as you suggest, as dimension keys? I thought I got an error trying to do this, but it could be a co-incidence.

    - can you explain further on what constitutes a column that is sortable in chronological order. Would values such as “September 2007″, “October 2007″ qualify, if so, how would it know how to sort them? What constitues sortability? Also, is there any benefit/value in defining chronological keys for each level in the time dimension?

    thanks

    Mark

  7. Krishnan Viswanathan Says:

    Mark,

    There are no restrictions on using the derived columns and they can be used as any other column in Answers and Dashboards.

    To clarify your 2nd part of the question, BI server natively doesn’t understand chronological sequence. It only understands the regular sequence (like ASCII). So, it obviously doesn’t sort values like “Sep 2007, Oct 2007…” and the method you used in your example for using the “sort order” is the right way to go about sorting these columns.
    Hence, you would not use these values (like Sep 2007, Oct 2007…) as chronological keys, but if you have columns with values like (200709, 200710…) which are sortable and also provide chronological sequence, they can be used as “Chronological key” in the BI EE server.
    Alternatively, you would define numeric values (as month_id or Quarter_id ) which are sequences and use that as chronological key for that level of the time dimension.

    There are benefits of defining the chronological keys in different levels in the time dimension especially if you are aggregating over time.
    For example, if you have a fact aggregated over month AND you have a corresponding month dimension table, you would then define the chronological keys at “day level” for base fact and at “month level” for aggregates. This will let the BI server navigate to the Month aggregate for Time series calculations for Month level and above.

    Regards
    -Krishnan

  8. EyesOnly Says:

    I want to know how to create the table times.I can’t find it.Please send me a script which including the date.Thanks
    My email address:
    dongkai.kang@hotmail.com

  9. Adrian Ward Says:

    Hi Mark

    - The thing about using a sort order, remember that the field that is used for sorting will be used in the SQL sent to the database in the group by and order by sections, so has to be at the same ‘level’ e.g. Month or quarter.

    - My advice is to create data in the datawarehouse at ETL time, and not create common fields on the fly.

    - One of the big challenges that clients like to pose is using a non calendar financial periods. Beware the solution offered by Siebel/Oracle, it is not easy for customers to maintain.
    My advice is to use S_PERIOD as a source if you can (i.e. you have the CRM db!)

    Adrian

  10. Mark Rittman Says:

    Donkai,

    The TIMES table is part of the Videostore (VIDEO5) sample data that comes with Discoverer Administrator. If you install that dataset when creating an EUL, you’ll get the TIMES table and all the others. If you’ve not got Discoverer available, you can do something similar by selecting against your own fact table using the transaction date (or similar) column to provide a list of dates, assuming this column has a DATE datatype rather than just an integer.

    regards

    Mark

  11. S Khan Says:

    Just a quick question, when you have many dates within the application, e.g., effective date start, effective date end, etc. and when you are trying to create a Time Dimension, what steps you need to create a single Time Dimension? Have not used Oracle Warehouse Builder yet, but conceptually I would like to know. I am currently using Oracle HRMS 11.2 and is investigating what to use for developing Warehouse. I can create all other dimensions but looking at different dates that needs to come to the Time Dimension, I am not sure how to create a single Time Dimension. I am assuming that if we end up using Oracle Warehouse Builder, it may build the dimension using the wizard..?

  12. Временные изменения показателей | demyanov.ru Says:

    [...] прочтения заметки Марка Риттмана об анализе временных изменений, [...]

  13. jayalakshmi chidambar Says:

    now i have a doubt.plz anybody help me out.
    i have to have two dates as “sanction date” and “fund release date” as two dates in dimension table (time_by_day).how can i take two time_id’s in fact table.plz tel me

  14. praveen Says:

    Thank you. your article helped me a lot

    regards,

    praveen

  15. Ross Goodman Says:

    I am trying to use this on a “real world” example, specifically trying to get the todate() function to work.
    I’m getting:
    “State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22040] To use TODATE function, the storage level of the query (‘[TIME.Week Number]‘) must be a static level. (HY000)”
    Any ideas?
    Ross

  16. Mark Rittman Says:

    Ross,

    I had that error as well, I think it came about when I derived the various time dimension level values using OBIEE functions (EXTRACT(), I think). I went back to creating the time dimension using an Oracle view, then imported this into OBIEE and based the time dimension of off this, the error went away then.

    As there’s unfortunately no publicly available error guide for OBIEE though, I don’t know what this error really means, or whether it’s due to what I think or was solved by what I did. Now I use a time dimension SQL view, or table, though, the error doesn’t occur.

    Is this what you were doing?

    regards,

    Mark

  17. Ross Goodman Says:

    In this case we actually have a time dimension table. We are using only physical table columns. The only deviation is that the lowest grain is week. All tables are keyed by year_no, week_no.
    I have a week_ending column, I have used this as the chronological column.
    The only reason I mention this is because the error referrs to week number but my formula is trying to get a YTD so is using year.
    Ross

  18. Irene C Says:

    Thank you so very much for this article! Your steps and explanations were thorough. I was able to get past my Month-to-date issue.

  19. Patricia Keane Says:

    Hi, can anyone help with ideas on how to set up a YTD or MTD for previous year or month to the current date. With relation to the example in this article for month ago sales if we ran this e.g. on 21st Jan 2000- can we set up another field that would show month ago Dec 1999 till the same date i.e. 21st Dec 1999 ? I am trying to achieve this for a YTD but should be same idea.

  20. Pete Scott Says:

    @Patricia
    Do you have the example “PAINT” BIEE example set up – in that they use an alias table set up in the physical layer to allow dates to be mapped to another. They use for last year reporting. It is worth a look.

    The other option is enrich your date dimension table in the data base to include columns like same_date_last_year, same_date_last_month etc and use these values in the fact selection

  21. wildmight Says:

    Great post! I really appreciate it. I would like to join Patricia’s question. I’d think the solution is to combine AGO and TODATE together. Just not sure how to. Maybe like this – sales YTD – TODATE (sales, year-dim)…sales PY YTD would be AGO (sales YTD, year) – would this work?

  22. Wildmight Says:

    Patricia, I’ve solved this problem. I’m not sure why my previous post isn’t shown, but here’s how I did it – it’s a combination of AGO and TODATE.
    You create a value – let’s say Sales MTD by just how Mark showed us – TODATE (Sales , month – dimension)…then you create a logical column with the following formula AGO (Sales MTD, Month Dim, 12)
    that should give you a MTD measure you’re looking for. I hope this is clear. Please let me know if it works for you.

  23. j Says:

    I did the YTD, MTD using the example. However, I also need to do a rolling 12 month calculation in my report along with YTD, PY YTD. I’m not sure how I can achieve this? Thanks.

  24. j Says:

    The article is very helpful. I’m not sure how I can solve this problem where I need to have YTD, PY YTD and rolling 12 month calculation. I got YTD and PY YTD calculations but not rolling 12 month calculation. I’m having trouble doing the rolling 12 calculation.

  25. Mark Rittman Says:

    Hi J,

    Offhand, I don’t know how to do it, certainly the AGO and TODATE functions won’t give you the data you need. If I was running against an Oracle database, I’d probably use the EVALUATE() function to pass through a call to an Oracle Database analytic function to perform the calculation, I’m sure one of the LAG, LEAD or WINDOW functions would give you the results you’re looking for.

    regards, Mark

  26. Rittman Mead Consulting » Time Series in OBIEE compared with Discoverer Says:

    [...] in OBIEE compared with Discoverer I just finished reading through Mark Rittman’s post titled OBI EE, Time Dimensions and Time-Series Calculations Here, Mark goes on a wee journey to look at how time-series queries in OBIEE (as the title suggests [...]

  27. Vivacious Says:

    Hi, good article. AGO worked fine, however I am getting same results that of MEASURE values with TODATE.

    e.g
    Month Sales MAGO MTD
    Jan 10 10
    Feb 20 10 20
    Mar 30 20 30

    Whereas I am expectin MTD values to be 10,30,60… Please help

  28. SChang Says:

    I have a question (and hopefully not a problem). I am using the AGO function and it works fine when I have multiple months selected in the filter or report but if I only have one month selected then the previous month value shows up as null. Any ideas?

  29. Peter Scott Says:

    @Vivacious
    I think that you are hitting a bug [fixed in OBIEE 10.1.3.3] that may occur against certain Oracle database versions and the way in which data is fetched from the database. You only see this if the report contains both AGO and TODATE. There is a work around that can be applied – you need to change the setting for one database properties in the OBIEE admin tool. Oracle support should be able to tell you which one

  30. SChang Says:

    Peter,

    Thanks. My mistake for not providing enough details. We are on 10.1.3.3 and currently, we only have the AGO function in the report (and business model). Hitting my head on the wall…I don’t think it’s the time dimension because then it wouldn’t work with multiple months selected either. Could this still be a OBIEE Admin setting? cache setting? something?

Website Design & Build: tymedia.co.uk