Building an Azure Analysis Services Model for Azure Blobs


What are Azure Blobs?

Microsoft Azure storage is one of the most extensible and reliable cloud storage available for enterprises. Microsoft Azure has become a popular choice among entrepreneurs and start up business owners, thanks to many Azure training centers available around the country. There are various types of storage options to use – Blobs, Tables, Queues and Azure files. All of them work on the same concept and technology but used with different scenarios.
Implementing DevOps with Microsoft AzureDevOps for Web Development
Implementing DevOps with Microsoft Azure
DevOps Bootcamp
Jenkins Essentials

AZURE BLOBS

For each storage account, there are containers similar to folders in file system, which contain blobs.

Blobs can be further of two types – block or page

Block Blob Page Blob
For streaming workloads For random workloads
Consists of sequence of blocks Consists of array of pages
Limit – 200 GB 1 TB per blob

Azure Analysis Services

Azure analysis services is a cloud based powerful analytical engine for enterprise level data modelling which is built on top of very successful MS SQL server analysis engine. It is one of the core of Microsoft overall BI capabilities over cloud. Some of the benefits are –

  • Supports DirectQuery, partitions, row-level security, bi-directional relationships and transactions.
  • Supports tabular models at 1200 and 1400 preview compatibility levels.
  • Built-in authentication with Azure Active Directory
  • You can use familiar tools with it –SQL server Data tools for Visual studio, SQL server management studio.
  • Database administrators can keep on managing their data and models over SSMS.
  • Task automation support with PowerShell and Azure Resource manager.

Azure analysis services support data connection from various online and on premise sources:

Build an Azure services model on top of Azure blob storage

There are situations where we need to carry on analysis, high performance query execution and real time reporting on large volumes of unstructured data or terabytes of data files. This transformation was never feasible before, until Azure analysis models started building over blob storage. This is a scalable solution to use blobs with Azure data factory to coordinate the data movement to SQL data warehouse on the cloud. It involves data extraction, transformation and loading with high reliability, availability and efficiency.

Major steps involved in the process are –

Establishing Account

With Azure account in place, you need to go to Resource manager and create a Blob storage account and Azure analysis server. For the server you need to do the following –

  1. Logon to Azure portal
  2. Click New – > Intelligence + analytics – > Analysis Services
  3. Fill the mandatory fields on the pop up window like Server name, subscription and resource group, location and pricing.
  4. Click Create
  5. Navigate to More services – > Analysis services

Here you go! You are ready to create and deploy models for analysis.

Getting Data over Blob storage

You can upload data files or unstructured data to your blob storage by using a command line utility – AZCopy

C:\Program Files (x86)\Microsoft SDKs\Azure > AzCopy /Source /Destinaton

Here destination is the URL of your blob storage like – https://&lt;account>.blob.core.windows.net/<container>/<blobname>

Once the utility connects and transfers data, it provides the number of files transferred successfully along with the time taken.

Creating tabular models

Once the data is transferred to the blob storage, you can start creating tabular models in 1400 compatibility mode using blob connector. For now, all the files are stored inside one table as lists as connector cannot automatically recognize them as separate tables.

There are numerous options to separate out tables. One faster way is to bifurcate programmatically using Tabular object model or TOM. Once the query is generated for each table, which needs to be renamed as of table name for continuous data flow. The goal is to bring each data file as a separate table. File content when opened for a table, automatically creates columns as per the delimiters provided in the input files. Sometimes column header becomes tedious task for large number of tables, for which we can use first column as column name before import.

Adding queries

With tabular models in place, you are now in a position to drill down the data in various tables to write queries and generate useful models for further analysis.

Conclusion

There are numerous options to define the process of storing huge volumes of data into tabular form and generating models by using Azure analysis server over Azure blobs. The seamless data transfer and conversion gives powerful ability and BI capabilities to large enterprises to interpret data from heterogeneous sources and forms and bring it into one common platform for getting insights and predictive reporting with tools available in analysis engine. The level of scalability and extensibility provided by Azure analysis over unstructured data with combining blob storage in beyond comparison.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s