Automation of Excel TM1 Based Reports

[jim wood]

Guys,

We are looking to automate the production of some excel reports. Here is our plan of attack (Well my plan really ):

1) Add some VBA script to each report that will: automatically logon, refresh the data (easy I know), publish the file to pdf and finally close the file.

2) Create a windows scheduled batch to run each report in turn.

Could you guys let me know if there are any issues with any of the above and also if you have had experience of completing a similar approach,

Jim.

PS. May run the schedule from SQL in stead of windows if possible.

[wissew]

Good plan. That's what we do.

[bihints.com]

Hi Jim,

I have put together some VBA code that reads from a TM1 cube what reports to update, where to save them, whom to email/notify it or print.
All entries are consolidated into rollups such as "Monday" "Tuesday" etc.. So the script runs only the reports on the given days specified.

So I do not need to change or add VBA to an existing report, I just need to give the location of the report in that central reporting cube and what to do with it (save as values somewhere, email, print..)

Hopefully I should clean up that code and publish it. It is pretty basic actually and I'm sure other people on the forum have come up with something similar and certainly better.

[vaneagle]

I do a similar process from ms access. Refresh data in excel and save via windows scheduling.

In the next 6 months, perhaps longer (ie once i learn how) i plan to use tm1 to do the same thing...

ie refresh reports, save or send to pdf and then email.

It sounds like methodlogy os sound!

[jim wood]

Many thanks all. I will post on here any code that we create,

Jim.

[Olivier]

Hi Jim,

I have seen use this principle in order to "burst" reports out of Tm1 cubes.
1 master file contain all the vba and the configuration and the references to the folders and templates to be used.

One issue we are having regurlarly is for example a "Save Data All" happening on the server while the report are beeing produced, it will prevent the refresh sequence to happen properly without stopping Excel from continuing bursting, very likely to produce half cooked reports...needing the automtion to be re run several times for full completion...

I guess it can be administrate more efficiently,
but i don't see report bursting as a convenient approach to deliver reporting out of Tm1.
( even if i think i understand your constraint in producing disconnected reports i.e pdf...)

Hope this helps.

Kind Regards,

Olivier

[jim wood]

It's more of an automation of what we already do. Also we currently run all our automated loads / saves through SQL. What I am going to do is add the reporting part to the same SQL schedule after all saves to make sure we avoid the issue you have raised. Also we looking at a package called ghost script to publish all the excel reports to pdf before they are emailed,

Jim.

[Olivier]

As you publish in pdf, i am sure you have consider web based publication ?
Why do you distribute througth email ?
Is that because you consider the web wouldn't provide any added value to your publications ?

i am just trying to figure out how much your environment match my current one

Kind Regards,

Olivier

[jim wood]

Good question.... We are going to do both. We are going to post the reports on a site but we are also going to email some reports for 2 reasons:

1) Some of the reports would be considered too risky in the wider company domain

2) We have some directors who still think that the web is sole domain of the spider.

[Olivier]

Fair enougth

Funny how data security becomes an issue as soon as you talk about web were usually you find that previously the excel reports were hanging in a totally unsecure NT network ...

On the other hand i find quite rare that web publishing add any value to the publication itself.
I feel that publishing a pdf or static report througth the web doesn't really achieve much appart from reducing paper and disk space consumption...

I beleive a big area of improvement in our field is in the improvement of intereactivity of web publication.



Olivier

[rscully]

Hi Jim-
We're looking to do the same thing you did. Would you be willing to share the code so that I don't have to recreate it?

Thanks!
Rich

[jim wood]

We didn't do this in th end. The whole idea of reporting kicked off a massive project in our company. We have purchased Oracle OBIEE to deliver it and we are in the process of rolling it out now,

Jim.

[rscully]

lol... well thanks for getting back to me I certainly appreciate it! Good luck with your Oracle rollout.

Rich

[ParisHilton]

We didn't do this in th end. The whole idea of reporting kicked off a massive project in our company. We have purchased Oracle OBIEE to deliver it and we are in the process of rolling it out now,

Jim.

Why did you go for OBIEE over IBM-Cognos BI?

[VRGultom]

We are looking to automate the production of some excel reports. Here is our plan of attack (Well my plan really ):

1) Add some VBA script to each report that will: automatically logon, refresh the data (easy I know), publish the file to pdf and finally close the file.

Why don't you use OLAP programming such as dot net, VB, etc separately to access data from TM1 cube?
it is better than VBA script in excell

2) Create a windows scheduled batch to run each report in turn.
If I am not mistaken you can use chore to do that

Could you guys let me know if there are any issues with any of the above and also if you have had experience of completing a similar approach,

[jim wood]

We didn't do this in th end. The whole idea of reporting kicked off a massive project in our company. We have purchased Oracle OBIEE to deliver it and we are in the process of rolling it out now,

Jim.

Why did you go for OBIEE over IBM-Cognos BI?

I didn't. Also TM1 was not included in BI when we went through the selection process and it's inclusion was only a rumour that Cognos would neither confirm or deny. Oh and the demo given by Cognos wasn't great. I sat through it. I still feel now it's 2 hours I'll never get back. I think they thought that with EP and TM1 already in the business it would be a push over. One slight problem, both are owned by Finance and IT completed the selection process with "some" advice from the business.

Is OBIEE perfect? No. Is it better than Congos BI? Not sure. We are finding weaknesses all over the shop but I'm sure the same would apply if we had gone for Cognos BI.