TM1 Excel Advice/Training

[lrob555]

I'm hoping for a little guidance.
I'm currently working on the cube side of tm1 writing and developing cubes, processes etc.
But have recently been asked to look at creating excel based reports based on them. I was wondering if anyone knew of any good training or places to start to look at this. Specifically using MDX and VBA to create parameter based reports for users.

Many Thanks

[Wim Gielis]

Hi

For the reports and making them dynamic with MDX'es: I would suggest that you start exploring this area in the Cube Viewer.
After that, make an "Active form" and swap the relevant MDX statements into the formulas (TM1RPTROW formula).
The MDX could be chopped up to use cell references, where the user makes choices on parameters.

For the VBA side of report creation and maintenance, how is your knowledge of:
- Excel itself
- VBA itself
- TM1 itself

These are the 3 main ingredients that you need. You might go somewhere with it if your knowledge is not too advanced,
but to go a long way, in my opinion, you need to master all of those 3.
And once you know them, I guess it is relatively straightforwards to set up dynamic reports and analyses.

[lrob555]

Thanks for the response I'll start playing with the cube viewer functions.

My knowledge of TM1 if purely from the designing and creating cubes themselves so a fair bit of TI and SQL.

I do have had previous experience in VBA with integrating applications, but I'm finding the TM1 functions themselves tricky to get a grip on and can't seem to find any training courses which deal with this side of things. So any book or course suggestions would be greatly appreciated.

[Alan Kirk]

Thanks for the response I'll start playing with the cube viewer functions.

My knowledge of TM1 if purely from the designing and creating cubes themselves so a fair bit of TI and SQL.

I do have had previous experience in VBA with integrating applications, but I'm finding the TM1 functions themselves tricky to get a grip on and can't seem to find any training courses which deal with this side of things. So any book or course suggestions would be greatly appreciated.

If you're talking about MDX, I suggest you make your first stop this thread. 600-ish downloads don't lie.

If you're talking about the functions within an active form like (as a random instance) TM1RptElLev... no, they're not immediately intuitive but when you've plugged away at them for a while they start to make sense. The Reference Guide is your best friend for this but ultimately it's playing with them that will give you your moment of revelation.

If you're talking about "macro" functions, again the Reference Guide (particularly this bit) is helpful. The only painful thing about them is that because the functions are in a different workbook you need to call them using the Application.Run method. Once you get the hang of doing that (passing the function name followed by the arguments) it's fairly straightforward; it's only the absence of Intellisense and the need to double check the arguments that's an irritation.

You may also want to take a look at IBM Cognos TM1 The Official Guide which is now a couple of versions out of date, but less so for what you're looking at doing. Be aware that if you read Part I including the chapters "Challenges to Meeting Enterprise Performance Management Requirements", "The Promise of In-Memory Analytics with IBM Cognos TM1", and "Evolution of IBM Cognos TM1", all of which are as free of substantive content, choc full of sales-speak, and worthy contenders for a full game card of Technobabble Bingo as their names suggest, you will want to throw the thing into the nearest wall at quite some substantial velocity. Start from Part II and there's a bit more for you to work with.

[lrob555]

Thank you,
I'm off to do quite a lot of reading and banging my head on the desk when it doesn't work for the 100th time, but at least I have a place to start so thank you.

[tomok]

Thank you,
I'm off to do quite a lot of reading and banging my head on the desk when it doesn't work for the 100th time, but at least I have a place to start so thank you.

I have a lot of dents in my head from years of working with TM1. I wear them as a badge of honor. Seriously though, you learn twice as much by just trying things and seeing what works.