by In this tutorial, you will learn the basics of SQL Server 2019 Analytics Services. We would cover setting up SSAS on our local computer and create a Tabular Model.
The step by step video for this tutorial will be updated in The Tech Pro Channel.
In this tutorial we’ll cover the following;
- What is SSAS?
- Some Prerequisites
- SSAS Setup
- Install SQL Server Data Tools (VS)
- Setup Analysis Services Tabular Project
- Create a New Tabular Project
1. What is SSAS?
SSAS stands for SQL Server Analytics Services and is simply a data analysis tool by Microsoft. Using SSAS, you can analyze data coming from various sources and produce some summaries of useful information.
Using SSAS you can create two types of models;
Tabular Model – this is just a kind of database. Or say a database that is a bit more advanced that normal relational database. A tabular model are in-memory databases that uses table and other relational components such as relationships, joins, rows and column etc
Multidimensional Model – this is a data model that support very large amount of data. Similar to but not exactly big data. Multimensional data model in addition to tabular data supports other things like dimensions, measures perspectives, data sources and perspectives. We’ll cover these in subsequent tutorials.
Now let’s get into the practical!
2. Some Prerequisites
SQL Server installed as well as SQL Server Management Studio.
Add the AdventureWorks2019 database to the server. You can get it from here.
We’ll do further setups as we go. So simply follow the procedure below.
3. Setup SSAS
We would follow the steps below to setup SSAS
Step 1 – Run the SQL Server setup. You will come to the screen below:
Step 2 – Click on the first link ‘New SQL Server stand-alone installation or add features to an existing installation’.
Step 3 – Follow the Wizard steps. And when it comes to the Feature Selection page, select Analysis Services as shown below:
Step 4 – When you get to the Analysis Services Configuration, choose Tabular Mode as shown below
Step 5 – Follow the steps and complete the installation.
4. Install SQL Server Data Tools
You need to install SQL Server Data Tools (SSDT). According to Microsoft, this tool has been integrated into Visual Studio. So following the steps below, you’ll be taken to where you’ll download and install Visual Studio 2019.
Step 1 – Follow the same process but select SQL Server Data Tools. See figure below:
Step 2 – Click on Install SQL Server Data Tools. You’ll be taken to the download page for Visual Studio. Install it.
5. Setup Analysis Services Tabular Project
To be able to complete the project we, would need some data. And this data would come from the AdventureWorks Data Warehouse file. So follow the steps below:
Step 1 – Download the AdventureWorksDW file from the above link
Step 2 – Open Management Studio and restore the Adventureworks database.
Step 3 – Open Visual Studio. For the first time, you will choose Continue Without Code as shown below
Step 4 – Click on Extensions on the menu on the Extensions Manager. Enter Tabular in the search box and find Microsoft Analysis Services Projects as shown below. Click on Download to download it
6. Create a Tabular Model Project in Visual Studio
Now we would would create an SSAS project in Visual Studio.
Step 1 – Start a new Project in Visual Studio. In the list of Project Types, select Analysis Services Tabular Projects as shown below
Step 2 – In the next window as shown below, give the project a name and click Create
Step 3 – In the tabular model designer, select Integrated Workspace as shown below:
At this point, we have successfully completed the setup. The final window for the new project is as shown below:
In the next part, we would try to understand the various types of models available in SSAS.
