Tm1py: Increment/Overwrite on a write back to a cube

[dfrankenCW]

Hi there

I had to do an incremental load using tm1py only to realize that there is no option to increment a load. (Like good old TI)

a) Will this be worthwhile in adding a parameter to the cube writeback to increment or overwrite.
b) If not, what is the quickest way to do an incremental load?

Thanks and happy tm1pying!

Douw

[rkvinoth]

1. Get the data for the dimension combination (tuple)
2. Increment the value
3. Write the value to the dimension combination (tuple)

IMO, export the data into a csv file and load it into TM1 using a TI process.

[MariusWirtz]

Hi @dfrankenCW,

Yes. You could go down the TI road as @rkvinoth suggests.
Write to CSV and call a dummy TI process that does a CellIncrementN for every line in the CSV.
It should work but it's not very pretty IMO. It wouldn't work if python doesn't run on the same machine though.

If you are looking for a pure REST / TM1py solution, you could probably do this:
If you are using the write_values_through_cellset function you could first execute the MDX with the execute_mdx_values function to retrieve a list of values. Then you could add the retrieved values to the values that you actually write (both lists should have exactly the same length) and then execute the write_values_through_cellset function with the MDX and the new values.
Of course, this approach only works if the MDX is not zero suppressed.

Actually, we could also build this functionality into the write_values_through_cellset function. I think we could add an additional boolean argument increment that drives it.

[scrumthing]

@MariusWirtz wouldn't it be better do to it the other way around?

• build your cellset with the incremental values
• retrieve the existing values
• add the existing values to the cellset with the new values
• write to cube
  That way you do not have to worry about a zero suppressed mdx...
  And that way you could probably implement it in the write_values_through_cellset function but don't you still need an mdx to get the existing values?

[MariusWirtz]

@scrumthing,
So you are suggesting to add the new increment argument to the write_values function.
From the keys in the cells dictionary, we can retrieve the existing values and do the incrementation in py. Yes.

I will do two changes this week:

1. add the new increment argument to the write_values function.
2. change the write_values function to use the MDX based write_values_though_cellset function behind the scenes. Instead of calling the tm1.Update REST action on the cube, because it's ~ 2x faster and simplifies the increment logic. Since the MDX is constructed inside the function (based on the keys), we can be sure that it's not zero suppressed.

[scrumthing]

Sounds like fun. Looking forward to testing it. :-)

[MariusWirtz]

@scrumthing,

feel free to test the new write function with the increment argument.

pip install https://github.com/cubewise-code/tm1py/archive/feature/write-with-increment.zip --upgrade

The increment causes surprisingly little overhead.
In my test case retrieving the cell values (to do the increment) from ~500k took only a second or two after the cellset was created. Executing the MDX initially and updating the cells, on the other hand, take most of the time.