TM1 Forum

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!

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');

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.

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

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.

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,

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.
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.

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!

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!

Read this http://www.tm1forum.com/viewtopic.php?p=34078

Quite bummed. I looked at the other thread carefully. I have tried using If(count=1); and count=count+1; in various positions in my statement and can't get any of them to work correctly. I also put count = 0; on my Prolog tab.

This is my current statement that is working without error (but is still doing the duplicating). I am posting the original versus what I have played with as to not confuse. I am not sure if it's an ordering thing? I am not used to this syntax

Here is the original:

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%''
)'));

Can I possibly do something like a SELECT DISTINCT in my INSERT INTO statement or something like that? Where it only chooses the DISTINCT? Not sure how I would do that either. Just another idea as it's the DISTINCT is what I want.

My cube is unique. It's a list of Accounts. There is only 1 account and then each account has various mappings (columns).

Thanks again for any help! And appreciate your patience!

All you need to do is execute an SQL statement that INSERTs the Account record into the table:

INSERT INTO database.table (Account) VALUES (vAccount)

and then use UPDATE to do the other values:

UPDATE database.table SET Account_Description = vAccountDescription, Account_Group_Prefix = vAccountGroupPrefix, Account_Group = vAccountGroup, Status = vStatus WHERE Account = vAccount

You'll need to refine the syntax but you should get the idea.

Currently the TI gets one record per cell of the account cube.

So the key thing is only to get one input record per account. Use as your TI source a view that e.g. just selects ACCOUNT_GROUP from the relevant dimension. You will then get 1 record per ACCOUNT with only the value for ACCOUNT_GROUP.

Then instead of using the TI field variables for the other field values (as they are no longer there) you just use CellGetN and CellGetS on the cube to get the values for a complete INSERT statement.

e.g.

Thanks Tomak and Duncan.

I've been trying Tomak's suggestion and still getting 4 records... I have been using the following:

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 )

VALUES

(''%vAccount%'')'));

ODBCOutPut('SERVERNAME', Expand ('UPDATEdatabase.table

SET
Account_Description = ''%sAccountDescription%'',
Account_Group_Prefix = ''%vAccountGroupPrefix%'',
Account_Group = ''%vAccountGroup%'',
Status = ''%vStatus%''
WHERE
Account = ''%vAccount%'' '));

I'm gonna try Duncan's suggestion now. I'll see if I can figure it out.

Thanks again! I sure wish I were using Analyst still b/c it was sooooooo much easier with Analyst!

jimicron wrote:I'm gonna try Duncan's suggestion now. I'll see if I can figure it out.

Please do, as this is the direction to follow.

jimicron, I am glad my code worked, thank you for the feedback, but I want to encourage you to apply Duncan P's code with SCAN and INSRT functions, as that example trurly limits number of performed operations to minimum.

I wanted to keep my code simple, but I did not balance properly between simplicity and efficiency.

Well, I just got it to work!

I did go to my Cube View that I am using to publish and changed it to hide all columns but the one (Account Description). SO, when you open up that view, you only see Accounts down the left hand side and then a data column that has Account Descriptions in it.

On my Variables tab, I still have my vAccount (Other), vAccountMeasures (Ignore), Value (Ignore), vAccountDescription (Other), vAccountGroupPrex (Other), vAccountGroup (Other), and Status (Other).

Prolog tab has an ODBCOpen but nothing else... and my Epilog tab has ODBCClose but nothing else.

My Data tab is where pretty much everything is at and it looks like this:


#****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('BOMSSTEST95', Expand ('INSERT INTO master_data_pub.COGNOS_ANALYST_APP.tm1_account

(
Account ,
Account_Description ,
Account_Group_Prefix ,
Account_Group ,
Status
)

VALUES

(
''%vAccount%'' ,
''%sAccountDescription%'',
''%vAccountGroupPrefix%'' ,
''%vAccountGroup%'' ,
''%vStatus%''
)'));

I am able to publish now and it is successful with only one row per Account showing. The only problem now is that I have to clear out the table every time before I run my TI process to publish. I suspect it's because I have Account set as my Primary Key in the table and also due to the INSERT INTO command.

All of this coding is brand new to me so I was not able to get Duncan's code to work. I didnt know where to insert it into my existing and I didn't know how to edit it.

Thanks again!