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.

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
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 made easy on the 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...