Issue with ISFIRSTOCCURANCE funtion
Issue: One of our Integration task failed with an Error :
Too many cells for ISFIRSTOCCURRENCE calculation: actual=50144479 maximum=50000000
Steps taken to avoid this was to chunk the files to less number of rows. The error still appears.
What is the actual reason for the error? Or what need to be done get any error that indirectly point to this message.
Answers
-
It looks like you are using the ISFIRSTOCCURRENCE function in a line item. That function has a limit of 50 Million Cells. By running the import Integration action, you are likely adding to a list which is one of the dimensions on the line item using the ISFIRSTOCCURRENCE, which is causing the cell count of the line item to exceed 50 million (By 144,479 cells).
Without knowing how/why that function is being used, it is hard to recommend a workaround. I would confirm if it is required, or if there is a way to do that calculation on a smaller line.
Jason
0 -
Anaplan Classic Engine has a limit of 50 million cells for this function, which is why you’re encountering this error.
Some time ago, this limit was smaller (around 10 million, if I correctly remember).
As far as I know, the only way to avoid this issue is by keeping the number of cells within this limit.
If you encounter this situation in an Anaplan module and use the first occurrence method, I assume you need to import a very large number of rows and then filter out duplicates based on specific criteria or identify distinct values for certain columns.
These transformations are more to a relational database environment (datawarehouse, data lake) than to a multidimensional database.
This could be one of those cases: if Anaplan can do it, it's not necessarily the best place where to do it :).
You could think about to "move" these transformations on the datasource environment.
If you want to maintain and do the transformation on the Anaplan system, I suggest looking into Anaplan Data Orcherstrator (ADO).
This seems to be a classic case where all the transformation needed could be solved in ADO.
Hope it helps,
Alex
0 -
Hi @deepskay ,
It is linked to the maximum limit for that function as specified in the error message. It means the cell count is too high (Which would be as a result of the list members applied)
You could try to re-evaluate if you need all the members in that module? Try using subsets, time ranges (in case it applies to that dimension) or split the import to different source modules/staging layer where you carry IFO & then a final source module.0 -
0
-
Hi All,
After reading the comments before posting this issue, the first step I took was to chunk my import file to most granualar level I could. I chunked the files to 5000 rows and 21 columns. few files were successful and few files failed. I am not sure if that low number should hit the limit. If it was limit issue then other files shouldn't have been successful. At this point I think there is some different underlining error that is causing this but Anaplan is throwing this error.
0 -
Hi @deepskay ,
Can you share a screenshot of the module (blueprint view) in which you're trying to import it to?
Additionally, it need not only be the contents in the new file but it is the total that is getting exceeded. As an example, if there's a limit of 100 rows & there are already 98 records, only 4 records in the new file would hit that limit & cause the issue whereas in the past a file with 98 records would have gone through as well.0
