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
Daniele
Frequent Visitor

General dissatisfaction and performance issues

Hi All,
    After having our tech guys setting up a PostgreSQL database for me to build our datawarehouse on it, i have done all the automation that dumps the data from our main MongoDB to the datawarehouse, and then started with PowerBI.
I've created several reports, and when i was trying refreshing them, it was always throwing Exception errors at me when connecting to PostgreSQL, at least 3 or 4 times before working.
So then we upgraded to Pro, in order to get more space and relying on the scheduled automatic refresh, which back then wasn't supporting PostgreSQL. When got to open a ticket, i was called from a guy with broken english that suggested me to use another database... Really?
Now PostgreSQL is included in the list of automatic refresh sources. 
It fails. Everytime. If i try to refresh manually, at some point, after trying the usual 3 or 4 times, it starts, and then if i'm lucky, it slowly finishes.
On one of my biggest reports i'm connecting to a few tables of the same PostgreSQL database with around 700k rows and no more than 10/12 columns, a couple of locally stored, small excel files, and a few API calls to Pipedrive. The PBIX file is around 300MB and it's just impossible to refresh. To the point i have to try and refresh manually every data source, of coures trying 3, 4 or 5 or more times per source. To cut it short, it takes me 1 hour to refresh this dashboard...
I make extensive use of PBI, with over 20 reports shared throughout the company, and I cannot believe such poor performance.
And icing on the cake, i cannot open a ticket and i get prompted a page that says "Something went wrong."... well thank you.
Maybe i'm doing something wrong. Maybe there's a restriction somewhere that i'm not aware of. 
I still think PBI is a great product and want to use it, but lately it's been frustrating and i'm considering other solutions.
If somebody can help I would be glad.
Sorry for the lenghty message and thanks.

8 REPLIES 8
Greg_Deckler
Super User
Super User

You should use a different database. 🙂

 

Honestly, it is very likely the connector to PostgreSQL that is at fault here. That same amount of data in SQL Server or Azure SQL DB would import in minutes. That being said, I have no idea about your network infrastructure, storage, etc. so too many factors to isolate the cause. But, it might be worth spinning up an Azure SQL DB or SQL Server on an MSDN or development license. You can get a 30-day free trial of Azure SQL DB. Load your data and try it. If the performance is great, at least you know it really is Power BI's connector to PostgresSQL.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler
I think i will test Azure.
My question now is: why advertising something as if it works flawlessly when it clearly doesn't?
Had i knew about these issues previously, the whole setup would have been different. Now i'm in the middle of a new job with dashboards and reports developed upon something reluctant to work, andwill need to dump the data into another database, change the automations (Talend jobs, Jenkins etc.), all of this in a hurry, and triyng not to look like a total idiot to non technical people in the company.
Not cool.

Azure is not an option, too expensive after the first month. Not setting up the whole system only for a month.
Will try something different. 
Suspicions are sneaking into my mind though. 
This could be my first and last subscription to PBI if what i think is true.
But first, i will test other data sources and the gateway extensively and see if it's only a PostgreSQL problem.

Anonymous
Not applicable

daaaaaaamn daniele.

 

Without knowing more, it's hard to say what's going on here, but I would be hesistant to quickly blame Power BI.  I have clients using postgresql and have not heard these complaints.

 

Could be you have some crazy expensive calculated column (which gets recomputed during refresh)

Could be your are pull from postgresql via a view that performs badly

Could be your database hardware is failing

 

I'm pretty skeptical to make the blanket statement "Power BI performs badly against postgresql".  It's *possible*, but... I'm skeptical.

Thanks for the reply @Anonymous. I based my answer on what @Greg_Deckler said, as i see he's very experienced and answers pretty much every topic here. 
If you get "You should change database as an answer" from an experienced member, you would think he knows what he's talking about (and i think so).
@Greg_Deckler not sure we are talking about the same service here. I've started the same free trial with 200$ credits free, and was about to start an SQL server. The prompted prices were not lower than 150$ per month. I might have done / selected something wrong maybe?
About the crazy calculations. Here's an example of a dashboard that fails to update:
4 tables with 680k rows of which 2 has 20 columns, one 17 columns and one 9 columns. Then other small tables with 30k records or 300 records.
There's one main key column in one of the big tables, where the other tables are in a 2 way relationship with, and yes there are a few calculations done, also in the big tables, but it's stuff like:
MyCalculation = CALCULATE ( COUNTROWS ( 'thistable' ), 'thistable'[thiscolumn] = "lorem ipsum" ).
I don't know if this is too much.
all of the data is fetched via connection string to an amazon server.
Anyway, the main issue is that online there's no way it's working, and on the desktop app, i need to refresh a minimum of 3 times, up to 10/12 to avoid getting errors like these 2:
-exception while reading from stream postgres
-error remaining connection slots are reserved for non replication superuser connections
Of which the second very seldom compared to the first.
And all of this happens even with really small amount of data from our PostgreSQL datawarehouse.
As i said, i really like Power BI and if someone could help solving this i would be a happy and thankful guy.

It sounds like you are spinning up an Azure SQL Server via Infrastructure as a Service (IaaS). What I am suggesting is that you spin up an Azure SQL DB instance instead. This is more of SQL Server database as a Service so more SaaS based. Much less expensive than running a server in the cloud.

 

I didn't realize that you were running your PostgreSQL box in Amazon. There could definitely be latency issues there going from on-premises to the the Amazon cloud or from Azure to Amazon. The nice thing about Power BI and using Azure SQL DB is that you can potentially have all of it in essentially one data center or at least all within Azure with fast connections between Azure data centers.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Listening to @Greg_Deckler is indeed a good plan!

 

I just looked -- and have a client w/ an S1 instance in Azure ($30/mo), with 23 million rows of data using only 5gb of 250gb available... and I suck them all into power bi w/ no probs.

 

 

Hang on, an Azure SQL DB at $15 or $30 per month is too expensive? And, you can go here: https://azure.microsoft.com/en-us/services/sql-database/ and get the first month free.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.