An introduction to SSAS Tabular models
Today I will cover how to get a SSAS tabular up and running. SSAS is an OLAP mining tool that can help an enterprise track and manage KPI data and also assist in the ware housing process.
The data I will be working on today is from adventure works 2017, the .bak file can be found here
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak
Restore this bak file to a your server instance.
First thing is first, make sure you have SQL server data tools installed. I was using SQL server 2017, so I needed to use a seperate standalone installer, but you can will know you have installed correctly if when you open visual studio 2017 and click new project you see the business intelligence and analysis service option as project types.
Once you have SSDT installed, click new project and under analysis services click Tabular for the project type, give your project a descriptive name.
Make a note of the instance that you have your datawarehouse on we will need that information during the setup of our tabular model.
Select a the Workspace Server as your instance.
Once the project loads right click on Data Sources -> Import from Data Source, choose SQL Server
This will prompt the Table Import Wizard, provide a SQL Server Authed login to this diaologue and select the database.
Under impersonation information, select service account
Now choose the tables that will be imported, for my model I choose the following :
The import will run and bring all the table data into the SSAS tabular model
Notice that if you browse the diagram view all the relationships are mapped out
Finally lets connect to the SSAS model and run a simple query, you have the options of MDX and DAX to query your model.
I will be using a short DAX query for my example.