Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Using Excel Services to Improve Data Retrieval Performance

Technical Case Study

Published: October 2011

Microsoft IT (MSIT) used Excel™ Services to improve the data retrieval performance in a reporting platform used by the finance department at Microsoft. By using Excel Services in the middle tier, MSIT was able to improve data retrieval performance by an average of 75 percent.

Download

Download Technical Case Study, 593 KB, Microsoft Word file

Situation

Solution

Benefits

Products & Technologies

Microsoft IT wanted to develop an Excel add-in to introduce consistency and reduce the duplication of effort caused by ad-hoc reporting across different business units within the finance organization. The organization also had a need to improve data retrieval performance for the finance users in all global regions who rely on the complex data that is often aggregated from distributed sources.

MSIT developed a custom Excel-based add-in to author and publish standard workbooks that were made available through a reporting catalog. Excel Services was leveraged as a middle tier to improve data refresh performance.

  • Eliminated the need for analysts to create separate versions of the same report manually
  • Excel add-in provided a common template that ensured the data analysis in the report could be shared consistently
  • Average data refresh performance gain of 75 percent
  • Microsoft Excel
  • Microsoft SharePoint
  • Excel Services
  • SQL Server Analysis Services

To support the different business groups at Microsoft, the finance department works with diverse and complex data that resides in multiple data warehouse systems. These systems collect and serve different types of data such as revenue, operational expenses, and organizational headcount. Reports created by financial analysts were pulling data from distributed sources, often duplicating efforts and producing inconsistency across the organization. Report refresh times were long, and working with the data sets to achieve specific views often required multiple report refreshes. These refreshes were particularly challenging for international users located far from the primary data center in Washington State.

In response to this challenge, MSIT developed a custom Excel-based add-in to author standard workbooks and used Excel Services as a middle tier to improve performance. Excel Services is a Microsoft™ SharePoint™ technology that makes it simple to use, share, secure, and manage Microsoft Excel workbooks as interactive reports in a consistent way throughout the enterprise.

This case study, intended for technical implementers and IT pros, shares some of the insight Microsoft gained while creating a custom add-in to provide users with standard Excel workbooks. It also describes the challenges Microsoft IT faced during the report refresh process and how they improved the performance using Excel Services in the middle tier. Finally, it shares implementation details and best practices that can be referenced by an organization that is considering a similar solution. It does not cover all of the features of the add-in nor should it be used as a deployment roadmap, because every organization is different and will have its own individual requirements.

Developing an Add-in to Provide Common Workbook Templates

Excel workbooks enable the finance groups to work more efficiently, minimize costs, and improve business productivity. Developing an add-in to provide common workbook templates eliminated the need for analysts to manually create separate versions of the same report, which was resource-intensive and costly, and sometimes yielded incongruent results.

Each workbook MSIT developed using the Excel add-in provided a common template that ensured the data analysis in the report could be shared consistently across the company.

The Excel workbook add-in was designed to retrieve data from SQL Server™ Analysis Services cubes, using Excel cube formulas. Because reports can have up to 100,000 individual cells, and formulas become exponentially larger when multiple parameters are added, MSIT determined that manually entering cube formulas in every cell was not feasible and could lead to potential errors.

Once the templates were developed, they were published through a SharePoint report catalog, as illustrated in Figure 1.

Figure 1. Sample view of Excel workbook templates in the report catalog

Figure 1. Sample view of Excel workbook templates in the report catalog

After the user downloads the workbooks and refreshes the data, the workbook connects to the data source using Windows security (NTLM) to determine the level of permissions they have in the source data.

The workbook adds Office data connection information at runtime based on the load-balancing service. When users refresh the report data, formula calculations are invoked via Excel object model APIs. In turn, Excel converts these cube formulas into multiple MDX queries and retrieves the data from the corresponding SQL Server Analysis Services servers as illustrated in Figure 2.

Figure 2. Refreshing report data

Figure 2. Refreshing report data

While the add-in provided a practical way to produce standard reports that enabled users to view consistent information, refresh on demand, and change filter values to make the report relevant to their business or organization, report refresh performance was initially suboptimal because:

  • The reports are very large, up to 100,000 cells in a single report.

  • Excel translates these cube formulas into multiple, chained MDX queries and executes in SQL Server Analysis Services.

  • The SQL Server Analysis Services database servers are in Redmond, WA, and users can be located anywhere around the world. Many of the geographically dispersed users have slower network speeds and higher latency, which also affected the performance of the reports.

  • No central caching was present as in Excel Services.

Improving Performance by Using Excel Services as a Middle Tier

To improve the performance, Microsoft IT used Excel Services as a middle tier between the client-side user workbooks and the SQL Server Analysis Services. After authoring the report, the add-in publishes the report in Excel Services.

When the user refreshes the report template, the add-in starts the communication with Excel Services via the web services rather than calculating the formulas directly in Excel.

Figure 3. Design with Excel Services

Figure 3. Design with Excel Services

The add-in opens the Excel Services session for the report, passes the parameters, and refreshes the data. User security is passed through Kerberos authentication and constrained delegation. Because the database server and the Excel Services server are in the same geographic location, the communication and performance between them is improved. After the refresh is complete, the add-in imports the calculated worksheet from the Excel Services into the user's report workbook.

Report Authoring Experience

To improve report creation by automating formula creation, report authors provide metadata about the report, and the Excel add-in generates the full matrix of cube formulas for the report. As illustrated in Figure 4, the add-in collects the report layout, parameters, and data sources that are entered by the report author and automates the formula generation.

Figure 4. Report filter properties

Figure 4. Report filter properties

The cube formulas in the report formula sheet refer these cells for connection and criteria information.

=IFERROR(CUBEVALUE('RoB SampleMD'!$I$17,'RoB
SampleMD'!$L$37,'RoB SampleMD'!$I$3,'RoB
SampleMD'!$I$4,'RoB SampleMD'!$I$12,'RoB
SampleMD'!$I$13,'RoB SampleMD'!$I$15,$B17),0)

Publishing Reports to Excel Service

The Excel add-in removes the publishing complexity for the report writer by automating the following steps:

  1. Exports the report worksheets, formulas, and metadata into a new workbook.

  2. Adds the ODC connections. XLRA queries a SharePoint list for all the possible servers for the report and adds a connection for each of them.

  3. Publishes the workbook to Excel Services.

  4. Stores the Excel Services workbook name and location in the report workbook.

Once a report is complete, its template is distributed to end users through the finance department's SharePoint portal.

User Report Refresh

When a user opens and refreshes a report, the add-in performs the following tasks:

  1. Updates the selected criteria values in the parameter range of the report workbook.

  2. Calls the load-balancing web service for the available server and populates the corresponding connection name in the parameter cell.

  3. Reads the Excel Services workbook name and URL and opens the Excel Services session.

    ExcelWebServiceRef.OpenWorkbook(WorkbookURL, "en-US", "en-US", out status);
  4. Switches to manual mode to start the calculation after all the parameters are updated.

    ExcelWebServiceRef.SetCalculationOptions(SessionId, ExcelWebServiceRef.WorkbookCalculation.Manual);
  5. Passes the parameter values to the Excel Services workbook.

    ExcelWebServiceRef.SetRange(SessionId, ReportMetaExcelSheet.Name, RangeCoordinates, ArrayOfValues);
  6. Calls Refresh().

    ExcelWebServiceRef.RefreshAsync(this.SessionId, null);
  7. Switches back to Automatic calculation mode.

    ExcelWebServiceRef.SetCalculationOptionsAsync(this.SessionId, ExcelWebServiceRef.WorkbookCalculation.Automatic);
  8. Calls Calculate().

    ExcelWebServiceRef.CalculateWorkbookAsync(this.SessionId, ExcelWebServiceRef.CalculateType.CalculateFull);
  9. After the calculate function is complete, it downloads the calculated worksheet and imports it into the report workbook.

    ExcelWebServiceRef.GetWorkbook(this.SessionId, ExcelWebServiceRef.WorkbookType.PublishedItemsSnapshot, out status);

Conclusion

End users were able to leverage Excel's rich client-side features, which in conjunction with Excel Services as the middle tier helped improve data retrieval performance. MSIT performed a comparative analysis of thousands of reports across the many regions and the availability of caching in Excel services significantly factored in measurable performance gains. MSIT was able to determine that, in addition to overall performance improvements, once the data was cached in Excel Services, refresh performance was improved by an average of 75%. The table below provides performance improvement metrics for a small representative sample of the reports MSIT analyzed.

Report

User location

Direct refresh

Excel Services

Performance improvement

Report 1

Denmark

0:06:28

0:01:37

75%

Report 2

Australia

0:02:01

0:00:51

58%

Report 3

China

0:06:34

0:00:39

90%

Report 4

Australia

0:12:38

0:01:44

86%

Report 5

Russia

0:05:19

0:00:57

82%

Report 6

Redmond

0:01:05

0:00:37

43%

For More Information

For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Order Centre at (800) 933-4750. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the web, go to:

www.microsoft.com

www.microsoft.com/technet/itshowcase

© 2011 Microsoft Corporation. All rights reserved.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, Excel, SharePoint, and SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.