Microsoft's business intelligence (BI) offering has been evolving over the past few years with several products playing a role. SQL Server 2005 contains a database engine, an analysis services engine, and a report services engine, while Excel 2007 has been enhanced to support up to 2 million rows of data. Other products such as PerformancePoint Server 2007 and recently acquired ProClarity offer scorecarding and analytics. All in all, it is an offering that often feels cobbled together out of available pieces. Worst of all, there has never been a single centralized view of BI data in the Microsoft world. Excel spreadsheets might be e-mailed to users while reports are viewed through a web site and data is accessed through custom applications.
The role of SharePoint in this environment is to act as the front end to various business intelligence sources. While SharePoint is far from a complete BI solution, it does bring some focus that can help make better use of the Microsoft BI offering. In this chapter, I examine the use of SharePoint for interacting with business intelligence data and help you understand how it works with some key Microsoft BI products. Note that while I present information that can be used in both MOSS and WSS, most of the capabilities discussed here require MOSS. Therefore, I'll assume you have access to MOSS throughout the chapter.
The home for all BI data within MOSS is the Report Center. You may create a Report Center as a separate site collection using the available template or utilize the one that is created as part of the Collaboration Portal. If you set up the development environment detailed in Chapter 2, you already have a Report Center available. Figure 8-1 shows a Report Center home page with several modifications already made.
The primary role of the Report Center is to allow the creation and display of dashboards. Dashboards in the Report Center are pages that consist of scorecards and reports. Scorecards are lists that display quantitative measures of organizational performance called key performance indicators (KPIs). Reports are Excel spreadsheets delivered through Excel Services. Both scorecards and reports may act as front ends for a variety of data sources. Therefore, dashboards provide a centralized mechanism for viewing business intelligence data.
Follow these steps to create a new dashboard:
Log in to VSMOSS as a site administrator.
Click the Reports tab to view the Report Center.
Click the Dashboards link in the Quick Launch area.
In the Reports Library, select New
In the File Name field, enter Performance.
In the Page Title field, enter Organizational Performance.
In the Layout list, select One Column Vertical Layout.
Select the option labeled Create a KPI List for Me Automatically.
Click the OK button. This will give you an empty dashboard that you can populate through the rest of the chapter.
Scorecards are a mechanism for displaying data designed to support decision making at the executive level. The scorecards in the Report Center are really just custom lists that support three key fields: KPI Value, KPI Goal Threshold, and KPI Warning Threshold. The KPI Value field is a numeric measurement for a KPI. This measurement can be anything that the organization thinks is important such as sales, profit, and so on. The KPI Goal Threshold is typically the minimum value at which the goal can be considered to have been met. For example, an annual sales goal of 100 million may have a threshold value of 90 million. The KPI Warning Threshold is the value at which any further decline indicates the goal has not been met.
The scorecards you create in the Report Center use the KPI Value, KPI Goal Threshold, and KPI Warning Threshold to associate a green, yellow, or red icon with the KPI. These icons together are called the stop light and are used as a visual indicator for the KPI.
Follow these steps to create some KPIs:
On the Organizational Performance dashboard, select New
In the Name field, type Customer Satisfaction.
In the Value field, type 82.
In the field labeled Display When Has Met or Exceeded Goal, type 85.
In the field labeled Display When Has Met or Exceeded Warning, type 70.
Click the OK button.
Repeat the previous steps to add all of the KPIs listed in Table 8-1.
Table 8.1. Sample KPIs
Name | Value | Goal | Warning |
|---|---|---|---|
Customer Satisfaction | 82 | 85 | 70 |
Financial Performance | 85 | 90 | 80 |
Internal Processes | 90 | 80 | 70 |
Learning and Growth | 50 | 70 | 60 |
When you create the KPIs, you'll notice that all of the values must be numeric, but there are no units associated with the values. Furthermore, you cannot make any changes to the data types in the KPI list without breaking the underlying mechanism that determines what icons to display. This means that scorecards should indicate something about the values in the name of the KPI (e.g., Customer Satisfaction Survey Rating) for it to make sense to the end user.
Another problem with the Report Center scorecards is that KPIs cannot have child KPIs. Child KPIs are important for creating aggregated measures that are appropriate for a given user. For example, the head of marketing may be interested in the aggregated results of marketing expenditures while regional managers may only want to see their individual data. In fact, most organizations have policies that prevent individuals from viewing performance data not directly related to their role. The workaround for most of these issues lies in creating multiple scorecards and making use of more sophisticated data sources such as SharePoint lists, Excel spreadsheets, and SQL Analysis Services. I discuss dashboard data sources in the section titled "Understanding Data Source Options."
The Report Center has a Reports Library, which holds Excel spreadsheets suitable for use with Excel Services. You can create a new spreadsheet directly from the Reports Library and use it in a dashboard. Furthermore, the Report Center has a data connection library where you can store all of your Office Data Connection files so that spreadsheets can access external data sources. I discuss Excel Services and Office Data Connections in detail in Chapter 4, so I will not repeat the information here. I'll just focus on using the spreadsheets within a dashboard.
Follow these steps to use a spreadsheet in your dashboard:
In the Report Center, click the Reports link in the Quick Launch area.
In the Reports Library, select New
In the Name field, type Customers.
In the Title field, type Customer Information.
Click the OK button.
When the Customers report appears in the library, select Edit in Microsoft Excel from the drop-down list associated with the report.
In Excel 2007, click the Data tab.
In the Data tab, click the From Access button in the Get External Data group.
In the Select Data Source dialog, navigate to C:Program FilesMicrosoft OfficeOFFICE 121033.
Select the FPNWIND.MDB database and click the Open button.
In the Select Table dialog, select the Customers table and click the OK button.
In the Import Data dialog, click the OK button.
In the spreadsheet, click the black down arrow next to the formula box (fx) and select Table_FPNWIND to select all of the data imported from the query. Figure 8-2 shows the spreadsheet with the table selected.
Click the Design tab in the ribbon.
Click the Unlink button to unlink the query from the data. This is necessary because this particular type of external query definition is not supported by Excel Services.
When prompted, click the OK button to remove the link.
Save the spreadsheet and exit Excel.
Click the link for the Organizational Performance dashboard in the Quick Launch area.
In the Select a Workbook section, click the link titled Click Here to Open the Tool Pane.
In the Tool Pane, click the ellipsis associated with the Workbook field.
In the Select a Link dialog, double-click the Reports Library.
Select the Customers report and click the OK button.
In the Tool Pane, click the OK button to close it.
In the Content management toolbar, click the Publish button. Figure 8-3 shows the final dashboard.
Note
If you receive an access denied error, you will have to add the Reports Library as a trusted location using the setup outlined in Chapter 4.
Dashboards in the Report Center may utilize many different data sources for both scorecards and reports. Besides manual data, scorecards may use SharePoint lists, Excel spreadsheets, and Analysis Services cubes. Reports in the form of Excel Services spreadsheets may use SharePoint lists, databases, and cubes. In this section, I review the various data sources and how to use them within scorecards and reports.
When you use a SharePoint list as a data source, you start by creating views in the list that filter the data in ways that you want to show it. As an example, I created a list with sales data from multiple regions. I then created several views that show the data for different regions. Figure 8-4 shows a view of the example sales data from the Central region.
Once the list is filtered into views, you can use the list in a scorecard to create more specific KPIs. From a scorecard, you select New
In order to use list data in a spreadsheet, you must export it. In order to export the data, select Actions
Excel spreadsheets deployed via Excel Services can be used as a data source for scorecards. You create a new KPI from a spreadsheet by selecting New
When you select a cell, you may choose to use the value as it appears in the spreadsheet or you may create a simple formula. For example, you can multiply a cell by a value to create a threshold value. You can also simply type fixed values in for the thresholds. Figure 8-6 shows the cell picker interface with a cell selected and some values added by hand.
Both scorecards and reports may use cubes as a data source. Cubes are data structures that are optimized for queries and analysis. These data structures are built from data warehouses that bring together relational data from many different databases. In this section, I explain how to create a cube and use it with scorecards and reports.
Understanding Data Warehouses
Creating a cube always begins with understanding the data found in your organization's business systems. These systems utilize relational databases to store information, and this is a concept that is well-understood. If you are reading this book, you have probably written many applications that use relational databases. The key point of emphasis here, however, is that these relational databases are designed for optimal data storage. Figure 8-7 shows a classic relational database diagram from a customer relationship management (CRM) system representing the relationships between an organization's sales opportunities, customer companies, customer contacts, and the organization's employee working the deal.
Although relational databases are designed mainly for optimal storage, the vast majority of organizations regularly use them directly as enterprise reporting sources. Initially, this seems to make sense. Reporting systems such as SQL Reporting Services can connect directly to these databases and run reports, and the available data is always up to date because the report is run directly against the transactional system. Invariably, however, challenges arise with this approach for several reasons.
Because relational databases are not optimized for reporting, running enterprise reports against transactional systems can have a significant performance impact. In fact, the performance degradation can be so great that it affects the internal processes of the organization.
The organization generally reacts to this situation by scheduling enterprise reports to run at night. This seems like a good idea because the information workers can use the system by day and the enterprise reporting infrastructure can use the system by night. This solution may work initially, but as reporting needs grow so does the processing time required for the reports. In many cases, this leads to a situation in which reports are still running when the workday begins.
The solution to the reporting problem rests in creating a data warehouse. A data warehouse is a separate database created from one or many systems, and it is optimized for enterprise reporting. Although data warehouses can be quite complicated and incorporate many data sources, the concept is pretty simple. While relational databases are optimized for storage with no repeating data, data warehouses purposely repeat data to make it easier to generate reports. Furthermore, because the data warehouse is separate from the business system, reports can be run any time of the day.
The process of creating a data warehouse begins by moving data from target systems into a new database called a staging area. The purpose of the staging area is to join data from different systems into a single set of tables and to reconcile the data from the various systems. Reconciling the dataor cleansing the datais required because different systems may have different formats, conflicting data, or incorrect values. The process of moving the data from the business systems to the staging area is known as extract, transform, and load (ETL).
Once the data is cleaned, it is then moved from the staging area into the data warehouse. The value of the data warehouse rests in its table structure. Instead of a relational structure, data warehouses utilize a fact table. A fact table can be thought of as a view that joins many relational tables together to focus on a certain area. For example, if we want to run reports about the sales opportunities shown in Figure 8-7, we might create a fact table of opportunities that joins together the four tables in a single view. This fact table would contain the value of every sales opportunity as a row, along with the primary keys for the other tables of interest.
The primary keys contained in a fact table are said to be the dimensions of the table, while the values contained in the fact table are called measures. Dimensions are used to create views of the measures in your reports. The fact table I describe, for example, would allow us to create a report that shows sales opportunity dollar values by employee. This report is a classic pipeline report used by all sales organizations.
In a data warehouse, the dimensions in the fact table are joined to tables that contain the actual values for the dimensions. This database structure results in a centralized fact table joined with many dimension tables. The resulting structure is called a star schema because the database diagram resembles a star. Figure 8-8 shows a star schema.
Understanding Cubes
A data warehouse is an excellent structure for use with enterprise reporting tools such as SQL Reporting Services; however, reporting represents only one part of an organization's business intelligence needs. Along with classic reporting, organizations need a way for information workers and senior management to analyze performance data. Analysis involves more than just static reporting, because it allows people to answer questions by dynamically manipulating performance data.
As an example, consider what happens when an executive receives a report that quarterly earnings have dropped by 20 percent. The natural reaction is to ask why earnings are down. If an organization has no analysis capability, the IT department is left writing report after report, trying to create a view of the data that will provide an answer. With the analysis capability embodied in a cube, data may be directly manipulated to provide different views without writing a single report.
A cube is built using the star schema of the data warehouse as a starting point. SQL Analysis Services transforms the data warehouse into a cube. Just like a data warehouse, the cube will have facts, measures, and dimensions. The difference between the data warehouse and the cube is that the cube will be optimized for analysis so that different views can be created on the fly without running a new report.
In order to create a cube, you must have SQL Server 2005 Analysis Services available. If you created the development environment described in Chapter 2, you installed Analysis Services on the VSSQL machine. Along with Analysis Services, you also installed the SQL Server Business Intelligence Studio. The Business Intelligence Studio uses many of the same visual elements that are found in Visual Studio 2005, but the environment is specifically designed for the development of Business Intelligence projects such as cubes and reports. If you create a new project in Business Intelligence Studio, you'll see many of the familiar project types from Visual Studio along with the special Business Intelligence projects shown in Figure 8-9.
After you create a new Analysis Services project, Business Intelligence Studio creates a series of folders in the Solution Explorer. These folders represent the steps in a process for creating a cube. This process involves defining the data source to use, a view of that source, and then the cube. In each case, you can perform the required steps by right-clicking the appropriate folder and selecting New from the context menu. Figure 8-10 shows the folder set that is created.
The first step is to connect to the data warehouse where the facts and dimensions are located. This is done by creating a new data source in the Data Sources folder. Creating a new data source simply allows you to specify a database connection. Ideally, this database would be a data warehouse, but it can actually be any database.
The next step is to define a view of the database to use when making the cube. The view is used to focus the cube just on the data of interest. Ideally, this view would have a single fact and several dimensions just like Figure 8-8. Most data warehouses have many facts and dimensions, so creating a view allows you to simplify the data.
The final step is to create the cube. Analysis Services provides a wizard for creating cubes that can detect facts and dimensions. You can run the wizard by right-clicking the Cubes folder and selecting New Cube from the context menu. You can then work your way through the wizard to ensure that the correct facts and dimensions are identified. Once the cube is built, you may deploy it to Analysis Services where it is ready to be used in scorecards and reports.
Using Cube Data
The best way to make use of a cube through SharePoint is to first define an Office Data Connection file and save it in the data connection library in the Report Center. Creating an Office Data Connection file can be done directly through Excel 2007 and then uploaded into SharePoint. The files are typically saved with an ODC extension and are located in the My DocumentsMy Data Sources folder. Figure 8-11 shows the Data tab in Excel with choices for different data sources.
Once an Office Data Connection file is uploaded to the data connection library in the Report Center, you may add a KPI based on the cube data by selecting New
In order to use the cube data in a spreadsheet, you can create a new report by selecting New
No discussion of BI would be complete without including SQL Reporting Services. SQL Reporting Services is the mechanism for creating, deploying, and consuming enterprise reports. If you set up the development environment outlined in Chapter 2, you already have SQL Reporting Services installed on VSSQL. As a starting point, I like to add this link directly to the Quick Launch area of the Report Center for easy access.
Follow these steps to add a link to the Quick Launch area:
Log in to VSMOSS as a site administrator and open the intranet site.
Click the Report Center tab.
Select Site Settings
On the Site Navigation Settings page, click the Add Heading link.
In the Navigation Heading dialog, enter Report Server in the Title field.
Enter
http://VSSQL/Reports/Pages/Folder.aspxin the URL field.Click the OK button.
On the Site Navigation Settings page, click the OK button.
SQL Reporting Services reports are created in the Business Intelligence Studio. Creating reports can range from a very simple wizard-based process to a complex project containing rules and logic. The subject of SQL Reporting Services is the topic of many books and I cannot cover it in detail here. However, you can create a simple report for use with SharePoint.
Follow these steps to create a report:
Log in to VSSQL as an administrator.
Select Start
In Business Intelligence Studio, select File
In the New Project dialog, select Business Intelligence Projects from the Project Types tree.
Select Report Server Project Wizard from the Templates list.
Name the project SharePointReport.
Click the OK button.
When the Report Wizard starts, click the Next button.
On the Select Data Source screen, select the New Data Source option.
Name the data source MOSS.
Click the Edit button.
Type VSSQL in the Server Name drop-down list.
Type WSS_Content in the Database Name field.
Click the OK button.
In the Report Wizard, click the Next button.
Type SELECT FullUrl, Title FROM Webs in the Query String field.
Warning
Accessing the SharePoint content database directly is potentially dangerous because you could destroy critical data. Generally, it's fine to read these databases but not write to them.
On the Select a Report Type screen, click Finish.
On the Completing the Wizard screen, type Webs in the Report Name field.
Click the Finish button.
When the report is created, select Build
Log in to VSMOSS as a site administrator.
Click the Report Center tab.
Click the Report Server link you created earlier. Figure 8-12 shows the final report as it appears on a test server.
Initially, you may think that SQL Reporting Services should be tightly integrated with the Report Center. After all, the Report Center is supposed to be the single location for all BI information. Unfortunately, at this writing there is little integration. Microsoft does have grand integration plans that will emerge along with SQL Server 2005 Service Pack 2, but for now we are limited to using some web parts that were designed for the previous version of SharePoint.
Because SharePoint 2007 has backward-compatibility support, we can use the web parts originally intended for SharePoint 2003 to browse reports and display them. One web part, called Report Explorer, displays a list of all reports in Reporting Services. Another web part, called Report Viewer, displays a single report. In order to install these web parts, we'll have to follow a process that worked in SharePoint 2003. This process involves installing a cabinet file using the STSADM.EXE command-line utility. I discuss SharePoint 2007 web parts in detail in Chapter 10, but I'll assume you have enough knowledge of web parts under SharePoint 2003 to follow this installation.
Follow these steps to install the Reporting Services web parts:
Log in to VSSQL as an administrator.
In the File Explorer, navigate to C:Program FilesMicrosoft SQL Server90ToolsReporting ServicesSharePoint. The web parts and supporting files are located here in a single CAB file.
Right-click the SharePoint folder and select Sharing and Security from the context menu.
Select the option to Share This Folder and click the OK button. You will access this folder from VSMOSS to install the web parts.
Log in to VSMOSS as a SharePoint administrator.
In the File Explorer, select Tools
In the Map Network Drive dialog, enter \VSSQLSharePoint in the Folder field and click the Finish button.
Navigate to the mapped drive, right-click the
RSWebParts.cabfile and copy it to the root of theC:drive so you can access it easily.Open a command window and navigate the directory C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12in.
Execute the following command line to install the web parts. Be sure that the
urlparameter is correct for your installation:STSADM.EXE -o addwppack -filename C:RSWebParts.cab -url http://vsmoss/sites/intranet
Open the intranet site and click the Report Center tab.
Select Edit Page from the Site Actions menu.
Click the Add a Web Part link in any zone.
In the Add Web Parts dialog, select the Report Explorer web part.
Select Modify Web Part from the Edit menu associated with the Report Explorer web part.
Enter
http://VSSQL/Reports/Pages/Folder.aspxin the Report Manager URL field.Click the OK button.
Click the Publish button to publish the modified page. You should now see the Report Explorer web part in the Report Center. Figure 8-13 shows the Report Explorer with several reports.
SQL Server 2005 Service Pack 2 promises to bring much tighter integration between SharePoint and Reporting Services. At this writing, no preview of the integration was available, but published reports indicate that after installing SP2 you will be able to configure Report Services in SharePoint Integration Mode. This mode will enable publishing, viewing, and managing reports through SharePoint. Unfortunately, there will be no migration path for existing report servers. Only new instances can integrate with SharePoint.
Reports will be published into SharePoint libraries and will have associated metadata like any content type. Additionally, versioning and workflow can be used with the reports. SharePoint will manage all of the security, and the report creation tools will be updated to work inside SharePoint. Some functionality such as subscriptions and scheduling will still reside in the report server. Plans seem to call for tight integration with the Report Center and web parts. Reports can be made part of any dashboard and web parts can be used to filter the data displayed in a report. By the time you read this book, a preview, or even a release, of SP2 will likely be available.
Dashboards in MOSS allow you to use a variety of data sources and presentation methods to display reports, information, and KPIs on a single page. These data sources can include SQL Server Analysis Services cubes, Excel spreadsheets, SharePoint lists, and manually entered values. In this exercise, you will create a dashboard that displays information from a cube presented in an Excel spreadsheet and a scorecard.
In this exercise, you will be creating a cube with data from the AdventureWorks database. However, you will not use the AdventureWorks database directly. Instead, you will use a data warehouse based on the AdventureWorks database. A sample data warehouse is available from the Microsoft site that you can use for this exercise.
Follow these steps to install the AdventureWorks data warehouse:
Log in to VSSQL as an administrator.
Open the browser and navigate to
http://go.microsoft.com/fwlink/?linkid=31046.Run the
AdventureWorksBI.msifile.Select Start
Connect to the default instance of SQL Server running on VSSQL.
In SQL Server Management Studio, right-click the Databases folder and select Attach from the context menu.
In the Attach Databases dialog, click the Add button.
In the Locate Database Files dialog, select the
AdventureWorksDW_Data.mdffile and click the OK button.In the Attach Databases dialog, click the OK button.
Close SQL Server Management Studio.
Once the AdventureWorks data warehouse is installed, you are ready to make a cube. The cube is created using the SQL Server Business Intelligence Development Studio, which is a development environment based on Visual Studio 2005. In this environment, you will make a new project, connect to the data warehouse, define a cube, and deploy it to Analysis Services.
Follow these steps to get started:
Log in to VSSQL as a local administrator.
Select Start
In the SQL Server Business Intelligence Development Studio, select File
In the New Project dialog, select Business Intelligence Projects from the Project Types list.
Select Analysis Services project from the Templates list view.
Name the new project AWCube and click the OK button.
In the Solution Explorer, right-click the Data Sources folder and select New Data Source from the context menu.
Creating a New Data Source
After the new project is created, you can connect to the AdventureWorks data warehouse. Creating a data source will allow the cube to be built based on the dimensions and facts contained in the data warehouse. You will also specify in this section what credentials to use when Analysis Services connects to the data warehouse.
Follow these steps to define a new data source:
When the Data Source wizard starts, click the Next button on the welcome screen.
On the Select How to Define the Connection screen, select the option labeled Create a Data Source Based on an Existing or New Connection.
Click the New button.
In the Connection Manager dialog, select VSSQL from the Server Name drop-down list.
Select AdventureWorksDW from the database drop-down list.
Click the OK button in the Connection Manager dialog.
Click the Next button in the Data Source wizard.
On the Impersonation Information screen, select the option labeled Use the Service Account and click the Next button. This will use the service account to connect to the data warehouse.
On the Completing the Wizard screen, click the Finish button.
Creating a New Data Source View
A data source view is a single view of selected information in the data warehouse. This single view will be used to create the cube. The view will contain dimensions and facts that will be part of the cube.
Follow these steps to define a new data source view:
In the Solution Explorer, right-click the Data Source Views folder and select New Data Source View from the context menu.
When the Data Source View wizard starts, click the Next button.
On the Select a Data Source screen, ensure AdventureWorksDW is selected and click the Next button.
On the Select Tables and View screen, double-click the
dbo.DimCustomer,dbo.DimGeography,dbo.DimProduct,dbo.DimTime, anddbo.FactInterrnetSalesto move them to the Included Objects list. Figure 8-14 shows how the dialog should appear.On the Select Tables and Views screen, click the Next button.
On the Completing the Wizard screen, click the Finish button.
In the Tables list, right-click the
DimCustomertable and select Properties from the context menu.Enter Customer in the
FriendlyNameproperty.In the Tables list, right-click the
DimGeographytable and select Properties from the context menu.Enter Geography in the
FriendlyNameproperty.In the Tables list, right-click the
DimProducttable and select Properties from the context menu.Enter Product in the
FriendlyNameproperty.In the Tables list, right-click the
DimTimetable and select Properties from the context menu.Enter Time in the
FriendlyNameproperty.In the Tables list, right-click the
FactInternetSalestable and select Properties from the context menu.Enter Internet Sales in the
FriendlyNameproperty.
Creating the Cube
The cube is created from the data view definition. Creating the cube is fairly simple when using the Cube wizard in the development environment. After the cube is defined, it can be deployed to Analysis Services where it will be ready for use in spreadsheets and scorecards.
Follow these steps to create a cube:
In the Solution Explorer, right-click the Cubes folder and select New Cube from the context menu.
When the Cube wizard starts, click the Next button on the welcome screen.
On the Select Build Method screen, accept the default settings by clicking the Next button.
On the Select Data Source View screen, select the view you created earlier and click the Next button. This will cause the wizard to scan for fact and dimension tables contained in the data warehouse.
On the Detecting Fact and Dimension Tables screen, click the Next button.
On the Identify Fact and Dimension Tables screen, select Time from the drop-down list labeled Time Dimension table.
Click the Next button.
On the Select Time Periods screen, map the Time Property Name column to the Time Table column according to Table 8-2.
On the Select Time Periods screen, click the Next button.
On the Select Measures screen, clear the Promotion Key, Currency Key, Sales Territory Key, and Revision Number, because these are not actually measures you want to use in your cube. Figure 8-15 shows the selected measures.
Click the Next button.
On the Detecting Hierarchies screen, click the Next button.
On the Review New Dimensions screen, expand the Products node and the Attributes node. Uncheck the Large Photo attribute, because this field is so large it will have a negative impact on performance.
Click the Next button.
On the Completing the Wizard screen, click the Finish button.
Select Build
Once the cube is deployed, you can browse it with Excel. Excel will allow you to create a data connection to the cube using an Office Data Connection file. In this section, you will create an Office Data Connection file and use it to build a PivotTable against the deployed cube. This will give you an idea of how cubes work with Excel; however, you will not save this spreadsheet. This is because you will later upload the Office Data Connection file to SharePoint and recreate the spreadsheet to be deployed through Excel Services, which will allow the spreadsheet to appear in a SharePoint dashboard.
Follow these steps to browse the cube:
Log in to VSMOSS as a SharePoint administrator.
Select Start
In Excel, click the Data tab.
Select From Other Sources
When the data connection wizard starts, enter VSSQL in the Server Name field and click the Next button.
In the Select Database and Table screen, select AWCube from the drop-down list and click the Next button.
On the Save Data Connection File and Finish screen, enter AWCube.odc in the File Name field.
Enter AdventureWorks Cube in the Friendly Name field.
Click the Finish button.
In the Import Data dialog, click the OK button.
In the PivotTable Field list pane, select Order Quantity and Sales Amount from the Internet Sales field list.
Select State Province Name - Geography from the Customer field list.
Select Model Name from the Product field list.
After browsing the data, close Excel. You do not have to save the spreadsheet because you will create a new one in the MOSS Report Center.
Once the Office Data Connection file is created, you can upload it to the data connection library associated with the Report Center. Connection files in the data connection library are trusted and can be used in spreadsheets and forms throughout SharePoint. Therefore, the best practice is to add connections to the data connection library rather than simply using them from a client machine.
Follow these steps to add the data connection file for the cube:
Open the home page of the intranet you created in Chapter 2.
Click the Reports tab to open the Report Center.
In the Report Center site, click the Data Connections link in the Quick Launch area.
In the data connection library, select New
On the Upload Document page, click the Browse button.
In the Choose File dialog, navigate to My DocumentMy data Sources and locate the
AWCube.odcfile.Select the
AWCube.odcfile and click the Open button.On the Upload Document page, click the OK button.
On the Data Connections page, click the Check-In button.
In the data connection library, select Approve/Reject from the drop-down menu associated with the
AWCube.odcfile.On the approval page, select the Approved option and click the OK button.
After you have added the Office Data Connection file to the data connection library, you can use it to create a spreadsheet that is delivered through Excel Services. This will allow you to deliver the PivotTable through a web browser. Additionally, delivering the spreadsheet through the browser will make it possible to display it in a dashboard in the Report Center.
Follow these steps to create a new report:
In the Report Center site, click the Reports link in the Quick Launch area.
In the Reports Library, select New
On the Reports Library page, enter AWReport in the Name field.
Enter AdventureWorks Report in the Title field.
Enter an owner for the report and click the OK button.
In the Reports Library, select Edit in Microsoft Office Excel from the drop-down menu associated with the new report.
In Microsoft Excel, click the Data tab.
In the Data ribbon, click the Connections button.
In the Workbook Connections dialog, click the Add button.
In the Existing Connections dialog, click the Browse for More button.
In the Select Data Source dialog, enter the URL for the data connection library in the Report Center (e.g.,
http://vsmoss/Reports/Data%20Connections).Click the Open button.
Select the
AWCube.odcfile and click the Open button.In the Workbook Connections dialog, click the Close button.
In the Data ribbon, click the Existing Connections button.
In the Existing Connections dialog, select the AdventureWorks Cube connection from the Connections in their Workbook section and click the Open button.
In the Import Data dialog, click the OK button.
In the PivotTable Field list pane, select Order Quantity and Sales Amount from the Internet Sales field list.
Select State Province Name - Geography from the Customer field list.
Select Model Name from the Product field list.
Save the spreadsheet and close Excel.
The Report Center contains a library for creating dashboards. These dashboards are a combination of Excel Services spreadsheets and scorecards. In this section, you will create a new dashboard that uses the Excel spreadsheet you created earlier and creates a scorecard that displays KPIs.
Follow these steps to create a dashboard:
In the Report Center site, click the Dashboards link in the Quick Launch area.
In the Reports Library, select New
On the New Dashboard page, enter AdventureWorks in the File Name field.
Enter AdventureWorks Dashboard in the Page Title field.
In the Layout list, select One Column Vertical Layout.
Click the OK button.
Adding a Report
Reports are added to dashboards from Excel spreadsheets that can be deployed via Excel Services. In this section, you'll add the spreadsheet that you created earlier.
Follow these steps to add the report:
After the AdventureWorks dashboard is created, click the link titled Click Here to Open the Tool Pane under the Select a Workbook section.
In the tool pane, click the ellipsis next to the Workbook field.
In the Select a Link dialog, double-click the Reports Library.
Select the
AWReportfile and click the OK button.In the tool pane, click the OK button.
Adding a KPI
KPIs are added to dashboards from any of several sources. You can add a KPI from a cube, a spreadsheet, or manually. In this section, you will add a KPI from the Excel spreadsheet you created earlier.
Follow these steps to add a KPI:
Select New
On the AdventureWorks KPI Definitions page, enter Alberta Sales in the Name field.
Click the Browse button next to the Workbook URL field.
In the Select a Link dialog, select the AWReport workbook and click the OK button.
Click the Select button next to the Cell Address field.
In the cell selection dialog, select the cell representing the total Alberta sales.
Click the Set button next to the field labeled Cell Address for Indicator Value.
Enter 35000 in the field labeled Cell Address for Indicator Goal.
Enter 20000 in the field labeled Cell Address for Indicator Warning.
Click the OK button.
On the AdventureWorks KPI Definitions page, click the OK button. Figure 8-16 shows the completed dashboard.
