Daily Facts and yearly Target - how to solve Key Issue

[chriys1337]

Hi,

I am having one central fact table with a dimension calendar table. My facts are on daily basis. Now I am trying to add a yearly target and my calendar key (for each day) is making it hard to find the correct key. Please see (QV) example attached. Do you have a idea how to solve that problem daily key vs yearly target?

[sunny_talwar]

May be link table based model

facts:
LOAD *,
	 Site & '_' & Line as Site_Line_Key,
	 Site & '_' & Line & '_' & Date_Key as Site_Line_Date_Key;
LOAD * INLINE [
    Site, Line, Date_Key, Outcome
    DUS, A, 20181128, 30
    DUS, B, 20181127, 20
    HAM, B, 20181127, 1
];

cal:
LOAD *,
	 Year & '_' & Date_Key as Year_Date_Key;
LOAD * INLINE [
    Date_Key, Year, Month, Day
    20181128, 2018, 11, 28
    20181127, 2018, 11, 27
];

Target:
LOAD *,
	 Site & '_' & Year as Site_Line_Year_Key;
LOAD * INLINE [
    Site, Year, Target
    DUS_A, 2018, 50
    DUS_B, 2018, 50
    HAM_B, 2018, 100
];

LinkTable:
LOAD Site_Line_Date_Key,
	 Line,
	 Site,
	 Date_Key,
	 Site_Line_Key,
	 Left(Date_Key, 4) as Year,
	 Left(Date_Key, 4)  & '_' &  Date_Key as Year_Date_Key
Resident facts;

Concatenate (LinkTable)
LOAD Year_Date_Key,
	 Date_Key,
	 Year 
Resident cal;

Concatenate (LinkTable)
LOAD Site_Line_Year_Key,
	 SubField(Site, '_', 1) as Site,
	 Site as Site_Line_Key,
	 SubField(Site, '_', 2) as Line,
	 Year 
Resident Target;

DROP Fields Site, Date_Key, Line, Site_Line_Key From facts;
DROP Fields Date_Key, Year from cal;
DROP Fields Site, Year from Target;

[marcus_sommer]

Another approach would be just to make a date from your year, for example with makedate(Year) and now you could create a key for a joining/associating approach or just concatenating this table to your facts. Especially the last mentioned approach with concatenating both tables is very easy and avoids potential problems with facts without targets and reversed if they should be joined/associated (without a link-table like in Sunny's suggestion there would be always a gap).

A further possibility would be to distribute the yearly values to daily values with some kind of loop or by a join to a calendar.

- Marcus