I am not getting the result as it should be after creating a master calendar

[ferha_jafri]

Hello everyone,

I need help with regards to data modelling , in my case i have 13 tables out of which 5 are transaction tables in these 2 tables dont have relevant dates as the data feed there on the basis of month and year and then we have 3 tables in which we have date my issue is when i am creating a master calendar by concatenate these dates they not mapping correctly with all the tables as for some userid if the date in one table is 1/1/2013 for the same user there is some other date as 2/3/2009 due to which the data is not coming correctly i want to know how should i perform a data modelling in this case so that the master calendar synchronize with all the tables.It is urgent,please reply if someone knows the answer.

Thanks in advance

Ferha Jafri

[swuehl]

You might want to use several master calender for your distinct semantic dates.

Tutorial - Using Common Date Dimensions and Shared Calendars

[cn_sa_dev]

Hi,

Sounds like you have 2 issues - one is some tables have YYYYMM format and others have YYYYMMDD format.  The other issue is different date types eg YYYYMMDD, DDMMYYYY, etc.  Is that correct?

For the first issue you'll need two different keys that connect the transactional data tables to your master calendar, one at YYYYMM level and one at YYYYMMDD level.  This way selecting on item at YYYYMM level, lets say 201310 returns YYYYMM01, YYYYMM02, YYYYMM03...YYYYMM30 or 20131001, 20131002, 20131003...20131030.

So your master calendar will look like this:

%YM_Key	%YMD_Key	Date
201310	20131001	2013-10-01
201310	20131002	2013-10-02
201310	20131003	2013-10-03
…	…	..
201310	20131030	2013-10-30

For your second issue you need to convert all the dates to the same format as per example below.

Different formats	Standardised format	Calculation
01-10-2013	2013-10-01	=makedate(right(column1,4),mid(column1,3,2),left(column1,2))
2013-10-01	2013-10-01	Correct format

You can also convert all your dates to the number format (2013-10-01 = 41548) by using the floor function.

Hope that answers your question?

Chris

[ferha_jafri]

Hello ,

Yeh you are correct but what i did i convert all the dates to num as all the dates have different formats ,now my issue is when i create a master calendar dates in the tables cannot connect to the master calendar as there is one table suppose event in which we have activity dates now if i take one example as corresponding to user id 1010 event date is 1/1/2013 and in master calendar we have date 3/5/2009 so the mapping is not coming correctly.Any idea what should i do in this case.

Thanks in advance

Ferha Jafri

[#5]

If you are sure it's not to do with incorrectly fomatted dates, as chris points out above, you might need to look in to how the tables are relating in the data model. 

[cn_sa_dev]

Hi,

If the dates mean different things (for example transaction date VS posting date) it would be better to have two or more "master" calendars for each functional area (one for transaction dates and one for posting dates).  First you need to sanitize the data as discussed and then you can model it as per Swuehl's link above - one master calendar for each area where there are multiple dates for a userid as per your example.

Chris

[cn_sa_dev]

Hi Ferha,

Did this answer your question?