Delimiter Issue when exporting with TI to SQL databa ...

zrjz

Hi,

New to the forum (and new to TM1).

I am trying to "Publish" (to use Cognos Planning Analyst terms) or export a TM1 cube view to a SQL database. I finally got the code figured out and what to put on each of the Advanced tabs.

However, my issue is how TM1 is handling the delimiter. When IMPORTING you can tell it how to delimit, but when exporting using a TI process to publish to a SQL database, I am wondering if there is a way to handle my issue without dillying with the data in the cube (because it's automatically updated with another process).

My cube is this: I have a list of accounts and then various attributes for those accounts. One of the attribute columns in the cube is called "Account Description." We have several accounts that are related to Xi'an, China. Thus, the description has "Xi'an" in it which has an apostrophe.

My TI Process is failing at this point due to this because TM1 is trying to make another column (split my "Account Description" into two) but the database I am publishing to only has "Account Description." I know it's not the database because I also publish from Analyst and it accepts the same data. This is a TM1 issue and wondering if there is a way around it WITHIN TM1 without having to resort to something like SSIS.

Please let me know if this is not clear. I really appreciate any assistance. Please keep in mind that I AM new to TM1 so if you provide direction, it would be greatly appreciated to talk to me as if I were in 2nd grade   Thanks a lot!

cswing_5

To add:

This is being done in Turbo Integrator 9.5.2 (FP1)

I've tried the following in an attempt to resolve my issue.  Again all new so to an advanced person, this may appear to be silly.  Please don't be too hard on the newbie  

Data Source tab:  
IBM Cognos TM1 is checked and the Data Source Name:  common:Account->No Hierarchy  (I created a view which I am publishing)

Advanced Tabs:
Parameters:
None

Prolog:

#****Begin: Generated Statements***
#****End: Generated Statements****

ODBCOpen('SERVER', 'username', 'password');
DatasourceASCIIQuoteCharacter='';
SetODBCUnicodeInterface(-1);

Metadata:  

#****Begin: Generated Statements***
vAccountGroupPrefix=ATTRS('Account',vAccount,'Account Group Prefix');
vAccountGroup=ATTRS('Account',vAccount,'Account Group');
vStatus=ATTRS('Account',vAccount,'Status');
#****End: Generated Statements****

Data:  

#****Begin: Generated Statements***
vAccountDescription=ATTRS('Account',vAccount,'Account Description');
vAccountGroupPrefix=ATTRS('Account',vAccount,'Account Group Prefix');
vAccountGroup=ATTRS('Account',vAccount,'Account Group');
vStatus=ATTRS('Account',vAccount,'Status');
#****End: Generated Statements****

vSingle = ' ';


ODBCOutPut('SERVER', Expand ('INSERT INTO database.table
(
Account ,
Account_Description ,
Account_Group_Prefix ,
Account_Group ,
Status
)
VALUES
(
''%vAccount%'' ,
''%vAccountDescription%'',
''%vAccountGroupPrefix%'' ,
''%vAccountGroup%'' ,
''%vStatus%''
)'));


Epilog:
#****Begin: Generated Statements***
#****End: Generated Statements****

ODBCClose('SERVER');

tianya369

jimicron wrote:My TI Process is failing at this point due to this because TM1 is trying to make another column (split my "Account Description" into two) but the database I am publishing to only has "Account Description." I know it's not the database because I also publish from Analyst and it accepts the same data.  This is a TM1 issue and wondering if there is a way around it WITHIN TM1 without having to resort to something like SSIS.

You probably should post the code in your Ti process so we can see what your SQL looks like. I seriously doubt it is a TM1 problem because contrary to what you have stated, TM1 is not trying to make another column. The problem is the apostrophe in your data. You just need to replace the single apostrophe in your data with a double apostrophe using a combination of SCAN and INSERT on the variable before the line of code where you build your SQL string to do the INSERT into the table.

wcb79

On the Data Source tab there is no SQL as I am pushing data OUT not pulling it in.  

Thus, the Datasource Type is:  IBM Cognos TM1
Then, it asks for Data Source Name:  and that is where you click on the Browse... button and you choose your TM1 cube and then you choose the view.

The only other "code" there is is on the Advanced tab, which I have posted above.

こ忘了隠藏ご

Hi jimicron, seems like you misunderstood tomok's suggestion.

He generally suggests that you modify your Data tab code so single apostrophe gets replaced with double apostrophes.
Tomok suggests Scan function, I will suggest below character by character iteration (in case you have more than one apostrophe to handle in one string) [how much easier it would be having simple Replace fuction...].

The code below more or less should work, but please be aware I did not test/verify it, so even simple typo could occur.
Green is your code as it was, blue is what I added to or replaced in your code, red is also your code, but seems totally obsolete.

#****Begin: Generated Statements***
vAccountDescription=ATTRS('Account',vAccount,'Account Description');
vAccountGroupPrefix=ATTRS('Account',vAccount,'Account Group Prefix');
vAccountGroup=ATTRS('Account',vAccount,'Account Group');
vStatus=ATTRS('Account',vAccount,'Status');
#****End: Generated Statements****

vSingle = ' ';

sAccountDescription = '';
nCounter = Long ( vAccountDescription );
While ( nCounter > 0 );
  sCharacter = SubSt ( vAccountDescription, nCounter, 1 );
  If ( sCharacter @= '''' );
    sCharacter = '''''';
  EndIf;
  sAccountDescription = sCharacter | sAccountDescription;
  nCounter = nCounter - 1;
End;

ODBCOutPut('SERVER', Expand ('INSERT INTO database.table
(
Account ,
Account_Description ,
Account_Group_Prefix ,
Account_Group ,
Status
)
VALUES
(
''%vAccount%'' ,
''%sAccountDescription%'',
''%vAccountGroupPrefix%'' ,
''%vAccountGroup%'' ,
''%vStatus%''
)'));

And just few remarks at the end:
  1. You are using automatically generated code in your TI, which is not the best practice, better to copy the code from between Generated Statements lines and switch all variables in Variable tab to Other.
  2. If you insist on automatically generated code, you can at least switch off generating it for the Metadata tab - you will skip one full obsolete iteration on source rows.
  3. If rest of the variables (not only Description) can have apostrophes, you need to include similar processing for those variables also.

Hope this helps anyhow

fuanxx

jimicron wrote:The only other "code" there is is on the Advanced tab, which I have posted above.

Your second post is what I was asking for. I didn't see it because you slipped it in while I was in the middle of typing my response to your first post. My response still is correct. The problem is not with TM1, it's with having an apostrophe in your data. The apostrophe is a reserved character in SQL for delimiting data. When SQL encounters two apostrophes together it interprets that as data with a single apostrophe. Trust me, I have been living with this nightmare for years because my last name has an apostrophe in it.

lindejink

If you can have this problem across all your dimensions it is worthwhile creating an alias/attribute on all your dimensions, called "SQL Safe".

Then in a TI use the logic already described to clean the descriptions and populate the new attribute, and then reference this attribute in your export.

This means that you only execute your cleaning logic against each element once, rather than repeatedly for every row of data you are exporting.

Cheers,

yzbboy

If you are worried about the performance then in addition to Steve's suggestion you should use SCAN and INSERT - as suggested by tomo'k - instead of looping over each character.

Code: quote_char = '''';
quote_position = SCAN( quote_char, string_to_quote );
search_offset = 0;

While ( 0 <> quote_position );
   string_to_quote = INSRT( quote_char, string_to_quote, search_offset + quote_position );
   search_offset = search_offset + quote_position + 1;
   quote_position = SCAN( quote_char, SUBST( string_to_quote, search_offset + 1, LONG( string_to_quote ) - search_offset ) );
End;


When you are looping you use SCAN to find the location of the next quote character after the last one you found. SCAN is very fast and it means that if the string doesn't have a quote character you are not looping unnecessarily.

kbswangyang

Thanks everyone!

All of this coding is totally new to me.  And, again, very "green" to TM1 development.

I did get rid of these two lines on my Prolog tab:

DatasourceASCIIQuoteCharacter='';
SetODBCUnicodeInterface(-1);

And then, changed my Data tab to the following per JSTRYGNER's suggestion and it worked!!! (successfully)      

#****Begin: Generated Statements***
vAccountDescription=ATTRS('Account',vAccount,'Account Description');
vAccountGroupPrefix=ATTRS('Account',vAccount,'Account Group Prefix');
vAccountGroup=ATTRS('Account',vAccount,'Account Group');
vStatus=ATTRS('Account',vAccount,'Status');
#****End: Generated Statements****


sAccountDescription = '';
nCounter = Long ( vAccountDescription );
While ( nCounter > 0 );
sCharacter = SubSt ( vAccountDescription, nCounter, 1 );
If ( sCharacter @= '''' );
sCharacter = '''''';
EndIf;
sAccountDescription = sCharacter | sAccountDescription;
nCounter = nCounter - 1;
End;
ODBCOutPut('SERVERNAME', Expand ('INSERT INTO database.table
(
Account ,
Account_Description ,
Account_Group_Prefix ,
Account_Group ,
Status
)
VALUES
(
''%vAccount%'' ,
''%sAccountDescription%'',
''%vAccountGroupPrefix%'' ,
''%vAccountGroup%'' ,
''%vStatus%''
)'));


I'm still learning on the whole auto generated statements, etc.  I will look into that next and try that suggestion.  But, by doing the above, it's working!!  Thanks a lot for your help!!! Appreciate it a lot!

xiphiasjoe

I'm not sure if I should post a new thread about this or add it here. Please let me know.

The process is working now and publishing successfully, which is great. However, it's not totally accurate.    I hope that I can explain this in my newness.

I am publishing a two dimensional cube. Looks something like this:

Account     Account_Description    Account_Group_Prefix      Account_Group                           Status
100000      100000 Petty Cash       CASH                              Cash                                          Active
100100      100100 Other Cash      CASH                              Cash                                          Active
100200      100200 Software         PPE                                 Property, Plant and Equipment     Active
100300      100300 Patents           OTHA                              Other                                         Active

Even if I right-click on my View for my Account cube and click on "Export as text data" - the .CMA file that is produced looks just like the publish to the SQL database.  

The .CMA looks like this:

common:Account        100000        Account_Description                        10000 Petty Cash
common:Account        100000        Account_Group_Prefix        CASH
common:Account        100000        Account_Group                        Cash
common:Account        100000        Status                                        Active
common:Account        100100        Account_Description                        100100 Other Cash
common:Account        100100        Account_Group_Prefix        CASH
common:Account        100100        Account_Group                        Cash
common:Account        100100        Status                                        Active
common:Account        100200        Account_Description                        100200 Software
common:Account        100200        Account_Group_Prefix        PPE
common:Account        100200        Account_Group                        Property, Plant and Equipment
common:Account        100200        Status                                        Active
common:Account        100300        Account_Description                        100300 Patents
common:Account        100300        Account_Group_Prefix        OTHA
common:Account        100300        Account_Group                        Other
common:Account        100300        Status                                        Active

And the table on my SQL database looks similar to above... with the account listed 4 times.  

How do I get around this?  

I hope this is making sense for you.  Sorry about the formatting. I had put spaces above but when it is posted looks like the spaces are deleted.  I tried to represent columns above.  

Please let me know if you have any questions. Thanks a lot for your guys' help!