Annotation Cube: Loading new records

[20 Ton Squirrel]

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]

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]

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]

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

[lotsaram]

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.

[MarenC]

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]

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]

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.