PowerBI incremental Load from Mendix Source (PostgreSQL)

3
Hi – Has anyone managed to setup Microsoft PowerBI to complete Incremental loads from Mendix? I tried using Odata, but apparently Mendix’s implementation of Odata doesn’t support Query folding….. If anyone has managed to get this working, I’d be interested to know what connectors they used in PowerBI, and how they connected to their Mendix Dataset.   We’re self hosted, so may just try natively connecting using the PostgreSQL connector for PowerBI…   Anyone tried it, or have alternatives?
asked
3 answers
1

Hi Robbie,

I have a customer running PowerBI with Mendix /SQL server on premises on windows/azure. That works like a charm. I think your options are

  1. Host your application on premises with the powerbi postgresql connector
  2. Create a custom requesthandler that supports odata/queryfolding (quite a job)
  3. Create a CI/CD taskchain that downloads the backup every night to an environment that can be reached from powerbi (not really elegant, but it can)
  4. Implement database mirroring to your own environment and expose that to powerbi.
  5. File a feature request to Mendix for a solution.

 

Create a custom data connector for powerbi is limited to the microsoft stack (M-language) and not available for java.

answered
1

Thanks for the response Chris!  And glad to hear that there is at least someone using this configuration.  Surprised really it’s not more used if I’m honest…..

I’ve actually already submitted the idea of implementing query folding as part of Mendix Odata, but don’t have high hopes – Please feel free to Vote:  https://forum.mendix.com/link/ideas/2250

I’ve scheduled some Dev time between our developers and the BI team to test out the Postgresql route (Option 1), as this is the preferred option, given that it bypasses Mendix altogether, and goes direct to source (No user licensing issues, nor any Mendix overhead when pulling data).

 

I’ll report back to the community on validating this approach from an incremental load perspective. 

answered
0

I’m really happy to report that we managed to get PowerBI and Postgresql working well together, in fact, I’m shocked at the performance increase!!

 

We went from a table taking 20mins to refresh using Odata, to 50 seconds using Postgresql (Full Table Refresh).

 

But it gets even better than that, having removed the Odata overhead, you don’t need to worry about using Mendix User Licenses for accessing the Database, and the CPU and Ram utilization is far less.

 

But wait, there’s more good news…….

 

We additionally were able to configure Incremental refresh, so ultimately our refresh time went down to 10 seconds…..  From 20 Minutes!!!

 

answered