TM1- TI for sending cube data to sql table

[shivangi]

Hello,I have to integrate my tm1 cube data to a sql table through ti. I had made a ti using the odbcopen and close commands and insert command in the data tab.My records were going into the table, but one issue was coming that the elements that used to get hide when suppresszero was selected were not going to the table even though if i had not slected the suppress zero problem So to overcome this, i used viewskipextractzero function in the metadata tab.After adding this, all my elements are coming, but the time for the ti to run is taking long due to large noof records. so if someone can tell me a better method and some thing on how to optimize it.

[BariAbdul]

Hi Shivangi,Could you please try ViewExtractSkipZeroesSet in Prolog tab and see the result.Thanks

[shivangi]

Hi Shivangi,Could you please try ViewExtractSkipZeroesSet in Prolog tab and see the result.Thanks

hey i tried the function on the prolog tab, but the ti stops responding that is it gets hanged after 15 mins, my records are around 4- 5 croes

[Alan Kirk]

Hi Shivangi,Could you please try ViewExtractSkipZeroesSet in Prolog tab and see the result.Thanks

hey i tried the function on the prolog tab, but the ti stops responding that is it gets hanged after 15 mins, my records are around 4- 5 croes

I suggest that you post your exact code, along with specifics of how you have defined the cube view that you're using as your source. It's not at all clear from your posting whether you're turning suppress zeroes on or off but Bari Abdul is correct; if you change that setting you do it in the Prolog (which is executed before any of the data rows are and will therefore affect how many rows you receive), not the later tabs.

You should be aware that you should (almost) always skip zeroes when processing a view from a cube, otherwise it is very easy for the number of records to rapidly run to millions, and even billions. If you fail to suppress zeroes then the TI will have to process the number of N elements in the first dimension * the number of N elements in the second * the number of N elements in the third * the number of N elements in the fourth dimension and so on. You can do the maths for yourself on that one, and will probably come up with a fairly scary number.

If your problem is that:
(a) You have values in your SQL table from previous data; and
(b) You need TM1 to reset those values to 0,
including zeroes in the view isn't the way to go about that. Instead you should clear ALL of the data in the SQL table that's being affected first so that you have a blank slate, then export the new values straight from TM1.

This is the same principle as when you update a TM1 cube. You don't write zeroes into the cube, but instead zero out the whole area affected, then load the non-zero values.

[declanr]

On a general note I've always found multiple ODBC outputs to be slow, if you can ASCII it into a csv in your data tab then in the epilog do a single odbcoutput to action a bulk insert of your csv into the table.

[shivangi]

Hi Shivangi,Could you please try ViewExtractSkipZeroesSet in Prolog tab and see the result.Thanks

hey i tried the function on the prolog tab, but the ti stops responding that is it gets hanged after 15 mins, my records are around 4- 5 croes

I suggest that you post your exact code, along with specifics of how you have defined the cube view that you're using as your source. It's not at all clear from your posting whether you're turning suppress zeroes on or off but Bari Abdul is correct; if you change that setting you do it in the Prolog (which is executed before any of the data rows are and will therefore affect how many rows you receive), not the later tabs.

You should be aware that you should (almost) always skip zeroes when processing a view from a cube, otherwise it is very easy for the number of records to rapidly run to millions, and even billions. If you fail to suppress zeroes then the TI will have to process the number of N elements in the first dimension * the number of N elements in the second * the number of N elements in the third * the number of N elements in the fourth dimension and so on. You can do the maths for yourself on that one, and will probably come up with a fairly scary number.

If your problem is that:
(a) You have values in your SQL table from previous data; and
(b) You need TM1 to reset those values to 0,
including zeroes in the view isn't the way to go about that. Instead you should clear ALL of the data in the SQL table that's being affected first so that you have a blank slate, then export the new values straight from TM1.

This is the same principle as when you update a TM1 cube. You don't write zeroes into the cube, but instead zero out the whole area affected, then load the non-zero values.

Hello Alan, thanks for the reply.
Basically my cube is a one year model wherein after every new fiscal year, previous fiscal year values are discared. This activity is sort of a backup of the previous data, so we want all the measure elements whether they have value 0 or no to be captured in our database. Right now, my view is based on 4 dims, 1 measure(cube also has the same dims). I have not selected the suppress zero option while i had saved my view.But when i had created my TI , some of my elements were not flowing , which on troubleshooting i founded out that they are invisible when suppress zero is selected. so for that i used the following steps :
1) connected the data source as my view
2) in the variable tab, for the 5 parameters defined type as other
3) in d advanced tab: prolog:
#****Begin: Generated Statements***
#****End: Generated Statements****


ODBCOpen('RSV_Baan_1415_test','ssis_plng','admin123');

ViewExtractSkipZeroesSet('1_RSV','RSV_cube_db', 0);
in data :

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


sql='INSERT INTO rsv_cube_tm1(V1,h_location,t_period,V4,Value) values('''|V1|''','''|h_location|''','''|t_period|''','''|V4|''', '''|Value|''')';






ODBCoutput('RSV_Baan_1415_test',sql);

in epilog:

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


ODBCClose('RSV_Baan_1414_test');

i added the viewskip in prolog after suugestion from brian,but it didnthelp caus my ti got hanged in between and was not responding. I hope this will be sufficient for you to analyze.

[tomok]

This activity is sort of a backup of the previous data, so we want all the measure elements whether they have value 0 or no to be captured in our database.

A ridiculous requirement, IMO. As Alan explained, processing all intersections in even a moderate size cube can run into the millions of records. This is never going to be a viable solution using ODBCOutput. If you insist on exporting all the zeros you'll need to make it a two step process by exporting as a CSV (using ASCIIOUtout) and then have an external (meaning outside of TM1) job to pick that file up and import into the SQL table.

[pandinus]

If values are disappearing when you skip zeros there is something wrong with your feeders. Fix this and then please enable zero suppression in your TI.

[paulsimon]

Hi

I tend to agree that the requirement to include zeroes does not make sense.

If you want a backup of your TM1 cube as year end the simplest way is to simply do a SaveData and copy the files in the DatabaseDirectory.

If you do have to back it up to SQL then as suggested, output to CSV and use a BulkInsert.

If you really do need to output zeroes, first ensure that you are suppressing consolidations, since I would guess that you only need to output data for base level elements in each dim.

I would suggest that you output with zero suppression and then manufacture the zeroes in SQL as follows:

Output the elements in each dimension to different tables

In SQL construct a Cartesian Product of all the dimensions. Select from this and Insert into the database. Then update the rows from the table that has the zero suppression. Alternatively do a set division so you only write 0s for rows that don't have non-zero values.

If with your relatively small cube of only 4 dimensions you will probably have far more zeroes than data values

Regards

Paul Simon