TM1 report bursting

Post Reply
Wim Gielis
MVP
Posts: 1976
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

TM1 report bursting

Post by Wim Gielis » Fri Mar 15, 2019 10:34 am

Hello all,

My case is the following. I have a Perspectives report with DBRW and SUBNM and I would like to:
- generate a version of the report with hard values
- email the report to a number of recipients
- this should be done in an automated way

No manipulations need to happen in the report, only getting rid of formulas. This report should be generated every night.

Now, I have it working up to a certain degree. I.e. Excel is installed on the server.
A TI process executes a Scheduled Task on the server.
This task executes a VBScript. The vbscript opens up a new Excel instance, and also an Excel file with 1 macro.
The VBA code in the macro logs on to TM1 Perspectives (no API coding, just TM1p.xla file and its macros), opens the report, zaps the formulas, saves a copy of the file, closes the original file and shuts down Excel.

This works perfectly if me or anyone else is logged on to the server when the TI process executes the vbscript.
However, in the case of NO USER being logged on to the server at that time, it does not work completely. I don't understand why.
It seems that the Application.Run "TM1REFRESH" or "TM1RECALC" is the culprit - or any such code. There, in the VBA code, it stops suddenly, and only when no user is logged on to the server. Otherwise the REFRESH just happens fine.

Does it ring a bell ? Does anyone have a clue what is missing here ? Or should I approach it differently ? The requirement is to generate a report with hard values in a fully automated way - including that no user should do it manually or that someone be logged on to the server. The TM1 Print Report wizard is not an option ;-) Nor the PAW bursting.

I already changed a number of Windows options and settings but somewhere there might still be a hidden option.

Thanks !

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

User avatar
orlando
Posts: 64
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: TM1 report bursting

Post by orlando » Fri Mar 15, 2019 12:39 pm

Hi Wim,

i did the same for a customer with TM1 10.2.2, but i startet the excelsheet (with the macor) directly via the scheduler (without startet by TI)
there wasn't someone logged on - it worked fine

maybe you should try it with task scheduler only

regards
orlando

Wim Gielis
MVP
Posts: 1976
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 report bursting

Post by Wim Gielis » Fri Mar 15, 2019 1:08 pm

Thanks, I will try to set up a variant without TI and without the vbscript part in the middle. Focus on the Excel and scheduled task part only.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

User avatar
ykud
Posts: 47
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: TM1 report bursting

Post by ykud » Sat Mar 16, 2019 12:31 pm

Wim Gielis wrote:
Fri Mar 15, 2019 10:34 am
This works perfectly if me or anyone else is logged on to the server when the TI process executes the vbscript.
However, in the case of NO USER being logged on to the server at that time, it does not work completely. I don't understand why.
It seems that the Application.Run "TM1REFRESH" or "TM1RECALC" is the culprit - or any such code. There, in the VBA code, it stops suddenly, and only when no user is logged on to the server. Otherwise the REFRESH just happens fine.
Try this:
- create desktop folder in c:\windows\system32\config\systemprofile\desktop
- create desktop folder in c:\windows\sysWOW64config\systemprofile\desktop
if it doesn't help, try this as well
- Go to "Start" -> "Run" and enter "MMC comexp.msc /32"
- Go to the properties of Microsoft Excel Application, under Identity, change it to This User from The Launching User (which is set by default). Input the credentials of TM1 services user account

Wim Gielis
MVP
Posts: 1976
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 report bursting

Post by Wim Gielis » Sat Mar 16, 2019 2:27 pm

Hello Yuri

Thanks. I should have added the following too:

- This topic is a continuation of my earlier topic: https://www.tm1forum.com/viewtopic.php?f=3&t=14157
It's the same challenge and the same problem. Last year I thought it worked, but it does not. I forgot to update that topic, I did this now. The setting is that I am doing this again for a different customer, the end result is similar (produce a report while automating Excel and TM1)

- I created a Word document with things I did and checked. Please find it attached, for those of us that experience the same issue - while it still does not work. I was looking at the identity properties of Excel too, but I used the 'Interactive user'. I tested also what you wrote down, thanks for that, but still no avail.
The testing that I am currently doing:
- I am not using a TI process to start anything, but just a scheduled task
- The task runs with an account that is part of the 'Administrators' group
- If I schedule the task to run at a certain point in time, so scheduled, and I am logged on: it works fine.
- If I schedule the task and I log off (and everyone else is logged off): it does not work
- I simplified the VBA coding in the Excel file to just create a text file on the server itself on a disk there. No TM1 stuff whatsoever
- The paths are local and on the server, I also tested with \\servername\... UNC paths: it still works when logged on, not working when logged off
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

User avatar
ykud
Posts: 47
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: TM1 report bursting

Post by ykud » Mon Mar 18, 2019 12:22 am

Wim Gielis wrote:
Sat Mar 16, 2019 2:27 pm
- If I schedule the task to run at a certain point in time, so scheduled, and I am logged on: it works fine.
Can you see under what user 'excel.exe' process is running in this case? Yours or the one you set up in Scheduled tasks?

Interactive Identity setup will launch excel under your account, specifying an account should run it under that account.

Wim Gielis
MVP
Posts: 1976
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 report bursting

Post by Wim Gielis » Mon Mar 18, 2019 1:53 am

Hi,

If Excel identity is interactive, it's my username (wimgieli)
If Excel identity is the launching user, it's the username with which the task is created (tm1burstservice)

I now have the following:
- everyone logged off, Excel identity set as the user wimigeli and the scheduled task running under wimgieli: it runs a macro BUT !
* opening an Excel file normally (logged on again, as wimgieli) seems to produce error messages in Excel about the content of the file (I have to change the Excel identity back to the launching user to open up Excel files normally again - I wonder whether that will give adverse effects in the future in case (power) users log on to the server and open a file directly on the server
* Application.Run TM1REFRESH and similar macro's do not run while clearly the tm1p.xla is loaded (confirmed with text files). It's about twevrecalc1 and such errors
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

jrizk
Posts: 46
Joined: Thu Nov 19, 2009 10:38 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: 2010

Re: TM1 report bursting

Post by jrizk » Mon Mar 18, 2019 3:52 am

I don't think it will work when everyone is logged off. The only way it seems to work is to schedule the task under the Administrators group - not a user account which is part of the Administrators group - and this only runs when a user is logged on. The Excel identity is 'The Interactive user'. I think in my last post about this I mentioned you can run the task via TI using

sCmd = 'cmd /c schtasks /Run /TN "Your Task Name Here"';
ExecuteCommand (sCmd, 1);

also may have mistakenly mentioned to try this when logged off but it does require a login. Probably doesn't resolve having the report bursting done when there is no login but it's the only way it the scripts and Tm1/Excel appear to behave correctly - unless someone else has been able to get it to work with no login.
Attachments
sched.jpg
sched.jpg (28 KiB) Viewed 104 times
dcom.jpg
dcom.jpg (46.73 KiB) Viewed 104 times
J.Rizk
Tm1 for everyone

Wim Gielis
MVP
Posts: 1976
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 report bursting

Post by Wim Gielis » Mon Mar 18, 2019 8:30 am

Hello jrizk,

Thank your for confirming that this isn't going to work. But... it did work in previous versions of Windows. Anyways.

What are the different options I still have ?
- Someone here on the forum, or within my organisation, the customer's organisation, the Internet, ... knows a way to do it. I doubt it very much to be honest.
- Someone is logging on every day and generates the report(s). Not ideal of course.
- If such reporting is really important and involves more than 1-2 reports, I might think of a solution in which:
* in an automatic way, a 'user' is logged on if not already logged on, the report is created, and (optionally) the 'user' is logged off again. Not sure how difficult this will be.
* relying on someone to be logged on on the server because in that case it seems to work fine. If the file is not present in a certain folder and with the timestamp of today, probably due to no user being logged on, then shoot an email to some users in the organisation to ask them to do it manually
- This week a couple of thousands of Microsoft MVPs (Most Valuable Professionals) gather in Seattle at Microsoft for their annual summit, including Excel MVPs. I try my luck by shooting an email to friends/contacts there. So they can spend their evenings over beers and a real-world problem :-)
- Looking at a paid solution and understanding what are the requirements there (i.e. how can another solution automate the reports if we assume that it's not possible ? Different technology ?)

It will be important to not disturb users by changing settings on the server, if that's not necessary.

Thanks all for your efforts.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

Post Reply