Can we apply software engineering patterns like separation of concerns, single responsibility, and not repeating yourself to BI workflows?

I know a data analyst can do many functions from managing databases, connecting to data sources, creating data models, and design reporting/visualization. However, as you can see in job descriptions, they often look for a data analyst unicorn. Can one person do all these things? Probably they could, but they might not want to.

Narrowing down to separating data modeling from reporting design. This separation of responsibilities can be done with Azure Analysis Services.

Separation of Concerns

Can we separate responsibilities? For instance, one person creates the data models, and another one designs the reporting and visualization? If the data modeling is done in Power BI, how do you share data modeling management with others? How do you standardize the data modeling process for management, security, and scale? Is there a way to decouple data modeling out of Power BI?

Deploying a data model to Azure


In this blog post I am going to describe how to:

  1. Install Visual Studio in an Azure VM
  2. Create an Azure Analysis Services server
  3. Create a data model in Visual Studio
  4. Deploy the model to Azure Analysis Services
  5. Connect to the data model in Azure Analysis Services from Power BI.

Why use Azure Analysis Services

There is a scenario where multiple users create reports using Power BI to find insights from the same data source, and create KPIs that were already previously designed by other users. Leading to repetitive tasks, basically doing the work all over again from scratch, without a proper standardization of data governance.

The disadvantages of this workflow are:

  • Repetitive work among users
  • KPIs are not standardized
  • Data security is done in DAX during report design

Why use Azure Analysis Services:

  • Adds a layer between the data source and Power BI, separating data modeling from reporting/visualization
  • Move KPIs and data model out of Power BI. Move general KPIs out of Power BI. Users can define specific KPIs at reporting level in Power BI.
  • Standardize KPIs in one shared tabular data model
  • Data security is done in the Azure Analysis server

What is Azure Analysis Services

A managed platform for tabular data models in the cloud. Combine data sources, define metrics, and secure data. It doesn’t support multidimensional models.

Based on SQL Server Analysis Services, it supports in-memory and DirectQuery:

  • In-memory
    • Data is highly-compressed and cached
    • Fastest query response in large datasets