Time condition in two contexts (shared calendar dimension)

system · June 22, 2004, 12:02pm

We have two facts and a shared calendar dimension.

Granularity is defined as follows for the fact tables:

ActualSales: YearID, MonthID, ASales
ForecastSales: YearID, FSales

The calendar dimension is defined as follows:

Years table (Years) fields:
YearID

Months table (Months) fields:
YearID
MonthID
MonthDescription

(please note that MonthID contains values in the form 1, 2…12 instead of 200301, 200302…)

We have two different contexts defined, one for each fact table.

“Actual” context:
ActualSales.YearID=Months.YearID
ActualSales.MonthID=Months.MonthID
Months.YearID=Years.YearID

“Forecast” context:
ForecastSales.YearID=Years.YearID

And, finally, there are 4 objects defined:
Year: Years.YearID
Month: Months.MonthID
ActualSales: ActualSales.ASales
ForecastSales: ForecastSales.FSales

Almost everything works OK, the user can request any dimension or measure and contexts take care of the joins so there’s no cartesian product. BO takes care of the number of queries as well, and synchronizes the results, so year works as a shared level of the calendar dimension, while month doesn’t. Filters in the form year=prompt or month in () work OK as well, Year is filtered in both contexts while Month is only filtered in Actual context.

Our problem: a time range condition. While is possible to define it at the report level, it appears quite difficult for the user:

Things get even more complicated when trying to extend this model to a Year-month-day calendar dimension.

What if we define a condition at the universe level, so the user doesn’t have to learn this logic? Answer: contexts are not applied.

We define the condition as follows:

When the user queries both facts/contexts at the same time:

Year, Month, ActualSales, ForecastSales

With these answers to the prompts:

The condition is aplied only to the first context, so no filter for year is performed in Forecast context! Results include ForecastSales for all years, including year 2004!

2002
Month Sales  Forecastsales		
11    12121  120

2003
Month Sales  Forecastsales
1	  123    240
5	  232

2004
Month Sales  Forecastsales
             300

The same condition at document level works fine!

2002
Month Sales  Forecastsales		
11    12121  120

2003
Month Sales  Forecastsales
1	  123    240
5	  232

This is a simplified example, our universe contains 12 contexts and 4 different granularities regarding the calendar dimension (down to the hour level). So a shared calendar dimension is highly desirable. We would also like to avoid breaking the universe (one universe for each one of the 4 time granularities), as this will bring the need to manually link the shared dimensions at the report level.

We have searched the forum for “calendar table”, “calendar tables”, “time dimension”, etc. to no avail. We’ve also tried AggregateAware solutions, definition of a MonthID as Year||Month, aliases only for filters, only one calendar table with a DateID…

But we think that this is such a common problem that there must be some kind of “by the book” solution.

Thanks in advance for your time and the light you may shed.

puxtarri (BOB member since 2004-06-22)

system · June 22, 2004, 2:10pm

The solution is to use (multiple) aliases of your Dates table.

One thread on this topic is Linking Summary Tables for aggregate aware.

JP Brooks (BOB member since 2002-10-22)

system · June 22, 2004, 5:37pm

Hmmmm, that thread is full of interesting ideas (we take note of the date parameters table one).

But we have two questions:

Aggregate awareness: our fact tables are not summary tables of the same measure but two different fact tables with different granularity (number of complaints per customer and year and sales for customer and month, for instance). Can we still use an aggregate awareness solution? i.e., define only the time dimension objects as aggregate aware (not the measures) (the “also” in your sentence above).

If we’ve properly understood what you say in the other thread, this is a solution when there’s more than one date in a fact, but we have only one date per fact. Are we missing something here?

We’ll try to get a copy of The Data Warehouse Toolkit, you’re really a good seller .

Thanks.

puxtarri (BOB member since 2004-06-22)

system · June 22, 2004, 7:24pm

Puxtarri,

Good questions! Now let’s see if I understand your situation and questions correctly.

No, you are right, you definitely don’t want to split up the universe based on aggregation; that is just one thing that @Aggreegate Aware saves you from.

In principle, absolutely, and probably for yours (without building a test universe I get a little lost in all your details! ). Just as one doesn’t (need to) define as @AggAware objects - measure or dimension - that are only from any highest level tables. Don’t forget to set the Incompatibility, under Tools|Aggregate Navigation.

Yes, but keeping the date table unaliased causes the problem you found: date conditions overwriting when the user queries both fact tables in the same query. By using multiple date aliases, this allows users to query on, for example, “…forecast_sales.forecast_date = [some date] AND actual_sales.invoice_date > [some date range]…” in the same query.

The date_parameters table, with one column being a function returning the multiple dates needed for the desired date range, results in simple and readable SQL. It also allows you to quickly and easily create simple universe condition objects like "…date_parms.parameter=“Rolling Previous 12 Months” or “Previous Fiscal Year”; looking at the begin_date and end_date columns in the universe lets you quickly check that your condition object will bring back the dates you want. Since this table is joined to the main date alias table by “date_alias.main_date_column BETWEEN date_parms.begin_date and date_parms.end_date” it can do this.

Of course you need to add all these joins to the appropriate contexts.

This is why we added this column to our date table, in addition to Year and Month, and join on it when appropriate.

I can promise you, you won’t be disappointed!

JP Brooks (BOB member since 2002-10-22)

system · June 24, 2004, 5:28pm

OK, we think we have a solution, so it’s time to give back to the forum .

First of all: JP, you are the man, your idea of a specialized table for filtering is in the base of our solution.

This is what we’ve done. Basically, we have created a new shared dimension, the calendar range filtering dimension.

Following our (Puxtarri and me jointly wrote the previous posts) example above, we’ve created new date tables:

YearsFilter table fields:
YearFID

MonthsFilter table fields:
MonthFID

(please note that, in this case, MonthFID contains values in the form 200401, 200402… instead of 1, 2…12, like in Months table)

And we have joined this new dimension to the facts using links like the ones we had before BUT with the range filters added to them. These are the new contexts:

“Actual” context:
join 1: ActualSales.YearID=Months.YearID and ActualSales.MonthID=Months.MonthID
join 2: Months.YearID=Years.YearID
join 3: ActualSales.YearID||ActualSales.MonthID=MonthsFilter.MonthFID and MonthsFilter.MonthFID between prompt(‘start year’)||prompt(‘start month’) and prompt(‘end year’)||prompt(‘end month’)
join 4: left(MonthsFilter.MonthFID,4)=YearsFilter.YearFID

“Forecast” context:
join 5: ForecastSales.YearID=Years.YearID
join 6: ForecastSales.YearID=YearsFilter.YearFID and YearsFilter.YearFID between prompt(‘start year’) and prompt(‘end year’)

(in our real universe we’ve used joins more optimized than these, these are just examples)

And, finally, we’ve created a dummy condition:

“time from to” condition:
where clause: YearsFilter.YearFID=YearsFilter.YearFID

How does this work? As long as users are only selecting time objects, only the time dimension is used. When they want to filter using a range they use the condition, and BO is forced to include the joins with the prompts, thus filtering every context with time conditions according to their granularity (as the joins have been defined according to the granularity of the fact table they join to). And what’s more, when they only query the “Forecast” context they only get asked for the start and end years!

When users want to filter for a particular month or year (instead of a range) they can use the Year and Month objects, as any other object:

Year: Years.YearID
Month: Months.MonthID

And that’s all.

JP, thank you very much for your help.

mprost and Puxtarri

mprost (BOB member since 2003-05-29)

system · June 24, 2004, 6:00pm

You’re welcome! Glad I was able to help.

JP Brooks (BOB member since 2002-10-22)

system · June 25, 2004, 3:20pm

I should add that, if I were doing this, instead of always making my users fill in prompted fields, I would also create pre-set condition objects for standard/commonly-used/strange date periods, such as ‘Previous Fiscal Year Plus One Quarter’ or ‘Next 13 Weeks’, or any standard time period.

Not only do these predefined conditions save your users the time and bother of constantly typing in dates, but they help ensure that different users and different reports are comparing and reporting the same data. When these date periods are left to users to determine, mistakes will inevitably be made, resulting in data reporting errors.

It’s true that - since when using a date_parameters table, each aliased Dates/Time table needs its own alias of the date_parms table - you will have to create multiple copies of these condition objects to point to the different date_parms tables. But with just a small bit of work you can copy and paste them into the different universe folders you have, change the description slightly and the table_name in the definition, and - voila! - you’ve just saved everyone a significant amount of future work and confusion. Congratulations!; you’re a hero!

Cheers!

JP Brooks (BOB member since 2002-10-22)

system · June 29, 2004, 7:02am

You’re right, we’re going to explore this kind of predefined conditions with our users.

Thanks.

mprost

mprost (BOB member since 2003-05-29)