From PowerPivot to Tabular Part1

7 min. læsetid
12. april 2012 Skrevet af: inspari Del med en ven     

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.

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 you 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.

blog1

To create a PowerPivot model we need some data. I will use the Adventure Works data warehouse for SQL Server 2012. You can get if here but feel free to use your own data.

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.

blog2 

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

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

blog3

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 the FactInternetSales, DimDate and DimProduct. The wizard should end up show everything is good.

blog4

Now we have three sheets one for each table imported.

Next step is to setup relationships between the fact table and the dimensions. Press the Manage Relationships button in the Design ribbon.

blog5

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 your self. In my case PowerPivot manage to setup it up correctly. As you can see the is an active relationship between FactInternetSales and DimProduct. The is one active an 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.

blog6

For simplification let hide some of the data. Do this by right click an column and select 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.

blog7

For more information on how to create PowerPivot models I can recommend joining this class.