Azure Analysis Services - Linked Server Setup
Azure Analysis Services (AAS) is a relatively new product and because of this, it is limited in its connectivity with outside tools. QuerySurge however can connect with AAS, using a Linked Server as a proxy to Azure Analysis Services. The setup for this is described in this article.
Note: This setup requires an additional server running a SQL Server database instance to link to AAS. These must both be provisioned by your organization.
For the setup, you'll need to complete these prerequisites:
- Spin up either an additional Azure VM with SQL Server, a regular Azure VM with a self-installed SQL Server instance or an on-prem SQL Server instance. Depending on your licensing, either the Express, Standard, or Enterprise edition should be provisioned. This database instance is where you will configure a linked server which will act as a proxy to Azure Analysis Services.
- On this proxy database server, install the latest Analysis Services client libraries. These include MSOLAP, AMO, and AMOMD.
- Find the connection info - specifically, the Server Name and Model Name - for your Azure Analysis Services instance in the Azure Portal.
Consult the Knowledge Base Article Configuring Connections: Microsoft SQL Server Analysis Services using Linked Servers as a guide for the process of linking your SQL Server instance to AAS. Use the following specifics when you configure the linked server:
- Provider: Make sure that the latest version of Microsoft OLE DB Provider for Analysis Services is selected
- Product Name: Enter MSOLAP
- Data Source: Enter the complete Server Name value obtained from the Azure Portal
- Catalog: Enter the Model Name value obtained from the Azure Portal
- Authentication: Either map a local login to an Azure AD account, or select and enter credentials for an Azure AD logon (you may need to contact your Azure administrator for details).
You can now connect QuerySurge to the linked proxy SQL Server database instance to query Azure Analysis services. Setting up the connection to SQL Server follows the standard procedure (see this Knowledge Base Article for details).
Querying Azure Analysis Services via a Linked Server
Use standard DAX query syntax with the SQL Server OPENQUERY function. OPENQUERY documentation is available here.
Note: Queries run with this method must be under 8000 characters long, and all quotes must be escaped.
The following sample DAX query would be executed in AAS using the following syntax:
evaluate ADDCOLUMNS(SUMMARIZE('Internet Sales', Customer[Education]),
"Total Sales",
CALCULATE(SUM('Internet Sales'[Sales Amount])))
The same query would be executed on your linked server using the OPENQUERY syntax, and this is how you would execute it in QuerySurge:
SELECT * FROM OPENQUERY(AZUREAS, 'evaluate ADDCOLUMNS(SUMMARIZE(
''Internet Sales'', Customer[Education]),"Total Sales",CALCULATE(SUM(''Internet Sales''[Sales Amount])))')
Note: In the example above, the entire EVALUATE statement has been enclosed in single quotes; single-quoted values in the original query are now enclosed in double single-quotes, not in double-quotes. Also, double-quoted values in the original query are unchanged - they still have double-quotes.
Important Usage Note: While DAX queries can be elaborate and may return elaborately structured resultsets, QuerySurge is designed to work in a "tabular" world. So, QuerySurge (and the Linked Server!) expects all queries including DAX queries to return 2D resultsets that conceptually look like regular "tables". If you are dealing with data that is more complex, then the solution is usually to write multiple QueryPairs to cover your test requirements.