TM1 Challenge

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 1425
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

TM1 Challenge

Post by Wim Gielis » Thu Jun 22, 2017 6:45 am

Here's a challenge for those of us with too much time on their hands.

If you slice, snapshot or 'active form' data out of a cube and bring it to Excel, the row dimensions (if any) do not have a header. Given the layout of dimensions in titles, rows, columns, come up with a solution to determine what are the row dimension names and fill in the names, just above the first element in the row area. Not easy I presume.

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

User avatar
Alan Kirk
Site Admin
Posts: 5657
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: TM1 Challenge

Post by Alan Kirk » Thu Jun 22, 2017 7:05 am

Wim Gielis wrote:Here's a challenge for those of us with too much time on their hands.

If you slice, snapshot or 'active form' data out of a cube and bring it to Excel, the row dimensions (if any) do not have a header. Given the layout of dimensions in titles, rows, columns, come up with a solution to determine what are the row dimension names and fill in the names, just above the first element in the row area. Not easy I presume.
I know that you and active forms hate each other with a passion but I would think that that would be the only type where this would be (relatively) easy; you'd just need a formula or function to parse the relevant TM1RptRow function. For the others, short of some convoluted and processor cycle-wasting method that looks up the elements in the rows and compares them to the cube's dimensions (and this assumes that there aren't elements with the same names in multiple dimensions), the only other way I can think of is to parse out one of the DBRW formulas and read the dimension from the argument position. You could potentially use a UDF for this but it wouldn't be pretty... (Nor would it work with a snapshot, obviously.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Steve Rowe
Site Admin
Posts: 1626
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: TM1 Challenge

Post by Steve Rowe » Thu Jun 22, 2017 7:49 am

Couldn't you just look at the server explorer or use your business knowledge of the application and type the name in the cell?

Wim Gielis
MVP
Posts: 1425
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 Challenge

Post by Wim Gielis » Thu Jun 22, 2017 5:35 pm

True Steve, we could do that. The idea could be to have a menu item in the ribbon in Excel that would enter these missing headers.

When you snapshot or slice to Excel, it's often to check with for example an autofilter or a pivot table. Then you would need the headers filled in.

If I would look in the Server Explorer, I wouldn't have called it a challenge ;-)

I guess that in VBA we can come up with logic to determine what are the missing dimensions, but there is always a slight possibility that elements with the same name occur in multiple dimensions. In that case, only a DBRW formula would help us (which isn't there in a snapshot).

So yes it won't be easy and I leave it to those who want and have the time for it :-)
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

pandinus
Posts: 72
Joined: Tue Mar 18, 2014 8:02 am
OLAP Product: TM1, Cognos Express
Version: 10.2.2
Excel Version: 2013

Re: TM1 Challenge

Post by pandinus » Mon Jul 10, 2017 1:48 pm

It should not be too hard actually. All the required ingredients are available, just a matter of mixing them in the right manner to get to your required result.

With slice and active form you have the DBRW formulas which you can have a look at to determine the dimensions in your 'row columns'. No issues there.
In case of a snapshot you have all the information, except for the dimension names on your 'row columns'.

So, the next steps will get you what you want:
  • Check B1 to find out the relevant cube
  • Check A2 to Ax to find out which dimensions are not on the 'row columns', now you know the missing dimension/column names
  • Check if the first element of your first 'row column' (Arx) is in any of the missing dimensions.
    • In case of just one match, yay. On to the next column.
    • In case of multiple matches, check for Ar+1 until you run out of populated rows. Odds are you will end up with a unique dimension name before this happens.
      • In case of no unique dimension name yet, continue with the next column and repeat. You should end up with a smaller list of potential dimensions.
      • Continue checking until you can not longer find any uniques. This is the best you can do, yet in 99.9% of the cases you should be done now.
In my opinion this should not be too complex to write (unless you're also making a total mess of your column headers, which again might complicate stuff) but I cannot be bothered at the moment to invest time into this ;)

Wim Gielis
MVP
Posts: 1425
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 Challenge

Post by Wim Gielis » Mon Jul 24, 2017 11:11 pm

Thank you Pandinus for your reaction. I wrote the code (unpolished but it works fine :-) ) and if someone is interested, let me know. I doubt that there will be some interest given that TI code that I find useful (like http://www.tm1forum.com/viewtopic.php?f=21&t=13373 and http://www.tm1forum.com/viewtopic.php?f=21&t=13293 ) did not yield a reaction or thank you.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

Post Reply