Translator's description: Welcome to my blog: http://blog.9cbs.net/daidaoke2001/
The mistakes in the translation or improper look at it, this is also the greatest driving force I insist on the translation work.
My email: tangtaike@hotmail.com
For reprint, please notify us in advance.
Chapter 4 Transfer Components
In recent years, OLAP and data warehouse technology have spread and practical. OLAP already exists for a long time, but until it has been applied to the commercial sector recently. Today, you can't find an industry magazine that does not mention computer or information systems that do not mention OLAP or data warehouse technology. (If you don't know OLAP, please refer to "OLAP Brief Overview" section)
In many ways, the popularity of OLAP is not surprising. In the last 40 years, companies have become quite good at obtaining a lot of transaction information, and researching data in order to enable their core commercial systems to operate normally and develop new products. Once this information is obtained, it has become a valuable resource that can be used immediately - Many business people need to browse a variety of reports to help them decide towards market activities, sales performance, and production processes. But for commercial decision makers in these companies, unfortunately, these resources are always closely locked in a closed warehouse called the central IT department. The company's IT employee is busy with the normal operation of the company's core commercial system, usually there is no time to generate reports that countless people are eager to see, and the demand for these reports is often blurred and cannot be clearly defined. . Even if the IT department provides these reports, they are often very flexible. In fact, even if it is a very simple change, for example, it is only necessary to packet according to another field, so that IT department may need a new report - unfortunate, it will be a painful process. .
OLAP and data warehouse technology commitments can solve these problems to some extent, which allow IT organizations to pay attention to what they are best, and those who need to query data provide easy-to-use and powerful tools, so that they can generate any The data required for a paragraph. IT organizations will be committed to extracting, merging, and cleaning data; designing a Cube structure that meets business thinking modes; and develops a batch program that will load daily trading data into the OLAP server. Users can use the "cross-analysis" function to find answers to their specific questions - for example, how much is the average sales of each person in each state in my region? "Or," Compared with the same period last year How much is the sales of my products grow or decrease? "
The perspective table component is such a client tool that is often classified as "Business Intelligence" tool, which has easy-to-use and powerful features. Those who read comics "Star Bit Falun" may think that "business intelligence" mentions is self-contradictory (translator Note: The author means that business is impossible to intelligence), but this word is indeed It is possible to properly describe the functions of these tools. A large amount of data stored in the OLAP system is unused before being converted into information published in the report, which may become useful information of a person who develops business decisions. You can use a perspective table component to create a web-based powerful data analysis system that allows your users to sort, filter and re-packet data in a web browser. Many companies are studying the use of web browsers and corporate internal sites to deliver information to employees. However, static forms of data, and the same performance is limited as those with old print reports. Pivot table controls not only provide interactive data analysis capabilities, but also provide easy installation and upgrade. Easy distributed features are mainly due to the automatic code download mechanism of Microsoft IE, which can be used in all COM components on a web page. (We will discuss how to distribute in detail in Chapter 12.) Note
Although we have been discussing HTML and web pages, you should know that the perspective table control can not only run in the web page, but also run in Microsoft's VB page, and other control containers listed in Chapter 1. However, most of this chapter will only focus on how to use Pivot table controls on the web page, as many developers have discovered that users have great demand for this.
This chapter mainly tells the conceptual content of perspective meter components, and it is most useful to explain "why perspective report will be useful." Next, you will see various types of data that the perspective table control can receive. In addition, you can also read a brief overview of the technology of one of the key data sources of the perspective table assembly - OLAP. You will then learn the terms of each element in the perspective control, and understand how they are mapped to the underlying element of each type of data source. Finally, you will learn how to use perspective controls to add analysis capabilities for your solution.
This chapter will not completely tell how to create a perspective table in the design environment. Perspective table controls have a very comprehensive introduction to this issue; you can view it by clicking on the help button on the control toolbox. For related information in the Pivot table control in Microsoft Excel2000, you can view the help topic of Excel, which is: "About the Pivot table report for Microsoft Excel on the Web."
Chapter 1 Overview
The perspective table assembly can provide interactive data analysis capabilities to the table data source and OLAP data sources by combining perspective dynamic reporting functions and external data sequences (also called query tables). The output of the perspective table assembly is often referred to as a "crosstab" because it shows the sum of each type of intersection (total). For example, you can display each year on the line, displaying customer gender, such a cross-intended way to display sales information packets by product series. When using a table data source, the perspective table control can also display the detailed component of any combination value, and a normal list can also be used to display the entire table data.
When I talk about perspective control, I usually give a demonstration. Because trying to describe what it is like, it is always difficult to show its operational status than simple display. The technical details inside this control are very deeper, but its use is very natural and intuitive. Many business analysts can use this control very efficiently, but I don't know how it works. They only know that it can help them find answers to the question. Therefore, I recommend that you open the PivottableIntro.htm file under the CHAP04 folder on the book CD, use this example while using the function of this control. When you open this file, the example page will be shown in Figure 4-1. Figure 4-1. An example of a perspective table report.
The data source of this report comes from the OLAP service of Microsoft SQL Server. It contains a sales information of a fictitious grocery chain called Foodmart. I have exported this Cube to the Data / Sales.cub file on the book CD, so that you can use it even if you don't have an OLAP server on your machine; however, this Cube file is of course slower than the service-based Cube. some.
Example Page When initialization, the Pivot Table Control is configured to display reports from the total sales of various promotions issued by 1997 and 1998. Using this report, you can answer the question like this: "Which year is the most successful promotion of 1997?" Or "Which promotion is sold in the most amount of beverage products" for the first question, right click on 1997 Any number in that column, then select "Descending Arrangement" command, or select any number in that column in 1997, then click the descending button on the toolbar. The report will be reordered immediately and show the promotion of the highest sales volume in the first line, as shown in Figure 4-2.
Figure 4-2. A sorted perspective table report.
The first list is the sales of non-promotion activities, indicating that non-promotion has sold many more products than any promotion. The promotion of "Cash Forward Lottery" is second, but please pay attention to this promotion in 1998. This discovery may make the market supervisor why did not have this promotion in 1998, after all, it is the most successful promotion of 1997.
One of the most common analysis skills is to find more details of the data of interest. This is often referred to as "drill" in OLAP term. In fact, the perspective table control allows you to easily use this trick. For example, suppose a report shows the sales brought by the 1997 Cash Replacement Lottery Promotions, and you need to know if this promotion is particularly effective in a particular season. In other words, you need to understand the sales brought by this promotional activity in the 4 quarters of 1997. In order to show this details, you can double-click before the list of 1997, or click the plus sign on the left of the label. The report will be launched to show the 4 quarters of 1997, as well as the sales brought by the promotion every quarter, as shown in Figure 4-3.
Figure 4-3. A drilled perspective statement.
You can find that almost all sales happen in the first quarter, which indicates that this promotion is very popular when you have just launched, but with time, people will no longer be interested in it, this may be explained why The reason for this promotion is not conducted in 1998.
You can use the quick perspective interface on the page to quickly generate different sides of the data, but if you are browsing more complex reports, use the floating window named Pipetap Picture List (shown in Figure 4-4) Drag the way to the report.
Figure 4-4. Perspective table field list.
Figure 4-5. A complex perspective report.
Obviously perspective table components are very useful for sales analysis, but you can also use it to summarize any types of digital data across multiple categories. If combined with the chart components described in the previous chapter, it will become a powerful analysis tool. In Chapter 7, we will see a real example. Appendix: English
Chapter 4
The Pivottable Component
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. ")
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 Department Does Produce these Reports, The Reports Tend to Be Inflexible. in Fact, Requestin G A Simple Change Such AS Grouping Data by a Different Field Might Require The It Departments To Generate A New Report-Unfortunately, with a painfully solution 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 Upg rading. 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 coppealing.
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 Exce l Pivottable Reports on the web. "
Overview
Combining the PivotTable dynamic report and external data range (which is also known as query table) features in Excel, the PivotTable component provides interactive data analysis of both tabular and OLAP data sources. The output of the control is commonly referred to as a cross tabulation because it shows summary (aggregated) values for an intersection of categories. for example, you can display sales information grouped by product line within years down the rows, intersected with customer gender across the columns. When using a tabular data source, the PivotTable control can also show detail data rows for any aggregate value in addition to the summarized aggregates, or it can simply display all the tabular data in a flat list.Whenever I talk about the PivotTable control, I usually jump right into a demonstration. Trying to explain What It Does Is Infinitely Harder Than Simply Showing It in Action. The Technology Behind The Control Is Incredibly Abstract, But ITS Use is Actually Quite natural and intuitive. Many business analysts can use the control quite effectively but can not describe what it does with any degree of accuracy. They know only that it can help them obtain answers to their questions. For that reason, I encourage you to open the file PivottableIntro.htm from the chap04 folder on The Companion CD AND Experiment with it as i describe what this control can do .hen Opened, The Sample Page Looks Like Figure 4-1.
Figure 4-1. A Sample Pivottable Report.
The data source for this report is a sample cube that comes with Microsoft SQL Server OLAP Services. It contains sales information for a fictitious grocery chain named Foodmart. I have exported this cube to the Data / Sales.cub file on your companion CD so that you can use it without needing an OLAP server on your machine; however, this cube file is naturally slower than a server-based cube.The sample page initially configures the PivotTable control to display a report of the sales amounts attributed to various promotions offered in 1997 and 1998. Using this report, you can answer questions such as, "What was the most successful promotion in 1997?" or, "Which promotions helped to sell the most product in the Drink product family?" to answer the first question, right-click any number in the 1997 column and choose the Sort Descending command, or select any number in the 1997 column and click the Sort Descending toolbar button. The report immediately re-sorts to show the promotions with the HIGHEST SALES FIRST, AS Shown In Figure 4-2.
Figure 4-2. A sorted pivottable report.
The No Promotion item is first in the list, meaning that more products are bought in response to no promotion than any particular promotion. The Cash Register Lottery promotion is next, but notice that it was not run in 1998. This discovery might lead a marketing Manager to investigate, since it...............
One of the most typical analysis techniques is to ask for more detail about an interesting piece of data. In OLAP terminology, this is often called drilling down. In fact, the PivotTable control lets you easily perform this technique. For example, suppose that a report displays the sales attributed to the Cash Register Lottery promotion in 1997 and you want to know whether that promotion was more effective during a particular season. in other words, you want to know how the sales attributed to that promotion break down into the four quarters of 1997. to show the detail, double-click the 1997 column label or click the plus sign ( ) to the left of the label. The report expands to show the four quarters of 1997 and the sales amounts attributed to each, as Figure 4-3 illustrates.figure 4-3. A Drilled-Down Pivottable Report.
You can see That Almost All The Sales Occurred In The First Quarter, Indicating That The Promotion Was Popular When First Introduces Why It Was Not Run in 1998.
You can use the Quick Pivot interface on the page to quickly generate different cuts of the data, but to view more complex reports, drag items from the floating window called the PivotTable Field List (shown in Figure 4-4) to the report.
The field list displays all the totals and fields available in the data source. You can use this list to nest fields within each other, add more totals to the report, or put more fields in the filter area to restrict the data shown. For example , drag the Store field and drop it to the right of the list of promotion names to see how the promotion fared by country, state, and city. Next, drag the Sales Count total to the center of the report (where all the numbers are ) to see the quantity sold in addition to the dollar amount. Finally, drag the Gender field to the right of the Product field at the top of the report. Once you have dropped it, click the small drop-down button at the right of THE FIELD LABEL, Choose "M" to show Only The Sales Attributed to Men, And Click The Ok Button. The final report Should Look Like Figure 4-5.figure 4-4. The Pivottable Field List.
Figure 4-5. A Complex Pivottable Report.
The PivotTable component is obviously useful for sales analysis, but you can also use it to summarize any type of numeric data across many categories. When combined with the Chart component described in the previous chapter, it can be quite a powerful analysis tool. We will See A Real-World Example of this in chapter 7.
