Power BI Pro

[nouraali369]

Hello,

I have some questions related to Power BI in general and Pro in specific.

Please find it below:

1. in the below highlighted part, i can understand that 10 GB storage is applied in case of the import mode, this will be the size of data retrieved. But what i can't understand is the 1 million number or rows/hr, what this means ?

   

   2. In the below screenshot, since the direct query work against source data directly, in case of getting data from BigQuery, if size of table is more that 1 millions, in this case it won't be able to add it to the report, or what exaclty this limit means?

   

   3. In case of getting data from BigQuery using direct query and since report visuals are against raw data, then why when we publish the report, the semantic model is uploaded to the power BI service as below, can we disable this option:

   

   4. Can you please confirm if the below points are correct:

   • how to refresh data(semantic model) in both cases: import and direct query?
     import 
     Power BI Desktop--> manual  can be applied
     Power BI Service--> manual / scheduled refresh can be applied
     
     Direct Query
     Power BI Desktop--> not needed
     Power BI Service--> not needed but offline refreshes can be configured
     • Is there a difference between refreshing the report and refreshing the semantic model?
       Yes, refreshing the report requires re-reading semantic model 
              refreshing the semantic model requires re-reading source data
       refreshing the report
              Import --> Desktop and SAS --> manually refresh 
              Direct Query --> Desktop and SAS --> visuals updated upon interactions

       Thanks in advance.

[Anonymous]

Hi  @nouraali369 

For your first question , where did you see the limition about one million rows/hour ? From the Microsoft offical document ? Can you provide me with the relevant links?

For your second question, when you use Direct Query to connect to your data source, the maximum number of rows of results you return cannot exceed 1 million . Of course , Premium capacities can set different maximum row limits, as described in the blog post Power BI Premium new capacity settings.

For your third question , when you use Direct Query's connection mode to get data from a data source, you also import some metadata in from the data source, such as table names, column names, and so on. So uploading to the Service will definitely generate a semantical model, and this can't be avoided.

For your fourth question , you can get a better understanding of both refreshes with the official documentation below.

Data refresh in Power BI - Power BI | Microsoft Learn

Best Regards,
Community Support Team _ Ailsa Tao

[nouraali369]

@Anonymous 

Thanks for your response.

The link you are asking about is: is https://www.tutorialspoint.com/power_bi/power_bi_comparison_other_bi_tools.htm

It is still confusing to me the 1 million row limitation.

Let's say the source table is with 135 million rows and below is needed:

1. drop down menu of distinct values of specific column that may be > 1 million , would it return all values?
2. column chart represents count of records/key, e.g. 

   key	value
   BMW cars	300k
   Porsche	2m

   .
   1. would it be able to count the records (exceeding 1 million) for all keys?
   2. would it be able to retrieve all keys, if keys exceeded 1 millions?
3. table visual to display all 135 million rows, would it be able to display them all?

[nouraali369]

@Anonymous 

one last thing, in case of direct query mode, regarding the semantic model in power BI service, you said it stores metadata about the source data only, does it store data by any means ?

And where all of this is stored?

[Anonymous]

Hi  @nouraali369 

Limit of one million rows returned on any query: There is a fixed limit of one million rows placed on the number of rows that can be returned in any single query to the underlying source. This generally has no practical implications, and visuals themselves aren’t going to display that many points. However, the limit can occur in cases where Power BI is not fully optimizing the queries sent, and there is some intermediate result being requested that exceeds the limit. It can also occur whilst building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity would hit this limit if there were more than 1 million customers, until some filter were applied.

The error that would be returned would be “The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.”

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#other-implications

This error only in the power bi desktop, and in power bi service, it will not show this error but only return 1 million rows result in visual.

In a DirectQuery mode, your Power BI Report sends SQL query per each visual element (tile) separately.

For instance, you have a table where show two fields: City and SalaryUSD. In the same time, your SQL table with cities data has 20 million rows. If you drop to visual element (table) City and not aggregated SalaryUSD then Power BI will send a query like below

select City, SalaryUSD
from cities

which will return 20 million rows and will try to show it via table visual in Power BI, but it has a limit of 1 million rows to render.

And if you are able to change SQL query and do aggregation like below

select City, sum(SalaryUSD) as SalaryUSD
from cities

and in case result output of this query will have less than 1 million rows then it will be rendered on the report.

So this limitation has nothing to do with the license.

Best Regards,
Community Support Team _ Ailsa Tao

[nouraali369]

@Anonymous 

Thanks a lot for the detailed explanation.

You mean by this filter in the query itself or it can be something like an external slicer visual for example to narrow down the results?

Regarding the question related to the semantic model, can you please check and update me

[nouraali369]

Hello,

would appreciate your reply to my previous comment.

@Anonymous