Oracle Communities

Oracle Analytics and AI

Child Item

One fact table and measure based on two different time dimensions

Hi all,

I'm using OBIEE 11g. Please kindly advise me with building RDP to support the following report:

There is one fact table containing retail sales with daily granularity.

The fact is linked to multiple dimensions such as Product, Sale site, Supplier, Vendor and each of them containins parent-child hierarchy (no problem with that) and time dimension (level based hierarchy - from Years as top level to Days as the most bottom level).

Requirement for the report is to conduct aggregation over these dimensions and times (ex. Aggregated Sales in 1st quarter of year 2018 for Product category named "Meat"; Trade margin ...; etc).

Is also required to select via prompt two time periods:

1. Standard time period (SP)

2. Referential time period (RP)

and compare agregated measures in these two separated time periods.

Then there will be some calculated columns which compares and combine these measures to one. For example: Sale index = Sales during SP / Sales during RP.

These time period could be overlaping (ex.: SP: from January 2017 to December 2017; RP: from July 2017 to July 2018) or be disjunctive (ex.: SP: whole year 2017; RP: whole year 2018).

I understand that it will be necessary to create a second time dimension (also a hierarchy) for the Referential period that uses as Logical Table Source aliased physical tables from the Standard time period time dimension.

Then create association from fact to this new time dimension in Business Model.

But when I do this and then select disjunctive time periods I get no results since the conditions is too restrictive.

How do I di this? Is it even possible?

Any advice is much appreciated.

Thank you,

Jan Pilar

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

These two time dimensions ... did you start with aliases in the physical layer? I'm wondering if you get no results because of the resulting physical SQL joins that result in how things are modeled.

[Deleted User]

Jan,

I'm curious: Have you considered role-playing facts with a canonical time dimension instead of canonical facts with role-playing time dimensions?

Many business users have a hard time comprehending multi-time-dimensions.

Jan Pilar

Thank you for your concern. I made this diagrams to explain current situation in my RPD and what I expect from the report. Hopefuly this will answer both questions (from Thomas and Christian).

Physical layer:

A_* means alias of the physical table

Untitled Diagram.png

Business model:

Untitled Diagramg.png

My expectations are:

Prompt:

Select standard period: (select begining date and ending date of period using calendar view)

Select refrential period: (select begining date and ending date of period using calendar view)

Result (example):

Partner	Sum of sales (during SP)	Sum of sales (during RP)
All partners ⯆	49 000	22 300

Thomas Dodds

what do the physical joins look like?

between DWF_SALES and DWD_TIME_DAY?

between A_DWF_SALES and A_DWD_TIME_DAY?

Jan Pilar

"A_DWD_TIME_DAY"."DTD_DAY" = "A_DWF_SALE"."DTD_DAY"
"DWD_TIME_DAY"."DTD_DAY" = "DWF_SALE"."DTD_DAY"

DTD_DAY column is Date data type. It is primary key of DWD_TIME_DAY table.

Regading table DWF_SALE - it is foreign key representing date of sale.

....

By the way I've noticed that if I specify only one time period in prompt (doesn't matter which one) I do get result in appropriate measure.

When I fill out both of prompts then there are none results. (See following picture)

Robert Angel

And what are the equivalent logical joins here for the dates?

And can you post the physical sql that is generated when this runs?

And looking at your other joins am I correct that the two fact tables conform on everything apart from the time dimensions?

Do any of the other dimensions feature in the analysis that could be failing to have commonality in the query you are writing?

And can I endorse @Christian Berg 's sentiment of have you considered not having multiple time dimensions?

If this were my problem I would see if I could solve it with an alternative hierarchy on your time dimension and some additional modelling on the ETL that populates your fact to give you the referential period functionality.

If this is not possible it may also be possible to use a combination of physical calculations and logical time series calculations to achieve your requirement.

Jan Pilar

I've answered myself apparently.

I was able to do what was required with the existing state of the physical layer (see pictures above).

The only change I had to make was to change these two measures to level-based measures in business model.

I set logical level of the dimension Time RP for Sale measure in table Sale SP to top-level of hierarchy ("Years" in my case).

And vice versa I set logical level of the dimension Time SP for Sale measure in table Sale RP to top-level of hierarchy.

Now I'm able to display values in both measures in the same time while appling filter with both time dimensions.

This article (Solution #2) really helped me with resolving this issue:

https://greatobi.wordpress.com/2011/06/24/making-dims-facts-work-together/