How-To create an ultra-fast, concurrent DataMart for Self-service reports on Azure DWH and Azure DB


How-To create an ultra-fast, concurrent DataMart for Self-service reports on Azure DWH and Azure DB


With Analysis Services, you can mashup and combine data from multiple data sources, define metrics, and secure your data in a single, trusted semantic data model. The data model provides an easier and faster way for your users to browse massive amounts of data with client applications like Power BI, Excel, Reporting Services, third-party, and even custom apps.

Another really nice feature is the ability to create a model starting from a Power BI Desktop (*.pbix) file. Once you have a server created, you can create a tabular model directly in Azure portal. With the new Web designer feature, you can connect to an Azure SQL Database, Azure SQL Data Warehouse data source, or import a Power BI Desktop.pbix file. Relationships between tables are created automatically, and you can create measures or edit the model.bim file in .json format right from your browser.

What do you need:

  1. An Azure Analysis Services server at the Standard or Developer tier. New models created by using the Web designer are DirectQuery, supported only by these tiers.
  2. An Azure SQL Database, Azure SQL Data Warehouse, or Power BI Desktop (.pbix) file as a datasource. New models created from Power BI Desktop files support Azure SQL Database and Azure SQL Data Warehouse.
  3. A SQL Server account and password for connecting to Azure SQL Database or Azure SQL Data Warehouse data sources.
  4. Once you have all that, 1 minute of your time

To create a new tabular model

In your Analysis Server, create a new model by going to the server Overview > Web designer, click Open.

In Web designer > Models, click + Add.

In New model, type a model name, and then select a data source.

In Connect, enter the connection properties. Username and password must be an Azure SQL (DWH) SQL account.

Once you entered these, and you validated the connection by testing it, you’ll see the Tables and views tab, select the tables to include in your model, and then click Create. All obvious relationships are created automatically between tables if column names are the same with a key pair. You can also alter these relations to become a real inner join or change the relation properties

Your new model appears in your browser. From here, you can:

  • Query model data by dragging fields to the query designer and adding filters.
  • Create new measures in tables.
  • Edit model metadata by using the json editor.
  • Open the model in Visual Studio (SSDT), Power BI Desktop, or Excel.
  • Edit the relations manually in the Edit Relationships tab.

You can even edit these relationships in the script file:

Once the relationships have been made, you can start to write or design your query or open it in SSMS, SSDT, Excel and Power BI.

Per default this DataMart uses DirectQuery mode which means it doesn’t use much memory and you can size your Analysis services quite low, so that this feature isn’t to expensive.

Also Azure Analysis Services can be paused, lowering costs even further.

Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...