TM1 Forum

Is there anyway to have a view of aggregated data snapshotted to excel using a TI process?

I have a cube that is using daily values....its huge and if I use the ascii output for that it takes forever.
So I made a view to aggregate into weeks. I can snapshot into excel no problem and doesn't take too much time. I can't figure out how to use the TI asciioutput using aggregate weeks without it taking forever/crashing the server.

Would luike to have it run as a chore to run at night or something into excel...as a best case scenario

I am probably not explaining in the best way, but will try my best to answer if I required

Plunge pool wrote:I can snapshot into excel no problem and doesn't take too much time. I can't figure out how to use the TI asciioutput using aggregate weeks without it taking forever/crashing the server.

Do you exclude 0 cells in the views?
What exactly is in the view - to you create the view using TI code or do you create the view manually?

Hello,
I dont know that there is a way to "snapshot" to Excel from TI, I think you may have been on the right track with the AsciiOutput function although be aware that there are versions of 9.5.2 where the server crashes if you try to export consolidated values.
As you said in your post, perhaps some further clarification is required, but could you have an MDX statement on the time dimension to make sure that a CubeView is always looking at just your most recent data?

Yes, I exclude the zeros. I should rephrase that sentence. I can figure that out....but I am more interested in copying EXACTLY what is in the view I created and exporting EXACTLY what is in that view to a cma file or excel file. Hence the question about snapshotting to Excel

Thanks!

Why "using a TI process"?

The best way to get data from a view into Excel is to have an array of DBRW formulas in Excel and simply recalculate. Easy, quick, simple, no fuss. Why complicate things? If this requires automation and output in an excel file with no links to a tm1 server then IMO the easiest and most effective route would be windows scheduler with some vba to log in then refresh then hard copy then save as. Why does TI need to be part of the solution? There might be a valid reason but if so you haven't explained what it is.

lotsaram wrote:Why "using a TI process"?

The best way to get data from a view into Excel is to have an array of DBRW formulas in Excel and simply recalculate. Easy, quick, simple, no fuss. Why complicate things? If this requires automation and output in an excel file with no links to a tm1 server then IMO the easiest and most effective route would be windows scheduler with some vba to log in then refresh then hard copy then save as. Why does TI need to be part of the solution? There might be a valid reason but if so you haven't explained what it is.

Thanks for your insight Lotsaram. The only reason to have TI as part of the solution is to also give the option to give the user the ability to run a process on demand. I am intrigued by your suggestion though.

My end goal is to have a report to provide users, but I am also thinking of using this report as a datasource for other applications. This is why I want ot get the cube data to snapshot into excel in a certain way.

Plunge pool wrote:

lotsaram wrote:Why "using a TI process"?

The best way to get data from a view into Excel is to have an array of DBRW formulas in Excel and simply recalculate. Easy, quick, simple, no fuss. Why complicate things? If this requires automation and output in an excel file with no links to a tm1 server then IMO the easiest and most effective route would be windows scheduler with some vba to log in then refresh then hard copy then save as. Why does TI need to be part of the solution? There might be a valid reason but if so you haven't explained what it is.

Thanks for your insight Lotsaram. The only reason to have TI as part of the solution is to also give the option to give the user the ability to run a process on demand. I am intrigued by your suggestion though.

My end goal is to have a report to provide users, but I am also thinking of using this report as a datasource for other applications. This is why I want ot get the cube data to snapshot into excel in a certain way.

Sorry, one more thing that goes against your suggestion though. I don't think VBA supports logging in through the CAM screen right? ie. don't think I can get sendkeys to work via that route?

Hello,
I think you may be jumping ahead a little considering API programming and data exports at this stage. The requirement you have described (as I understand it) seems to be fundamental TM1/Excel functionality as suggested by lotsaram.
My suggestion would be

• Build a report in Excel that shows the data that you need using a slice or active form (which ever is most appropriate)
• If you need to increment dates to look at the most recent data you can do this in Excel using formulae to create column/row headers or you can reference a dynamic subset which uses MDX;

Using this approach, your report could be made available to all users via Excel directly or TM1 Web.
I note that you have also been looking at http://www.tm1forum.com/viewtopic.php?f=3&t=8845 which is about report bursting. You could perhaps use this idea to distribute your report should that become necessary.
I would be wary of exporting data to become a data source in it's own right.

AmbPin wrote:Hello,
I think you may be jumping ahead a little considering API programming and data exports at this stage. The requirement you have described (as I understand it) seems to be fundamental TM1/Excel functionality as suggested by lotsaram.
My suggestion would be

• Build a report in Excel that shows the data that you need using a slice or active form (which ever is most appropriate)
• If you need to increment dates to look at the most recent data you can do this in Excel using formulae to create column/row headers or you can reference a dynamic subset which uses MDX;

Using this approach, your report could be made available to all users via Excel directly or TM1 Web.
I note that you have also been looking at http://www.tm1forum.com/viewtopic.php?f=3&t=8845 which is about report bursting. You could perhaps use this idea to distribute your report should that become necessary.
I would be wary of exporting data to become a data source in it's own right.

Thanks. However, their is one more requirement. The cube is setup with daily values. I only need weekly values. Therefore a snapshot takes ALOT less time to generate on demand. I am aware of the bursting, and had even set this up, but that takes a long time to generate because of the nature of the cube.

Without knowing some more detail it is difficult to help but

• Could you put weekly consolidations in your time dimension
• Calculate the latest weeks consolidation in the spreadsheet/websheet

An alternative approach might be to build a reporting cube with weekly slots that is populated via TI from your daily data. As I say, without knowing your ircumstances it is difficult to assist properly.

Hello,
I was thinking about this from your earlier post:-

Plunge pool wrote:Therefore a snapshot takes ALOT less time to generate on demand

I assume you mean generating a snapshot to Excel from a cube view. I may be completely wrong here but I would have thought that the work involved for TM1 in the initial generation of eiher a snapshot or slice (or Active form for that matter) was pretty much the same, I certainly don't recall seeing any significant time differences.
Apologies if I have I mis-understood your point?

AmbPin wrote:Hello,
I was thinking about this from your earlier post:-

Plunge pool wrote:Therefore a snapshot takes ALOT less time to generate on demand

I assume you mean generating a snapshot to Excel from a cube view. I may be completely wrong here but I would have thought that the work involved for TM1 in the initial generation of eiher a snapshot or slice (or Active form for that matter) was pretty much the same, I certainly don't recall seeing any significant time differences.
Apologies if I have I mis-understood your point?

I know it sounds weird. But for some reason when I have the cube layed out in a weekly view, i guess its already "read". Therefore a snapshot at the point is a quick one.
Whereas slicing it/active form would require another read through the values in daily fashoin, and populate to excel.

I might be completely wrong herre though, as I am a compelte newb.

Plunge pool wrote:I know it sounds weird. But for some reason when I have the cube layed out in a weekly view, i guess its already "read". Therefore a snapshot at the point is a quick one.
Whereas slicing it/active form would require another read through the values in daily fashoin, and populate to excel.

I might be completely wrong herre though, as I am a compelte newb.

No, you're not wrong. It's called caching of calculated intersections and TM1 makes heavy use of this principle. However, I don't see what, if anything, that has to do with having a TI process create a snapshot versus doing it in an Excel sheet. First of all, you can't do it in TI because a TI process can only create a text file, not an Excel sheet. The best you could do is create a CSV file that Excel could read in. No formatting, no nothing, just values and maybe column headers if you know what you're doing. On the other hand, with Excel you can do most anything you want, just range value the TM1 formulas as your last step. This is very common and their are a number of posts on this site about how to do it.

I agree with tomok. A well thought out Excel sheet will give you the best user experience and provide the opportunity to interact further with that data should people wish to perform further investigation of the high level results presented.

When Excel introduced > 1 Mill rows I saw people trying to use this huge grid for snapshots. Then TM1 (I have tested using 9.5.2) becomes very slow after a certain limit (pure data). Lets say 1 min for 50.000 lines, but 120 min for 1.000.000 lines.

Apart from the fact, that I hate this, there are always some people, who want to see their "wallpaper" and load it into Excel and play with it.
(useless to tell them, that's why we enjoy TM1)

The only workaround I found was to run a TI, which writes into a .csv (as already mentioned above). Even if the file size runs high to several hundred MB, the TI is still able to write my example in less than 10 min and Excel can open the file very fast.