Create Tables in Azure Analysis Services

This document guides you through the process of creating tables in Azure Analysis Services using various methods, including the Azure portal, Visual Studio with Analysis Services projects, and Tabular Editor.

Understanding Tables in Analysis Services

In Azure Analysis Services, tables represent entities in your data model. These tables are typically derived from source data, such as relational databases, flat files, or other data sources. Each table contains columns, which represent attributes of the entity.

Methods for Creating Tables

1. Using the Azure Portal

The Azure portal offers a simplified way to create tables, especially for basic data sources. This method is often used for quick exploration or when integrating with Azure services like Azure SQL Database or Azure Synapse Analytics.

Navigate to your Azure Analysis Services resource in the Azure portal.
Under the "Model designer" section, click "Open in Visual Studio" or "Open in tabular editor". (Note: Direct table creation from the portal itself is limited; it primarily launches external tools).
If using Visual Studio, connect to your Azure Analysis Services server.
In the Model Designer, right-click on the "Tables" folder and select "Create Table from Data Source".
Follow the wizard to select your data source, choose tables or write queries, and import the data.

Note: For more complex transformations and data shaping, using Visual Studio or Tabular Editor is recommended.

2. Using Visual Studio with Analysis Services Projects

Visual Studio provides a robust environment for developing tabular models. Creating tables here allows for sophisticated data import, transformation, and relationship definition.

Ensure you have Visual Studio installed with the SQL Server Data Tools (SSDT) for Analysis Services.
Create a new "Analysis Services Tabular Project".
In the Solution Explorer, right-click on the "Data Sources" folder and select "New Data Source".
Configure the connection to your data source.
Right-click on the "Tables" folder and select "Import from Data Source".
Choose the data source you created.
Select the tables or views you want to import, or write a specific query to define your table.
Click "Finish" to create the table in your model.

You can then edit the table's properties, columns, and relationships within the tabular model designer.

3. Using Tabular Editor

Tabular Editor is a popular third-party tool that provides a powerful and efficient way to work with Analysis Services Tabular Models. It's known for its scripting capabilities and intuitive interface.

Download and install Tabular Editor.
Connect to your Azure Analysis Services server.
In Tabular Editor, right-click on the "Tables" folder and select "New Table".
You will be prompted to enter a DAX expression that defines the table. This expression typically uses functions like 'SQL' or 'Tbl' to import data.

Example DAX Expression in Tabular Editor:

DAX

EVALUATE
{
    (
        SELECT
            ProductID,
            ProductName,
            Category
        FROM
            [AdventureWorksDW2019].[DimProduct]
    )
}

This DAX expression imports data from the DimProduct table in the specified SQL Server database.

Tip: Tabular Editor is excellent for automating table creation and modification using C# scripting.

Best Practices

Source Data Quality: Ensure your source data is clean and accurate before importing.
Column Naming: Use clear and descriptive names for your tables and columns.
Data Types: Select appropriate data types for your columns to optimize performance and storage.
Relationships: Define relationships between tables correctly to enable meaningful analysis.
Performance Tuning: Consider partitioning and indexing for large tables.

Next Steps

After creating tables, you'll typically proceed to define relationships, create measures and calculated columns, and deploy your tabular model.