Connect Power BI Report to Read Only Replica

Objective

To connect Power BI Report refresh to read only replica server. 

Background

The Azure read replica feature allows you to duplicate data from your Procore database. Duplicating your data will cause 'read' operations (report refreshes) to no longer contend with 'write' operations (data syncs) for server resources.

Things to Consider 

Steps

  1. Open a Procore Analytics Report in Power BI .
  2. Select Transform Data from the Home tab.
  3. Select the appropriate table.
  4. Go to the Query Settings panel on the right. 
  5. Click the icon-settings-gear-grey2.png  icon next to Source.
  6. Select Enable SQL Server Failover support.
  7. Select OK.
    enable-SQL.png
  8. Optional: For queries where editing the source step is not possible, such as budget queries with parameters, add 'MultiSubnetFailover=true' at the end of the SQL statement. 
    multisubnet.png
 Note

These options can also be set when connecting to your SQL Server with a program such as SSIS or Azure Data Studio:

  1. Connect to your SQL Server.
  2. Click Options then select Additional Connection Parameters. 
  3. Add 'Application=ReadOnly' in the space provided. 
  4. Select Connect. 
    add-applicationintent.png

 

 

See Also