Tm1py: DataSpreadFailed error when updating an alias

[nicolasbisurgi]

Hi,
I'm working on a process to update a dimension with hierarchies and attributes. My source file has 57 attributes with ~57k elements so I created 57 data frames, their corresponding MDX views and calling the write_values_through_cellset() function asynchronized (workers=20).

One of them is firing an error (the rest are fine):
Text: {"error":{"code":"143","message":"DataSpreadFailed"}}
The odd part is that the attribute being updated is an alias, so not sure why it is attempting to spread data. I've also defined the series as an object in pandas (also as a str for debug purpose).

Do you have any idea why this could be happening? What would be attempting to spread if it is a string value?

Version
TM1py 1.5.0
TM1 Server Version: 11.6.00004.2

[rkvinoth]

1. Are all your 57 attributes alias?
2. Were you able to pin point the issue to any particular row in your DataFrame?

I would suggest you to find the DataFrame that is causing the issue then try using cells.write_values method by looping through that DataFrame.
You will eventually be able to find the row that is causing the issue.

If you still can't figure out, please provide some sample to replicate the issue.

PS: We already have a method for loading a DataFrame directly.

[MariusWirtz]

The error message DataSpreadFailed could mean that you are trying to write a string value into a numeric cell.
Perhaps the writing fails because you trying to write a value that is already the alias for a different element.

[nicolasbisurgi]

Hi Vinoth/Marius,
Thanks for the quick reply. Within these 57 attributes there are no Numeric ones, only aliases or text attributes.
From the 57 DataFrames:

• 55 were loaded successfully
• 1 failed due to Alias not-unique (giving the StringCellUpdateError)
• 1 failed giving the DataSpreadFailed and I don't know why.

Would it be possible to get two different error messages (StringCellUpdateError and DataSpreadFailed ) for the same kind error (alias not unique)?

I'll take Vinoth suggestion and try to write this DF back to PA using the cells.write_values to see if that gives me the problematic row.

I know you've included the dataframe to PA function, but it uses write_values function which is not as performant as the write_values_through_cellset() one. I have ~400 files (dimensions) with ~25 attributes each, so I want to make this as fast as I can.

I'll get back to you as soon as I get results. If you can think on anything else please let me know.

Thanks!!!!

nico

[MariusWirtz]

Hi @nicolasbisurgi,

actually, the fastest way to write is to use the new write function with the optional argument use_ti=True.
Behind the scenes, it uses unbound TI processes to write in the most efficient way.

from TM1py import TM1Service

with TM1Service(address="", port=12354, ssl=True, user="admin", password="apple") as tm1:
    tm1.cells.write(
        cube_name="}ElementAttributes_d1",
        cellset_as_dict={("e1", "Number"): 3},
        use_ti=True)

Also, it has a slightly different commit-mode. Like if one write operation fails the function throws a TM1pyException but only one cell is not committed while all the other cells are still written successfully.

If errors do occur, you can find the details in the TM1 server log.

You do need to upgrade to the current master branch, if you want to use this function
pip install https://github.com/cubewise-code/tm1py/archive/master.zip --upgrade

[nicolasbisurgi]

Hi Marius,
Thanks a lot for the advice. Just to be clear, are you saying that this function is faster than the write_values_through_cellset()? I'm calling the latter asynchronously, is that also a possibility for the new write function?
The capability of just rejecting one cell as opposed to all of them is great, specially when dealing with alias updates.
I already downloaded it so I'll make a few tests.

Thanks again!

nico

[MariusWirtz]

Hi @nicolasbisurgi,

absolutely. You can call the write function asynchronously.
Please let us know how it goes

[rkvinoth]

Just to be clear, are you saying that this function is faster than the write_values_through_cellset()?

To provide more context, loading data into cubes have always been faster via TI compared to REST API.
In order to mitigate that we are leveraging unbound TI and load the data using that.

[wimgielis]

Can I ask how the unbound TI works behind the scenes ? Do we need to have a
PRO file that is then executed by TM1py ?

Op do 7 jan. 2021 om 16:54 schreef Vinoth Kumar Ravi <
[email protected]>

Just to be clear, are you saying that this function is faster than the
write_values_through_cellset()?

To provide more context, loading data into cubes have always been faster
via TI compared to REST API.
In order to mitigate that we are leveraging unbound TI and load the
data using that.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/cubewise-code/tm1py/issues/448#issuecomment-756202580,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AEDHULOZVQ3A6S66M4XWZ3DSYXKKDANCNFSM4VWQMZIA
.

>

---

Best regards / Beste groeten,

Wim Gielis
MS Excel MVP 2011-2014
https://www.wimgielis.com http://www.wimgielis.be

[MariusWirtz]

It is implemented in a way that the TM1py user doesn't need to provide anything other than admin permissions.

Essentially what the function does is that it creates one or multiple unbound TI process with 16k CellPutN/CellIncrementN/CellPutS statements and executes it in the TM1 server. This approach is ~ 5 times faster than writing through existing REST functions.

You can find the implementation here:
https://github.com/cubewise-code/tm1py/blob/d34157dc5be0da0f016babf0efcc181691bbf276/TM1py/Services/CellService.py#L411

@wimgielis
What do you think about this approach? Any thoughts?

[rkvinoth]

Can I ask how the unbound TI works behind the scenes ? Do we need to have a PRO file that is then executed by TM1py ?

Its like having the temporary subsets or views. They get created, executed and removed on the fly.

[wimgielis]

That’s a nice approach, definitely !

Op do 7 jan. 2021 om 17:17 schreef Vinoth Kumar Ravi <
[email protected]>

Can I ask how the unbound TI works behind the scenes ? Do we need to have
a PRO file that is then executed by TM1py ?

Its like having the temporary subsets or views. They get created,
executed and removed on the fly.

—
You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub
https://github.com/cubewise-code/tm1py/issues/448#issuecomment-756217180,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AEDHULP2L37NHPBPKGSMEQDSYXNBVANCNFSM4VWQMZIA
.

>

---

Best regards / Beste groeten,

Wim Gielis
MS Excel MVP 2011-2014
https://www.wimgielis.com http://www.wimgielis.be

[nicolasbisurgi]

Hi All,
I've been taken this last week, hopefully this week I can find some time to dedicate to this. For the little time I could spend I can definitely see improvements, but I still get an error. I need to further validate the data in the source files as there are a lot of "forbidden" characters in it, but the outcome of using write() or write_values_through_cellset() should be the same right? As I do see different errors in each.

I know I'm not providing enough information, I hope I can have it by the end of the week. Thanks a lot for your help on this!

Nico

[nicolasbisurgi]

Hi @MariusWirtz and @rkvinoth
I did some cleaning on the source file and now I see less issues than before.
What I found out is that, if an attribute value contains a single quote (') the process will not work, example:

Problem 1:
For element: 022790411
Alias Value: 022790411 | Mavik, Tablets, 2mg 100's HUD | 022790411

Log - Error: Prolog procedure line (82): Syntax error on or before: "
s HUD | 022790411','
 missing comma " 

_Another example_
Attribute Value: Women's Health

Log - Error: Prolog procedure line (8445): Syntax error on or before: "
s Health','}ElementA
 missing comma " 

Problem 2:
After removing single quote from any value I still get the error saying CellType is consolidated, though not sure which element is triggering that:

Error: Prolog procedure line (6170): Cell type is consolidated
Error: Prolog procedure line (6170):     error repeats 3 times

I could try to trace this a bit more, but I still don't get how it could confuse a number to a str.

Problem 3 NEW
When submitting the 81 attributes I got this error for one of them:

REPORT_ALIAS --> 3 of 81 could not be completed due to Text: Error: Internal Server Error
Unable to execute the request at this time. Retry unavailable due to request size limitations.
 Status Code: 500 Reason: Internal Server Error Headers: {'Date': 'Fri, 15 Jan 2021 23:16:42 GMT', 'Server': 'Apache', 'Strict-Transport-Security': 'max-age=31536000; includeSubdomains, max-age=31536000; includeSubdomains', 'Content-Type': 'text/plain', 'Content-Length': '120', 'Content-Encoding': 'gzip', 'X-Content-Type-Options': 'nosniff', 'Connection': 'close'}

I thought, from what you explained, that the new function will divide it into 16k chunks, so size shouldn't be an issue? Or maybe it depends on how many characters you fill it in with?

Let me know your thoughts.

Thanks and have a nice weekend!

nico

[rkvinoth]

For starters I would export the prolog and epilog procedure here and create a TI process out of it. Running this TI manually will provide you a bunch of details. You can also find what's there at 6170

https://github.com/cubewise-code/tm1py/blob/a44842d5012f61d970f2b1b8d873e417e32184fe/TM1py/Services/CellService.py#L472

with open("TI_procedure.txt", mode="w") as file:
    file.writelines(process.prolog_procedure)

[MariusWirtz]

@nicolasbisurgi
Problem 1 is due to the single quote in the element name. This problem has already been addressed a44842d5012f61d970f2b1b8d873e417e32184fe, you can just upgrade to the latest version of TM1py.

Problem 3 is an Internal Server Error happening inside TM1. Generally speaking, this should never happen.

Probably two things are happening in TM1 at the same time that are not "compatible".
To resolve this issue you can probably upgrade to a more recent version of TM1.

I have seen this once when I was running unbound processes and security updates at the same time.
Once we have a reproducible case, we should report this to the Server team at IBM.

[nicolasbisurgi]

@MariusWirtz
Thanks for the fix. Problem is no longer occurring.
In regards to Problem 3, IBM just upgraded our servers last weekend and I no longer see this message when I execute the code.
Thank you very much for the support.
@rkvinoth - I'm still busy with other pieces but your suggestion to track the problematic value is great.

Regardsm,

nico

[pal-16]

The error message DataSpreadFailed could mean that you are trying to write a string value into a numeric cell.
Perhaps the writing fails because you trying to write a value that is already the alias for a different element.

I was facing a similar issue, Thank you, this statement solved my query. I was trying to write a value that was already assigned as an alias to a different element.