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, the second section, how to deal with data
One of the most important and most complicated aspects of the perspective table component is how it interacts with various data sources, and how it operates in a session. This section explains how the perspective control is communicating with the data source, and how to transfer and operate data during the session.
The function of the perspective check control is an uncertain - because most of its features depend on the type of data source it is connected. It basically can only use two types of data sources: table column data and multidimensional data. (Multi-dimensional data sources can also be called OLAP data sources; I will use these two terms in this book.) We will also discuss the use of XML data to make data sources. Although XML data appears to be similar to any other table column data source for the perspective control, it still has some needs that require special discussion.
Table column data source
The OLE DB data source of any public data table is a list of tables. In general, they are domains that belong to the relational database engine. However, this category can also contain non-relational data providers - as long as they have some form of text command syntax or named table? ? ? .
Figure 4-6 shows the initial appearance of the data returned to a table column data source into the perspective table component. (You can also view this report by running the PivottableList.htm file under the CD04 folder.)
This report is similar to the report generated after the external data area in an Excel spreadsheet. However, because the perspective control combines the features of the external data area and Pipeline report, you can now create a new total of the data in this report and create a new total for any field. For example, using the "Move to Row Area", "Move to Column Area" and "Autocalc" toolbox buttons, you can convert this normal data list to the perspective report as shown in Figure 4-7.
Figure 4-6. A perspective report for data loaded with a table column data source.
Figure 4-7. Pipetable report created by a normal data list.
Because the data source is a table column data source, the perspective control can display the details of any statistics - this means you can expand any numbers and check each line that makes up this number. Figure 4-8 shows an overall structure of accessing the list of data sources.
Figure 4-8. Access the table column data source.
When extracting data, the perspective table control first is first connected to the OLE DB provider defined in the Provider property in the connection string. When you create a report in the design environment, you generally select the desired provider in a list of data connection properties dialogs. The provider is a host COM component in the process of hosting on the client machine, usually communicating with the data server using a private protocol (if there is a server). For example, the SQL Server provider communicates with the server with a variety of protocols, and the most common protocol is called a naming pipe. However, the provider of the Microsoft Jet database needs to access file access to the MDB file because the Jet database engine is not a client-server system.
When the perspective control is connected to the data source, it passes the content in its CommandText property to the provider. You can set the CommandText property in the Data Source segment in the Properties Toolbox in the design phase, or you can also use code when running. The perspective table control uses the ADO's RecordSet object to perform command text, so any value that can be passed to the RECORDSET can be used in the CommandText property. These values typically include SQL statements, table names, view names, or stored procedure names. After the provider is executed, return an OLE DB IROWSET interface so that the client can access the data returned by the execution command. When operating a list of data sources, the Poscope Control uses ADO to immediately load the returned data to a component called Microsoft Clearance Engine (WCE), and WCE is a component provided by Microsoft Data Access Components (MDAC), which provides Advanced traversal, sorting, and filtering functions on any data provider. WCE loads data from the data source provider into its own memory cache, if the data contained in the cache bes out of the memory cap of the memory it allows, these data will eventually be changed to the disk. (This prevents WCE from exhausting all of your available system memory.) When the data is loaded into the WCE, the perspective table control implements filtration, sorting, and traversing data sets by communicating with the WCE.
When you start packeting the result set according to a field, or when you use the steps similar to the previous introduction to the method to create a statistical value, the magic thing happened. In order to form a cross-form, the perspective table control uses another data pipe called "Pipetable Service Component". This component is actually a client provider of an OLAP service, but it can also create temporary CUBE on the client without a data source. When you group or create a statistical value based on the field, the perspective control is passed to the Perspective table service component to the Perspective of the Pivot table service component. This engine creates a temporary file under the temporary folder of the Microsoft Windows operating system, so if your company's policy is not allowed to create a temporary file in the web browser, then you should pay attention to this problem.
Named temporary Cube file
When implementing this feature, one of the star data developers of the perspective table component, David Worktendyke, must design a scheme for named a temporary Cube file so that it does not overwrite any existing files or affects another in other applications. A perspective table control. His final scheme is to make a file name using the current process and thread ID and the usual CUB extension.
Therefore, when you are packet and create a statistical value in the perspective control of the list of tables, if you see some famous files in your temporary folder, remember it is a temporary Cube file. Don't worry - These files will be automatically deleted when the control is destroyed.
When operating table column data, the perspective table control automatically generates two time layers for each date or date / time field in the detail data. The first layer includes group intervals of the year, quarter, month, and day; the other layer includes a group, weekly group interval. (These two layers are required because weekly cannot be formed in a month.) These automatic formation layers make it easy to analyze those data with time dimension, allowing you to see the data summary of each time interval.
If you plan to use a perspective check on a web page, you may need to study how to use Remote Data Services (RDS). This is another data access pipe provided in MDAC, which uses HTTP to access the data source. When using RDS, the client only requires the provider of RDS providers, which is installed with the Office Web component. The RDS provider then communicates through the web server and the actual data provider (eg, SQL Server), which makes the original data source provider only on the server. If you need more information about RDS, please refer to the content of the data access section on the Microsoft website at http://www.microsoft.com/data. Multidimensional data source
You are likely to be very familiar with the list of data sources or relational data sources, but you may not know the multi-dimensional (or OLAP) source. Before I introduce each element in the perspective table assembly, please give me a simple introduction to the concept of a multi-dimensional database.
Introduction to OLAP
In the relational database, the tables and relationships are the most important data structure and concepts, and you build a database by defining a table containing a column (or more column), primary keys, rules, etc. Then you build relationships between these tables by specifying the foreign key of this table (matching the primary key of other tables). The foreign key to specify the primary key of the other tables is established between these tables. Once these work is completed, you can perform SQL statements on the database engine, you can use associated, sort, restrictions, and grouping as needed to meet customer needs.
In the multidimensional database, the primary key data structure is a Cube, or more accurately, is a hypercube. This structure is a N-dimensional matrix to visualize it is a bit difficult. The items included in each dimension are called MEMBERS, and the NMBERS's intersection forms a number. Let us look at an example, let us feel less abstraction.
Suppose we model a company's sales data in a super cube. In our example, we start from 2D: products and customers. The two-dimensional structure is very easy to vary, as it looks like a rectangle, you may have seen this rectangle when comparing two-dimensional information, such as a crosstab. Figure 4-9 shows a rectangle possible look.
Figure 4-9. A two-dimensional database.
Note that the customer name is displayed in one dimension, and the product name is displayed in another dimension, and the number of the central area is sales. There will be a value for any product and customer, representing the sum of the amount consumed on this product on this product. Also note that there is an additional member called All in each dimension. This member represents the statistical value of all members of the current dimension (generally the sum of all members). Therefore, the intersection of Customers.all and a product represents the total sales of this product. Similarly, PRODUCTS.ALL and a customer's intersection represent the total consumption of this customer. The intersection of two All members is all customers, and the total sales of all products.
Now it is imagined to add a third dimension containing the sales staff's name to this rectangle. The structure will become a three-dimensional cube and is shown in Figure 4-10.
Now three coordinates - Customers, Products, and Salespeople - determine each intersection in cubes, or units. A member called ALL has appeared in the sales staff, which represents the total sales of all salespersons. This structure allows you to view data from multiple angles, helping you answer a variety of questions. Because these values are stored in the structure, the multi-dimensional database can quickly access any set of these units.
Figure 4-10. A three-dimensional database. It is difficult to visualize the four-dimensional structure, but you can envision you need to summarize additional data values. For example, you may not only need to understand the sales of the goods, but also need to understand the number of sales. These more values have created fourth dimensions containing two members (sales items and sales amount). These values are called Measures in the multi-dimensional database; however, most data sources are dimensions as dimensions. Figure 4-11 shows a method of visualizing four-dimensional data.
All data will still be stored in a four-dimensional super cube inside the cubic database. However, you can use a four-dimensional data structure as a plurality of three-dimensional cubes to understand the four-dimensional data structure. If you need to see a specific customer, product and sales of products, product sales, you should look at the first Cube. If you need to understand the number of products sales of the same intersection, you should view the second Cube. Of course, you can extend this example to display the table in the cube, as well as the Cube in Cube, but I will go here, otherwise you will be crazy when you try to visualize the 16-dimensional space.
Figure 4-11. A four-dimensional database.
Most multi-dimensional databases also allow you to group members included in a dimension, which implicitly specified parent elements and child elements in the group. In fact, each dimension defines one or more layers within them, each layer contains one or more Levels, each of which contains a series of members. This imitates most of the natural structure of most classified data - products usually belong to a related product group, and customers usually live in a city in a country in a country, and the sales staff belongs to a regional area in a region, and so on. For example, a customer-dimensionality may contain levels all, national, state, cities, and customer names. Members of the National Level may be the United States, Canada and Mexico, and the collection of members in state levels may be Washington, Oregon, Great Britain - Colombia, Albert, Harry, Wrakulus, etc.
A single dimension may contain multiple layers. For example, if you have an employee dimension, you may need to calculate the travel expenses based on the department structure so that you can understand the sum of each manager and departmental supervisor, or you may need to understand all employees who are engaged in a work function (for example, market, Sales, product development, or administrative personnel. The members of the dimension are the same (all employees), but they have been organized to different layers and therefore created different statistics.
Many books, magazines, reports, and a large number of discussions have been discussed in the multidimensional database. If you have already purchased a multi-dimensional database, you are likely that your database's subsequent documentation is more detailed than I introduced here.
How to interact with OLAP data sources
Pipeline assembly and OLAP data source communication and interaction, and it is similar to the way of interacting with the table column data source communication. Figure 4-12 shows a picture of an overall description of this structure.
Figure 4-12. Interaction between the perspective table assembly and an OLAP data source.
Pivot Table Controls use Microsoft defined OLE DB for OLAP standards, which is supported by many multi-dimensional databases. This model is an extension of the OLE DB standard, so the interactive mode between the perspective table control and the OLAP data provider is similar to the interaction between the table and the table data provider. The control first is connected to the data provider, which is also a host COM component on the process of hosting on the client machine. The provider decides how it communicates with the multi-dimensional database. For example, the OLAP service uses TCP / IP's socket connection between the client and the server.
After the perspective table component is connected to the OLAP data source, it can display all layers and sizes in a specified hypercube through the Pivot Table field list window. When the user drags the layers and sizes into the perspective control, or when the developer uses the code insert layer and size, the perspective table control is generated in the MDX (multi-dimensional expression, the inquiry language defined by the OLE DB for OLAP). Required query request and execute them on the data source. The final data provider returns the results of the query, and the perspective table control will appear on the screen. When operating the OLAP data source, the amount of data transmitted over the network is very small. OLAP providers typically only send MDX query strings to the server, and the server returns just the names you see on the interface. The server will only send a statistical value back to the client instead of creating the base data necessary for these total values. This allows the perspective control to respond quickly to the request, improve the scalability of the system, so that a large number of concurrent clients can be supported.
Should I create a Cube or just group the table column data?
When I showpan the perspective table component to group and summarize the table column data, it is like a report from Olap Cube, people often ask me, "So why do I have to create a Cube?"
The answer to this question is divided into two parts. First, use a prerequisite, server-based super cube, often enhanced performance than a temporary cube that creates a table column data through a perspective control. Whenever you packet a new field in a list of fields, the perspective table control must recreate Cube and regenerate all statistics. And a server-based Cube only creates these statistics, all clients accessing this Cube share these statistics.
Second, a pre-created Cube can use multiple levels to define all layers to create a clear path to drill in the data. When the Pivot table control is packet the relational data, it will only be created for the date field; it cannot know that the fields such as Country, State, and City are actually the three levels of the same layer. In a pre-created Cube, you can define these layers, making the user to easily find the information they need.
Xml
The perspective table assembly has a special data source, a URL that returns a specific format XML data. In the ADO2.1 version, Microsoft's data access group (group developed MDAC) defines a set of XML formats that save OLE DB. They also create a data access pipe called Persistence Provider (persistent provider), which can access a row of OLE DB by reading and writing XML data in this format. The perspective table control can be loaded using this provider to load the XML data returned from a URL to the Microsoft cursor engine, and Figure 4-13 depicts the structure in this case.
Figure 4-13. Use a permanent provider to load XML data into the WCE.
I will explain immediately if you want to use this method, you must pass the type of connection string to the perspective control. However, in order to start discussions, it is necessary to insert a paragraph. The most important information required by the perspective table control is the URL that can be obtained from which the XML data stream can be obtained. Pivot table control passes this URL to a persistent provider, and the persistent provider then uses Windows's Internet service requesting this URL return result. The results are then parsed and loaded into the WCE, while the perspective control is then started to process the result data - just like it processing the table column data.
This format of this XML data is specific, unfortunate, and this format corresponding document is not complete. However, the easiest way to see this format is to use the Save method of the ADO RecordSet object to save the contents of a RecordSet in an AdpersistXML format to a file. If you need to dynamically generate data in this format, for example, in a Microsoft ASP page, you can use the RECORDSET's Open method to test your output. If you can load your XML data into an ADO Recordset object, it will also be successfully loaded into the perspective control, because the control is used as the same mechanism as the ADO Recordset object. You can view the source code for the solution described in Chapter 6, where there is an example of XML data in the ASP page.附 附.
How The Pivottable Component Works with Data
One of the most important and complicated aspects of the PivotTable component is how it interacts with various data sources and how it manages that data during a session. This section will explain how the PivotTable control communicates with data sources, as well as how data is transferred And manipulated a session.
The PivotTable control is a bit schizophrenic-much of its capabilities depend on the kind of data source to which it is connected Essentially, the PivotTable control can use only two kinds of data sources:.. Tabular and multidimensional (Multidimensional data sources can also be called OLAP data sources; I will use these two terms interchangeably in this book) We'll also discuss using XML data as a data source Although XML data looks like any other tabular data source to the PivotTable control, it has a few requirements.. That Warrant Special Discussion.
Tabular Data Sources
Tabular databases include any existing OLE DB data sources that expose tables of data. Traditionally, these are the relational database engines of the world. However, this category can also include nonrelational data providers-as long as they have some form of textual command syntax or Named Tables.
Figure 4-6 shows how a report initially looks when the PivotTable component is loaded with data returned from a tabular data source. (You can also see this report by running the PivotTableList.htm file from the Chap04 folder on the companion CD.) The report is similar to that produced by using an external data range in an Excel spreadsheet. However, since the PivotTable control combines the functionality of external data ranges and PivotTable reports, you can now group the data by any field and create a new total for any Field. for Example, Using The Move To Row Area, Move To Column Area, And AutoCalc Toolbar Butt Of Data INTO The Pivottable Report Shown in Figure 4-7.
Figure 4-6. A Pivottable Report Filled with data from a Tabular Data Source.
Figure 4-7. A Pivottable Report Created from a flat list of data.
Because the data source is tabular, the PivotTable control can show the details behind any total-meaning you can expand any number and see the rows that contributed to it right in place. Figure 4-8 shows the general architecture for accessing tabular data sources.
Figure 4-8. Accessing Tabular Data Sources.
When retrieving data, the PivotTable control first connects to the OLE DB provider named in the provider attribute of the connection string. When building a report in a designer, you typically will choose this provider from a list in the Data Link Properties dialog box. The provider is an in-process COM component that resides on the client machine and typically communicates to the data server (if it is an actual server machine) through a private protocol. for example, the provider for SQL Server communicates to the server using a variety of protocols, the most common being named pipes. However, the provider for Microsoft Jet databases requires file access to the MDB file because the Jet database engine is not a client-server system.After the PivotTable control connects to the data source, it passes THE CONTENTS OF ITS Commandtext Property to The Provider for Execution. You Can Set The Commandtext Property At Design Time Using The Data Source Section of The Property Toolbox, or You CAN set it in code at runtime. The PivotTable control uses the ADO Recordset object to execute the command text, so any value that can be passed to the Recordset's Open method can be used in the CommandText property. These values typically include SQL statements or the name OF A TABLE, View, or Stored Procedure. The Provider Then Returns An Ole DB Irowset Interface That Allows Access To Data Returned from The Command.
Working with a Tabular Data Source, The Pivottable Control Uses
ADO
to immediately load the returned data into a component known as the Windows Cursor Engine (WCE). The WCE is a component provided in the Microsoft Data Access Components (MDAC), offering advanced scrolling, sorting, and filtering functionality over any data provider. The WCE loads the data from the source provider into its own memory cache, which will eventually page to disk if it contains more data than its memory threshold will allow. (This keeps the WCE from using all your available system memory.) After the data is loaded into the WCE, the PivotTable control communicates with it to filter, sort, and scroll around the data set.The magic begins when you start to group the resultset by a field or when you create a total using steps similar to those described earlier. To perform the cross tabulations, the PivotTable control employs another piece of data plumbing known as the PivotTable Services component. This component is actually the client-side provider for OLAP Services, but it also can provide temporary cube creation on the client regardless of the data source. When you group fields or create a total, the PivotTable control hands the PivotTable Services component a reference to the data set and describes what dimensions and totals it needs in the temporary cube. This engine will create a temporary file in the folder that serves as the temporary folder for Microsoft Windows, so beware of this requirement if your company's policy is to not allow controls in a web browser to create temporary files.
Naming the Temporary Cube
When implementing this feature, one of the star data developers of the PivotTable component, David Wortendyke, had to devise a scheme for naming the temporary cube so that it would not overwrite any existing file or interfere with PivotTable controls running in other applications. His eventual scheme was to construct the name of the file using the current process and thread ID and the traditional CUB extension.So when grouping and creating totals in a PivotTable control using a tabular source, if you see some crazily named file in your temporary folder, remember That it is a temporary cube filebook created by the pivottable control. DON 'ottable-these files will be de deteted Automatically When the control is destroyed.
When working with tabular data, the PivotTable control also will automatically generate two time hierarchies for each date or date / time field in the detail data One hierarchy contains the grouping intervals Year, Quarter, Month, and Day;. The other contains the intervals Year , Week, and Day. (Both hierarchies are necessary because weeks do not neatly roll up into months.) These automatic hierarchies make it easier to analyze data that has a time dimension, allowing you to see summary values for each of the intervals.
If you plan to use the PivotTable control on a web page, you might also want to investigate using Remote Data Services (RDS). This is another piece of data access plumbing provided in MDAC, which accesses data sources over HTTP. When using RDS, the only provider needed on the client machine is the RDS provider, which is installed with the Office Web Components. The RDS provider then communicates with the real data provider-for example, SQL Server-through a web server, allowing the native data source provider TO EXIST ONLY ON RDS, Consult The Data Access Portion of the Microsoft Web Site At http://www.microsoft.com/data.multidimensional (OLAP) Data Sources
While you are most likely familiar with tabular or relational data sources, you might not be as familiar with multidimensional (or OLAP) data sources. Before I describe how the different elements of the PivotTable component map to the structures of a multidimensional database, let me Give you a brief Introduction To Multidimensional Database Concepts.
A brief overview of OLAP
In a relational database, tables and relationships are the primary data structures and concepts. You construct databases by defining tables that contain one or more columns, a primary key, rules, and so on. Then you relate those tables to each other by specifying the foreign keys that match primary keys in other tables. Once this is done, you can execute a SQL statement against the database engine and it will join, sort, restrict, and group data as needed to fulfill the request.
In a multidimensional database, the primary data structure is a cube, or more precisely, a hypercube. This structure is an N-dimensional matrix, which is a bit hard to visualize. The items contained in each dimension are called members, and the intersection . of N members produces a number Looking at an example will help make this much less abstract.Imagine that we are modeling sales data for a company in this hypercube in our example, we will start with two dimensions:.. Products and Customers A two -dimensional structure is fairly easy to visualize because it looks like a matrix you might see for comparing two dimensions of information, such as a crosstab report. Figure 4-9 shows how this matrix might look.
Figures 4-9. A Two-Dimensional Database.
Note that customer names appear in one dimension, product names appear in the other, and the numbers in the center are sales. For any combination of product and customer, a value is stored representing the amount of money the customer spent on the product. Also note that one extra member, named All, appears in each dimension. This member represents the total for all members in that dimension (often a sum of all the members). So the intersection of Customers.All and a specific product represents the total sales for that product. Similarly, the intersection of products.All and a specific customer represents the total sales made to that customer. The intersection of the two All members is the grand sales total of all products to all customers.
Now Imagine Adding to this Matrix A Third Dimension That Contains SalesPerson Names. The Structure Becomes A Three-Dimensional Cube and Conceptual Looks Like Figure 4-10.
Three coordinates-a customer, a product, and a salesperson-now determine each intersection or cell in the cube. Again a member named All appears in the Salesperson dimension and symbolizes the total sales for all salespeople. This structure can help you answer a variety of questions by allowing you to view the data from a number of perspectives. Since these numbers are stored in the structure, the multidimensional database can return any set of these cells quickly.Figure 4-10. A three-dimensional database.
It is harder to visualize four dimensions, but suppose you want to summarize additional data values. For example, you might want to track quantity sold as well as the dollar value of items sold. These multiple values create a fourth dimension that has two members ( . Quantity Sold and Dollar Value of Items Sold) These data values are called measures in the multidimensional database; however, most data sources treat measures like any other dimension Figure 4-11 shows one way of visualizing four dimensions of data..
Internally, the multidimensional database will still store all the data in one, fourdimensional hypercube. But you can conceptualize a fourdimensional data structure by thinking of the fourth dimension as multiple three-dimensional cubes. If you want to see the dollar value of items sold for a given customer, product, and salesperson intersection, you would look at the first cube. If you want to know the quantity sold for the same intersection, you would look at the second cube. you could of course expand this example to show For the Tables of Cubes and Cubes of Cubes-But I Will Stop Before your Mind Explodes from visualize 16-Dimensional Space.
Figures 4-11. A Four-Dimensional Database.
Most multidimensional databases also let you group the members contained in a dimension, potentially specifying a parent and set of children for each member. In fact, dimensions have one or more hierarchies defined within them, and each hierarchy has one or more levels, each of which has a set of members. This mimics the natural structure of most categorical data-products typically fall into groups of related products, customers live in cities within states within countries, and salespeople belong to certain districts that belong to certain regions and so on. For Example, The Customers Dimension Might Have The Levels All, Country, State, City, And Customer Name. The Set of Members At The Country Level Might Beusa
,
Canada
, and
Mexico
, While the set of members for the state level might
Washington
,
Oregon
,
British columbia
,
Alberta
,
Jalisco
,
VeraCruz
And So on.
It is possible to have multiple hierarchies within a single dimension. For example, if you have an Employees dimension, you might want to calculate travel expenses along organizational lines to see totals for each manager and department head, or you might want to see the totals for all employees that perform a certain job function, such as marketing, sales, product development, or executive staff. The members of the dimension remain the same (the individual employees), but they are organized into different hierarchies and therefore create different totals.
A number of books, journals, reports, and pieces of documentation describe multidimensional databases in depth. If you have purchased a multidimensional database, chances are the documentation that came with your database explains these concepts in much more detail than I have time to do here .How The Pivottable Component Interacts with Olap Data Sources
...................................................
Figure 4-12. The Intertion Between The Pivottable Component and an Olap Data Source.
The PivotTable control uses the OLE DB for OLAP standard defined by Microsoft and supported by many multidimensional databases. This model is an extension of the OLE DB standard, so much of the PivotTable control's interaction with the OLAP data provider naturally is similar to how it interacts with tabular data providers. The control begins by connecting to the data provider, which again is an in-process COM component that resides on the client machine. The provider determines how it will communicate with the multidimensional database. For example, OLAP Servicesuses a TCP / IP socket connection Between the client and the server.
After the PivotTable component is connected to the OLAP data source, it can display all the hierarchies and measures in the specified hypercube through the PivotTable Field List window. As the user drags and drops hierarchies and measures to the PivotTable control, or as the developer inserts hierarchies and measures programmatically, the PivotTable control generates the necessary queries in MDX (Multidimensional Expressions, which is the query language defined by the OLE DB for OLAP specification) and executes them against the data source. The data provider returns the results, and the PivotTable control displays them onscreen.When working with an OLAP data source, the amount of data transmitted across the network is quite small. The OLAP provider typically sends only the MDX query string to the server, and the server returns only the cells and member names that You See OnScreen. The Server Sends Only The Aggregate Values Back to The Client, Rather Than All The Underlying Detail Data That...........................
SHOULD I MAKE A CUBE or JUST Group Tabular Data?
When I show people That The Pivottable Component Can Group and Total Tabular Data So That Looks Like A Report from an OLAP CUBE, They Offen Ask, "SO why shop i make a cube?"
The answer is twofold. First, using a precreated, server-based hypercube often yields drastic improvements in performance over using the PivotTable control to create temporary cubes of tabular data. Every time you group a new field in the tabular data, the PivotTable control must re-create the cube and reprocess all the aggregates. A server-based cube processes the aggregates only once and shares them with all the clients accessing the cube.Second, a precreated cube can define hierarchies with multiple levels that establish a clear drill-down . path through the data When the PivotTable control groups relational data, it creates hierarchies for date fields only; it can not know that fields such as Country, State, and City are actually three levels of the same hierarchy In a precreated cube, you can. Define these hierarchies and make it it is ing. .. ...............
Xml
One Special Data Source for the Pivottable Component Is A Url That Returns XML Data IN A Specific Format. In The
ADO
2.1 release, the Data Access Group at Microsoft (the group that makes MDAC) defined an XML format for persisting an OLE DB Rowset. They also built a piece of data access plumbing called the persistence provider, which can save and load an OLE DB Rowset by writing and reading XML data in this format. The PivotTable control is capable of using this provider to load the Windows Cursor Engine with XML data returned from a specified URL. Figure 4-13 depicts the architecture for this scenario.
Figure 4-13. Using the pen with xml data.
In a moment, I will explain what type of connection string you must pass to the PivotTable control to use this approach. However, for purposes of the discussion at hand, the important piece of information the PivotTable control needs is the URL from which it should retrieve the XML data stream. The PivotTable control hands this URL to the persistence provider, which in turn uses the Internet services of Windows to request the results of that URL. The results are parsed and loaded into the WCE, and the PivotTable control continues on -just as it would when working with tabular data.The format for this XML data is specific and unfortunately is not well documented. However, the easiest way to see what it looks like is to use the ADO Recordset object's Save method with the adPersistXML format To save the contents of a recordset to a file. If you want to generate XML Data IN this Format Dynamically-for Example, In A Microsoft Active Server Page our output. If you can load your XML data into an ADO Recordset object, it will load into the PivotTable control because the control uses the same mechanism. For an example of generating XML data from an ASP page, see the source code for the solution Discussed in chapter 6.
