Integrate Azure Analysis Services
Learn how to integrate Azure Analysis Services with various data sources and applications to leverage its powerful analytical capabilities.
Connecting to Data Sources
Azure Analysis Services acts as an intelligent layer between data sources and client applications. It supports a wide range of data sources, allowing you to build comprehensive analytical models.
Supported Data Sources
Common data sources include:
- Azure SQL Database
- Azure Synapse Analytics
- SQL Server
- Azure Data Lake Storage
- Flat files (CSV, Excel)
- Third-party sources via custom connectors
Using Data Gateways
For on-premises data sources, you can use the On-premises data gateway to securely connect Azure Analysis Services to your local data. This gateway acts as a bridge, enabling data transfer without exposing your on-premises network.
Integrating with Client Applications
Once your Analysis Services model is deployed, it can be consumed by various business intelligence and visualization tools.
Popular Client Applications
- Power BI: Connect directly to your Azure Analysis Services model for interactive dashboards and reports. This is the most common and recommended integration path.
- Excel: Use Excel's Power Pivot or "Get Data" features to connect to Analysis Services.
- Tableau: Connect to Azure Analysis Services as a data source within Tableau.
- Custom Applications: Develop custom applications that leverage the Analysis Services client libraries (AMO, TOM, ADOMD.NET) to interact with your models programmatically.
Connection Strings
You will need a connection string to establish a connection from your client application to your Azure Analysis Services server. The general format is:
"Provider=MSOLAP;Data Source=;Initial Catalog=;Integrated Security=ClaimsBased;Persist Security Info=True;" Replace <your-server-name> with the fully qualified domain name of your Analysis Services server (e.g., asazure://premium.asazure.windows.net/your-server-name) and <your-database-name> with the name of your model database.
Integration Scenarios
Data Warehousing
Azure Analysis Services integrates seamlessly with data warehousing solutions like Azure Synapse Analytics. You can build a robust data warehouse and then create an Analysis Services model on top of it for high-performance analytics.
Real-time Analytics
By connecting to real-time data sources or using technologies like Azure Stream Analytics, you can build near real-time analytical solutions with Azure Analysis Services.
Data Migration
When migrating from on-premises SQL Server Analysis Services to Azure, you can redeploy your existing models to Azure Analysis Services with minimal changes. Tools like SQL Server Management Studio (SSMS) and Visual Studio can assist in this process.
Programmatic Integration
For advanced scenarios, you can use Analysis Services Management Objects (AMO) and Tabular Object Model (TOM) to manage and automate your Analysis Services deployments and operations. ADOMD.NET can be used to query models from applications.
Example: Using TOM for Deployment
You can automate the deployment of tabular models using C# with TOM:
using Microsoft.AnalysisServices.Tabular;
using System;
// ...
Server server = new Server();
server.Connect("asazure://premium.asazure.windows.net/your-server-name");
Database database = server.Databases.FindByName("YourModelDatabase");
if (database == null) {
database = new Database("YourModelDatabase");
server.Databases.Add(database);
database.Update(UpdateOptions.CreateForeground);
}
// Load model from file or create new model
// ...
database.Model.Update();
server.Disconnect();