How to create a cube snapshot using TI

[Bob Stuecheli]

This should be simple. We are running TM1 10.2.2. I need to create an Excel file from a slice of a cube using TI. I have no problem doing this manually, but I need it to happen at about 2:00 AM every day (and I only work the day shift) .

Our mainframe will create a file that will be FTP’d to a TM1 accessible folder. TI can recreate the cube with some processing. I just can’t figure out how to have TI then create the slice. I found a ProcessFunctionName of CubeSetSlicerMembers in the TurboIntegrator Guide pg 92, but of course it is not documented anywhere that I could find. Also, my searches in the forum have provided me with an answer.

Any help is appreciated.

Regards,
Bob

Edit: I really meant a snapshot (not a slice) - subject line corrected.

[jim wood]

Wouldn't it be easier to create an active form and adjust the subsets? That way anybody opening would see an update via MDX?

[Wim Gielis]

Hello Bob,

What's the reason for not just making the slice (once, manually), let TI update the cube(s) and press F9 in the slice in Excel ?
Why do you need to produce a new Excel file every day ?

Wim

[Bob Stuecheli]

The people accessing the Excel file don't have access to TM1, so I am not sure that an Active Form would work - although I have no experience using Active Forms.

The data on the mainframe changed every night so I need to refresh the Excel file daily. Just trying to automate the process so we have refreshed data first thing in the morning.

[tomok]

The issue here is that TM1 is not Excel and therefore cannot create slices in and of itself. Yes, there is an add-in for Excel that comes as part of TM1 that let's a client create a slice (even though what you really want is a snapshot, not a slice), but that action is something a TM1 client must do. TI's are processes that run on the TM1 server, independent of any client. TI's don't have the built-in capability to open Excel and perform keystrokes so I think this is a dead-end. There have been a number of posts on this board with your same request and I can't remember anyone having a TI-based solution.

What you really want to do is have Perspectives running on a stand-alone machine, and then have a macro that will connect to TM1, run the Print Report functionality to create the "Snapshots" you want and save them to the folder of your choice. I realize this is not elegant and would probably be frowned upon by your IT department but it's the only foolproof way to accomplish what you want that I know of short of dumping the data to an ODBC compliant database outside of TM1 and accessing it there.

[Bob Stuecheli]

Tom,
Thanks for the reply. You are correct, a snapshot is what I really wanted. Now that I search on snapshot (instead of slice) I am getting a lot of hits. Looks like I will have to create the snapshot manually every morning .

Thanks,
Bob

[David Usherwood]

Generally I would be with Tomok (believe it or not) - use Excel to generate Excel output from TM1. But it would be worth looking into Yuri Kudravcev's post on using Excel functions in TI via ExecuteJavaN/S - http://ykud.com/blog/cognos/tm1-cognos/ ... v-and-more. Yuri mentions an Apache foundation library for manipulating Office documents. Wouldn't be a walk in the park, but could be quite close to how you describe your requirement.

[Gabor]

If it's a single tab, which is needed in Excel, then a good workaround from my point of view is using TI to wite a .csv file.
This can be set up to look like a snapshot from a cube including header elements, rows and columns. Later on the file can be opened in Excel similar to natural file formats like .xlsx.
The csv doesn't allow additional formatting, but a snapshot is also unformatted. The only other limitation is, that you can't support multiple codepages with different settings for csv delimiter and decimal separator.

[lotsaram]

Generally I would be with Tomok (believe it or not) - use Excel to generate Excel output from TM1. But it would be worth looking into Yuri Kudravcev's post on using Excel functions in TI via ExecuteJavaN/S - http://ykud.com/blog/cognos/tm1-cognos/ ... v-and-more. Yuri mentions an Apache foundation library for manipulating Office documents. Wouldn't be a walk in the park, but could be quite close to how you describe your requirement.

Nice and fancy, but you don't always need nice and fancy. Excel VBA combined with windows scheduler will do the job nice and simple to open Excel, log on to TM1, burst some reports to required folders with required names. If there's no need to be complicated then best to be simple.

[Bob Stuecheli]

@Gabor and @lotsaram, thanks for the ideas. I will give them a try. I agree with keeping it simple. I will try the csv file approach first and see if it works for the end users. I would have to get IT involved for the Windows Scheduler so that will be my 2nd approach.

[Alexis Brussaut]

The csv doesn't allow additional formatting

Hi Gabor,
is there a way to generate (with turbo) an xls file with formats ?
Otherwise, I succed to generate a nice html file but xls is better.

Thanks a lot
Alexis.

[Wim Gielis]

Alexis,

What I can think of, is TI or Windows Task Scehduler, executing for example a vbscript code (ExecuteCommand).
There you can do whatever you want. For example, a TI process dumps the data in a csv file, and the vbscript picks up the data and formats.
Regarding formatting, I always tend to work with a template, then have vbscript copy the template and populate with data.
Like that, you don't need to do the formatting and stuff in vbscript code, but rather have it in the template.
It will also allow users more freedom in changing the template (within limits of course) without needing to change the vbscript code.

[Alexis Brussaut]

What I can think of, is TI or Windows Task Scehduler, executing for example a vbscript code (ExecuteCommand).

Hi Wim,
Thanks a lot, it's working very well and it's allowing to do lot of things.
I wish you a nice end of day.
Rgd