Microsoft’s 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.

Image source: https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-overview#use-the-tools-you-already-know

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:

FeatureDeveloperBasicStandard
PerspectivesYesYes
Multiple PartitionsYesYes
DirectQuery Storage modeYesYes
TranslationsYesYesYes
DAX CalculationsYesYesYes
Row Level SecurityYesYesYes
In-Mem StorageYesYesYes
Back-up and restoreYesYesYes

Tier Details as per given below –

InstanceQPUSMemory
(GB)
Developer TierD1203
Basic TierB14010
B28020
Standard TierS04010
S110025
S220050
S4400100

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 LevelServer Version
1400SQL Server 2017
1200Azure Analysis Services, SQL Server 2017, SQL Server 2016
1103SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012 SP1
1100SQL 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 behaviours in the Analysis Services engine. For example, DirectQuery and tabular object metadata have different implementations depending on the compatibility level.

Leave a Reply