Create Tables in Azure Analysis Services

This document guides you through the process of creating tables for your Azure Analysis Services model. Tables are fundamental building blocks for your data model, representing entities like customers, products, or sales transactions.

Understanding Tables

In Azure Analysis Services, tables are derived from data sources you connect to. You can import data from various sources, including relational databases, cloud storage, and other Azure services. Each column in your data source typically becomes a column in your Analysis Services table.

Steps to Create Tables

1. Connect to a Data Source

Before you can create tables, you need to establish a connection to your data source. This is typically done using SQL Server Data Tools (SSDT) or Visual Studio with Analysis Services projects extension.

Open your Azure Analysis Services project in SSDT or Visual Studio.
In the Solution Explorer, right-click on the Data Sources folder and select Add New Data Source.
Follow the wizard to configure your connection string, providing details like server name, database name, and authentication credentials.

2. Import Tables

Once your data source is connected, you can import tables from it into your Analysis Services model.

In the Solution Explorer, right-click on the Tables folder and select Add Table.
Alternatively, you can right-click on your connected Data Source and select Import New Tables.
The Table Import Wizard will appear. Select the tables you wish to import from your data source.
You can rename tables and columns during the import process if needed for clarity or to conform to naming conventions.

3. Configure Table Properties

After importing, you can further configure the properties of your tables.

Name: The logical name of the table within your model.
Description: A brief explanation of the table's purpose.
Source Table: The original table name in the data source.
Columns: You can manage columns, including renaming, changing data types, and marking them for import or exclusion.

Example: Importing a 'Sales' Table

Let's assume you have a SQL Server database with a Sales table containing columns like OrderID, ProductID, CustomerID, OrderDate, and Amount.

When importing this table, you would:

Select the Sales table from your data source.
Optionally, rename the table to something more descriptive like FactSales.
Ensure all relevant columns (OrderID, ProductID, CustomerID, OrderDate, Amount) are selected for import.

-- Example of data that might be in your source table
SELECT
    OrderID,
    ProductID,
    CustomerID,
    OrderDate,
    Amount
FROM
    YourDataSource.dbo.Sales;

Key Considerations

Note: When importing tables, it's a good practice to only import the columns you actually need for your analysis. This reduces memory usage and improves query performance.

Tip: Consider using descriptive and consistent naming conventions for your tables and columns to make your model easier to understand and navigate.

Handling Large Tables

For very large tables, consider techniques like partitioning or filtering at the source to reduce the amount of data imported into Analysis Services. This can significantly impact performance and manageability.

Data Types

Ensure that the data types of your imported columns are appropriate for the data they contain. Analysis Services supports a wide range of data types, and choosing the correct one is crucial for accuracy and performance.

Next Steps

Once you have successfully created your tables, the next logical step is to create relationships between them to define how different entities in your model are connected. This is essential for building a coherent and functional data model.

Proceed to the Create Relationships section.