Analytics in the cloud - Azure analysis services

by Daniel Holt on Sep 13, 2018 12:00:00 AM

Azure Analysis Services is a fully managed platform as a service (PaaS) that provides enterprise-grade data models in the cloud. Advanced mashups and modeling features are used to combine data from multiple data sources, define metrics and secure your data in a single, trusted tabular semantic data model. The data model provides an easier and faster way for users to browse massive amounts of data for ad-hoc data analysis. A trusted semantic model adds value and business logic to data. This makes advanced analytics easily available to end users to leverage their massive data stores and glean business value from data.

Features

Azure Analysis Services offers a full feature set with some caveats. The features available will depend on the tier chosen (more on tiers later). At the time of writing, Multidimensional and PowerPivot for Sharepoint models are not supported Tabular models are relational modeling constructs (model, tables, columns) articulated in tabular metadata object definitions in Tabular Model Scripting Language (TMSL) and Tabular Object Model (TOM) code. Partitions, perspectives, row-level security, bi-directional relationships and translations are all supported. Both in-memory and DirectQuery modes are supported.

In-memory mode

In-memory mode is the default tabular model and supports multiple data sources. Because the model data is highly compressed and cached in-memory, this mode provides the fastest query response over large amounts of data. It also provides the greatest flexibility for complex data sets and queries. Partitioning enables incremental loads, increases parallelization and reduces memory consumption. Other advanced data modeling features such as calculated tables and all DAX functions are supported. In-memory models must be refreshed (processed) to update cached data from data sources. With Azure service principle support, unattended refresh operations using PowerShell, TOM, TMSL and REST offer flexibility in making sure the model data is always up to date.

DirectQuery mode

DirectQuery mode leverages the backend relational database for storage and query execution. Extremely large data sets in single SQL Server, SQL Server Data Warehouse, Azure SQL Database, Azure SQL Data Warehouse, Oracle and Teradata data sources are supported. Backend data sets can exceed available server resource memory. Complex data model refresh scenarios aren't needed. There are also some restrictions, such as limited datasource types, DAX formula limitations and some advanced data modeling features aren't supported. Servers can be scaled up/down vertically if more resources are needed. Both modes (depending on tier) offer the ability to have up to seven query replicas for scalability. With scale-out, client queries are distributed among multiple query replicas in a query pool. Query replicas have synchronized copies of your tabular models. By spreading the query workload, response times during high query workloads can be reduced. Model processing operations can be separated from the query pool, ensuring client queries are not adversely affected by processing operations.

Integration

Azure Analysis Services has built-in integrations to a number of on-premise and cloud services including:

Azure SQLDB
Azure SQLDW
Azure Data Factory
Hadoop
Azure Data Lake
Azure Data Gateway

A familiar toolset can also be used to develop and administer Azure Analysis services including:

Visual Studio - SQL Server Data Tools
Powershell
SQL Server Management Studio
Object Model and scripting (TOM exposed via JSON through TMSL)

As for the end user and presentation layer tooling, Azure Analysis services supports the latest and most popular software including:

PowerBI
Microsoft Excel
Tableau

At the time of writing, SSRS is not supported, although there is a user voice request open and can be voted on here.

Security

At the forefront of all cloud services, the question of security comes up. Azure Analysis services offers a full set of security features as described below.

Firewall

Azure Analysis Services Firewall blocks all client connections other than those IP addresses specified in rules. This puts your company in control of who has access to what service. Rules are configured by specifying allowed IP addresses by individual client IPs or by CIDR ranges. The Power BI (service) connections can also be allowed or blocked. There is a handy switch in the portal to enable or disable this. Firewall rules can be configured in the Azure portal or via PowerShell.

Authentication

Azure Active Directory (AAD) is used to handle user authentication. Users log in using an organization identity and role-based access is used to give users access to the database. Users have to be part of the AAD for the subscription the server is located in.

Data security and encryption

Azure Analysis Services uses Azure Blob storage to persist storage and metadata for Analysis Services databases. Data files within Blob storage are encrypted using Azure Blob Server Side Encryption (SSE). When using Direct Query mode, only metadata is stored. The actual data is accessed through encrypted protocol from the data source at query time. Secure access to data sources on-premises in your organization is possible and is achieved by installing and configuring an on-premises data gateway. Gateways provide access to data for both DirectQuery and in-memory modes.

Roles

Analysis Services uses role-based authorization that grants access to the server and model database operations, objects and data. Users access a server or database with their Azure AD user who is assigned a predefined role. This allows granular permissions to be applied at a role level. The server administrator role is at the server resource level. By default, the account used when creating a server is automatically included in the server admins role. Additional user and group accounts are added by using the portal, SSMS, or PowerShell. Non-administrative end users who query data are granted access through database roles. A database role is created as a separate object in the database, and applies only to the database in which that role is created. Database roles are defined by (database) Administrator, Read and Read and Process permissions. User and group accounts are added by using SSMS or PowerShell.

Row and object level security

Tabular model supports row level security and is implemented by using DAX expressions that define the rows a user can query. Row filters using DAX expressions are defined for the Read and the Read and Process permissions. Tabular Model from SQL Server 2017 (compatibility level 1400) support object-level security and this can be configured in the Model.bim file using TMSL or TOM.

Monitoring and diagnostics

Azure Analysis Services is well integrated with the major monitoring and diagnostics features of Azure. This includes Azure Metrics, Event Hubs, Azure Storage and Log Analytics With the combination of these technologies a complete and extensive monitoring, diagnostics and alerting platform can be built at a very granular or a high level.

Pricing and tiers

Total cost depends on a number of factors. Including your chosen region, tier, number of query replicas and pause/resume schedules. Use the Azure Analysis Services pricing calculator to determine typical pricing for your region. This tool calculates pricing for a single-server instance for a single region. Keep in mind that query replicas are billed at the same rate as the server. There are three tiers when looking at Azure Analysis services:

Developer

This tier is recommended for evaluation, development and test scenarios. A single plan includes the same functionality of the standard tier, but is limited in processing power, QPUs and memory size. Query replica scale out isn't available for this tier. This tier doesn't offer an SLA.

Plan	QPUs	Memory (GB)
D1	20	3

Basic

This tier is recommended for production solutions with smaller tabular models, limited user concurrency and simple data refresh requirements. Query replica scale out is not available for this tier. Perspectives, multiple partitions and DirectQuery tabular model features are not supported in this tier.

Plan	QPUs	Memory (GB)
B1	40	10
B2	80	20

Standard

This tier is for mission-critical production applications that require elastic user-concurrency and have rapidly growing data models. It supports advanced data refresh for near real-time data model updates and supports all tabular modeling features.

Plan	QPUs	Memory (GB)
S1	40	10
S2	100	25
S3	200	50
S4	400	100
S8*	320	200
S9*	640	400

Conclusion

I hope this blog has helped you get an understanding of the features of Azure Analysis Services, If you need help in setting this up or you have a workload you are looking to migrate to Azure Analysis Services, then feel free to get in touch for some help.

Topics: Oracle Technical Track ASM

No Comments Yet

Let us know what you think

Share this