How to create a cube snapshot using TI
-
- Posts: 12
- Joined: Fri Dec 12, 2008 8:53 pm
- OLAP Product: TM1
- Version: 10.2.2, 8.4.5, 2.5 (in 1987)
- Excel Version: 2013
- Location: Troy, MI
How to create a cube snapshot using TI
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.
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.
Last edited by Bob Stuecheli on Tue May 10, 2016 6:56 pm, edited 2 times in total.
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: How to create a cube slice using TI
Wouldn't it be easier to create an active form and adjust the subsets? That way anybody opening would see an update via MDX?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: How to create a cube slice using TI
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
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
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 12
- Joined: Fri Dec 12, 2008 8:53 pm
- OLAP Product: TM1
- Version: 10.2.2, 8.4.5, 2.5 (in 1987)
- Excel Version: 2013
- Location: Troy, MI
Re: How to create a cube slice using TI
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.
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.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: How to create a cube slice using TI
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.
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.
-
- Posts: 12
- Joined: Fri Dec 12, 2008 8:53 pm
- OLAP Product: TM1
- Version: 10.2.2, 8.4.5, 2.5 (in 1987)
- Excel Version: 2013
- Location: Troy, MI
Re: How to create a cube slice using TI
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
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
-
- Site Admin
- Posts: 1454
- Joined: Wed May 28, 2008 9:09 am
Re: How to create a cube snapshot using TI
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.
-
- MVP
- Posts: 170
- Joined: Fri Dec 10, 2010 4:07 pm
- OLAP Product: TM1
- Version: [2.x ...] 11.x / PAL 2.0.9
- Excel Version: Excel 2013-2016
- Location: Germany
Re: How to create a cube snapshot using TI
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.
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.
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: How to create a cube snapshot using TI
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.David Usherwood wrote: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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 12
- Joined: Fri Dec 12, 2008 8:53 pm
- OLAP Product: TM1
- Version: 10.2.2, 8.4.5, 2.5 (in 1987)
- Excel Version: 2013
- Location: Troy, MI
Re: How to create a cube snapshot using TI
@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.
-
- Posts: 2
- Joined: Mon Oct 29, 2018 8:43 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: How to create a cube snapshot using TI
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.
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.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 2
- Joined: Mon Oct 29, 2018 8:43 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: How to create a cube snapshot using TI
Hi Wim,Wim Gielis wrote: ↑Mon Oct 29, 2018 10:49 am What I can think of, is TI or Windows Task Scehduler, executing for example a vbscript code (ExecuteCommand).
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