Manage Data Sources

This document provides guidance on how to manage data sources within Azure Analysis Services. Properly configured data sources are crucial for connecting your models to the underlying data. This includes understanding connection strings, credentials, and data source types.

Understanding Data Source Objects

In Azure Analysis Services, a data source object represents a connection to a single data source, such as a SQL Server database, Azure SQL Database, or an Azure Blob Storage file. You can define multiple data sources within a single Analysis Services model.

Creating a Data Source

You can create data sources using tools like Visual Studio with SQL Server Data Tools (SSDT) or by scripting with AMO (Analysis Management Objects) or TOM (Tabular Object Model).

Using Visual Studio (SSDT)

Open your Azure Analysis Services project in Visual Studio.
In Solution Explorer, right-click on the Data Sources folder and select Add Data Source.
The Data Source Wizard will appear.
Select the appropriate data source type from the Type dropdown. Common types include:
- SQL Server
- Azure SQL Database
- Azure Blob Storage
- Oracle
- Teradata
Enter the server name and database name (if applicable).
Configure authentication settings. You can use Windows authentication or a specific user account. For cloud sources, you might use Azure Active Directory authentication.
Test the connection to ensure it's valid.
Click Finish to create the data source.

Editing an Existing Data Source

To modify an existing data source:

In Solution Explorer, double-click the data source you want to edit.
The Data Source Editor will open.
You can update the server name, database name, or authentication details.
Click OK to save your changes.

Data Source Types and Connection Strings

The connection string is a critical component of a data source object. It specifies how Analysis Services connects to the data provider. Here are some common examples:

SQL Server Connection String Example

Provider=SQLNCLI11;Server=YourServerName;Database=YourDatabaseName;UID=YourUsername;PWD=YourPassword;

Azure SQL Database Connection String Example

Provider=SQLNCLI11;Server=tcp:your_azure_sql_server.database.windows.net,1433;Database=your_database;Uid=your_admin_login@your_azure_sql_server;Pwd=your_password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Azure Blob Storage Connection String Example

Provider=Microsoft.Mashup.OleDb.1;Data Source=https://yourstorageaccount.blob.core.windows.net/yourcontainer;Initial Catalog=;Persist Security Info=False;

When using Azure Blob Storage, you will typically provide credentials through the UI or manage them separately.

Note: It's recommended to use secure methods for storing and managing credentials, such as Azure Key Vault or Windows Integrated Authentication where possible, rather than hardcoding passwords directly in connection strings.

Managing Credentials

Credentials can be managed in several ways:

Embedded Credentials: Stored directly within the data source object. This is suitable for development but less secure for production.
Windows Authentication: Uses the credentials of the Analysis Services service account or a specific domain user.
Azure Active Directory (Azure AD) Authentication: Recommended for Azure data sources, using service principals or managed identities for secure access.
Service Principal: A security identity used by applications, hosted resources, and automated tools to access Azure resources.

Configuring Azure AD Authentication

For Azure data sources like Azure SQL Database or Azure Data Lake Storage, configuring Azure AD authentication is the preferred method:

Ensure your Analysis Services instance and the data source are configured for Azure AD authentication.
When creating or editing a data source, select Azure Active Directory as the authentication type.
You will typically need to specify the Tenant ID, Client ID (for a service principal), and optionally a Client Secret or Certificate.
Alternatively, you can use the Analysis Services service identity if it's granted permissions to the data source.

Tip: Regularly review and update credentials, especially if using hardcoded passwords or expiring keys. Automating this process with Azure Key Vault can significantly enhance security.

Data Source Views

Data Source Views (DSVs) provide a logical layer over the physical data sources. They allow you to rename columns, define relationships, and hide sensitive information before it's exposed to the model. When you create a data source, you will often create a DSV based on it.

Best Practices

Use Specific Permissions: Grant only the necessary read permissions to the Analysis Services service account or service principal on the data source.
Avoid Over-Sharing: Don't grant administrative privileges to the Analysis Services identity on the data source.
Secure Credentials: Store credentials securely using Azure Key Vault or other secrets management solutions.
Use Data Source Views: Leverage DSVs to abstract the physical data sources and prepare data for modeling.
Monitor Connections: Regularly check data source connection health and refresh statuses.

Important: Changes to data source connections, especially in production, should be planned and tested thoroughly to avoid disruption to your reporting and analytics.