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.

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

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.

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.

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

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.

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

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

• Click Finish button.

 

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

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

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

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

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?

• Create Excel Pivot Table from SQL Server
• Import data into SQL Server
• SQL Server 2008 R2 Installation

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!