Mapping Period Dimension with flat file Date

[sh226]

Here is what i have.
1. Existing "ABC_ACTIVITY" Dimension
2. Existing "DEF_LOCATION" Dimension
3. Existing "GHI_VEHICLE" Dimension
4. New Measure Dimension "XYZDIMENSION" holding all the data columns from flat file.
5. Existing "PERIOD" Dimension that rolls up to Month-->Qtr--->Year
Here is what i have in my new flat file
1. Namecode that will map to above dimension "ABC_ACTIVITY" (successfully completed)
2. Location city mapped to dimension "DEF_LOCATION" (successfully completed)
3. Fund code mapped to dimension "GHI_VEHICLE" (successfully completed)
4. Few data columns. I have created Measure Dimension "XYZDIMENSION".
When i create a Cube holding above five dimensions, I don't see any data loaded into the cube. it looks like I am doing something wrong that is not mapping date column with period dimension. I have copied and pasted error below. So far i have done following to resolve this but not successful.
1. i changed date column to every possible date format in Excel but did not work however one of the format i applied and it loaded all data to year 2012(even i have Quarterly data from 2007 to 2011 in flat file). I am New in TM1 and Hope i explained it clearly. Please help me to resolve this issue. Thanks in advance

Here is the example of error that i get.
"1234 - Lin Gr","Pied Tri B","SE","Sep-06","766523.52","39129.27","577638.78","Fund I","0.15","38500000","34961307","3538693","1769346.5","","","","","8366859.68","327823","","","",Data Source line (1) Error: Data procedure line (3): Invalid key: Dimension Name: "bpmPeriod", Element Name (Key): "Sep-06"
Error: Data procedure line (3): error repeats 14 times
"1234 - Lin Gr","Pied Tri B","SE","SE","Dec-06","1161870.51","28149.32","626039.49","Fund I","0.15","38500000","34961307","3538693","1769346.5","28706.98","36550062.85","28300000","28434890.72","","276137","222025.56","","",Data Source line (2) Error: Data procedure line (3): Invalid key: Dimension Name: "bpmPeriod", Element Name (Key): "Dec-06"
Error: Data procedure line (3): error repeats 14 times

[declanr]

Don't use the excel date formatting.
Just change that column to be string based on whatever the n-level elements are in your dimension.

e.g. if you have an element in your dim called "Jun-2011", in your flatfile type 'Jun-2011 note the " ' " symbol to convert to text as opposed to being a number or date etc.

[sh226]

Thanks for your reply. i already tried this method by looking into one of other post and did again as you explained and it did not work. Please see attached screen shot what i have done so far. Thanks

[David Usherwood]

You want to get rid of the leading quote you needed to put in on the date field. Set the variable type to Other, add a new variable, set it to string (why, oh why, isn't this the default?), then click on Formula and write the TI code to remove the quote eg
vPeriod2 = trim(subst(vPeriod,2,50));

[rkaif]

By looking at the sample error and screen shot you have posted it looks like the element in the dimension and the data in the CSV file are not matching with each other. You have Sep-06 in the CSV file while in the dimension you have Sep 2006. Because these two are different that is why you are getting the error while loading the data into the cube.

[sh226]

Thank you David for your help
it worked and finally i have my first cube built.
Now please help me to write a code to map all of my data columns in Measure dimension "XYZDIMENSION" with existing General ledger Account dimension "Account". Here is the example. Thank you so much for your help.
1000-1200 (either Parent or Child Element in Account dimension) will map with Revenue Tab
4040-9910 (either Parent or Child Element in Account dimension) will map with Mgt Fees Tab and so on