Migrate from Azure Analysis Services to Power BI Premium
This article targets Azure Analysis Services (AAS) data modelers and administrators. It provides them with guidance and rationale to help migrate their AAS databases to Power BI Premium or Power BI Embedded.
Power BI has evolved into the leading platform for both self-service and IT-managed enterprise business intelligence (BI). With exponential growth in data volumes and complexity, Power BI customers demand enterprise BI solutions that scale to petabytes, are secure, easy to manage, and accessible to all users across the largest of organizations.
For over two decades, Microsoft has continued to make deep investments in enterprise BI. AAS and SQL Server Analysis Services (SSAS) are based on mature BI data modeling technology used by countless enterprises. Today, that same technology is also at the heart of Power BI datasets.
In this article, the terms data model, semantic model, BI model, tabular model, database, and Power BI dataset have the same meaning. This article commonly uses the terms data model for AAS model and dataset for Power BI model. This article describes the process of migrating to Power BI Premium but this also applies to Power BI Embedded.
In recent years, Microsoft has taken great strides to deliver AAS capabilities to Power BI Premium. To that end, Power BI instantly inherited a large ecosystem of developers, partners, BI tools, and solutions that were built up over decades. Today, the full set of Power BI Premium workloads, features, and capabilities now results in a modern, cloud BI platform that goes far beyond comparable functionality available in AAS or SSAS.
Today, many customers have Power BI reports that live connect to AAS. Naturally, these customers are asking whether there's an opportunity to consolidate by hosting their data models alongside their reports in Power BI. They often ask questions like:
- Does all the AAS functionality we depend on work in Power BI?
- Is Power BI backwards compatible with AAS tools and processes?
- What capabilities are available only in Power BI?
- How do we compare costs between AAS and Power BI?
- Why is Microsoft converging enterprise and self-service BI?
- How do we migrate from AAS to Power BI Premium?
- Is AAS marked for deprecation?
- What's Microsoft's roadmap for enterprise data models?
Answers to many of these questions are described in this article.
The decision to migrate to Power BI Premium depends on the requirements of each customer. Customers should carefully evaluate additional benefits in order to make an informed decision. We expect to see organic migration to Power BI Premium over time, and our intention is that it happens on terms that the customer is comfortable with.
To be clear, currently there aren't any plans to deprecate AAS. There is a priority to focus investment on Power BI Premium for enterprise data modeling, and so the additional value provided by Power BI Premium will increase over time. Customers who choose Power BI Premium can expect to benefit from alignment with the Microsoft BI product roadmap.
Convergence of self-service and enterprise BI
Consolidation of items (like reports and dashboards) in Power BI results in simplified discovery and management due to co-location. Once consolidated, there's no need to bridge the gap between AAS and Power BI. Central IT teams can then more easily adopt self-service items that have become popular yet are resulting in a management burden for the business. IT can take over such items. They can operationalize them for mission-critical decision making based on governed data that's aligned with corporate standards and with lineage transparency. Simplifying this workflow by sharing a common platform promotes better collaboration between the business and IT.
Power BI Premium
Thanks to its distributed architecture, Power BI Premium is less sensitive to overall load, temporal spikes, and high concurrency. By consolidating capacities to larger Power BI Premium SKUs, customers can achieve increased performance and throughput.
Scalability benefits associated with Power BI Premium are described later in this article.
AAS provides the Analysis Services database engine for hosting data models, which is a core component of a Microsoft enterprise BI architecture. In fact, Power BI Premium is a superset of AAS because it provides much more functionality. The following table lists features supported in AAS and Power BI Premium. The table focuses on - but isn't limited to - Power BI dataset-related capabilities.
|Feature||AAS||Power BI Premium|
|Paginated reports, which are ideal for reports that are designed to be printed, especially when table data overflows to multiple pages||No||Yes|
|Dataflows, which store fragments of data intended for use in a Power BI dataset||No||Yes|
|AI with dataflows, which use artificial intelligence (AI) with Cognitive Services, Automated Machine Learning, and Azure Machine Learning (AML) integration||No||Yes|
|Metrics, which curate key business measures and allow tracking them against objectives||No||Yes|
|Unlimited report distribution to anyone (even outside the organization)||No||Yes|
|Business-driven interactive reports, workspaces, and apps||No||Yes|
|Platform scalability and resiliency|
|Power BI Premium architecture, which supports increased scale and performance||No||Yes|
|Optimized dataset memory management||No||Yes|
|Scale limits per data model instead of per server||No||Yes|
|CPU smoothing for refresh resiliency||No||Yes|
|Autoscale, which automatically adds compute capacity to avoid slowdowns under heavy use||No||Yes|
|Business continuity and disaster recovery (BCDR) with Azure regions and availability zones||No||Yes|
|Interactive analysis over big data|
|Large model sizes (up to 400 GB with compression)||Yes||Yes|
|Hybrid tables, which comprise in-memory and DirectQuery partitions that can help deliver near real-time results over large tables||No||Yes 1|
|Automatic aggregations, which use state-of-the-art machine learning (ML) to continuously optimize DirectQuery performance||No||Yes|
|User-defined aggregations, which can improve query performance over very large DirectQuery tables||No||Yes|
|Query scale-out, which distributes client queries among replicated servers||Yes||Yes|
|Bring Your Own Key (BYOK), which allows customers to use their own encryption key to encrypt data stored in the Microsoft cloud||No||Yes|
|Virtual network connectivity, which allows Power BI to work seamlessly in an organization's virtual network (VNet)||No||Yes 1|
|Azure Private Link, which provides secure access for data traffic in Power BI||No||Yes|
|Single sign-on (SSO) for DirectQuery sources, which allows connecting to data sources by using the report user's identity||No||Yes|
|Row-level security (RLS), which restricts access to specific rows of data for specific users||Yes||Yes|
|Object-level security (OLS), which restricts access to specific tables or columns for specific users||Yes||Yes|
|Firewall, which when enabled, allows setting allowed IP address ranges||Yes||No 3|
|Microsoft Purview integration, which helps customers manage and govern Power BI items||No||Yes|
|Microsoft Information Protection (MIP) sensitivity labels and integration with Microsoft Defender for Cloud Apps for data loss prevention||No||Yes|
|Content endorsement, to promote or certify valuable, high-quality Power BI items||No||Yes|
|Compatibility with Power BI Desktop||No||Yes|
|Composite models including using DirectQuery for Power BI datasets and AAS||No||Yes 1|
|Translations for multi-language model versions observed by the Power BI service||No||Yes|
|Analysis Service engine semantic modeling||Yes||Yes|
|Incremental refresh, which uses policies to automate partition management and can help deliver near real-time reporting (see hybrid tables)||No||Yes|
|Deployment pipelines, which manage the lifecycle of Power BI content||No||Yes|
|Scheduled refresh, which keeps cached dataset data current||No||Yes|
|Enhanced refresh, which allows any programming language to perform asynchronous dataset refreshes by using a REST API call||Yes||Yes|
|Backup and restore||Yes||Yes|
|Dataset workload settings, which control Premium capacity workloads||No||Yes|
|Server properties, which control Analysis Services server instance properties||Yes||Yes 1|
|Alias server names, which allow connecting to an Analysis Services server instance by using a shorter alias||Yes||No|
|XMLA endpoint enabled APIs for scripting and compatibility with services for automation and ALM including Azure Functions, Azure Automation and Azure DevOps||Yes||Yes|
|Support for all Power BI data sources||No||Yes|
|XMLA endpoint, which allows open-platform connectivity for data model consumption and visualization tools, including third-party tools||Yes||Yes|
|Multi-Geo feature, which helps multinational customers address regional, industry-specific, or organizational data residency requirements||Yes||Yes|
|Data hub integration, which helps users discover, explore, and use Power BI datasets||No||Yes|
|Data lineage view and dataset impact analysis, which help users understand and assess Power BI item dependencies||No||Yes|
|Monitoring and diagnostic logging|
|Premium capacity metrics app, which provides monitoring capabilities for Power BI capacities||No||Yes|
|Power BI audit log, which tracks user activities across Power BI and Microsoft 365||No||Yes|
|Azure Log Analytics (LA) integration, which allows administrators to configure a Log Analytics connection for a Power BI workspace||Yes||Yes 1|
|Metric alerts in Azure Monitor, which provide a way to get notified when one of your multi-dimensional metrics crosses a threshold||Yes||No|
|XMLA endpoint, which allows diagnostic logging tool connections, including SQL Server Profiler||Yes||Yes|
|SQL Server Extended Events (xEvents), which is a light-weight tracing and performance monitoring system useful for diagnosing issues||Yes||No|
1 In public preview
3 Use VNet connectivity and Azure Private Link instead
When comparing Power BI Premium to AAS costs, be sure to consider factors beyond price per core. Power BI provides reduced cost of ownership and business value, and with many features that are only available to Power BI data models.
Also, assuming you already use Power BI in your organization, calculate costs based on the existing profile that combines AAS and Power BI. Compare the existing profile with the target profile on Power BI Premium. To determine the target profile, be sure to consider the following points:
- Region requirements.
- The largest AAS data model size in each region.
- The number of users in each region.
- The number of users required to develop and manage content.
- CPU consumption across AAS and Power BI Premium.
CPU consumption across AAS and Power BI Premium may vary significantly due to numerous factors. Factors can include the use of other workloads on the same capacities, refresh patterns, and query patterns. We recommended that you perform in-depth analysis to quantify comparative CPU consumption across AAS and Power BI Premium for migrated models.
To help determine the right type and number of licenses for your business requirements and circumstances, see this related article.
Many AAS customers already have Power BI reports that connect to AAS. So, migration to Power BI can represent an opportunity to consolidate BI items in Power BI Premium. Consolidation makes the larger sized Premium SKUs more economically viable and can help to provide higher levels of throughput and scalability.
The Premium Per User (PPU) license is a per-user license that provides a lower-cost price point for Premium. PPU licenses are typically purchased by small and medium-sized companies. They support all the Premium capabilities for data modeling listed earlier.
It's possible to incrementally upgrade Power BI Pro licenses to PPU licenses.
A Pro (or PPU) license is required to publish and manage Power BI content. Pro licenses are typically assigned to developers and administrators, not end users.
Development and test environments
AAS offers the D and B SKUs at lower cost with reduced service-level agreements and/or fewer features than the S SKUs. Some AAS customers use these SKUs for development and test environments. While there's no direct equivalent in Power BI, it might make sense to use PPU licenses for development and test environments. Such environments typically don't have large numbers of users because they're limited to developers and testers. Alternatively, consider using an A SKU in Azure for testing Premium capacity functionality.
For more information, see:
Power BI Premium delivers scalability, performance, and cost-of-ownership benefits not available in AAS.
Power BI Premium provides features that enable fast interactive analysis over big data. Such features include aggregations, composite models, and hybrid tables. Each feature offers a different way to optimally combine import and DirectQuery storage modes, effectively reducing memory use. AAS, on the other hand, doesn't support these capabilities; the entire data model uses either import or DirectQuery storage mode.
Power BI Premium limits memory per dataset, and not per capacity or server. Conversely, AAS requires all data models fit in memory on a single server. That requirement can compel customers with large data models to purchase larger SKU sizes.
Thanks to the distributed nature of the Premium architecture, more datasets can be refreshed in parallel. Performing concurrent refreshes on the same AAS server can lead to refresh errors due to exceeding server memory limits.
In Power BI Premium, CPU consumption during refresh is spread across 24-hour periods. Power BI Premium evaluates capacity throughput to provide resilience to temporal spikes in demand for compute resources. When necessary, it can delay refreshes until sufficient resources become available. This automatic behavior reduces the need for customers to perform detailed analysis and manage automation scripts to scale servers up or down. Premium customers should decide on the optimal SKU size for their overall CPU consumption requirements.
Another advantage of Power BI Premium is that it's able to dynamically balance the datasets depending on the load of the system. This automatic behavior ensures busy/active datasets get the necessary memory and CPU resources, while more idle datasets can be evicted or migrated to other nodes. Datasets are candidates for eviction when they're not used. They'll be loaded on-demand so that only the required data is loaded into memory without having to load the whole dataset. On the other hand, AAS requires all data models be fully loaded in memory always. This requirement means queries to AAS can rely on the data model being available, but – especially for Power BI capacities with a high number of data models when some of them are used infrequently – dynamic memory management can make more efficient use of memory.
Lastly, Power BI Premium is able to better utilize next-generation hardware rollouts to benefit from scalability and performance enhancements.
Considerations and limitations
There are considerations and limitations to factor into your planning before migrating to Power BI Premium.
AAS and SSAS use roles to manage data model access. There are two types of roles: the server role and database roles. The server role is a fixed role that grants administrator access to the Analysis Services server instance. Database roles, which are set by data modelers and administrators, control access to the database and data for non-administrator users.
Unlike AAS, in Power BI, you only use roles to enforce RLS or OLS. To grant permissions beyond RLS and OLS, use the Power BI security model (workspace roles and dataset permissions). For more information, see Dataset permissions.
For more information about Power BI model roles, see Dataset connectivity with the XMLA endpoint (Model roles).
When you migrate a data model from AAS to Power BI Premium, you must take the following points into consideration:
- Users who were granted Read permission on a model in AAS must be granted Build permission on the migrated Power BI dataset.
- Users who were granted the Administrator permission on a model in AAS must be granted Write permission on the migrated Power BI dataset.
Power BI Premium supports XMLA endpoint-enabled APIs for scripting, such as Tabular Model Scripting Language (TMSL), Tabular Object Model (TOM), and the PowerShell SqlServer module. These APIs have almost symmetric interfaces to AAS. For more information, see Dataset connectivity with the XMLA endpoint (Client applications and tools).
Compatibility with services for automation, including Azure Functions, Azure Automation, and Azure Logic Apps, is enabled in the same way.
Generally, scripts and processes that automate partition management and processing in AAS will work in Power BI Premium. Bear in mind that Power BI Premium datasets support the incremental refresh feature, which provides automated partition management for tables that frequently load new and updated data.
Like for AAS, you can use a service principal as an automation account for Power BI dataset management operations, such as refreshes. For more information, see Dataset connectivity with the XMLA endpoint (Service principals).
Like for AAS, applications can use a service principal to query a Power BI Premium per capacity or Power BI Embedded dataset by using the CustomData feature.
However, you can't assign a service principal to a model role in Power BI Premium. Instead, a service principal gains access by assignment to the workspace admin or member role.
You can't use the CustomData feature when querying Premium Per User (PPU) datasets because it would be in violation of the license terms and conditions.
Impersonation for testing
Impersonation techniques, including the EffectiveUserName and the Roles connection string properties, are supported by AAS and Power BI Premium. You typically use them when testing security roles.
Setting up network security in AAS requires enabling the firewall and configuring IP address ranges for only those computers accessing the server.
Power BI doesn't have a firewall feature. Instead, Power BI offers a superior network security model by using VNets and Private Links. For more information, see What is a virtual network (VNet)?.
Data sources and credentials
AAS defines credentials for each data source declared in the TOM tabular metadata. However, Power BI doesn't work that way. Because Power BI can share data sources credentials across multiple datasets, credentials are set in the Power BI service.
Any XMLA-based process that sets data source credentials must be replaced. For more information, see Dataset connectivity with the XMLA endpoint (Deploy model projects from Visual Studio).
Backup and restore
Backup and restore in AAS requires Azure Blob storage, while in Power BI Premium it requires an Azure Data Lake Storage Gen2 (ADLS Gen2) account. Apart from the storage account difference, backup and restore work the same way in both products.
For more information, see Backup and restore datasets with Power BI Premium.
On-premises data gateway
Both AAS and Power BI Premium use the same on-premises data gateway to connect to data sources. However, the setup steps are different.
For information on how to set up gateway data sources for Power BI Premium, see Add or remove a gateway data source.
Unlike AAS, Power BI Premium doesn't support server properties. Instead, you manage Premium capacity settings.
Unlike AAS, Power BI Premium doesn't support alias server names.
Dynamic management views (DMVs)
Some DMVs that work in AAS aren't accessible in Power BI Premium because they require Analysis Services server-admin permissions. Power BI has workspace roles, but there isn't a workspace role that grants the equivalent of Analysis Services server-admin permissions.
You can use the SqlServer PowerShell module AAS cmdlets to automate dataset management tasks, including refresh operations. For more information, see Analysis Services PowerShell Reference.
However, the Az.AnalysisServices module AAS cmdlets aren't supported for Power BI datasets. Instead, use the Microsoft Power BI Cmdlets for Windows PowerShell and PowerShell Core.
AAS integrates with Azure Monitor for diagnostic logging. The most common target for AAS logs is to Log Analytics workspaces.
Power BI Premium also supports logging to Log Analytics workspaces. Currently, the events sent to Log Analytics are mainly AS engine events. However, not all events supported for AAS are supported for Power BI. The Log Analytics schema for Power BI contains differences compared to AAS, which means existing queries on AAS may not work in Power BI.
Power BI offers another diagnostic logging capability that isn't offered in AAS. For more information, see Use the Premium metrics app.
SQL Server Extended Events (xEvents) are supported in AAS but not in Power BI Premium. For more information, see Monitor Analysis Services with SQL Server Extended Events.
Both AAS and Power BI support Azure AD B2B collaboration, which enables and governs sharing with external users. Notably, the User Principal Name (UPN) format required by AAS is different to Power BI.
To identify the user, Power BI utilizes a unique name claim in Azure AD while AAS uses an email claim. While there may be many instances where these two identifiers align, the unique name format is more stringent. If using dynamic RLS in Power BI, ensure that the value in the user identity table matches the account used to sign in to Power BI.
Azure Analysis Services scale-out is supported by Power BI Premium. For more information see Power BI Dataset Scale Out.
The Microsoft Azure Analysis Services to Microsoft Power BI Premium migration feature in Power BI migrates as AAS database to a dataset in Power BI Premium, Power BI Premium Per User, or Power BI Embedded workspace. For more information, see Migrate Azure Analysis Services to Power BI.
For more information about this article, check out the following resources:
- Migrate from Azure Analysis Services to Power BI Premium: Migration scenarios
- Migrate Azure Analysis Services to Power BI
- Questions? Try asking the Power BI community
- Suggestions? Contribute ideas to improve Power BI
Power BI partners are available to help your organization succeed with the migration process. To engage a Power BI partner, visit the Power BI partner portal.
Submit and view feedback for