Three facts Link Table and Master Calender

[praveenak]

Hi,

Am new to this link table concept.

Can you please help me to resolve below issue?

I have three fact tables like below.

Facttable-1:		Facttable-2:		Facttable-3:
FieldA,		FieldA,		FieldA,
FieldB,		FieldB,		FieldB,
FieldC, FieldD, FieldF,		FieldC, FieldE, FieldG,		FieldC, FieldE, FieldG,
Date1,		Date1,		Date1,
Date2,		Date4,		Date6,
Date3		Date5		Date7
from TableName100;		from TableName200;		from TableName300;

i.e., all three facts have almost entire data set with common columns and also few extra other columns as shown above.

Please help me to build a link table and master calender for all the date fields 1 through 7.

Thanks for your help.

[MK9885]

If the values are same in common fields, combine the facts into one one.

Same field name will create synthetic keys

You can use Join or Concatenate functions to create one fact table. It will be much cleaner.

end result will look like below.

FactTable:

FieldA,

FieldB,

FieldC,

FieldD,

FieldE,

FieldF,

FieldG,

Date1,

Date2,

Date3,

Date4,

Date5,

Date6,

Date7

Check this link to know more how to join based upon your data

Understanding Join, Keep and Concatenate

Master Calendar:

Master Calendar

Find the reply by ISHFAQUE AHMED in above link. You'll get the Master script, edit the dates and Date Format (MM-DD-YYYY) according to your requirements as it might be different in your data.

To link Master Calendar to your fact, create a DateID field in your fact Table

trim(date(YourDateFieldHere,'YYYYMMDD'))

as [DateID],

YourDateFieldHere <<< This can be your Date1,Date2,Date3,Date4....

Choose one date field and create a DateID to link Master.

Note: There are/can be different ways to approach this but I'm giving my opinion. Maybe someone else can do it in more optimized way.

Thanks.

[krishna_2644]

Link table can be created in the following way as scripted in the attached qvw.

u will have something like

Hope that helps.

[praveenak]

Thanks Krishna for the help.

But one question. Lets say if I have 30 common fields in all facts (like Fields A,B,C in given scenario), I cant make the Link Key using all of those, right? Or if I take only three or four of them, it will create Synthetic table again.

Any suggestion to overcome this also.

Also can u suggest on master calender as well, since when I use the master calender for all the date fields 1 thorugh 7, while reloading application it gives the error 'Field not Found'. I have even verified the date formats and looks identical.

Thanks.

[neha_shirsath]

Hi Praveena,

If you have 30 common fields in all facts table then rather than creating composite key/Link table you can concatenate the tables and create the one table of all, so that same fields will concatenate the data and different fields will append the data.

And for 'Field not found' error in calendar, check the date field which calendar not getting from your resident table or else can you post the error and script of your calendar and table used.

Thanks & Regards,

Neha