I am working on an application, where in Calendar Table the datekey is coming from SQL and its like 77463 format as its a key and in the same master calendar I have the YearMonth dimension in the format of 2011-07. I have an external excel sheet with below data which I am trying to link to my data model master calendar. I am trying something like Join load any suggestions please?
YearMonth
Expenses
2011-03
8,114.20
2011-04
2,859.15
2011-05
5,222.29
2011-06
7,237.46
2011-07
4,979.86
After I load this data, it shows me as 42350 instead of the series of numbers as 77463 and I tried changing the data format in Excel to DD/MM/YYY but that doesnt help.
I want to link this data to my master calendar for the expenses to use across the application any help please..
if I got you right, your master cal is based on days. Its key is a number representing one day - let's call it a DayDate. Your excel-data comes with a date based on month as the expenses are per month. So I would transform the YearMonth into a DayDate concatenating a '-01' to YearMonth. May be you have to calculate(transform, reformat, ....) this DayDate into the according number of your master calender key. After this you have a valid key to link your excel data (now based on a DayDate) to your master cal using eg. left join.
After this all your reports sholud work as expected.
