Data Modeling Help Needed

Hi dallass ,

You're dealing with a perfect storm for slow performance - 35 tables, cross-source DirectQuery, and BigQuery. No wonder it's crawling.

The brutal truth: DirectQuery with BigQuery is often a nightmare. BigQuery is designed for big analytical queries, not the hundreds of tiny queries Power BI fires off. Plus, mixing BigQuery and SQL Server means Power BI can't push anything down to the source - it's doing all the heavy lifting locally.

What's probably happening: Every time someone clicks a slicer, Power BI is generating separate queries to both sources, pulling data back, then trying to merge it. That's why your caching isn't helping much.

Real fixes:

Split the **bleep** thing - Separate reports for BigQuery vs SQL data. Stop trying to make them play nice together in one model.

Import what you can - Weekly retail data? That's probably small enough to import instead of DirectQuery. Save DirectQuery for the massive transactional stuff.

Pre-aggregate in BigQuery - Create summary tables for your common views (store/week rollups). BigQuery loves big scans, hates small lookups.

Kill unused columns - Those "hundreds of columns" are slowing every query even if you're not using them.

The webcall thing makes sense - this kind of mess usually needs someone to look at the actual model and say "why the hell are you doing it that way?"

What's your biggest pain point - the initial load time or when users interact with filters?

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.