This chapter covers Azure Analysis Services (AAS), a fully managed Platform as a Service (PaaS) that provides enterprise-grade semantic models for business intelligence. For the DP-900 exam, you need to understand how AAS fits into the Azure analytics ecosystem, its core capabilities (tabular models, DAX, partitions, roles), and how it differs from other services like Azure SQL Database or Power BI datasets. Expect approximately 5-8% of exam questions to touch on AAS, often in comparison scenarios or identifying the correct service for a given BI requirement.
Jump to a section
Imagine a large warehouse (your raw data) filled with thousands of different types of items—sales records, inventory logs, customer profiles—each stored in its own box (source database). The warehouse manager wants to answer questions like 'What was the total revenue for electronics in Q3 across all regions?' without having to open every box each time. So the manager builds a 'data cube factory' (Azure Analysis Services) that pre-processes and organizes the data into a set of interlocking cubes (tabular models). Each cube is a multi-dimensional structure where dimensions (time, product, region) form the edges and measures (revenue, count, profit) are stored in the cells. When a sales director asks a question via a reporting tool (like Power BI), the tool sends a query to the factory. The factory does not go back to the warehouse; instead, it slices the pre-built cube to instantly retrieve the exact aggregate value. The factory uses a specialized language (DAX) to define complex calculations, like rolling averages or year-over-year growth, which are computed on the fly from the stored aggregates. If the warehouse gets new data, the factory can refresh its cubes (process data) by pulling only the changes. The factory also manages security: it knows which employees are allowed to see which cubes (roles) and can even hide certain slices (row-level security) so a regional manager only sees their region. This factory is highly available—if one cube production line fails, another takes over (high availability with multiple replicas). In contrast, a direct query to the warehouse (like querying a SQL database directly) would be slower and more complex, requiring joins and aggregations each time.
What is Azure Analysis Services?
Azure Analysis Services (AAS) is a fully managed PaaS offering that hosts semantic data models in the cloud. A semantic model is a business representation of data that defines business logic, calculations, and relationships, enabling end users to perform self-service analytics using tools like Power BI, Excel, or custom applications. AAS uses the same engine as SQL Server Analysis Services (SSAS) Tabular mode, but as a cloud service, Microsoft handles infrastructure, patching, and high availability.
Why Azure Analysis Services?
In a typical data warehouse, raw data is stored in relational tables optimized for storage and query performance. However, business users need to analyze data across multiple dimensions (time, geography, product) with complex calculations (year-over-year growth, rolling averages). Writing these queries directly against a data warehouse is error-prone and slow. AAS pre-aggregates and caches data in an in-memory columnar store (VertiPaq engine), which compresses data and stores it by columns rather than rows. This allows queries to return results in milliseconds even over billions of rows.
How It Works Internally
AAS models are built using Visual Studio (SSDT) or the web designer. The model defines: - Tables: Imported from data sources (Azure SQL DB, SQL Server, Oracle, Teradata, Power BI datasets, etc.) using either Import mode or DirectQuery mode. - Relationships: Defined between tables (e.g., Sales[ProductID] -> Product[ProductID]). - Measures: DAX formulas that define aggregations (e.g., Total Sales = SUM(Sales[Amount])). - Dimensions: Tables used for filtering and grouping (e.g., Date, Customer, Product). - KPIs: Visual indicators based on measures. - Roles: Security definitions that restrict access to specific rows or tables.
Once deployed, AAS loads the data into memory (Import mode) or passes queries through to the source (DirectQuery mode). The VertiPaq engine compresses each column using techniques like dictionary encoding and run-length encoding. For example, a column with 10,000 rows and 100 distinct values might compress to a dictionary of 100 entries and an index of 10,000 small integers.
Key Components, Values, Defaults, and Timers
Tiers: Developer (D1), Basic (B1, B2), Standard (S0, S1, S2, S3, S4, S8, S9), and higher. Each tier has different memory and QPUs (Query Processing Units). For example, S0 offers 3 GB memory, 10 QPUs; S4 offers 25 GB memory, 50 QPUs.
Processing: Data refresh can be scheduled via Azure Automation, Logic Apps, or the REST API. Full process can take minutes to hours depending on data volume.
Query limits: Maximum query response size is 1 GB per query. Timeout default is 600 seconds (10 minutes).
Concurrency: Standard tier supports up to 100 concurrent queries per QPU (actual limit depends on model complexity).
High Availability: Azure SLA 99.9% for Standard tier. Multiple replicas can be deployed in different availability zones.
Backup: Automatic backups every 12 hours, retained for 14 days. Manual backups can be exported to Azure Blob Storage.
Authentication: Azure Active Directory (AAD) integration. Supports organizational accounts and service principals.
Firewall: Can restrict access to specific IP ranges.
Configuration and Verification Commands
To deploy a model, you typically use:
<!-- In Visual Studio, right-click project -> Deploy. Enter server name (e.g., asazure://westus.asazure.windows.net/myserver) -->To process a database using PowerShell:
# Install module: Install-Module -Name SqlServer
Invoke-ProcessASDatabase -DatabaseName "AdventureWorks" -Server "asazure://westus.asazure.windows.net/myserver" -RefreshType "Full"To test connectivity:
# Using SSMS, connect to: asazure://westus.asazure.windows.net/myserverTo check server properties via REST API:
GET https://westus.asazure.windows.net/servers/myserver?api-version=2017-08-01
Authorization: Bearer <token>Interaction with Related Technologies
Power BI: AAS can be a live connection source for Power BI datasets. Users build reports on top of the semantic model without seeing underlying data.
Azure Data Factory: Can orchestrate data refreshes by calling AAS REST API or using the Azure Analysis Services connector.
Azure SQL Database: Common source for AAS models. Import mode loads entire tables; DirectQuery sends queries to SQL at query time.
Azure Synapse Analytics: AAS can connect to Synapse SQL pools for large-scale data.
Excel: Users can connect to AAS via Power Pivot or the Data tab -> From Other Sources -> From Analysis Services.
Power Automate: Can trigger data refresh on a schedule or in response to events.
Differences Between Import and DirectQuery Modes
Import Mode: Data is copied into AAS memory. Queries are fast (sub-second) but data freshness depends on refresh schedule. Best for moderate data volumes (up to tens of GB).
DirectQuery Mode: No data is stored in AAS. Queries are sent to the source database each time. Suitable for very large datasets where caching is impractical, but query performance depends on source database speed. Not all DAX functions are supported in DirectQuery (e.g., time intelligence functions have restrictions).
Partitions
Large tables can be partitioned to enable parallel processing and incremental refresh. For example, a Sales table might have partitions by month: Sales_Jan2023, Sales_Feb2023, etc. During a refresh, only the latest partition needs to be updated, reducing processing time. Partitions are defined in the model and can be managed via TMSL (Tabular Model Scripting Language) or the REST API.
Security: Row-Level and Object-Level
Row-Level Security (RLS): Filters rows based on the user's identity. Implemented via DAX filter expressions in roles. For example, a Sales Manager role might have a filter: Sales[Region] = USERNAME().
Object-Level Security (OLS): Hides entire tables or columns from certain roles. For example, a 'Salary' column can be hidden from non-HR roles.
Monitoring and Diagnostics
Azure Monitor provides metrics like: - QPU Usage: Average, max, per-minute. - Memory Usage: Current and peak. - Query Count: Number of queries per second. - Processing Unit: CPU usage.
Diagnostic logs can be sent to Log Analytics for advanced troubleshooting. Common issues include: - Out of Memory: Model size exceeds tier memory limit. Solution: scale up or reduce data. - Slow Queries: Often due to inefficient DAX or missing relationships. Use VertiPaq Analyzer (free tool) to analyze model size and cardinality. - Processing Failures: Check data source connectivity, permissions, and data type mismatches.
Best Practices for Exam
Remember that AAS is a PaaS service — you don't manage VMs or OS.
It supports tabular models only (multidimensional models are deprecated in SSAS, but AAS only supports tabular).
The main query language is DAX (Data Analysis Expressions), but you can also use MDX for certain scenarios.
AAS can be used as a live connection source for Power BI reports, ensuring a single version of truth.
For large datasets, consider Azure Synapse or Azure Data Lake with Azure Analysis Services on top for aggregation.
Exam Trap Patterns
Trap: AAS is the same as Power BI datasets. Reality: Power BI datasets also use the same engine, but AAS is a separate PaaS service for enterprise scenarios requiring dedicated capacity, row-level security, and integration with non-Power BI tools.
Trap: AAS supports multidimensional models. Reality: AAS only supports tabular models. Multidimensional (OLAP cubes) are only available in on-premises SSAS.
Trap: DirectQuery mode stores data in AAS. Reality: DirectQuery does not cache data; it passes queries through to the source.
Trap: AAS can be used for real-time streaming. Reality: AAS is not designed for real-time; it is optimized for analytical queries on historical data. Use Azure Stream Analytics for real-time.
Summary
Azure Analysis Services is a powerful semantic layer that accelerates BI by pre-aggregating data and providing a business-friendly interface. For DP-900, focus on understanding its role, deployment modes (Import vs DirectQuery), security features (RLS, OLS), and how it differs from other Azure data services. Know the key terms: tabular model, DAX, VertiPaq, partitions, roles, and QPU.
Design the Tabular Model
In Visual Studio with SSDT, create a new Analysis Services Tabular project. Define tables by connecting to source databases (e.g., Azure SQL DB). For each table, select columns to import. Define relationships between tables by dragging columns (e.g., Sales[ProductID] -> Product[ProductID]). Create measures using DAX, e.g., Total Sales = SUM(Sales[Amount]). Optionally, create hierarchies (e.g., Year > Quarter > Month) and KPIs. Set partitions for large tables to enable incremental refresh. Finally, define roles for security: a role can have row filters using DAX expressions like `=Sales[Region]=USERNAME()`.
Deploy to Azure Analysis Services
In Visual Studio, right-click the project and select Deploy. Enter the server name (e.g., asazure://westus.asazure.windows.net/myserver). The deployment process creates or updates the database on Azure. It uploads the model metadata and, if using Import mode, imports all data into the VertiPaq engine. The deployment may take from seconds to minutes depending on model size. After deployment, verify the server appears in the Azure portal with the database listed under 'Models'. You can also use SQL Server Management Studio (SSMS) to connect and browse the model.
Configure Data Refresh
Since data in source systems changes, you must schedule regular refreshes. In Azure portal, navigate to your AAS server and select 'Data Sources'. You can set up credentials for each source. Then create an automation schedule using Azure Logic Apps or Azure Automation. For example, a Logic App can trigger a REST API call to process the database. The processing command: POST https://westus.asazure.windows.net/servers/myserver/models/AdventureWorks/refreshes. You can specify refresh type: Full, Clear, DataOnly, Calculate, Defragment. Incremental refresh for partitions reduces load time.
Connect Reporting Tools
End users connect to the AAS model from tools like Power BI, Excel, or Tableau. In Power BI Desktop, select Get Data -> Azure -> Azure Analysis Services. Enter the server and database name. Choose either 'Connect live' (queries sent directly to AAS) or 'Import' (copies data into Power BI). For live connections, all security roles apply. Users see only the data they have permission to. Reports built on AAS are lightweight because aggregations are computed server-side. In Excel, use Data -> From Other Sources -> From Analysis Services.
Monitor and Scale
Use Azure Monitor to track QPU usage, memory, and query counts. If QPU consistently exceeds 80%, consider scaling up to a higher tier (e.g., from S1 to S2). Scaling can be done via Azure portal or PowerShell. For high availability, deploy multiple replicas in different regions or availability zones. The service automatically fails over. Also, set up diagnostic logs to capture slow queries or errors. Use VertiPaq Analyzer to optimize model size: reduce cardinality of columns, remove unused columns, or use DirectQuery for very large tables.
Enterprise Scenario 1: Centralized Sales Analytics
A multinational retail company uses Azure SQL Database as its operational data store. Regional sales data is collected daily. The BI team builds an AAS model that combines sales, inventory, and customer data. They create measures like 'Same Store Sales Growth' and 'Inventory Turnover'. The model is partitioned by month to allow incremental daily refreshes. Power BI reports connect live to AAS. Row-level security ensures each regional manager sees only their region. The solution scales to 500 concurrent users with sub-second query response. Common misconfiguration: forgetting to refresh partitions after adding new data, leading to stale reports. The team uses Azure Automation runbooks to trigger refresh nightly.
Scenario 2: Financial Reporting with DirectQuery
A financial services firm has a massive data warehouse in Azure Synapse with billions of rows. They cannot import all data into AAS due to cost and time. Instead, they use DirectQuery mode. The AAS model defines measures and relationships, but queries are sent to Synapse at runtime. This allows users to slice by year, product, and region without moving data. However, complex DAX calculations like time intelligence (e.g., year-over-year) are limited. The firm mitigates by pre-aggregating data in Synapse using materialized views. Performance tuning involves indexing Synapse tables and optimizing DAX to minimize query complexity.
Scenario 3: Embedded Analytics in a SaaS Application
An ISV (Independent Software Vendor) builds a SaaS analytics product. They embed AAS models into their application using the Azure Analysis Services REST API. Each customer gets a dedicated model with row-level security isolating their data. The ISV uses the Developer tier for development, then scales to Standard for production. They process data on demand using the API. Challenges include managing many small models (thousands) and ensuring each model stays within memory limits. The ISV uses scripting to automate model creation and deployment. They monitor QPU to decide when to scale per customer.
Common Pitfalls
Over-provisioning memory: Choosing a tier too high for the data volume wastes money. Use VertiPaq Analyzer to estimate memory.
Under-provisioning QPU: High concurrency leads to throttling. Monitor QPU and scale out if needed.
Ignoring partition strategy: Full refreshes on large tables take hours. Use incremental refresh with partitions.
Misunderstanding DirectQuery limitations: Not all DAX functions work; test thoroughly.
What DP-900 Tests on Azure Analysis Services
DP-900 objective 3.4 focuses on 'Describe analytics workloads in Azure'. Within that, candidates must understand the role of Azure Analysis Services as a semantic model layer. The exam will test:
Distinguishing between Azure Analysis Services, Power BI, and Azure Synapse Analytics.
Knowing that AAS uses tabular models and DAX.
Understanding Import vs DirectQuery modes.
Recognizing that AAS provides row-level security and object-level security.
Identifying appropriate use cases: enterprise BI requiring single version of truth, complex calculations, and governed self-service.
Common Wrong Answers and Why Candidates Choose Them
Wrong: 'Azure Analysis Services is a data warehouse.' Why chosen: Candidates confuse analytical services with storage. Reality: AAS is a semantic layer; it stores a copy of data (in Import mode) but is not a data warehouse. It queries data warehouses like Azure Synapse.
Wrong: 'AAS supports both tabular and multidimensional models.' Why chosen: SSAS on-premises supports both. Reality: AAS only supports tabular. Multidimensional is deprecated even in SSAS.
Wrong: 'DirectQuery mode caches data in memory for fast queries.' Why chosen: The word 'Direct' sounds like it might be faster. Reality: DirectQuery does not cache; it sends queries to source each time. Import mode caches.
Wrong: 'You can use T-SQL to query AAS.' Why chosen: Many data professionals know T-SQL. Reality: AAS uses DAX and MDX, not T-SQL. T-SQL is for relational databases.
Specific Numbers and Terms on the Exam
VertiPaq: The in-memory columnar storage engine. Know that it compresses data.
DAX: Data Analysis Expressions. Be able to identify a DAX formula example (e.g., SUM(Table[Column])).
QPU: Query Processing Unit. The measure of compute capacity.
Tiers: Developer, Basic, Standard. Standard is production.
Import vs DirectQuery: Import stores data; DirectQuery passes through.
Row-Level Security (RLS) : Implemented via roles and DAX filters.
Partitions: Used for incremental refresh.
Edge Cases and Exceptions
AAS and Power BI: Power BI Premium can also host semantic models (Power BI datasets). AAS is for when you need a dedicated service independent of Power BI, or when you need to integrate with non-Microsoft tools.
AAS and Azure Analysis Services Web Designer: Microsoft deprecated the web designer in 2021. All models must be created in Visual Studio or using the TMSL/REST API.
AAS and Azure Government: AAS is available in Azure Government regions but with limited features.
AAS and Multidimensional: As of 2023, Azure Analysis Services does not support multidimensional models. If the exam mentions 'cube', they might be referring to tabular models (which are also cubes conceptually).
How to Eliminate Wrong Answers
If the scenario mentions 'real-time streaming' or 'IoT', eliminate AAS. It is for analytical queries on historical data.
If the scenario requires 'storing raw data', eliminate AAS. Use Azure Data Lake or Azure SQL.
If the scenario requires 'complex DAX calculations' and 'governed self-service', AAS is likely the right choice.
If the scenario mentions 'Power BI only', consider Power BI datasets instead of AAS.
If the scenario mentions 'multidimensional cubes', eliminate AAS (unless it's a trick — they might mean tabular).
Azure Analysis Services is a PaaS semantic model service that uses the VertiPaq in-memory columnar engine.
It supports tabular models only; multidimensional models are not available in AAS.
The primary query language is DAX; MDX is also supported.
Import mode caches data in memory; DirectQuery passes queries to the source without caching.
Row-level security is implemented using roles with DAX filter expressions.
Key metrics to monitor: QPU usage and memory. Scale up if QPU consistently exceeds 80%.
Partitions enable incremental refresh, reducing processing time for large tables.
AAS is not for real-time processing; it is optimized for analytical queries on historical data.
These come up on the exam all the time. Here's how to tell them apart.
Azure Analysis Services
Dedicated PaaS service, independent of Power BI licensing.
Supports any client: Power BI, Excel, Tableau, custom apps via XMLA.
Row-level security via roles and DAX filters.
Scaling via tiers (S0, S1, etc.) with QPU and memory limits.
Requires separate deployment and management; no built-in visualization.
Power BI Datasets (Premium)
Part of Power BI Premium; requires Premium capacity or Premium Per User.
Primarily used within Power BI ecosystem, but also supports XMLA endpoints in Premium.
Row-level security similar, but managed in Power BI Desktop or service.
Scales with Power BI Premium capacity (v-cores).
Integrated with Power BI reports, dashboards, and apps; easier for Power BI-only shops.
Mistake
Azure Analysis Services is the same as Power BI datasets.
Correct
While both use the same VertiPaq engine and DAX, AAS is a separate PaaS service with dedicated capacity, independent of Power BI licensing. Power BI datasets are part of Power BI Premium or Shared capacity. AAS can be used by any client (Excel, Tableau, custom apps), not just Power BI.
Mistake
AAS supports multidimensional (OLAP) models.
Correct
AAS only supports tabular models. Multidimensional models (cubes) are only available in on-premises SQL Server Analysis Services (SSAS). Azure Analysis Services is tabular-only.
Mistake
DirectQuery mode stores a copy of the data in AAS.
Correct
DirectQuery does not cache data. Queries are sent to the source database each time. Import mode stores a copy. DirectQuery is used for very large datasets where importing is impractical.
Mistake
AAS can be used for real-time data processing.
Correct
AAS is designed for analytical queries on historical data. Data refreshes are batch-oriented (scheduled or on-demand). For real-time, use Azure Stream Analytics or Azure Synapse with streaming.
Mistake
You can query AAS using T-SQL.
Correct
AAS uses DAX (Data Analysis Expressions) and MDX (Multidimensional Expressions). T-SQL is for relational databases like Azure SQL Database. Clients connect via the XMLA protocol.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Import mode copies data from the source into AAS memory, providing fast query performance but requiring periodic refreshes. DirectQuery mode does not store data; queries are sent to the source database each time. Import is best for moderate data volumes needing sub-second response. DirectQuery is for very large datasets where caching is impractical, but query performance depends on the source.
Yes. Power BI Desktop can connect live to an AAS model, or import data from it. This provides a single semantic model that multiple reports can use, ensuring consistency. AAS also supports Excel, Tableau, and custom applications via the XMLA endpoint.
Use Azure Active Directory for authentication. Implement row-level security (RLS) by creating roles with DAX filter expressions. For example, a role for 'Sales Managers' might have a filter `=Sales[Region]=USERNAME()`. Object-level security (OLS) can hide tables or columns from certain roles.
QPU stands for Query Processing Unit. It is a measure of compute capacity. Different tiers offer different numbers of QPUs (e.g., S0: 10 QPUs, S4: 50 QPUs). QPU usage is monitored via Azure Monitor. If QPU is consistently high, consider scaling up.
You can schedule refreshes using Azure Logic Apps, Azure Automation, or the REST API. The refresh operation can be full (reload all data) or incremental (refresh only specific partitions). You need to provide credentials for each data source.
No. AAS is designed for analytical queries on historical data. Data refreshes are batch-oriented. For real-time analytics, use Azure Stream Analytics or Azure Synapse with streaming capabilities.
VertiPaq is the in-memory columnar storage engine that compresses and stores data by column. It uses techniques like dictionary encoding and run-length encoding to reduce memory footprint and speed up queries. It is the same engine used in Power BI and SSAS Tabular.
You've just covered Azure Analysis Services — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?