Microsofts Azure Analysis Services for Advanced BI Activity

Before trying to understand Azure, let’s get a gist of Semantic Model. Semantic Model is quintessential for Self Service BI activity. Self Service BI allows business users to analyse data without IT hassles of programming, joins, unfriendly column names and uncalculated fields. Semantic Model renders user-friendly values like YOY Sales, % Change over Prior Period, Data Security (Row Level Security) and much more.

All folks using SQL Server Analysis Services have to set up heavy infrastructure to cater to ad-hoc reporting and data analytics in real time. Azure Analysis Services help in scaling up and scaling down based on the volume of data. This data load varies through weekdays, weekends, month ends and quarter ends. Azure Analysis Services also help in completing entire MS BI stack on cloud along with Power BI, SQL Server and Azure Datawarehouse.

What is Azure Analysis Services?

Azure Analysis services (AAS) is the latest offering from Microsoft in Microsoft Azure Services. Built using Microsoft SQL Server Analysis Services on the cloud platform, enterprise users can access it from anywhere. Azure Analysis Services supports Tabular model at the 1200 compatibility level and Tabular 1400 is in the preview stage. Some of the key features that are currently supported are Direct Query, Partitions, Row Level Security, Bi-Directional relationships and translations.

Are my current tools obsolete?

Absolutely not. SQL Server Data Tools (SSDT) for Visual Studio (2017) and SQL Server Management Studio (SSMS) (2016) both can still be used for Development and Deployment.

Connectivity with Data sources

Azure Analysis Services can connect to data sources both On-Premise and On-Cloud. OnPremises data sources include SQL Server, Oracle or Flat Files and On-Cloud Data sources include Azure SQL Database, Azure SQL Data Warehouse.

Models can be cached in a highly optimised in-memory engine to generate fast responses for interactive tools like Power BI, SQL Server Reporting Services, Tableau and QlikView.

Azure Analysis services is a fully managed Platform as a Service (PaaS). There are 3 main types of Service Tiers – Developer (D1), Basic (B1 & B2) and Standard (S1, S2 & S4).

Features wise differentiation can be studied in the following table:

Feature Developer Basic Standard
Perspectives Yes Yes
Multiple Partitions Yes Yes
DirectQuery Storage mode Yes Yes
Translations Yes Yes Yes
DAX Calculations Yes Yes Yes
Row Level Security Yes Yes Yes
In-Mem Storage Yes Yes Yes
Back-up and restore Yes Yes Yes

Tier Details as per given below –

Instance QPUS Memory
(GB)
Developer Tier D1 20 3
Basic Tier B1 40 10
B2 80 20
Standard Tier S0 40 10
S1 100 25
S2 200 50
S4 400 100

 

Security in the Azure Analysis Services:

How is User Authentication handled?

User authentication is handled by Azure Active Directory (AAD). Users can use Organisation Account Identity with Access to the database. These User identities must match the default entries in the Azure Active Directory where Azure Analysis Services server resides.

What assures Data Security?

Azure Analysis Services utilises Azure Blob Storage wherein the Data files are encrypted using Azure Blob Server Side Encryption. Currently, Azure Analysis Services supports only the Tabular Semantic Model.

Who can consume Azure Analytical Services Cube?

Any BI tool which can build MDX and DAX queries should be able to read data from Azure Analysis Services. Some of the examples are Power BI Service, Power BI Desktop, Excel and SQL Server Reporting Services.

Different Compatibility Levels for different versions of SQL servers are given in the table below:

Compatibility Level Server Version
1400 SQL Server 2017
1200 Azure Analysis Services, SQL Server 2017, SQL Server 2016
1103 SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012 SP1
1100 SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012 SP1, SQL Server 2012

One needs to carefully select Compatibility Level as upgrading it later is not possible.

*The compatibility level refers to release-specific behaviors in the Analysis Services engine. For example, DirectQuery and tabular object metadata have different implementations depending on the compatibility level.