KPI Partners Blog

Comparing Arbitrary Time Periods in OBIEE

Posted by KPI Partners News Team on Thu, Nov 10, 2011 @ 11:07 AM

by Kurt Wolff

The Ago() “time series function” can be used to show data for a  previous time period, as long as the previous time period corresponds to a level that has been defined in the period hierarchy. A typical period hierarchy containing day, month, quarter, and year levels would allow you to use the Ago function to construct measures showing data for day ago, month ago, quarter ago, year ago (or N days ago, N months ago, etc.).

However, sometimes there is the need to compare facts in arbitrary time periods. For example, if an analyst wanted to compare sales from March 19 to March 23 2008 with sales during the period March 30 to April 12 2009 (not entirely fanciful – these were the two weeks before Easter in the United States in 2008 and 2009), the Ago function is not a solution.

In addition, use of the Ago function requires you to define separate measures for all levels, Ns, and base measure combinations that you might want to use. So if you had the base measures Quantity Sold and Amount Sold you could end up with separate measures for Quantity Sold Week Ago, Quantity Sold 2 Weeks Ago, Quantity Sold 3 Weeks Ago, …, Quantity Sold Month Ago, Quantity Sold 2 Months Ago, …, Quantity Sold Year Ago, Quantity Sold 2 Years Ago, …  and the same for Amount Sold and every other base measure. In addition, you might define variances or % Change measures around each of these combinations – i.e. Quantity Sold % Change vs. Week Ago, vs. 2 Weeks Ago, vs. 3 Weeks Ago, ….

This post is about designing a dashboard page that provides an easy way for users to select an arbitrary time period and an arbitrary comparison time period and have the measures aggregated over those two time periods along with variance or % Change calculations.  The basic functionality would look like this to the user. The user sets values in a dashboard prompt for the base period (for example,  2/7/2009 through 2/28/2009). Then he/she selects the comparison period (for example, 1/3/2009 through 1/24/2009. Then he/she clicks the Go button in the prompt.

Arbitrary   Picture1

 

 

 

 

 

Or the user could compare the period from 3/1/2009 through 3/18/2009 with the period from 2/8/2009 through 2/28/2009.

Arbitrary   Picture2

 

 

 

 

 

There are several approaches you might think of to provide a working solution. I’ll discuss two here, but there might be others.

The first involves creating two separate queries that have two different time constraints, unioning the results together, and then combining results using a pivot table.

There are some downsides to using a union, because with a union you give up navigation, you give up column selectors, you give up calculating a % Change, and you will have some  deficiencies with charts. The upside is it needs only a simple change in the metadata.

Using A Union (“Combining Similar Queries”)

This approach seems to work best if you create a second presentation column to enable setting two period constraints in a dashboard prompt.

Arbitrary   Picture3

 

 

 

 

 

 

Build a dashboard prompt having both “Date” and “Comparison Date” using the “is between” Operator with both.

Arbitrary   Picture4

 

 

 

Build a dashboard prompt having both “Date” and “Comparison Date” using the “is between” Operator with both.

Arbitrary   Picture5

 

 

 

 

 

The column formulas are
Geography.State,
Facts.”Quantity Sold”,
0, 
Facts.”Quantity Sold”.

Click “Combine Similar Queries” and construct the second query(you can take a shortcut here by copying the original query). In the second query, change the filter to use the Comparison Date column.

describe the image

 

 

 

 

 

 

 

 

 

The column formulas in the second query are Geography.State,
0,
Facts.”Quantity Sold”,
-Facts.”Quantity Sold”.

When you run the query (no constraints having yet been set on dates), you end up with something like this:

Arbitrary   Picture7

 

 

 

 

 

 

 

 

Then use the pivot table view to sum up the columns, grouping by the common non-aggregatable column values. Be sure to set the aggregation rule for each fact column (aggregation = sum).

describe the image

 

 

 

 

 

 

 

If you want to chart the results, you may need to use the pivot table chart view.

Arbitrary   Picture9

 

 

 

 

 

This is how it looks on the dashboard after date values have been entered.

Arbitrary   Picture10

 

 

 

 

 

 

Second Period Dimension and Fact Table Alias

Another approach is to introduce a second period dimension and an alias of the fact table. This requires more metadata work but you end up getting back all the features you forego with unions. Unions (and the other set operation queries using intersect and minus operators) were supposed to become “first class query citizens” several years ago, but have not made it (yet). In the meantime, we need to find other approaches. The steps are outlined here.

Create new aliases of the period table and fact table.Arbitrary   Picture11

Create physical joins. The table TIMECOMPAREPRODUCTS and TIMECOMPAREGEOG were the existing product and geography tables.

Arbitrary   Picture12

 

 

 

 

 

 

Add the second period table to the business model, including the logical joins.

Arbitrary   Picture13

 

 

 

 

Add a second period dimension to the business model.

Arbitrary   Picture14

 

 

 

 

Add a new logical fact table source.

describe the image

 

 

Create new comparison base measures and map them into comparison logical fact table source.

Arbitrary   Picture16

 

 

 

 

Because the new period dimension does not relate to all facts (nor, now, does the old period dimension) , set the level attributes of the base and comparison measures to the Total level for the dimension that does not relate to them.

Arbitrary   Picture17Arbitrary   Picture18

More than likely, you will want to create variance and % Change measures.

Arbitrary   Picture19

 

 

 

 

 

Add the new objects to the presentation layer.

Arbitrary   Picture20

 

 

 

 

 

 

 

 

 

 

 

You can now create dashboard pages that support arbitrary time comparisons along with regular navigation and drilling. Combining this with column selectors, the dashboard page gives users a lot of latitude to do the analyses they need without using Answers.

Arbitrary   Picture21

 

 

 

 

 

 

Put something like this together and, if you are curious,  see how simple the resulting SQL is compared to what happens with the Ago function!

Tags: Kurt Wolff, Oracle BI, Blog



Subscribe to the KPI Blog