Over the years Microsoft Excel has managed to become a very popular tool--especially within the analytical community. In this article we will look at how the average business user can use Excel PivotTable, PivotChart or PowerPivot to view and analyze business intelligence data.
Contrary to popular belief, you don't have to be extremely technical to access and analyze business intelligence data using PowerPivot. But you do need to have a good understanding of the business objectives you want to achieve when you sit down to access business intelligence data.
In this article we will look at how to create PivotTables and PivotCharts using out-of-the-box Excel capabilities, which can automatically create measures from selected datasets. We will then discuss how you can view and work with business intelligence data using the PowerPivot add-in, which (last I checked) could be downloaded from the following page: http://technet.microsoft.com/en-us/library/gg413462.aspx.
If you haven't had a chance to check out my previous business intelligence articles you may want to look at Business Intelligence Overview: Companies Remove Guess-Work To Make Sound Decisions and Business Intelligence: Using Mining Structures, Models, Cubes & Dashboards. These articles provide some basic insight to business intelligence data and a few of the tools used to access and transform data.
Microsoft Excel is a popular spreadsheet application that provides the ability to not only copy and paste data; but also to connect to external data sources. It's PivotTable capability provides a way to view measures such as total sales or total customers for comparison or other purposes. Excel's basic PivotTable even provides some basic ability to measure results. In this section we'll take a quick look at how you can connect to an external data source and work with data using Excel's built-in PivotTable.
In my example I connect to a SQL Server database (throughout this article I continue to use the AdventureWorksDW). I plan to access my data using a data source so I will create one that I can use over and over again. To do this I have to use Window's Administrative Tools, which can be accessed from the Control Panel.
I can then use the ODBC Data Source Administrator to add a new File DSN. In my example I created a File DSN that connects to the AdventureWorksDW database. This DSN provides access to all of the tables and views in the AdventureWorksDW database.
Now, from within Excel, I can select Insert -> PivotTable to display the Create PivotTable dialog used to connect to a data source. (Alternatively, the dialog can also be used to select a range of cells in a spreadsheet already populated with data or open a new worksheets.) After I select "Use an external data source" and then click the "Choose Connection" button (on the Create PivotTable dialog) I am able to select the File DSN I created. (Notice I can create as many Excel PivotTables or PivotCharts as I want to and I never have to create another connection to the database because I can just select the file I created instead.)
I can then select the database table or view that contains the data I want to view. In this example I selected a view I created because, unlike PowerPivot, Excel's out-of-the-box PivotTable lets you add one table or view at a time. (For those who are wondering, a view is a database object made up of tables or other views and can include specific fields from each table or view).
Once the desired table or view is selected you can then build the PivotTable in one of two ways: You can drag and drop the columns onto the PivotTable. Or, you can click the checkbox beside each column in the "Choose field to
add to report" area. The fields are added to the PivotTable in the order
they are selected or dropped.
Below is a picture of a PivotTable with the columns added. The PivotTable automatically creates measures (i.e., total or count columns) as a calculated member using standard SUM aggregation. In this example, business users can analyze total sales for a customer. Once the PivotTable is created users can use Excel's Design tab to select and apply a PivotTable style if the table will be included in a presentation.
In the following example I moved two columns to the Report Filter area so business users could measure total sales by region and product.
Notice the PivotTable adds the drop-down's business user's need to select a Region and/or Product Subcategory to filter the PivotTable.
If you need to present PivotTable in a chart format Excel's PivotChart feature can help. To create a PivotChart from a PivotTable you can select Insert -> PivotChart. You would then follow the same steps used to create the PivotTable (i.e., select the connection or data on the existing spreadsheet, select the database object (table or view) then select the columns). If you create the PivotChart from a new connection, Excel creates a PivotTable and then creates a PivotChart from the PivotTable, as shown below.
In the previous section I created a view (using SQL Server and Transact SQL (T-SQL)) for my PivotTable because I wanted to use data from a view and multiple tables. If you do not have SQL Server skills and you need a solution that is more robust than Excel's out-of-the-box PivotTable, PowerPivot is a viable option. PowerPivot provides business users some of the same capabilities offered through SQL Server Business Development Studio and a BI project created with Visual Studio 2010 or higher. This section provides an overview of PowerPivot.
In the previous section I created a DSN file and used it to make the connection between Excel and SQL Server. In this section I use PowerPivot to create a connection directly to SQL Server. Still, PowerPivot provides a wide range of data sources users can connect to, including the ability to connect to an Analysis Services Cube. The following picture shows a few of the many data sources users can connect to using PowerPivot.
In my example, I selected Microsoft SQL Server as my data source. PowerPivot locates the servers available and automatically populates the Server Name field with the list of available servers. Once a user selects the Server Name PowerPivot then displays a list of available databases on the server. The user can then select the data warehouse database. For more information on connecting to data sources see the article titled Add Data To Your PowerPivot Workbook.
Note that PowerPivot also supports connections to a SQL Server Parallel Data Warehouse (also called SQL Server PDW). This connection type is used to connect to large data warehouses that span across multiple servers. When this connection type is used to create a connection--the connection is made to the primary data warehouse server used to manage the data warehouse.
Once the user has selected the server and the data warehouse database; the user can then select the desired tables (shown in the picture on the left). Users can use the Preview & Filter option to deselect the table fields that should not be included in the PivotTable (shown in the picture on the right). In the previous section as the user selects the fields to be included in the PivotTable--Excel automatically creates the report. PowerPivot, however, requires more interaction because users have more options available.
Once the user has deselected the columns that should not be included in the PivotTable; updates can be made by selecting Table -> Table Properties. This displays the Edit Table Properties window, shown below, so users can select/deselect table fields.
Once the user selects the desired tables and fields the user can chose to create calculated fields (which can also be done using Excel's out-of-the-box PivotTable feature). However, the user can also create relationships between tables and perform several other tasks not available when using Excel's out-of-the box PivotTable.
To effectively cover all of PowerPivot's capabilities my next article, which will be Part 2 of this article, will discuss the following topics in detail and include examples:
Contrary to popular belief, you don't have to be extremely technical to access and analyze business intelligence data using PowerPivot. But you do need to have a good understanding of the business objectives you want to achieve when you sit down to access business intelligence data.
In this article we will look at how to create PivotTables and PivotCharts using out-of-the-box Excel capabilities, which can automatically create measures from selected datasets. We will then discuss how you can view and work with business intelligence data using the PowerPivot add-in, which (last I checked) could be downloaded from the following page: http://technet.microsoft.com/en-us/library/gg413462.aspx.
If you haven't had a chance to check out my previous business intelligence articles you may want to look at Business Intelligence Overview: Companies Remove Guess-Work To Make Sound Decisions and Business Intelligence: Using Mining Structures, Models, Cubes & Dashboards. These articles provide some basic insight to business intelligence data and a few of the tools used to access and transform data.
Microsoft Excel & PivotTables/Chart
Microsoft Excel is a popular spreadsheet application that provides the ability to not only copy and paste data; but also to connect to external data sources. It's PivotTable capability provides a way to view measures such as total sales or total customers for comparison or other purposes. Excel's basic PivotTable even provides some basic ability to measure results. In this section we'll take a quick look at how you can connect to an external data source and work with data using Excel's built-in PivotTable.
In my example I connect to a SQL Server database (throughout this article I continue to use the AdventureWorksDW). I plan to access my data using a data source so I will create one that I can use over and over again. To do this I have to use Window's Administrative Tools, which can be accessed from the Control Panel.
Now, from within Excel, I can select Insert -> PivotTable to display the Create PivotTable dialog used to connect to a data source. (Alternatively, the dialog can also be used to select a range of cells in a spreadsheet already populated with data or open a new worksheets.) After I select "Use an external data source" and then click the "Choose Connection" button (on the Create PivotTable dialog) I am able to select the File DSN I created. (Notice I can create as many Excel PivotTables or PivotCharts as I want to and I never have to create another connection to the database because I can just select the file I created instead.)
I can then select the database table or view that contains the data I want to view. In this example I selected a view I created because, unlike PowerPivot, Excel's out-of-the-box PivotTable lets you add one table or view at a time. (For those who are wondering, a view is a database object made up of tables or other views and can include specific fields from each table or view).
Below is a picture of a PivotTable with the columns added. The PivotTable automatically creates measures (i.e., total or count columns) as a calculated member using standard SUM aggregation. In this example, business users can analyze total sales for a customer. Once the PivotTable is created users can use Excel's Design tab to select and apply a PivotTable style if the table will be included in a presentation.
In the following example I moved two columns to the Report Filter area so business users could measure total sales by region and product.
Notice the PivotTable adds the drop-down's business user's need to select a Region and/or Product Subcategory to filter the PivotTable.
PivotTable & PivotChart
If you need to present PivotTable in a chart format Excel's PivotChart feature can help. To create a PivotChart from a PivotTable you can select Insert -> PivotChart. You would then follow the same steps used to create the PivotTable (i.e., select the connection or data on the existing spreadsheet, select the database object (table or view) then select the columns). If you create the PivotChart from a new connection, Excel creates a PivotTable and then creates a PivotChart from the PivotTable, as shown below.
Working With PivotTables in PowerPivot
In the previous section I created a view (using SQL Server and Transact SQL (T-SQL)) for my PivotTable because I wanted to use data from a view and multiple tables. If you do not have SQL Server skills and you need a solution that is more robust than Excel's out-of-the-box PivotTable, PowerPivot is a viable option. PowerPivot provides business users some of the same capabilities offered through SQL Server Business Development Studio and a BI project created with Visual Studio 2010 or higher. This section provides an overview of PowerPivot.
Connecting to the Data Warehouse
In the previous section I created a DSN file and used it to make the connection between Excel and SQL Server. In this section I use PowerPivot to create a connection directly to SQL Server. Still, PowerPivot provides a wide range of data sources users can connect to, including the ability to connect to an Analysis Services Cube. The following picture shows a few of the many data sources users can connect to using PowerPivot.
In my example, I selected Microsoft SQL Server as my data source. PowerPivot locates the servers available and automatically populates the Server Name field with the list of available servers. Once a user selects the Server Name PowerPivot then displays a list of available databases on the server. The user can then select the data warehouse database. For more information on connecting to data sources see the article titled Add Data To Your PowerPivot Workbook.
Note that PowerPivot also supports connections to a SQL Server Parallel Data Warehouse (also called SQL Server PDW). This connection type is used to connect to large data warehouses that span across multiple servers. When this connection type is used to create a connection--the connection is made to the primary data warehouse server used to manage the data warehouse.
Selecting & Filtering Database Tables
Once the user has selected the server and the data warehouse database; the user can then select the desired tables (shown in the picture on the left). Users can use the Preview & Filter option to deselect the table fields that should not be included in the PivotTable (shown in the picture on the right). In the previous section as the user selects the fields to be included in the PivotTable--Excel automatically creates the report. PowerPivot, however, requires more interaction because users have more options available.
 |
 |
Once the user has deselected the columns that should not be included in the PivotTable; updates can be made by selecting Table -> Table Properties. This displays the Edit Table Properties window, shown below, so users can select/deselect table fields.
Once the user selects the desired tables and fields the user can chose to create calculated fields (which can also be done using Excel's out-of-the-box PivotTable feature). However, the user can also create relationships between tables and perform several other tasks not available when using Excel's out-of-the box PivotTable.
To effectively cover all of PowerPivot's capabilities my next article, which will be Part 2 of this article, will discuss the following topics in detail and include examples:
- Working With PivotTables in PowerPivot (Continued)
- Creating relationships between tables.
- Creating measures (calculated fields).
- Creating Hierarchies. (Note that with Analysis Services users can create hierarchies to work with different views of dimension members. PowerPivot also supports this capability, as will be seen in Part 2 of this article.)
- Working With Perspectives (which may include dimensions, hierarchies, measures, etc.).
- Working With PivotCharts in PowerPivot
