3

Has anyone tried to connect Civi to Power BI? I'm considering using this platform for some more in depth analytics.

3
  • How did it work out? I am considering the same
    – Flying
    Jun 22 '20 at 6:11
  • We've had to put this on the back-burner for the moment but we did encounter some issues that were making it difficult to make a connection. Our challenges revolved around creating a secure bridge between PowerBI and the SQL database. There are some services that help in creating that bridge but we didn't get that figured out before pausing this project.
    – J. Rolfs
    Jun 22 '20 at 19:21
  • Thx for reply. I just discovered that Power BI Desktop doesn’t come with a Mac version either...
    – Flying
    Jun 22 '20 at 19:28
0

I don't believe anyone has, but Power BI has a MySQL connector component, so it appears that would be all you need:

https://powerbi.microsoft.com/en-us/integrations/mysql/

1
  • Thanks for looking into this. We'll give it a try and see how it goes.
    – J. Rolfs
    Feb 6 '18 at 22:55
5

Connecting with API could be a better solution. This is how I did it:

Step 1: Prepare data in CiviCRM

  • Get the site key
  • Create a CiviCRM and Drupal user with correct access rights
  • Get(Create) the API-key of that user
  • Create a data processor with relevant data, accessed with API
  • First column to be an index key, used to connect with other CiviCRM data sources
  • Amounts to be Aggregate functions, 2 decimals, comma as decimal separator,
    and space as thousand separator
  • Avoid including any personal information in the data processor API-call, than there will be no privacy risk involved with PowerBI.
  • With data processor API calls, only get-function is available. That means no risk of anyone updating/creating values in database
  • Test by input URL to a browser 

https://[site-URL]/[CiviCRM folder]?entity=[name of API-call or data processor]&action=get&api_key=[api-key of a CiviCRM&Drupal user with proper access rights]&key=[Site key]&version=[API version]&json{"sequential":1,"options":{"limit":0}}   

Step 2: Proxy (if relevant)

  • Add API-call to CiviProxy
  • Get different site URL, CiviCRM folder, Api-key and site keys
  • Build up a new URL
  • Test by input URL to a browser

Step 3: Connect Power BI

  • Get data with Web connection
  • Input URL that is tested after proxy   

Step 4: Create graphs and statistics in PowerBI 

  • Transform columns to correct data type!
  • Schedule automatical refresh(es). Daily at 03.00 am is a good suggestion, as that will likely not cause any performance issues in CiviCRM
  • Connect different data sources in Power BI, like website traffic

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.