Finding a great enterprise modeling tool can be a tough decision, especially when many back-end databases and data warehouses are moving to cloud storage. Azure Analysis Services (Tabular Model 1400) is a great tool that allows users to build tabular models from a wide variety of Data Sources. Some of the advantages of Azure Analysis Services that many on premise data modeling tools miss out on are fast data refresh, large data workloads, cloud storage.
In this article we will focus more on importing data from multiple data sources and see some of the capabilities Analysis Services provides. One of the best capabilities of using Azure Analysis Services is pulling data from many different sources and being able to integrate your data sources at the modeling level. There are many sources of data we can choose from including flat files, cloud databases, on-premise databases, other modeling tools, and CRM’s.
Using the default import mode, tables are processed (which can be automated) and stored in Azure Analysis Services until deployment is ready. Before deployment, we can build relationships between tables and model our data. Below is an example of how an Azure SQL Data Warehouse is connected to both an SAP HANA Calculation View and an Azure SQL Database table. We are only able to get data from SAP HANA Views and not their source tables.
Looking at the model diagram that Analysis Services provides for us, there is a one-to-many relationship between the HANA Product table, pulled from the calculation view in SAP HANA, and the Azure SQL DW Fact Sales table, pulled from the Azure Data Warehouse. Azure Analysis Services has built in functionality of determining cardinality between two tables, once we initially draw that relationship. Drawing relationships between tables with a predefined relationship from the same data source will not be necessary, since Azure Analysis Services will automatically create it.
It should be noted that that the Analysis Services database is all stored within the Azure platform, rather than with traditional SSAS models being stored on-premise. One should also keep in mind that Azure Analysis only supports tabular models and won’t yet be able to build multidimensional models in Microsoft’s cloud modeling tool. However, the use of the cloud leverages Azure Analysis Services with the ability to scale up, down and out. With the pay as you go feature, you don’t have to pay for resources not being used and you also have the option of turning off services for a period with much less use. Azure Analysis Services also comes to a great advantage if data sources are already on the cloud and a company wants to keep up with Microsoft’s updates.
Now, since we have the tables imported into Azure Analysis Services and established the relationships, we are ready to model our data and later push it to Excel or our visualization tool of choice.
