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