Solution Advise

[Rawath]

Hi Power BI Community,

I am currently working on a project where I need to build a scalable and efficient reporting solution in Power BI. However, I am restricted to using Power BI Pro, and I’m running into some challenges due to its limitations. I’d like to seek advice on the best possible approach for my use case.

Project Requirements:

1. The data resides in a PostgreSQL database with multiple schemas representing different countries (e.g., india, us,aus, etc.).
2. Each country’s schema contains approximately 20–30GB of data for the last 3 years.
3. The report should support real-time updates or frequent refreshes.
4. Users need to:
• Analyze data for individual countries.
• Compare data across countries (if feasible).
5. My current licensing allows only Power BI Pro (1GB dataset size limit, no Premium features).

Explored Approaches:

1. Universal Table (Import Mode):
• Merge all country data into one universal table.
• Load this into Power BI using Import Mode with incremental refresh.
• Challenges: Pro’s 1GB dataset limit makes this infeasible for large datasets.
2. Schema Switching (DirectQuery):
• Dynamically switch schemas based on a parameter in Power BI.
• Query live data directly from the database using DirectQuery.
• Challenges: Refreshing the schema dynamically can cause delays, and Pro’s throttling limits may affect performance.
3. Hybrid Model (Import + DirectQuery):
• Use Import Mode for historical data (e.g., 3 years).
• Use DirectQuery for real-time data (e.g., last 10 weeks).
• Challenges: Not supported in Power BI Pro, requires Premium.

Current Dilemma:

• I want to build a solution that works with Power BI Pro, but the above limitations make scalability and performance testing difficult.
• Additionally, I lack access to Premium features (e.g., Hybrid Tables, larger datasets, incremental refresh on large datasets).

Questions for the Community:

1. What’s the best approach to handle this scenario given that I only have Power BI Pro?
• Should I rely on DirectQuery (Schema Switching) or a Universal Table with strict filtering to stay within the 1GB limit?
2. If I eventually upgrade to Premium (PPU or P1), would the Hybrid Model be the most scalable and efficient option for this type of requirement?
3. Are there any other strategies I should consider to optimize performance while working within the constraints of Power BI Pro?

[lbendlin · Accepted solution]

Looks like Direct Query is your only choice for now. 

P SKUs are no longer available. You would have to procure a Fabric SKU.