Chapter 4 -- The PivotTable Component | Programming Microsoft Office 2000 Web Components (Microsoft Progamming Series)

[Previous] [Next]

Chapter 4

During the last few years, OLAP and data warehousing technology have exploded in popularity and practicality. OLAP has been around for quite some time, but it did not achieve a critical mass in the business world until recently. Today, you can hardly pick up a computer or information systems trade journal that does not mention OLAP or data warehousing. (For those of you not familiar with OLAP, see "A Brief Overview of OLAP.")

In many ways, the OLAP explosion is not a surprise. During the last four decades, corporations have become extremely adept at capturing large amounts of transactional and research data in order to run their core business systems and develop new products. Once that information has been captured, it's a valuable resource just waiting to be tapped—most businesspeople want to see summaries to help them make decisions about marketing campaigns, sales efforts, production processes, and so on. Unfortunately for the business decision makers in these corporations, this resource often is locked safely in that impenetrable vault known as the centralized IT department. The IT employees have their hands full keeping the core business systems running and usually do not have the time to generate the myriad desired reports, the requests for which are often vaguely phrased and poorly defined. If and when the IT department does produce these reports, the reports tend to be inflexible. In fact, requesting a simple change such as grouping data by a different field might require the IT department to generate a new report—unfortunately, with a painfully slow turnaround.

OLAP and data warehousing promise to alleviate some of these problems by letting IT groups concentrate on what they do best and by putting simple yet powerful tools in the hands of those with questions about the data, enabling them to generate any slice of data needed. The IT group can spend its time extracting, consolidating, and cleaning the data; designing a cube structure that matches the business's mental model; and developing batch programs to load daily transactional data into the OLAP servers. Users can "slice and dice" to find answers to their particular questions—for instance, "How much did we sell per capita for each state in my region?" or, "How much have the sales of my product increased or decreased compared to this time last year?"

The PivotTable component is one of those simple yet powerful client tools often categorized as "business intelligence" tools. While those who read the comic strip Dilbert on a regular basis might consider "business intelligence" an oxymoron, it's a good description of what these tools aim to deliver. The mass of data stored in an OLAP system is useless until it is translated into information presented in a report, which hopefully becomes useful knowledge to someone making a business decision. You can use the PivotTable component to build powerful, web-based data analysis systems that allow your users to sort, filter, and regroup data within the web browser. Many corporations are discovering that web browsers and intranet sites provide an easy way to convey information to employees. However, static representations of data are as limiting as those obsolete printed reports. Not only does the PivotTable control deliver interactive data analysis, but it also offers easy installation and upgrading. Much of the ease of deployment can be attributed to Microsoft Internet Explorer's automatic code download mechanism, which is used for all COM components on a web page. (We'll discuss deployment in greater detail in Chapter 12.)

NOTE
Despite all this talk of HTML and web pages, you should realize that the PivotTable control runs in Microsoft Visual Basic forms and the other control containers listed in Chapter 1 as well as it runs in web pages. However, most of the discussions in this chapter focus on using the PivotTable control on a web page simply because many developers find this immensely appealing.

This chapter will discuss the conceptual details of the PivotTable component, starting with an explanation of why a PivotTable report is useful. Next, you will see the various types of data that the PivotTable control can consume. Plus, you'll get a brief overview of OLAP technology, one of the key data sources for the PivotTable component. After that, you will learn about the terminology of the elements in the PivotTable control and see how they map to the underlying elements in each type of data source. Finally, you'll discover how to use the PivotTable control to add analysis features to your solution.

This chapter will not explicitly cover creating a PivotTable interactively in a designer. The PivotTable control's help file covers this topic quite thoroughly; you can view it by clicking the control's Help toolbar button. For information on publishing Microsoft Excel 2000 PivotTable reports to the PivotTable control, see the Excel help topic entitled, "About displaying Microsoft Excel PivotTable reports on the Web."