Send Data from TM1 to Cognos Controller

[christophe]

Dear,

I am looking to send data from TM1 10.2 to a Cognos Controller through the staging tables "method". Therefore i use a TI that based upon a view is sending data to the staging tables of controller. Data is then processed by controller and available in it.
What i would like to achieve is the following:

An user is loading data through an excel interface in which we added a button to launch the TI that will generate a view to be exported to Staging tables.
Now sometimes a user wants to "zero" some of the data in the excel view, zero values are indeed send to TM1 no problem. But in order to be also reflected to Controller we need to include blanks/null values in the view that is used for exporting.
Hence this is getting very big and takes a lot of time to be processed as all null/blanks are also taken into account.

Is there a way we could limit "dynamically" in the TI process the data that is send to the staging tables to only use what is previously send from the excel sheet into TM1.
Even if i create a view that is showing the same as in the excel, including all the zero's is taking a long long time.

I am just wondering if i have the right solution here from the start.

Any recommendation to get data from excel interface to controller without having the user wait 10m+ to see results in controller? in the other direction the FAP is working, but not in that direction

Thank you for your help and advice,

Best regards,

Chris

[jim wood]

one way you can do this is to make sure if you're including zeroes that you are making the best possible selection for the view. For example if you are exporting every element from a dimension within the cube yet only 2 have data then you need to make sure they are sent. If you have an easy to slim your view then great.

Another option is to create another version of the same cube. Copy the data you're exporting first to this copy, then complete your data clear. The en export without suppress zeroes selected but only export if the copy cube contains data for that element combination. I hope that makes sense?

Jim.

[BrianL]

Not sure if this is fit's your process, but you could also try sending the zero's over to controller as a separate push. The idea would be to have the same TI that loads the zero's into TM1 also load them into controller. Then you wouldn't have to send all zeroes along with the rest of the view data.

Also, maybe UNDEFVALS could help?

[christophe]

Hello Jim,

Thanks for your answer, i am going to try to look into those options, i thought that it could be easier and certainly quicker to implement.
We could indeed make a view selection but even that is taking quiet a lot of time.

Hi Brian,

Thanks also.
We would then need to have to TI's and wait until both are finished to have the right data...not sure i understand well the solution here.


The main thing is to have a technique to find out in TM1 which cells have been updated during the last data push and only update those (zero and not zero) into controller.

[pandinus]

Why not store the data that is pushed to controller to a separate measure/version in your cube?
Use this data to build your view and push zeroes for every data point that you come across in your data tab.
Next, zero-out your separate measure.

[jim wood]

Why not store the data that is pushed to controller to a separate measure/version in your cube?
Use this data to build your view and push zeroes for every data point that you come across in your data tab.
Next, zero-out your separate measure.

Using another version is great idea. You will however need to apply a STET override rule if you have any rules in place. Also make sure you hide it with security if you want to keep it,

Jim.

PS. Make sure you clear out any holding version date once you've completed the export to Controller.
PPS. Make sure you include rule calculated numbers in your view build. I'm guessing you already have this anyway.

[christophe]

Thanks a lot for your answers!
Will try that, it seems also to me a very good idea.

Very much appreciated!

Chris