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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Macwin
Helper I
Helper I

Perfomance Questions

Hello Guys,

Im still working on my Project with a large dataset.
Since I cant use import mode because of the size restriction I have to use direct Query. My Datasource is a database in postgresql.
The problem is reading and measuring takes to much time.
A simple distinct count measure lasts 336.000 MS (counting for 1 day). 

The Datasource is getting 2.7 million rows every hour.

What I did so far to boost the perfomance:
- no complicated queries (just reading)
Data Import Best Practices in Power BI - SQLBI
- https://docs.microsoft.com/de-de/power-bi/power-bi-reports-performance
- activated some options 

- created a native query with parameters (does not work with direct query)

My Goal:

- Users can do Lifetime Measures on daily base, weekly base or even monthly base (avg, median, min,  max etc.)
- Embedding with BI Premium to PowerApps Portal (already working)

My Questions:
- are there more options to improve the perfomance or am I something missing ?
- Should I switch to an other sql server model ? like mssql or azure sql for better synergy ?   
- Does it take that long because Im using everything on a local computer (psql server, bi desktop) ? 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

In this video, they talked about having an aggregated table on the live connection. Check if this can help

https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...

View solution in original post

Hi @Macwin ,

 

Should I create the hidden aggregated table in bi querie editor or should I create a aggregated table in my SQL server.

It's better to create the hidden aggregated table in Edit Queries.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

9 REPLIES 9
jessegorter
Helper I
Helper I

I do not know a lot about postgresql, but if it has something like sql profiler you may check what queries PBI sends to it and tune those.

 

Does it have options like clustered columnstore index? Then that may be interesting to try.

I switched to azure database for postgressql 

Count with a Filter takes 101.000 ms and distinct count with a filter takes 37000 ms (Data for single day)

Should I create the hidden aggregated table in bi querie editor or should I create a aggregated table in my SQL server.

 

 

To your Question -> there is something called timescaledb, but its only for bigger data. We going to setup up this later but at the moment there is no need since Im only testing with data of 1-3 days.

Hi @Macwin ,

 

Should I create the hidden aggregated table in bi querie editor or should I create a aggregated table in my SQL server.

It's better to create the hidden aggregated table in Edit Queries.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

v-lionel-msft
Community Support
Community Support

@Macwin


"are there more options to improve the perfomance or am I something missing ?"

I didn't find more options to improve the performance. In  Fact, this official document is quite comprehensive.

Power BI performance best practices 

 

"Should I switch to an other sql server model ? like mssql or azure sql for better synergy ? " 

I don't think it's necessary. All DirectQuery requests are sent to the source database, so the required visual refresh time depends on how long that back-end source takes to respond with the results from the query (or queries).

 

"Does it take that long because Im using everything on a local computer (psql server, bi desktop) ? "

This is a factor. You can refer to the official document :

Use DirectQuery in Power BI Desktop 

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

amitchandak
Super User
Super User

In this video, they talked about having an aggregated table on the live connection. Check if this can help

https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...

Thank you guys composite model works like a charm. Now I only need to setup good aggregated table layers.

Otherwise my .pbix file is gettting over 1 Gig 

I think this composite feature is perfect for my use case. But im not sure how to correctly setup this up on my project.

 

image.png

This is how I tried to setup up the compositie model.

"AGG Table" and "Hourly updated Tablet" are connected through the time/date column.

Masterdata contains static infos about the gathered items in the direct query table. 

 

Hi @Macwin ,

First,  take "Direct Query" connection model to get data.
Second, modify the "Storage mode".

Manage storage mode in Power BI Desktop 

cc2.PNG

Third, Create relationship between tables.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

I already set it up but I don´t know if I set it up correctly

Blue = Import | Red = Dual | Yellow = Direct QueryBlue = Import | Red = Dual | Yellow = Direct Query

The masterdata tables are actually only for static infos, like name,etc. 

I dont need them for measures. 

if this setup is correct, my aggreated table (its a view with group by time) is the problem I think.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.