Tabular and Multidimensional Model in one SQL Server 2016

Tabular and Multidimensional Model in one SQL Server 2016

At the time I am writing this simple tutorial I have my subject on university on topic Business Intelligence and form my final project of that subject I need to create OLAP and TABULAR for data analysis using SQL Server and other BI Tools.

So the topic of this tutorial is how to have both Tabular and Multidimensional Model in one SQL Server 2016.

Short answer will be you need to have two instances of SQL Server one for OLAP and other named instance for TABULAR.

If you don't know how to do that, let's get started with tutorial:

Install SQL Server 2016.

If you are not familiar with installation please take a look at my tutorial in this blog post here:

Follow all the steps and at the end, you will have your DB engine and other stuff that you select in the part of the feature selection.

Now, this tutorial will be based on if you don't have any selection in part of Analysis Services. If you already have TABULAR or OLAP (Multidimensional) installed on your SQL Server instance skip next steps and go to the part where I am installing Analysis Services on named instance.

Install OLAP on default instance.

If you have installed SQL Server with basic features, now we will install Analysis Services with OLAP model.

Run the setup and follow these steps:

Navigate to feature selection and select Analysis Services:

Next, choose the default instance for this analysis service:

Set up your startup settings for this service:

And the main part is to select in which mode our server analysis service will be in this case on ma "main" SQL Server instance I will use Multidimensional and Data Mining Mode.

Click on next and install it, wait a couple of minutes depending on your PC and you will be good to go with your analysis service in OLAP mode.

Now we are going to install TABULAR mode on another "named" instance of SQL Server.

Install TABULAR on the named instance.

Run setup and navigate to the Installation type selection:

And select "Perform a new installation of SQL Server 2016"

Click Next and under feature selection choose Analysis Services:

Click Next to proceed to the next part:

Choose named instance and give it a proper name in my case it is: MSSQLServer_TAB

Click Next and configure service settings about account and startup...

Next and again we have the main part where we need to configure a mode that this instance of analysis service will be, now I will select Tabular mode and add current user because I am an admin in this scenario.

Click Next and we are close to the final result of this tutorial. You will get this dialog with an overview of features and settings to click on Install.

After this, you are done and you have both OLAP and Tabular on the SQL Server when you need one of them you will use the instance in which that mode is installed.

In this tutorial, I was installing OLAP first but that doesn't need to be in your case, you can do Tabular on "main" default instance and OLAP on named.

This is not a developer tutorial or blog post but it is something that I am using right now on in the school on Business Intelligence subject so I wanted to share this simple tutorial with you.

Hope that this was helpful for you.

Best regards!