Data Modeling in Azure Simplified 101

on Data Integration, Data Modeling, Data Modelling, Data Visualization, Data Warehouse, ETL, ETL Tutorials, Microsoft, Microsoft Azure • April 25th, 2022 • Write for Hevo

Data Modeling in Azure - Featured Image

Data Modeling in Azure is a new preview service in Microsoft Azure where semantic Data Models can be hosted. Users in your organization can then connect to your Data Models via tools such as Excel, Power BI, and others to generate reports and perform ad-hoc data analysis.

Consider a scenario in which you have data stored in a large database to understand the value of Azure Analysis Services. You want to make that data available to your business users or customers so they can do their own analysis and report creation.

Once your data has been properly modeled for consumption by your users, Azure Analysis Services provides additional features to improve their querying experience. The most important of these is the option to store the data in an in-memory columnar cache, which can speed up queries to sub-second performance. This not only improves the query experience but also reduces the query load on your underlying database by hitting the cache.

Are you willing to give it a shot? Follow the instructions in the rest of this blog post to see how simple Data Modeling in Azure is.

Table of Contents

What is Data Modeling?

Data Modeling in Azure - Data Modeling Image
Image Source

The practice of evaluating data objects and their relationships to other things is known as Data Modeling. It’s used to look into the data needs of business processes. The Data Models are designed to be used in a database to store data. The Data Model is concerned with what data is required and how data is organized, rather than with what actions must be taken.

A Data Model is analogous to an architect’s blueprint for a building. It is a method of documenting complex software system designs in the form of a simple graphical representation. To describe how the data will flow, the diagram will be created using text and symbols. It’s often referred to as a blueprint for developing new software or reengineering existing applications.

Key Features of Data Modeling

Here are some Data Modeling characteristics:

  • Cost-cutting Measures: Data Models allow you to develop apps at a lower cost. Data modeling typically consumes less than 10% of a project’s budget and has the potential to reduce the 70% of a project’s budget that is typically allocated to programming.
  • Broader Perspectives: A Data Model serves as a nexus for determining scope. It provides something practical to help corporate sponsors and developers agree on what is and is not included with the product.
  • The Enhanced Documentation: Models serve as a foundation for long-term maintenance by documenting fundamental concepts and language. Even if your staff changes, the documentation will come in handy.
  • Risk Management: A Data Model is useful for quantifying software complexity and gaining insight into project risk and development effort.
  • A Good Starting Point for Data Mining: A model’s documentation serves as a starting point for analytical data mining. Data from a company’s daily operations can be loaded into a dedicated database known as a “Data Warehouse.”
  • Shortened Time to Market: You can also build software faster if errors are detected early on. Some processes can also be automated using a Data Model.

Replicate Data in Minutes Using Hevo’s No-Code Data Pipeline

Hevo Data, a Fully-managed Data Pipeline platform, can help you automate, simplify & enrich your data replication process in a few clicks. With Hevo’s wide variety of connectors and blazing-fast Data Pipelines, you can extract & load data from 100+ Data Sources straight into your Data Warehouse or any Databases. To further streamline and prepare your data for analysis, you can process and enrich raw granular data using Hevo’s robust & built-in Transformation Layer without writing a single line of code!

Get Started with Hevo for Free

Hevo is the fastest, easiest, and most reliable data replication platform that will save your engineering bandwidth and time multifold. Try our 14-day full access free trial today to experience an entirely automated hassle-free Data Replication!

What is Microsoft Azure?

Data Modeling in Azure - Azure logo
Image Source

Microsoft Azure is a well-known Microsoft Service that provides public cloud services. You can use Azure in one of three ways: Software as a Service (SaaS), Platform as a Service (PaaS), or Infrastructure as a Service (IaaS). Microsoft Azure enables reliable storage and computing power. Furthermore, approximately 80% of Fortune 500 companies rely on Microsoft Azure services to meet their cloud computing needs. When you use its services, you can be certain that your data will not be compromised by unauthorized access and that you will have enough backups to survive server crashes.

Developers can use any of the three models to build new applications or host existing ones in the public cloud. You can also use Microsoft Azure services to create Cloud-based Virtual Machines (VMs) and Databases.

Key Features of Microsoft Azure

You can improve your Data Modeling in Azure by utilizing the following Microsoft Azure features:

  • Analytics Assistance: Microsoft Azure includes analytics and data reporting tools that can help you gain deeper insights from business data. These tools can help businesses find new leads, improve customer service, and make strategic decisions.
  • Hybrid Capable: Microsoft Azure provides on-premises data centers as well as public cloud services. Businesses can then choose between the two options or implement a hybrid model.
  • Storage System that is Efficient: Microsoft Azure has a plethora of delivery points and data centers. As a result, it can provide faster data delivery and improve your user experience for Data Modeling in Azure.

What is Azure Analysis Model?

Data Modeling in Azure - Azure Analysis Model
Image Source

Azure Analysis Services is a fully managed platform as a service (PaaS) that offers cloud-based enterprise-grade Data Models. To combine data from multiple data sources, define metrics, and secure your data in a single, trusted tabular semantic Data Model, use advanced mashup and modeling features. The Data Model makes it easier and faster for users to conduct ad hoc data analysis with tools such as Power BI and Excel.

Azure Analysis Services integrates with a variety of Azure services, allowing you to create sophisticated analytics solutions. The integration with Azure Active Directory allows for secure, role-based access to your critical data. Include an activity that loads data into the model to integrate with Azure Data Factory pipelines. Azure Automation and Azure Functions can be used to orchestrate models in a lightweight manner using custom code.

Data Modeling in Azure

Before we can get started with Data Modeling in Azure, we must first create an account and a server instance in that account. It is also assumed that the user has the necessary administrative privileges to connect to and operate the server using various tools.

Check that the necessary firewall settings are in place to allow incoming connections to the server instance. Assuming this is already in place, we can move on to the actual exercise of Data Modeling in Azure.

Step 1: Create an Analysis Services Server

Select Data Analytics > Analysis Services from the drop-down menu

To get started with Data Modeling in Azure, Choose a server name, a Resource Group, and a pricing Tier. It uses the lowest tier subscription, D1, or developers preview, for development purposes. Higher versions can be used depending on the volume of data.

Data Modeling in Azure - Analysis Server
Image Source

What makes Hevo’s Data Modeling Capabilities Unique

The ideology behind creating a manual Data Pipeline is one that requires a lot of time, effort, and understanding. Automated tools like Hevo can automate this process without writing a single piece of code. Its integration with a wide range of data sources such as SQL Server, MongoDB, DynamoDB, and much more help to map your data accurately and generate valuable insights from them.

Check out what makes Hevo amazing:

  • Integrations: Hevo’s fault-tolerant Data Pipeline offers you a secure option to unify data from 100+ data sources (including 40+ free sources) and store it in SQL Server or any other Data Warehouse of your choice. This way you can focus more on your key business activities and let Hevo take full charge of the Data Transfer process.
  • High-Speed Data Loading: Loading compressed data into SQL Server is slower than loading uncompressed data. Hevo can decompress your data before feeding it to Database. 
  • Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
  • Scalability: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Try our 14-day free trial & Set up Data Modeling in Azure quickly

TRY OUR 14 DAY FREE TRIAL

Step 2: Make a Dummy Data Source

On our server, we’ve installed a sample Adventure Works tabular model database. The sample model is a finished version of the Adventure Works Internet Sales (1200) Data Model sample.

Click New model in the server Overview.

In the New Model > Choose a data source, double-check that Sample data is selected, and then click Add.

Data Modeling in Azure - Data Source
Image Source

Check that the AdventureWorks sample model has been added to Overview.

You can also select a dataset from a SQL Data Warehouse/Azure SQLDB or a Power BI file. Using the Azure Analysis web designer, you can easily add multiple datasets from various external sources and create models.

Data Modeling in Azure - Data Source selection
Image Source

Step 3: Make a Data Model

Once the model has been loaded, navigate through the Model and Query tabs. The data set has already been loaded with a table schema.

To get a model view of all the tables, click Arrange All. According to your needs, you can also add/delete/change relationships in any of the tables.

Data Modeling in Azure -
Image Source
Data Modeling in Azure - Data Model
Image Source

Select the columns on the right by clicking the Query tab. In the customer table, we want to calculate the Yearly Income, Total Children, and Number of Children at Home. Once the columns have been checked, click the Run button to query the results.

Then, to get the DAX query, click the Show DAX button. Make a copy of the query so we can use it in the model.

Data Modeling in Azure - DAX Query
Image Source

Step 4: Install Power BI and Connect to the AAS

Click on the Customer > Measure > Add Measure table.

For Data Modeling in Azure, We’ve added three measures and pasted the DAX query from the previous section here. When finished, click Save Model to save the changes to the Azure Analytics Server. The data from this newly created model will be visualized in the following section.

Data Modeling in Azure - Measure Table
Image Source

To see the model we created in the designer, you must first install the PowerBI desktop. You can get it by clicking here. After installation, sign in using your organization’s or personal credentials and continue with Data Modeling in Azure.

You can also use other reporting tools such as Tableau, D3.js, High Charts, and so on.
Select Get Data > Azure > Azure Analysis Services Database from the drop-down menu.

Data Modeling in Azure - Getting data from Azure
Image Source
Data Modeling in Azure - DAX Query AAS Server name
Image Source

You will be prompted to enter the AAS server name. Copy and paste the server name from the Azure portal, then choose the option to connect live.

Congratulations! You have successfully linked the model in the AAS with PowerBI. To view the data, you simply need to drag and drop the columns. The DAX queries and logically implemented in the model run behind the scenes as soon as you drag any column to the workspace.

Data Modeling in Azure - Example
Image Source

With PowerBI, the data model can be easily shared with any Business Unit without exposing the internal structure of the data model. The client or business can now easily analyze data without having to perform any internal queries or additional operations within the model.

This architecture is highly scalable and useful because it removes the dependencies associated with enterprise-grade application software such as Visual Studio/SSMS/SSRS/other Microsoft tools.

Benefits of Data Modeling in Azure

Here are some benefits of Data Modeling in Microsoft Azure:

  • Maximum UI Adaptability: Integrating Data Modeling in Azure into an Excel worksheet or a Power BI report is a breeze because both applications provide out-of-the-box connector options. This means that creating interactive charts and visualizations is quick and easy, thanks to drag-and-drop report creation in a familiar Office toolset.
  • Making money from your Data Insights: One of the reasons to expose your data to the outside world is to monetize access to it. If your data, or the insights you provide on top of it, are assets in and of themselves, then custom applications and APIs offering the same levels of analysis enable the creation of new business models based on the Data Economy.
  • Updated Model Schema: While Data Modeling in Azure appears to be a read-only data model at first glance, there are reasons why you may need to update parts of the model at run-time. Triggering the data ingestion process to refresh the data is supported via a variety of mechanisms, but it is also possible to programmatically update the underlying model schema.
  • Validation of Algorithms: Finally, writing DAX expressions to create calculated columns and measures in your model is the same as developing any other type of code, and the same quality gates and development processes should be used. Most people don’t consider Data Modeling in Azure for unit testing, but it is entirely possible to use the client SDKs and your preferred testing framework to execute queries.

Conclusion

In this article, we began with an existing Azure Analysis Services server setup and learned how Data Modeling in Azure is carried out. We learned how to create ready-to-use Excel, Power BI Desktop, and Visual Studio files for data and schema exploration after the model was deployed.

To become more efficient in managing your databases, it is preferable to integrate them with a solution that can perform Data Integration and Management procedures for you without much difficulty, which is where Hevo Data, a Cloud-based ETL Tool, comes in.

To become more efficient in handling your Databases, it is preferable to integrate them with a solution that can carry out Data Integration and Management procedures for you without much ado and that is where Hevo Data, a Cloud-based ETL Tool, comes in. Hevo Data supports 100+ Data Sources and helps you transfer your data from these sources to Data Warehouses in a matter of minutes, all without writing any code!

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of Data Modeling in Azure in the comments section below!

No Code Data Pipeline For Your Data Warehouse