Migrate from Azure Analysis Services to Power BI Premium - Power BI

• Read original article here

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.

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 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:

Answers to many of these questions are described in this article.

Consolidation of artifacts 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 artifacts that have become popular yet are resulting in a management burden for the business. IT can take over such artifacts. 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.

Perhaps what's generating the most interest for Power BI Premium as the choice for data models is the introduction of Power BI Premium Gen2 (Premium Gen2). Thanks to its distributed architecture, the new generation architecture 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 Premium Gen2 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 Premium Gen2. The table focuses on - but isn't limited to - Power BI dataset-related capabilities.

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:

Many AAS customers already have Power BI reports that connect to AAS. So, migration to Power BI can represent an opportunity to consolidate BI artifacts in Power BI Premium. Consolidation makes the larger sized Premium Gen2 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 Gen2. PPU licenses are typically purchased by small and medium-sized companies. They support all the Premium capabilities for data modeling listed earlier.

Power BI Premium provides unlimited distribution of Power BI content to end users without requiring Power BI Pro licenses.

The cost of Power BI Premium is split equally between frontend and backend v-cores. Frontend v-cores are mostly responsible for the web service, reporting, and user experiences. Power BI datasets consume resources from backend v-cores. To get the best value from Power BI Premium, customers should strive to strike a balance between frontend and backend usage. For more information, see What is Power BI Premium? (Capacity nodes).

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.

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.

For more information, see:

Premium Gen2 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.

Premium Gen2 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 Gen2 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 Premium Gen2, CPU consumption during refresh is spread across 24-hour periods. Premium Gen2 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 Gen2 customers should decide on the optimal SKU size for their overall CPU consumption requirements.

Another advantage of Premium Gen2 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, Premium Gen2 is able to better utilize next-generation hardware rollouts to benefit from scalability and performance enhancements.

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:

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.

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)?.

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 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.

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.

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 Gen2 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.

For more information about this article, check out the following resources:

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.