Create Excel Pivot Table from SQL Server

Now, we installed SQL Server 2008 R2 Express Edition and created Demo database; we can start importing data into Excel.  I am going to use Excel 2010 but you can follow similar mechanism to import data into Excel 2007.  In this blog, I am going to show you how to:

  • Create a view in SQL Server
  • Create a data connection in Excel 2010
  • Create a PivotTable from SQL Server (with 3.9 million records)

Create a view in SQL Server

What is a view in SQL Server?  Why do we need a view? 

A view is a virtual table.  We have a few physical tables in Demo database that actually store data.  FactSales is the main table that contains 3.9 million records.

image

If we open the table, we would see a lot of data.

image

Do you notice one thing?  They are all numbers.  This table contains all sales information, so I expect to see items like cost, price, discount…; they are all number items.  However, I would also expect to see other non number items, e.g. store information, product description…etc.  Where are all these critical information?

FactSales has nearly 4 million records; it is a huge table and we would like to minimize its size.  Instead of storing every information into this already huge table,  we move some of the repetitive information to other tables.  For example, we have a DimStore table; it has all the stores information.

image

FactSales would use the StoreId to point to DimStore table if we need the details store information.  This is how we can effectively store data and improve  system performance.

However, when we want to do sales analysis, we want to see a full picture; our business analysts would not know where store 1 is or what product 1763 is.  This is why we have to create a view, a logical table that contains information that our business analyst can understand.

I am going to discuss the details of how to create a view in another blog and just showed you the view that I just created.

image

This view allows us to view the stores information, products information together with the sales information.

image

Create a data connection in Excel 2010

Most of us work with data that is stored directly in the workbook.  However, our data is now stored in SQL Server, an external data source.  In order to retrieve data from SQL Server into our Excel workbook, we can either import data from SQL Server (using Import / Export Wizard) or setup a data connection between Excel and SQL Server.  I recommend data connection in most circumstances.

The main benefit of connecting to external data is that we can periodically analyze this data without repeatedly importing the data to your workbook.  All we need to do is to refresh our Excel workbooks from the data source through the data connection.

This is how we create a data connection:

  • Click Data ribbons.
  • Click From Other Sources.
  • Click From SQL Server.

image

  • Enter (local) in Server name.
  • Click Next button.

image

  • Enter Demo in Select the database that contains the data you want:.
  • Select DetailedSalesInfo (the view we just created).
  • Click Next button.

image

  • Click Finish button.

 image

This is all we need to do.  And all the connection information is stored in a file, so we can reuse it anytime we want.

Create a PivotTable from SQL Server

Once the connection is created, we can retrieve data from SQL Sever into Pivot Table,

  • Select PivotTable Report
  • Click OK button..

image

  • From this point on, you are using the features of Excel PivotTable.

image

  • Pull CalendarYear and CalendarQuarter into Column Labels.
  • Pull ProductCategoryName, ProductSubcategoryName in Row Labels.
  • Pull SalesAmount into Values.

image

Here is the PivotTable, an ordinary Excel PivotTable.  No one can tell that there are 4 million records behind this PivotTable.

image

Conclusion

We don’t have to use SQL Server to store data, we can use Oracle, DB2 or even Microsoft Access but I really encourage you to store data in a robust database management system.  Excel is great for analysis but it is not designed to store data; especially not for a lot of data or multi users controlled environment.

It requires certain technical knowledge to build a well designed database; I am not suggesting that everyone can do it yourself.  But most of the database tasks are one time; once the database is created; we can automate the import / export processes, we can reuse the database connections.  You would be just focus on what you do the best, i.e. analysis using Excel.

Data is one of the most important assets in our organization; it empower use to make smarter and more efficient decisions.  Do you want to properly manage your data and business? or just let Excel chaos to continue dominating your office?

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

5 Responses to Create Excel Pivot Table from SQL Server

  1. Pingback: SQL Server 2008 R2 Express Installation « Technologies and your business

  2. Pingback: Import data into SQL Server « Technologies and your business

  3. Pingback: Is Excel our only option? « Technologies and your business

  4. Pingback: 2010 in review « Technologies and your business

  5. Ramesh says:

    Thanks a lot. Excellent tutorial. Was of great help

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

Follow

Get every new post delivered to your Inbox.

Join 32 other followers

%d bloggers like this: