Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

need help

Hi All,

          Need your expertise suggestion and guidance on the below which iam facing.

i have a 2 fact tables which are having 360 and 330 miilions of rows and iam taking these tables from sql query into power bi with direct query and with direct query we have a limitation of 1M records.Need some suggestion on this to take data from SQL queries which has 660Million rows approximately.

 

Regards

Anitha.

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Hi @Anonymous ,

 

//Need some suggestion on this to take data from SQL queries which has 660Million rows approximately.

If over 1 million rows are returned from Direct Query, Power BI returns an error (unless in Premium capacity, and the row count is under the admin-set limit) (1 million row limitation in direct query is the number of rows returned.)

Reference: Use DirectQuery in Power BI Desktop

 

Such queries can put a huge strain on the performance of data sources and reports. You need to optimize some queries, as Wu says, to filter out the required data.

DirectQuery model guidance in Power BI Desktop

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

ray_ux
Memorable Member
Memorable Member

@Anonymous direct query isn't always good. Better to use import for performance wise. Can you give us a bit of context on what you trying to do with the data. I suggest filtering the tables with sql queries  and don't pull the whole data. e.g., limit 100, filter recent date,etc. 

Anonymous
Not applicable

Hi Ray_wu,

                   i have to create a power bi report using the sql queries which that much of Data.

 

Regards

Anitha.

there is a limit of 1m rows data.
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#other-implications

Not sure if the premium will increase the data capacity but either way you will have to filter down the fact tables, there no way you can load them into the report visual and even if it did the performance would take a long time to load.  The only way i can think of is to optimize this query, such as using filters 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.