Pivot Table 'Exceeds maximum number of input records' on excluded column

Robert Angel

Hi,

My version is 12.2.1

An end user creates an answers report with a table and a pivot table.

He adds a field that increases the volume of data considerably, the table works fine, but the pivot table errors, even though the field in question is in the excluded section with - exceeds maximum number of input records.

There are no 'Is Prompted' filters, nor are there any Prompted sections on the pivot.

I pointed the user temporarily at creating the two separately as a workaround, but surely the pivot should not fail with this??

thanks for your input in anticipation,

Robert.

DB_Home

Hi Robert,

Try this,

Make the changes to your table & Pivot table parameters in 12c instanceconfig file as per 11g instanceconfig file. Earlier I faced the same problem and it was resolved for me.

Thanks,

Madhav

Robert Angel

Thanks I am a veteran of the instanceconfig file and upper limits on pivots, rows etc, but this does not answer my underlying question, why does an excluded column in a pivot table cause the error, I had understood that for a pivot table the data is retrieved from the server first and then pivoted on the web, so surely if I have an analysis based on a working tabular report and I add another view for a pivot table and remove the column that, all other things being, would bring the pivot to a manageable size, then the pivot table should work.

I have tested the workability of this by creating just the pivot and deleting the offending field, sure enough, no error. Add the field and it fails, my question is why when it is excluded and is there any better workaround to this than upping config limits which will apply to everything?

Joel

Hi @Robert Angel

If I understand correctly you are hitting the exceeds maximum number of input records error when you have a column in the excluded columns section. If so, the query that the BI Server generates is based on all the columns added to the Analysis even those that have been excluded.

The rationale behind this is that if you have several views on a compound layout with a subset of the columns in each of the views for example, you'll have some excluded columns in each of these views which are required in other views. As such, all columns are retrieved and SQL generated includes them all.

As you suggested to your end user, the solution would be to create 2 separate analyses each containing the required columns only.

Christian Berg-0racle

"Excluded" has no impact on export. It will ALWAYS be queried and hence always be contained in the data stream.

Robert Angel

Thanks all, I guess in simple terms if my extra column makes the volume of rows 500000 instead of 500 then the pivot still has to handle, group, summarise etc the extra volume of data, yes?

Joel

That is correct

Christian Berg-0racle

The pivot handles what it has to. If the data stream all of a sudden instead of 500 distinct rows provides it with 500'000 rows with 500 distinct members in some attribute...well...toughies