Answers.Body: <p>I don't believe anyone has, but Power BI has a MySQL connector component, so it appears that would be all you need:</p>
  
  <p><a href="https://powerbi.microsoft.com/en-us/integrations/mysql/" rel="nofollow noreferrer">https://powerbi.microsoft.com/en-us/integrations/mysql/</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-02-06T22:55:18.147Z
Answers.Text: Thanks for looking into this.  We'll give it a try and see how it goes.
Answers.UserDisplayName: J. Rolfs
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-02-06T19:23:14.873Z
Answers.LastActivityDate: 2018-02-06T19:23:14.873Z
Answers.UserDisplayName: Hershel
Answers.Body: <p>Connecting with API could be a better solution. This is how I did it:</p>
  <p><strong>Step 1: Prepare data in CiviCRM</strong></p>
  <ul>
  <li>Get the site key</li>
  <li>Create a CiviCRM and Drupal user with correct access rights</li>
  <li>Get(Create) the API-key of that user</li>
  <li>Create a data processor with relevant data, accessed with API</li>
  <li>First column to be an index key, used to connect with other CiviCRM data sources</li>
  <li>Amounts to be Aggregate functions, 2 decimals, comma as decimal separator,<br />
  and space as thousand separator</li>
  <li>Avoid including any personal information in the data processor API-call, than there will be no privacy risk involved with PowerBI.</li>
  <li>With data processor API calls, only get-function is available. That means no risk of anyone updating/creating values in
  database</li>
  <li>Test by input URL to a browser </li>
  </ul>
  <p>https://[site-URL]/[CiviCRM folder]?entity=[name of API-call or data processor]&amp;action=get&amp;api_key=[api-key of a CiviCRM&amp;Drupal user with proper access rights]&amp;key=[Site key]&amp;version=[API version]&amp;json{&quot;sequential&quot;:1,&quot;options&quot;:{&quot;limit&quot;:0}} 
   </p>
  <p><strong>Step 2: Proxy (if relevant)</strong></p>
  <ul>
  <li>Add API-call to CiviProxy</li>
  <li>Get different site URL, CiviCRM folder, Api-key and site keys</li>
  <li>Build up a new URL</li>
  <li>Test by input URL to a browser</li>
  </ul>
  <p><strong>Step 3: Connect Power BI</strong></p>
  <ul>
  <li>Get data with Web connection</li>
  <li>Input URL that is tested after proxy 
   </li>
  </ul>
  <p><strong>Step 4: Create graphs and statistics in PowerBI</strong> </p>
  <ul>
  <li>Transform columns to correct data type!</li>
  <li>Schedule automatical refresh(es). Daily at 03.00 am is
  a good suggestion,
  as that will likely not cause any performance issues in CiviCRM</li>
  <li>Connect different data sources in Power BI, like website traffic</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-06-01T07:37:45.507Z
Answers.LastActivityDate: 2021-06-22T19:09:14.947Z
Answers.LastEditDate: 2021-06-22T19:09:14.947Z
Answers.UserDisplayName: Flying
Body: <p>Has anyone tried to connect Civi to Power BI?  I'm considering using this platform for some more in depth analytics.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-06-22T06:11:59.927Z
Comments.Text: How did it work out? I am considering the same
Comments.UserDisplayName: Flying
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-06-22T19:21:23.777Z
Comments.Text: 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.
Comments.UserDisplayName: J. Rolfs
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-06-22T19:28:54.453Z
Comments.Text: Thx for reply. I just discovered that Power BI Desktop doesn’t come with a Mac version either...
Comments.UserDisplayName: Flying
Tags: <civireport>
Title: Connecting Power BI to Civi
ThreadText: # Connecting Power BI to Civi
  
  (Asked by: username_0 on 2018-02-01)
  
  Has anyone tried to connect Civi to Power BI? I'm considering using this platform for some more in depth analytics.
  
  **Tags:** civireport
  
  > **Comments:**
  > **username_1**: How did it work out? I am considering the same  
  > **username_0**: 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.  
  > **username_1**: Thx for reply. I just discovered that Power BI Desktop doesn’t come with a Mac version either...  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2018-02-06. Score: 0 | Accepted answer
  
  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/>
  
  > **Comments:**
  > **username_0**: Thanks for looking into this.  We'll give it a try and see how it goes.  
  
  ---
  
  ### Answer by username_1 on 2021-06-01. Score: 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
  
  
  ---
  
  