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:
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:
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.