From PowerPivot to Tabular Part1

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

Read part 2 here!

In this first blog post we will create a quick PowerPivot model. It will be quick and a bit dirty hence with more time and effort we could build a nicer model. But for the understanding, this should be fine.

If you haven’t already installed PowerPivot you can get it here. Download the PowerPivot for SQL 2012. You will need to download the 32bit or 64bit version depending on your version of Excel. To find out if your Excel is a 32bit or 64bit version open Excel, select File –> Help. In the lower right corner, you can see the version.

To create a PowerPivot model we need some data. I will use the Adventure Works data warehouse for SQL Server 2012. 

Let’s get some work done.

Start open Excel and navigate to the PowerPivot tab. If you can’t see the PowerPivot tab. PowerPivot isn’t installed.

Press the PowerPivot Window button, to open PowerPivot. A new window will open.

The first step will be getting some data. Select From Database –> From SQL Server.

Setup your connection. As you can see I use the IP of my server, but you can also use a server name. While my test server isn’t on the domain I use SQL Server authentication.

On the next steps in the wizard select the tables you want to use. I use FactInternetSales, DimDate, and DimProduct. The wizard should end up showing everything is good.

Now we have three sheets one for each table imported.

The next step is to set up relationships between the fact table and the dimensions. Press the Manage Relationships button in the Design ribbon.

PowerPivot will try to guess the relations. If you have a nice well-formed data warehouse PowerPivot will do a great job else you will need to set it up yourself. In my case, PowerPivot manages to setup it up correctly. As you can see the is an active relationship between FactInternetSales and DimProduct. There is one active and two inactive relationships between FactInternetSales and DimDate. This is one of the limitations in PowerPivot you can not have roleplaying dimensions. If you need to filter on DueDate you have to create an extra date dimension called ex. DimDateDue.

For simplification let hide some of the data. Do this by right-clicking a column and select the hide from client tool. In FactInternetSales hide everything except OrderQuantity and SalesAmount. In DimProduct hide everything but the EnglishProductName. In DimDate hide everything but DateKey, EnglishMonthName and CalenderYear

Save the model and press the PivotTable in the home ribbon to enter Excel.

Now use your normal pivot table skills to analyze your data.

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.