Physical & Business model design with fact tables with different granularity

[Daniele DeFaveri]

Hi Guys,
I have a doubt about how to model the follow situation in the Physical
& Business model of the OBIEE.
Simplifying a lot the situation:
I have two fact tables
FT_1 (Month,Measure_1) which data have month granularity
FT_2 (Day,Measure_2) which data have day granularity

and a Time dimension:

D_TIME(Day,Month,year) which data have day granularity

I would like to create a business model and a subject area of
presentation with these 3 tables in this way (D_Time, FT_1 month ,
FT_2 day).

How do you suggest to design the physical layer (join between tables
etc.) and the business models to obtain this model so that it could be
transparent to the final user?

Thanks in advance!
Regards,
Daniele

[Jeff McQuigg]

Physical: You need a month grained table which it looks like you
don't have. If you really can't make one (recommended), then use
either a view or an opaque view. Something similar to : SELECT
DISTINCT MONTH FROM D_TIME. Then do your two joins: FT_1 to
Month(table or view whichever you pick) and FT_2 to Day using whatever
fields you have available to you..
Business: Make a single date dimension, with 2 Logical Table sources.
One for the Month table and the other is day table. Put them into a
hierarchy.
Its up to you if you want to make 2 logical facts or 1 - it really
doesn't matter aside from imparting business meaning. In either case
you'll need to make 2 separate LTS, 1 for FT_1 and another for FT_2.
Make sure you set the content tab on all 4 LTSs in your model.
Presentation is also up to you. I suspect that you have more than 2
metrics in real life, so how you organize it is pretty flexible - just
be consistent and think about ease of use.

Jeff M.

[Stewart Bryson]

Nicely done Jeff.

Sent from my iPhone

> --
> You received this message because you are subscribed to the Google
> Groups "OBIEE Enterprise Methodology Group" group.
> To post to this group, send email to
> obiee-enterpri...@googlegroups.com
> To unsubscribe from this group, send email to
> obiee-enterprise-met...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/obiee-enterprise-methodology?hl=en
>
> All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

[Daniele DeFaveri]

Hi Jeff,
very good, this is what I want to hear and what I make on my BM, but I
found out some problems on this kind of modelling.
In particular it looks to work and to be a good model, but I found
problems in using timeseries.

In details, I build a model exactely as you said:
Physical
FT_1.month = V_TIME_MONTH.Month (where V_TIME_MONTH is a table
with the distinct of the year-month)
FT_2.Day = D_TIME.Day

Business
FT_1 (logical source FT_1)
FT_2 (logical source FT_2)
D_TIME (logical source FT_1 & FT_2) the leaf of this dim is the
Month (no info of day inserted)

So, in this way it looks great from my point of view, If I create
reports on the FT_1 at month level it works as espected, If I create a
reports on the FT_2 at day level it works.
The problems begin if I try to creates a AGO measure on the measures
of the FT; something strange happens and it does not works as
aspected.
Moreover if I have to create a Month to Date measure on the day Fact
table it could work only if I insert also the day info in the logical
dim table and hierarchy, but in this way the time series functions
have problems on the FT of month.

So my doubts.. Is this kind of model valid in any situation?

Any feedback will be apreciated.

Regards,
Daniele

[Robert Tooker]

This looks very familiar. I've had a similar scenario with weekly and daily data where some of our measures are only available at the weekly grain and others at daily grain. Some of the weekly grain measures are required to be reported along side the daily measures but aggregated over the week. Additionally, some of the weekly measures need to use time series functions and the last aggregation function.

My solution was to create a daily calendar table, and from that a weekly calendar table (like a mini dimension). In the BM&M I created a weekly logical table and a daily logical table. The weekly logical table has two sources - week and day - to support side by side at week level, and the daily logical table also has two sources - week and day - to support aggregated tables of the daily information (not necessary in your scenario). Hierarchies were created in both and levels set appropriately.

The subject areas were then daily and weekly based, ie measures that we only store at week would only be in weekly subject areas, but measures stored at day grain can be in both. The subject areas aren't specifically for week and day reporting, but just happened to fall this way.

In this way the lowest level of the hierarchy is preserved for the measures only stored at the week level, obiee doesn't try to drill to day, and you can still use the time series functionality.

Not sure if you scenario is as clean cut but hope that helps!

Robert

[Jeff McQuigg]

How have you mapped your Time Dimension? It should be 1 logical table
with 2
logical table sources. Fields must be in common, and you must have
the
required sequential numbers for time series to work.

Year: Both
Month: Both
Month Num: Both
Day: Day Only
Day Num: Day Only

Before dealing with your time series, you should be able to cleanly
drill down
on just time by itsself - from year to month to day and have OBI
choose the
correct table in the SQL.

Jeff M.

[Michael Wilcke]

For time series to work on all levels you need to set the
chronological key on day and month (using Day Num and Month Num in
Jeff's example above)

Mike

[Daniele DeFaveri]

Hi all!
thanks for replies!
I did all this things (one logical table with two logical souces with
field in common, able to drill down, and set the cronological keys)
but it give me errors when I try to use the to_date function on the
monthly fact table...
Unluckily now I haven't time to go deeper in this issue (too many
other big issues to fight with) and I find a way to workaround it...
I'm glad to hear that you think as me that this should be the right
way to model it...
I will let you know If I'll finde some more time to test it in
details.
Regards,
Daniele