Power BI with large dataset

[simonphilp7]

I have a dataset of about 30GB and 500 million rows in a SQL Server. I have tried connecting to this via DirectQuery but it is unfeasibly slow. Just wondered what might be some other options for me. 

Many thanks

[FarhanJeelani · Accepted solution]

Hi @simonphilp7 ,
Working with such a large dataset in Power BI can definitely be challenging, especially when using DirectQuery. It’s not surprising that you’re facing slowness—it’s a common issue with datasets of this size because every interaction with visuals sends queries back to the SQL Server, which can cause bottlenecks.

I suggest creating two separate views in SQL: one for "cold" data and another for "hot" data. The "cold" data view should contain historical data, while the "hot" data view should focus on the most recent or last 3 months of data. Once you've set up these views, import them into Power BI and append them together. This approach will help you filter and analyze the current data more quickly, as it will be more frequently accessed.

Alternatively, here are some other strategies you might consider:

1. Aggregation Tables: If most of your reporting involves summary-level data (e.g., monthly sales totals, yearly trends), you can create pre-aggregated tables in SQL Server and import them into Power BI. This reduces the volume of data being queried in real time while still allowing you to use DirectQuery for drill-through scenarios.

2. Hybrid Models: Instead of relying fully on DirectQuery, you could mix Import and DirectQuery modes in a composite model. For example, import frequently used summary tables and leave only the detailed, rarely queried data in DirectQuery mode.

3. Partitioning: SQL Server supports table partitioning, which can help speed up DirectQuery performance. By partitioning the data on frequently filtered fields (like date), you can reduce the amount of data being queried.

4. Data Reduction: Do you really need all 500 million rows in Power BI? Applying filters or trimming unused columns in the SQL query could significantly cut down the dataset size.

5. Use an OLAP Solution: SQL Server Analysis Services (SSAS) or Azure Analysis Services could be game changers. They’re designed to handle large datasets efficiently and let you pre-process aggregations and calculations. Power BI connects seamlessly to these models.

6. Incremental Refresh: If you don’t already use it, incremental refresh in Power BI can work wonders. It loads only new and changed data, minimizing the amount of data Power BI processes.

7. Performance Tuning: Sometimes, SQL Server itself needs a bit of love—indexes, query optimization, and proper database tuning can significantly improve DirectQuery performance.

8. Alternative Storage: Depending on your environment, exploring other scalable data solutions like Azure Synapse Analytics or BigQuery might be worth considering. These platforms are designed for handling massive datasets with speed.

Please mark this as solution if it helps you. Appreciate kudos.

[simonphilp7]

Thanks a lot for your help!