Data Warehouse - Faster Access

Introduction 

This guide explains the new scale-out solution implemented to enhance performance for our Data Warehouse users. With two synchronized servers and a load-balancer, users can experience improved data retrieval speeds. Here we have provided instructions on how you can optimise your access.

 

How to: Faster Access to the Data Warehouse

To take advantage of these performance improvements, all that’s required is to set one of the options below: 

1. In PowerBI Desktop:

This can be enabled by selecting the ‘Enable SQL Server Failover support’ under Transform data > Data source settings.

When you publish the report to the PowerBI service, this will also reduce the refresh times of your PowerBI.com reports. 

2. Connecting from a client other than PowerBI: 

Inform the load balancer that we’re only reading data by adding the following to the connection string:

ApplicationIntent=ReadOnly

  • In SSMS this can be added under Additional Connection Properties:

SQL_SERVER.png

  • Please note you must also specify the database name as well:

SQL_SERVER_2.png

  • Azure Data Factory (ADF): Edit the relevant linked service, and add a new additional connection property:

edint_linked_service.png

For more information on connection strings click here. We strongly encourage all our PowerBI and ETL users to set this flag to gain optimal Data Warehouse performance.