Tabular and Multidimensional Model in one SQL Server 2016

Friday, January 06, 2017

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 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 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 default instance for this analysis service:

Setup 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 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 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 a 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 a admin in this scenario.

Click Next and we are close to the final result of this tutorial. You will get this dialog with overview of features and settings 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 instance in which that mode is installed.

In this tutorial I was installing OLAP first but that don'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 wright 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! Almir Vuk IT Blog

You Might Also Like