hi,
This is a known problem in 4.x series (i havent worked on twinfin).
This problem happens, when one of your partition by clause has more rows to be processed. As you know each partition has to fit in a "single spu", so if data volume is more then system cannot handle that partitions data in another spu ( i wish i could provide exact volume i can give, but i have to dig my old-old emails .. )
eg:
count(distinct cust_key) over (partition by level1) as level1 --
if level1 has only two distinct values and any one distinct value has more records. Then system throws this error.
you can check by running select level1, count(distinct cust_key) from tab group by level1 to see how many partitions level1 creates and count.
output would look something like below
level1 | count
0 | 100000000
1 | 1500000
if you see above, one partition with 0 has more than 100 mil records. So NPS cannot handle this large partition.
Workaround is to create a intermediate table with grouped data.
i'm sure that only one or two levels out of your 8 levels would cause this error. This is system limitation, either remove that one level of analytical calculation or create one temporary table and do process in two steps.
hope this helps.
