Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i'm creating a qvd from a fact table that has multiple date dimensions associated with it.
I want to do a couple of things.
•Pull only the dates I will be loading from the fact table which I can do with the following query.
•Display only the distinct Years from those dates to the users.
The problem is that these are actually different dimension keys so I will get duplicate years.
Is there a way to extract just the unique years but still have the dim keys to associate with the model?
select distinct wpdd.plcy_dt_dim_key, PLCY_DT_YR_YY as "Policy Year",wpdd.*
From AWH.WR_MTD_COMB_DTL_F wmcdf, AWH.WR_VAL_DT_D, awh.wr_plcy_dt_d wpdd
where wmcdf.VAL_DT_DIM_KEY = WR_VAL_DT_D.VAL_DT_DIM_KEY
and wpdd.plcy_dt_dim_key = wmcdf.plcy_dt_dim_key
and AWH.WR_VAL_DT_D.VAL_DT_DAY_MDY >= '01-JAN-2013'
and AWH.WR_VAL_DT_D.VAL_DT_DAY_MDY <= '01-JAN-2018'
order by 2
Yes you can.Create a master calendar where the date field has values from all the date dimensions and create a link key.
Can you share a link to an example, I know how to create a master calendar it's the link key that i'm struggling with
check this out..
Thanks, i'm trying this now. One more question, if I want this shared is it best practice to create the master calendar in a qvd and store it so it's common among applications?
No.do master calendar in data modelling tier.
storing in qvd and using it in multiple apps is a very bad idea as Master calendar is derived from the apps data itself and you may not have same data in other apps.
I find creating a communal Master Calendar QVD covering more dates than I will ever require very useful on many occasions.
I can load it at the start of the load script with a Where condition to only load the required date range. The load probably won't be optimised, but it is only a small QVD and will be a sub 1 second load.
Then when I am later loading large transactional QVD's I can do an optimised Where Exists against the Calendar Dates loaded.
Hence I can automate things to only load the transactional data needed for my required dates, often this year and the last N years, maybe the last 24 months.
Thanks Bill, in regards to the following, do you have any examples I can see?
Then when I am later loading large transactional QVD's I can do an optimised Where Exists against the Calendar Dates loaded.
Hence I can automate things to only load the transactional data needed for my required dates, often this year and the last N years, maybe the last 24 months
Here is a generic example.
First load just this year's dates from the pre created Calendar QVD
Calendar :
LOAD
Date(Date) as [Transaction Date] ,
Day ,
Month,
Year ,
MonthYear
FROM [lib://blah1/Calandar.qvd] (qvd)
where Year = Year(today())
;
Then load just the transactions from its pre created Transactions QVD where the [Transaction Date] exists from the Calendar Load
Transactions :
LOAD
*
FROM [lib://blah2/Transactions.qvd] (qvd)
where exists ( [Transaction Date] )
;