From PowerPivot to Tabular Part2

In this series of blog posts, I will show you how to create a PowerPivot model, how to convert it to an analysis services tabular project, and finally how to set up security.

In this second blog post, we will upgrade our PowerPivot model from the first blog post to a Tabular project.

Read part 1 here!

To convert your PowerPivot model to a Tabular project you need SQL Server Analysis Services installed in tabular mode. If you need to run both the good old multidimensional mode and the new in-memory tabular mode you need to install to instants of analysis services. I think most customers will end up running both. At least the next couple of years.

Now open SQL Server Data Tools and select new project. Select a Business Intelligence, Analysis Services, Import from PowerPivot project. I call the project InternetSales.

Click next, select the .xlsx file from the previous blog post and click yes to the warning about only metadata can be loaded due to security reasons. You should end up with a model but without data.

To get data we need to change the connection. Go to the Model menu and select existing connections. Edit the connection, and type in the password if you are using a SQL user.

When trying to test the connection you will properly get an error.

This error appears because Excel using the SQL client version 10 (sqlnclo10.dll) and SQL Server Data Tools can’t find this dll unless it is installed on the server. Don’t worry it’s easy to change the connection to use the new version 11 dll.

Open the advanced dialog for the connection, and change the provider to SQL Server Native Client 11.0. Now everything is fine.

Go to the Model, Process, Process All to import data to all three tables.

Now we are actually done. The only thing left is to deploy the Tabular project into production. Select Build, Deploy InternetSales

Testing the model can be done by using Excel, your favorite front-end tool, or in this case my favorite front-end tool SQL Server Management Studio. Open SSMS and connect to Analysis services. If you have more instants select the one running in Tabular mode.

You will see a database called InternetSales. Right-click it and select browse to test the tabular model. If you see two databases like I do the one called InternetSales-kks-[GUID] is a temp database it will disappear when you close SQL Server Data Tools.

As you can see it’s very easy to import PowerPivot model into a Tabular project. Hence I see PowerPivot as a very powerful POC tools.

Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.