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.