dmr with 2 date from 2 differents sources

[philelmousse]

Hello,

I retrieve 2 tables with 2 date columns. I would like to know how build my time dimension in framework manager based on that ?

I'm a bit lost...

[blom0344]

No, no that is very wrong.. If you model this properly you add a calendertable as source for the dates and join the other tables accordingly. All dimensional data is then derived from the calendertable. This  is especially true in cases where dates are stored in more than new table as in your case

[philelmousse]

I know. The problem is that I can't create a new table. There is no workaround possible like "build" a query subject like a table ?

[blom0344]

Theoretically you can write an SQL query subject that reads all available dates from existing tables performing unions to make sure every date is scored only once. This basically works like a database view on the tables.
The benefit would be that the contents is always 'synchronized' with existing data in tables, but derived columns can only be defined through functions and performance may suffer (absence of a unique index for instance)

[philelmousse]

ok i will try with that union and will check if the perf are not good enough. Thanks a lot

[tjohnson3050]

I once build a time dimension on the fly using recursive sql and prompts for begining and ending dates.

The prompt macros could be replaced with logic for current date +- a certain number of years based on what you know about the date ranges of your own data.

This is for Oracle, but with syntax tweaks it could work for other db's as well.

Using a data source query subject paste in:

with    Q1 (TD_KEY,DT) as (
select  1,
        to_char(to_date(#sq(prompt('BEG_DATE','DATE'))#,'YYYY-MM-DD'),'YYYY-MM-DD')
from    DUAL
union all
select  TD_KEY+1,
        to_char(to_date(DT,'YYYY-MM-DD')+-1,'YYYY-MM-DD')
from    Q1,DUAL
where   to_date(DT,'YYYY-MM-DD') < to_date(#sq(prompt('END_DATE','DATE'))#,'YYYY-MM-DD'))
select  distinct
        to_char(to_date(DT,'YYYY-MM-DD'),'YYYY') as YR_NO,
        to_char(to_date(DT,'YYYY-MM-DD'),'MM') as MO_NO
from    Q1
order by YR_NO,MO_NO