THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

BI Beginner: Diagram View in Excel PowerPivot

Excel 2013 and Excel 2010 offer a Diagram View in PowerPivot that isn’t generally well known beyond the community of BI professionals and Excel power users. In Diagram View, database tables appear as tables in PowerPivot, complete with referential integrity. If you have Excel 2010, you’ll need to download and install SQL Server 2012 Service Pack 1 PowerPivot for Excel 2010. Although the name suggests that your data source must be SQL Server 2012 Service Pack 1, that is not the case. It works with a variety of data sources as shown below.

When you use PowerPivot, you are bringing data from your data source into Excel. You’ll need enough memory and disk space for the data you are working with. If your data changes on the server, you can refresh the data because the Excel file is both a local repository of the data as well as a set of rules on how you’ve decided to present and manipulate your data. You can share the Excel file with others and they can connect to the data source and refresh the workbook with the latest data whenever they want.

For certain power users and business analysts, it makes far more sense to give them Excel’s PowerPivot to analyze their data than making them learn SQL and use SSMS or SSDT.

Here are the steps to connect to a data source (SQL Server in this example, but it could another data source) and view the data in Diagram View. Begin by selecting the DATA tab in Excel, then use From Other Sources to select your data source.

NOTE: Some of the screen captures are annotated in blue because it is a safe color to use with all forms of color blindness.

DataSource2

Figure 1. Use From Other Sources on the DATA tab to select your data source.

Make a connection to your data source using whatever credentials are required. In this example, the local SQL Server was used. Notice that a single dot (e.g. period) was used instead of an actual server name because a single dot defaults to the local server.

connect

Figure 2. Connecting to the local SQL Server using a dot instead of specifying the server name.

To show the relationships between tables in your PowerPivot diagram, you must check Import relationships between select tables. How many tables you can select depends on the quantity of data and the amount of free memory on your local machine where you are running Excel. If you click the Select Related Tables button, you might bring in more data than your machine can handle.

SelectTables

Figure 3. How to specify the tables you want in your PowerView diagram. Notice there are 5 steps to follow.

Remember that you are saving both data and the rules you specified when creating your workbook. You should write a meaningful description so that the purpose of the workbook is clear.

SaveODC

Figure 4. Enter a meaningful description.

Specify Table in the Import Data dialog box so that your data source’s tables are instantiated as tables in Excel.

table 

Figure 5. Select Table in the Import Data dialog box.

If your machine doesn’t have the capacity to handle all of the data you’ve specified, you’ll receive an error message similar to the following:

boundsError

Figure 6. To show you what happens when too much data is required, the Select Related Tables button shown earlier was clicked.

After the data source’s tables are downloaded into table structures in Excel (notice that each table has its own tab or worksheet in Excel), click the POWERPIVOT tab in Excel and then click Manage to start PowerPivot.

Manage

Figure 7. First click the POWERPIVOT tab and then click Manage.

A new PowerPivot window appears. Click Diagram View to show the tables and their relationships.

PowerPivot

Figure 8. Click Diagram View on the PowerPivot window.

Keep in mind that the diagram is of the local copies of the tables in Excel, not the originals on the server. The copies of the tables in Excel have the same referential integrity (relationships) that they have in the original data source.

DiagramView

Figure 9. Diagram View in PowerPivot.

Excel has many features to help you manipulate and analyze data. Use the right tool for the job. Sometimes Excel is a far better data analysis tool than SSMS and T-SQL.

Thanks to SQL Server MVP Robert Cain for his invaluable assistance in writing this post.

Published Saturday, February 23, 2013 11:32 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement