Deriving distinct columns from a table

[Anonymous]

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

[Qrishna]

Yes you can.Create a master calendar where the date field has values from all the date dimensions and create a link key.

[Anonymous]

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

[Qrishna]

Creating A Master Calendar

check this out..

[Anonymous]

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?

[Qrishna]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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] )

;