Use of Calendar Table and Link Table in ETL

[neerajthakur]

Where should I make a calendar table in ETL?

Following different examples I have observed people are creating it in Transformation Layer, but why , why not in Loading ?

Use of Link Table: 

Making LinkTable following two stages, first loading fields from Table 1 and concatenating with Table 2 and then making keys out of it and this Link Table is created in Loading , after some research I have come to know it saves RAM, if it is made in Transformation Extra Storage space would be consumed as it is loaded again in LinkTable.

Someone with sufficient knowledge on these topics according to best practices, please throw some light.

[marcus_sommer]

I'm not quite sure what you mean with transformation layer but mostly it's enough to create a master-calendar once and storing the data within a qvd and loading the needed parts (the wanted fields, the from - to periods and with an appropriate renaming to the target structure) whenever you need them.

Further a lot of useful information like flagging working-days, YTD, MTD, LYTD, …, rolling periods, consecutive counting of weeks, months and a lot more are difficult to impossible to create without a master-calendar approach and why should it be done again and again in each application.

Within the most scenarios link-tables aren't needed nor useful else they are an expensive detour. The officially recommendation is to develop a datamodel in the direction of a star-scheme and if there are multiple fact-tables to merge them into a single table. Each kind of transformation which is needed could be done within and/or against this single fact-table and probably none of them is saveable by putting a link-table between fact-tables. Especially if the datamodel becomes really a bit more complex with multiple fact-tables - the link-tables could increase to a n times larger table as the real data and/or fact-tables with link-tables are linked to a further fact-table / link-table complex with another link-table …

- Marcus