Annotation Cube: Loading new records

Post Reply
20 Ton Squirrel
Posts: 13
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

Annotation Cube: Loading new records

Post by 20 Ton Squirrel » Fri Nov 06, 2020 8:17 pm

I need to transition comments from Excel worksheets to TM1. I wrote VBA to export comments to a CSV, replacing any linefeeds with \n. Now I'm stuck trying to add these comments to the annotation cube.

I've seen code that describes how to edit pre-existing annotations but nothing about adding new ones. Does anyone have some guidance here?

The structure of the annotations seems simple enough, just a string detailing various properties with enclosures.

Code: Select all

[
 { 
   "caption":"",
   "creator":"somebody",
   "id":"GsYaDSlFvG",
   "properties":{"commentLocation":"Dim1Ele1,Dim2Ele1,Dim3Ele1,Dim4Ele1",
                 "commentType":"ANNOTATION",
                 "commentValue":"Meow meow\nhello world",
                 "objectName":"CubeNameGoesHere"},
   "timeCreated":"20201106174817"
 }
]
Is it kosher to inject the various properties along with random 10-character ID, then slap that into the annotation cube with a CellPutS?

I don't know where else that ID might be stored, not sure if screwing around with that will break something.

20 Ton Squirrel
Posts: 13
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

Re: Annotation Cube: Loading new records

Post by 20 Ton Squirrel » Fri Nov 06, 2020 9:16 pm

A bit of experimenting showed I can, indeed, just insert a new annotation into the cube. I did this manually by pasting in a doctored string directly to the annotation cube. I just made up a 10-character string for this new annotation. It loads fine browsing comments within TM1 web.

The question remains, however... is this safe to do?

20 Ton Squirrel
Posts: 13
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

Re: Annotation Cube: Loading new records

Post by 20 Ton Squirrel » Sun Nov 08, 2020 10:29 pm

Aaaaaaaand with a bit FURTHER tinkering I have it all working just fine. I think. Unless someone comes up with a reason why not.

Essentially, the ID appears to be a unique identifier for the annotation within that particular area. I see no other place that the ID is tracked within the TM1 structure. So long as your routine builds a fresh 10-character ID it should be fine. Just whipped out a process that loaded in 600+ comments, it all displays nicely within TM1 Web.

The loop to create the ID looks something like this...

Code: Select all

                vPosEnd = 10 ;
                vPos = 1 ;
                WHILE ( vPos <= vPosEnd ) ;           
                    vAntId = vAntId | CHAR ( INT ( ( 90 - 65  + 1 ) * RAND + 65 ) );
                    vPos = vPos + 1 ; 
                END ;  
Bearing in mind that 65-90 are the ASCII character codes from A to Z. The calculation above is just a quick/dirty randomizer between two numbers.

Wim Gielis
MVP
Posts: 2588
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Annotation Cube: Loading new records

Post by Wim Gielis » Sun Nov 08, 2020 10:31 pm

Thanks for the feedback. Maybe for future reference you might want to add the TI process here (with anonymous data).
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Using TI to change elemant names, like Biden to Trump

lotsaram
MVP
Posts: 3441
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Annotation Cube: Loading new records

Post by lotsaram » Mon Nov 09, 2020 9:01 am

As long as you respect the json format of the cell annotation cube you can create comments with TI process. You can even create the }CellAnotations_ cube on the fly with TI and it works fine. We have developed some solutions where an allocation process also automatically creates comments so that the user gets traceability of where the numbers are coming from.

Other useful things you can do but in the other direction is to process the }CellAnnotations cube and strip out just the commentary to export them or put in a format that is more easily reportable.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

MarenC
Posts: 127
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Annotation Cube: Loading new records

Post by MarenC » Mon Nov 09, 2020 9:33 am

As per this post

viewtopic.php?t=15299&p=76108

In workspace I needed to copy comments between versions and I expected I would have to pass in all sorts of information to the JSON string, like the new version I was putting the comment to.

But I found that a simple copy and paste of the comment in the annotation cube sufficed.
I was surprised that I didn't have to pass in the version and all I had to do was copy the string as is.

Maren

David Usherwood
Site Admin
Posts: 1426
Joined: Wed May 28, 2008 9:09 am

Re: Annotation Cube: Loading new records

Post by David Usherwood » Mon Nov 09, 2020 9:46 am

I've done little or nothing with annotations - but I see that TM1PY supports them and the methods look quite straightforward:
  • create(annotation)
  • delete(annotation_id)
  • get(annotation_id)
  • get_all(cube_name)
  • update(annotation)

20 Ton Squirrel
Posts: 13
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

Re: Annotation Cube: Loading new records

Post by 20 Ton Squirrel » Mon Nov 09, 2020 4:06 pm

Wow, thanks for all the fab advice everyone! I'll see about posting the full process separately, it is a bit lengthy and project-specific... hopefully not too horrifying to you veterans of TM1. :oops:

Post Reply