Pivot Tables in SSAS?

And you thought I was not going to talk about pivot tables for awhile…

This week I’m going to take a look at SQL Server 2012, specifically SQL Server Analysis Services (SSAS).  Since SSAS was introduced in SQL Server 2005, it supported only a single mode of operation.  That mode was to use a multi-dimensional model for building cubes that frankly many DBAs had trouble with because it encouraged denormalization of the data to flatten the structure, reduce the number of tables and create a star or snowflake schema for building cubes.

Then Excel 2010 introduced PowerPivot which used a new engine called the VertiPaq engine which allowed users to build pivot tables and charts from relational tables without necessarily having to go through all of the denormalization that SSAS expected.  PowerPivot was introduced in two flavors, PowerPivot for Excel and PowerPivot for SharePoint.  Both are distributed by Microsoft free of charge.  Both relied on the new VertiPaq engine but the SharePoint version also required SQL Server 2008 R2 because the SSAS engine was modified to support this new VertiPaq mode specifically for Excel spreadsheets published to SharPoint.

With SQL Server 2012, you can install SSAS using a third mode, the tabular mode which allows you to directly build in SSAS pivot tables using the same VertiPaq engine used by PowerPivot for Excel.  So why should you care?  In Chapter 9 of Microsoft’s free ebook: Introducing Microsoft SQL Server 2012, the authors provide a table to compare the features between the three server modes for SSAS.  Rather than repeat that here, download the book at:  http://blogs.msdn.com/b/microsoft_press/archive/2012/03/15/free-ebook-introducing-microsoft-sql-server-2012.aspx.  There is a lot of other good information about the new SQL Version here.

So onward to the practical demo for today.  I am going to show screens here using SQL Server 2008 R2 since many of you may not have converted to SQL 2012 yet.  Since the multi-dimensional part of SSAS has not changed in SQL Server 2012, the steps described here should work the same way.  I’m also going to use a Contoso data set which you can download from: http://www.microsoft.com/en-us/download/details.aspx?id=18279

To begin a SSAS project, open SQL Server Business Intelligence Development Studio from the folder Microsoft SQL Server 2008 R2 in your Start menu.  Create a new project.  In the New Project dialog, select Business Intelligence Projects as the Project Type and Analysis Services Project as the Template.  In the figure below, you can see that I named the project ContosoPivot, but you can name your project whatever you like.

After you click OK, you will see the standard development environment that uses the Visual Studio shell.  Go to your Solution Explorer panel.  If you do not see a panel labeled Solution Explorer, open the View dropdown menu and click on Solution Explorer there.

First, you need to define your data source.  Right click Data Sources in your project in Solution Explorer and select New Data Source from the dropdown menu.

If you already have a connection defined, you can simply select it and click Finish.  However, if you do not have an existing data connection to the database you want to use, click the New button.

In the connection manager, make sure your Provider says: Native OLD DB\SQL Server Native Client 10.0, at least if you are using SQL Server 2008 R2.  Otherwise, use the dropdown arrow to display the available providers and hopefully you will find the correct one for your version of SQL.  Next, enter your server name.  If you are running this on your local machine, that is probably your machine name.  You can also use: Localhost, or you can use the dropdown arrow to scan for available database servers, but the fastest way is to manually enter your server name if you know it.  Next you can specify the type of authentication and if you are using SQL Server Authentication, the user name and password.  I prefer to use Windows Authentication for two reasons.  First, it is one less password to remember, and second, SQL authentication passes the username and password over the network in clear text.  Finally, use the dropdown for the prompt: Select or enter a database name to select the database you want to use in the selected server.  These options appear in the figure below.

(Note: There is a button to test the connection.  You may want to do this to insure that the authentication provided earlier has permission to access the database.)

When you click the Ok button, you will again see the Data Source Wizard, but now your new connection should appear in the Data Connections box on the left.  Select the data connection and click Next.  Here you can provide impersonation information that defines how SSAS connects to the data source.  You could use a custom account that limits rights to the database by using a custom Windows user name and password.  Keep in mind that you must give this user rights to the database through SSMS.  You can also use a service account or you can use the credentials of the current user.

Click Next to finish defining the Data Source by giving it a name.  By default, the wizard uses the name of the database as the source name.  Usually this is acceptable.  Click the Finish button to complete the wizard.  You should now see your data source in the project with the extension .ds.  Now you need to create a Data Source View to define which tables you want to use.  Right click on the Data Source Views folder in the Solution Explorer.

In the first screen after the Welcome screen, you can select one of the Relational Data Sources you created in the previous step.  You can also create a new data source here by clicking the New Data Source button.  Let us assume that the Data Source exists and you can just select it and click Next.

In the Select Tables and Views page of the wizard, you can select the tables you want to use in your cube.  Only tables you select to be part of your view can appear in the cube.  You do not need to include all of the tables in the selected database.  I prefer to select first the fact table I want to use which in this case is: FactSales.

I could manually select the dimension table I wanted to use or I could just select the fact table in the Included Objects list and then click the Add Related Tables button.  The wizard looks at the relations between the keys in the fact table and the other tables in the database to determine which tables to pull over to the Included Objects list.  If the wizard pulls over any tables I really do not care about for the pivot table I am trying to create, I can simply select them and use the left pointing arrow key to send them back to the Available Objects list to wait for another opportunity to join a cube.

When I click Next, the wizard prompts me to provide a name for the view.  Again, by default, it uses the name of Data Source that is usually fine with me.

You will then see a diagram of your tables that shows our fact table FactSales as the center of a star schema.  We will come back to this diagram in future weeks to show how we can rename objects to more user-friendly names and create calculated columns.


First, let us just right click on the fact table header.  This opens a popup menu.  Select Explore data from the list of options.

SSAS now shows us a new window with a set four tabs across the top: Table, Pivot Table, Chart, and Pivot Chart.  In the Table view, you can see all of the fields in the fact table.  In fact, this almost looks like an Excel spreadsheet of the data.

Next, click the Pivot Table tab.  This begins by displaying an empty pivot table with a list of the fields in the fact table in something that looks like the Excel Pivot table field list along the right side of the screen.  You can drag any of the fields into the table to create display measures as a function of any of the other fields such as the dimension keys.  In the figure below, you can see that I can display data by year and quarter.  In fact, there are several other date options available from the field list.  How did this data get there when the original fact table did not include these fields?  There was a field that contained a Date Key.  SSAS uses this field to create automatically some of the more common hierarchy elements for dates.  On the other hand, the Channel Key shown as the row dimension only contains four values, 1 through 4 which by themselves does not tell us much.  However, the point is that we were able to generate a simple pivot table directly from the fact table.

The third tab is Chart.  Clicking on this tab displays simple counts of records by the selected fields from the fact table.  It picks several fields by default, but if you click the bar at the top of the charts (Currency Key, Store Key, Promotion Key, and Channel Key in the figure below, you can pick any of the fields from the fact table.  Note that for some fields, the data chart groups values together to display the columns because any individual chart will not display more than 10 columns.

Finally, the fourth tab is a pivot chart.  In the chart below, I am only displaying the sum of the Sales Amount by Channel Key.  I could add another field as a series by dragging one of the fields to the Drop Series Fields Here area on the right side of the chart.

Perhaps if we had further denormalized the fact table by adding the dimension labels into the fact table instead of having keys to link to dimension tables, we could have created the entire pivot table/chart analysis right here, captured the screen images and pasted them into a Word document as a report.  However, what I showed here today is merely a way to get a quick look at the data, not to create a final product.  To create a final product, you will want to take the next step and create a cube.  Next week I will continue this example by showing how to build that cube.

C’ya later.


3 comments on “Pivot Tables in SSAS?

  1. Howdy! This post could not be written much better! Looking at this article reminds me of my
    previous roommate! He always kept preaching about this.
    I am going to forward this information to him.
    Pretty sure he’s going to have a great read. I appreciate you for sharing!

    • The tabular model in SSAS 2012 is essentially the same as the Power Pivot model when displayed in Excel with a few different menu changes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s