Error: Resultset of a query to external data source exceeded the maximum allowed size of 100000 rows

[Anonymous]

Hello,

I am trying to connect Power BI with one of the Oracle Exadata's table (which has more than 1M records) and I am facing "Result set of a query to external data source exceeded the maximum allowed size of 100000 rows" error.

There is one slicer and couple of tables in the dashboard. I have already tried the following,

1. Enabled the query reduction from "Options" by checking Slicer and Filter selection
2. Tried to implement Top-N filter functionality for the attribute used in the Slicer

The strange thing is that the dashboard worked fine when the data source was IBM Netezza and now when I changed the source to Oracle Exadata I started seeing this issue. Everything is the same in terms of number of tables, slicer and even the data is same. Not sure where am I going wrong!

Please advise!

Thanks,
Jash

[Anonymous]

Hi, I'm also facing same issue.

Could please help me to solve this issue.

[v-xicai]

Hi  @Anonymous ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @Anonymous ,

You can use SQL query to limit the data rows, Like code below.

SELECT TOP ( 1000001 )

        [t1].[Order Number],

        [t1].[Order Line Number]

FROM   [t1]

By the way, Oracle Exadata connector is unavailable in Power BI currently. You can post your new idea here https://ideas.powerbi.com/forums/265200-power-bi-ideas , to improve the Power BI.

It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

While you can try to use the ODBC connecter to connect to Oracle Exadata, refer to this case to find the steps.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai, 

Thank you so much for your response. I am able to connect to Oracle Exadata successfully there is no with that. The issue is row limitation error of 1000000. 

I belive if I use the below mentioned SQL it will restrict my entire resultset, what I want is to restrict the number of values that the slicer returns. Meaning when someone tries to search for a particular oder# the slicer should restrict the results to top-N based on the Order# entred and not bring the entire population of Order# which is more than 1M. Is there a way wherein we can achieve this? 

Thanks, 
Jash. 

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can try to create rank column like DAX below.

 Rank= RANKX(FILTER(Table1 ,Table1 [Order# ]=EARLIER(Table1 [Order# ])),Table1 [Sales],, Desc, Dense)

Then create a filter measure like DAX below, then put measure Filter1 in the Visual Level Filter of goal visual, and setting the Filter1 as "is not blank".

Filter1= IF(MAX(Table1[Rank])<=1000000, 1, BLANK())

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I'm also facing same issue.Can you please explain the formula once and also in direct query , the rankx function is not available. How to achieve that?