TI Process - Meta Data Behavior

Post Reply
dharav9
Posts: 72
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

TI Process - Meta Data Behavior

Post by dharav9 »

Hi, All

I have a TI process where Data Source is an excel CSV file. This is an existing TI process and working normally under following structure.

In, this TI we upload headcount information to the cube. In current TI, if new employee is not part of employee dimension then enter the new employee in the employee dimension through dimensionelementinsertdirect function. Once this activity is done then it enters the employee related data in to cube.

In new structure, i wanted to assign new employee operation in to meta data with dimensionelementinsert function.

Data Source Column 2: is ignored in variable tab having data "Lance, Christopher".
Data Source Column 14: is having string data type which represents the employee type: Full Time, Part Time etc
Data Source Column 15: is having numeric data type which represents the standard hours for an employee

i got following error:

"126",""Lance"," Christopher"","Christopher","Lance","137","systemanalyst","","abc","10410","4077","72","A","R","Full time","100","1","PG_Semi-Monthly","9/5/2018","9/5/2018","","LOC_ZA","Remote - ABC"
Data Source line (2) Error: MetaData procedure line (0): Cannot convert field number 15, value "Full time" to a real number.

According to me, due to comma deliminator ""lance"."christopher"" => count as 2 words.

is there a way to avoid this ?

Thank You

Dharav
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TI Process - Meta Data Behavior

Post by tomok »

Go back to the person that is creating the file and ask them to use something else as the delimiter.My personal preference is the pipe character, "|", as that is rarely in data. They could also use tabs, or any other character that is unlikely to be embedded in the data.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
babytiger
Posts: 78
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: TI Process - Meta Data Behavior

Post by babytiger »

If you want to stick with csv, then I would suggest getting the source file tidied up, as I believe the csv file is not correctly created.

In the scenario you've shown, if correctly formatted in csv, it should be
"126","Lance, Christopher","Christopher","Lance","137","systemanalyst","","abc","10410","4077","72","A","R","Full time","100","1","PG_Semi-Monthly","9/5/2018","9/5/2018","","LOC_ZA","Remote - ABC"

There should be only be one set of quotes around Lance, Christopher. And if you specify the quote character as ", it would remove the quotes and separate your data row into relevant fields.

But you can still run into problems when commas or quotes are used in the data, so they aren't the best delimiter characters. As tomok suggested, "|" is rarely used in data. I personally, prefer the use of tabs in files (char(9)), with no quotes around fields.
MK
Post Reply