Use SQL Server 2000 Analysis Service in ASP. NET

Summary

Many organizations use Online Analytical Processing (OLAP) technology to analyze their key business data. OLAP-based data Drilling provides a way to query multi-dimensional datasets and obtain modes through drilling data. ASP. NET and Microsoft Office Web Components (owc) Enable Web-based OLAP reports. The owc control can be embedded on web pages andProgramYou can compile the notebook tTable and chart components of the script. The authors use ASP. NET, owc, and SQL Server 2000 Analysis Services to construct a Web-based OLAP Report Application to illustrate the use of the PivotTable and chart components.

More and more companies are using Online Analytical Processing (OLAP) and Data drilling technology to process their complex data. OLAP is a technology specifically used to process multi-dimensional datasets. The patterns and relationships between these data are not clear. Microsoft SQL Server 2000 analysis services is closely integrated with SQL Server 2000 and more closely integrated with SQL Server later versions, it is becoming a widely accepted choice for OLAP processing and data drilling.
Because of the wide application of OLAP technology, developers need to rely on OLAP-based multi-dimensional data sources to design and develop report design solutions. This is a complex project, because the OLAP-based report design or data drilling technology requires more flexibility and greater interactivity. Data drilling technology is defined as not static, but allows users to create their own views within the three-dimensional space of the constructed multi-dimensional dataset according to their needs. This is in stark contrast to the traditional relational database system (RDBMS)-based queries, which are subject to the relationships between defined tables. RDBMS queries are undoubtedly creative, but have certain limitations. With OLAP-based data structures, you can perform queries more flexibly. The traditional static report design is useless for querying and understanding OLAP-based data.
Software developers face four problems when designing an OLAP-based report design solution:
1. This solution must be able to connect to and query multi-dimensional data sources. 2. This solution must ensure flexible and convenient use of data;
Third, since data views that are important to users cannot be predicted, this solution must allow users to store customized report designs; fourth, this solution must provide a readable interface containing charts, because the report design containing charts is visually more effective.
Please allow me to add article 5: This solution should be Web-based, that is, it should be a server-based Web application that can be accessed through a browser (via Internet or Intranet ), what Should developers do with these five demanding requirements?
In fact, Microsoft provides a group of components called Office Web Components (owc) to help us build a complex web-based, user-friendly OLAP solution. Owc is a set of COM controls used to publish workbooks, pivot tables, and charts on the web ), it provides users with the same rich interactive experience as Excel. It also provides developers with rich programming interfaces. With these powerful and flexible interfaces, developers can design professional solutions. Figure 1 displays the owc PivotTable component, which can connect to the OLAP data source and use convenient drag-and-drop interfaces to perform query operations.

Figure 1 owc extends tTable component

The purpose of this article is to meet the requirements of developers, assuming they are using ASP. NET and Office Web Components (owc) to develop a customized report design solution for OLAP on the web. We will provide an XML-based Web Services integrated application architecture, and demonstrate how to establish the application architecture and configure OLAP data sources for access through http protocol. In addition, we will describe how to deploy this solution to users. Most Web applications are fully installed on web servers and do not require user interaction during application deployment, however, OLAP and owc have certain requirements for users who want to use your customized solution.

Important concepts and terms

specialized terms in OLAP technology are obstacles to understanding the true benefits. This document assumes that you are familiar with OLAP, especially SQL Server 2000 Analysis Services. Even so, before continuing the discussion, we should first clarify the important terms and concepts involved in this article and applied in the broad OLAP and data drilling fields.
first, it is important to distinguish OLAP from data drilling. OLAP-based data sources or data warehouses can be subdivided into one or more multidimensional datasets, which are a multidimensional data structure that can be queried using MDX. A multi-dimensional dataset contains a set of attributes called dimensions. dimensions can be roughly understood as fields in a data table. Different from fields in a data table, dimensions also contain a set of levels) hierarchy ). For example, a region dimension can be divided into quarterly, monthly, and weekly dimensions. A Multidimensional Dataset contains a set of measurement values (measure ). The measurement value is an actual value, generally a numeric value. For example, you can use the "retail" cube to view the sales status (measurement value) of each item by time (dimension) and location (dimension ).
SQL Server 2000 analysis services enables you to process OLAP through an MMC snap-in called Analysis Manager. This is just a pivot table interface integrated into analysis services. OLAP-based data is often viewed through a pivot table, it allows you to drag multiple dimensions and metric values to a layout similar to spreadsheet. The PivotTable control converts a drag-and-drop operation to an MDX query. Then, the MDX query is sent to the OLAP data source and the result set is returned.
Analysis Manager installs a PivotTable service on the database server, which contains an ole db provider that allows you to connect to OLAP data sources ). The provider is named Microsoft ole db provider for OLAP services 8.0. Without this provider, you cannot connect to the OLAP data source. The connection string used for this Provider includes the datasource attribute used for the data warehouse and the datamember attribute that represents the name of the multi-dimensional data set to be connected. The mongottable service must also be installed on the client. Otherwise, the mongottable control on the client cannot communicate with the OLAP data source.

Web-based OLAP Report Application Framework

Figure 2 shows the general framework of the Web-based OLAP report application. It consists of three methods, numbered in sequence to display the workflow of the program. They provide the following features:

The HTTP connection between the PivotTable control on the client and the OLAP data source.

Dynamically query OLAP data sources directly from the client's mongottable control (without any intermediate components)

Use XML Web Service to access customized PivotTable report data from a relational database structure

Figure 2 OLAP framework

XML plays a central role in the program. The owc javasttable control serializes its content locally to XML format. You can not only write data in XML format, but also use XML data to load the PivotTable control, as long as the XML data matches the XML schema. Therefore, ASP. NET developers can create XML-based Web Services for interactive access to the owc javasttable control. A web method generates formatted XML for the PivotTable control (pathway 1), and another method accepts serialized output from the PivotTable control and saves it to permanent media such as databases or files.
Once a connection is established between the mongottable and OLAP data sources, you can use the drag UI in the mongottable control to perform custom data query. When the control is updated, it dynamically generates a multi-dimensional expression (MDX) and sends the request to the OLAP data source (pathway 2) through the HTTP gateway ). This communication is independent of web servers, and the Web also directly accesses the database. In this structure, the role of the Web server is to establish an initial connection between the replicttable and OLAP data sources to ensure that the customized replicttable report is saved and retrieved.

Office Web Components

the functions provided by owc10.0 are not only powerful but also well known to users. Therefore, your customized solutions will soon become familiar with Excel users. Figure 3 is an overview of the owc control (version 10) delivered along with Office XP, note that the previous version of owc (Version 9) is released with Office 2000, and the programming interface and user interface provided by it are much inferior. Although the features we discussed do not all support previous versions, most owc (version 10) features apply to owc (Version 9 ).
when using the owc control in a web project, few resources are available to ASP. NET developers. In most online examples, Code assumes that you use the client script language (such as VBScript) to interact with the owc control. Although this is a feasible method, it ignores the possibility of using more powerful ASP. NET functions. Create a server-side owc instance, establish a connection, set properties, and use this instance to generate XML data for a separate consumer tTable control client instance. The programming logic is included in OLAP data access objects. For some architectures, see pathway 1 in Figure 2. XML data is then transmitted to the client component through the XML-based Web service. See pathway 1.
figure 4 lists the owc controls we will use in this article, namely, the PivotTable and chart controls. The spreadsheet and Objective C controls are not listed here because they do not involve the architecture we discuss here. Figure 5 and figure 6 respectively describe the programming interfaces of the PivotTable and chart controls.
javasttable has a complex interface, but the most important of which is the xmldata attribute. This attribute is the XML Representation of the entire receivttable content, including the connection information to the OLAP data source. Xmldata also saves the dimension and measurement values of the current view and the formatting information of the application. Xmldata can be set and obtained. It is an important means to dynamically customize the PivotTable control.

Configure OLAP data sources for HTTP access

Before we begin to build our web-based OLAP report design program, we must configure the analysis services data source so that IIS can access it. The IIS web site and analysis services must be installed on the same server, and ensure that the IIS web site contains a file named msolap. asp. This file provides a bridge between http port requests and analysis services data sources. Msolap. asp is stored in the \ Program Files \ Microsoft Analysis Services \ bin directory. We recommend that you use the Secure Socket Layer (SSL) authentication mechanism to configure a secure connection (https) to access the data source. Therefore, you must purchase an SSL Certificate for the analysis services server. Of course, we can also use non-secure (HTTP) connection to access the data source, but we do not recommend this.
Once IIS is installed on the analysis services server, configure a new web site for HTTPS access. First, create a web site directory outside the default web root directory (\ Inetpub \ wwwroot. For security purposes, we recommend that you stop the default web site. In addition, if you have installed SSL authentication, You need to disable port 80 on the firewall and open port 443.
Next, set msolap. copy the ASP file from the \ Program Files \ Microsoft Analysis Services \ bin directory to the new web site directory. Use the IIS manager to configure the new web site directory as a web application, add the Host header name (for example, olap.mycompany.com) to the web site ).
Third, purchase and install SSL authentication on the server. During the purchase and authentication process, make sure that the web site can be accessed through port 80. After the authentication is completed, disable port 80 on the firewall and open port 443. Use the IIS manager to set web directory security to prohibit anonymous access and allow basic authentication. Finally, reset IIS to accept the changes.
After HTTPS is created, you must update the connection string pointing to the OLAP data source. The format of the connection string is as follows:

Provider = msolap; user id = domain \ USERNAME; Password = password; Data
Source = https: // SERVERNAME; initial catalog = olapdatasourcename
 

For more information, see the knowledge base.Article(Knowledge Base) q279489:"How to connect to Analysis Server 2000 by using HTTP Connection".

Secure access to OLAP

Once the OLAP data source can be accessed through https, Security Trust is indispensable. Because now anyone who can browse the website may access the data source on the website. For example, If a remote user knows the URL of the analysis server and the name of the initial catalog, he can open the Excel file and connect the external tTable file to the OLAP data source.
Analysis Services implements Multidimensional Dataset security rules based on Windows NT user accounts. The basic idea is to create one or more local Windows NT user accounts on the server and assign them SQL Server database roles. Then, assign the database role to a cube role and bind the database role to a specific cube. The multi-dimensional dataset role inherits the Windows NT user ID and password. You can add the user ID and password to the OLAP data source connection string.
It is worth noting that an OLAP data source may contain multiple cubes. You can connect to a specific cube by setting the <datamember> xml tag value in the owc PivotTable control to a valid multi-dimensional dataset name (explained in the next section. This cube must be assigned a role that corresponds to the trust in the OLAP data source connection string.

Create an application

Now we are using ASP. NET and owc 10 to create an application. The User Interface of this application is included in a web form named owc10.aspx, as shown in figure 7. The Interface contains an embedded client owc javasttable control and a set of buttons, which are used to activate OLAP data source connection, save and obtain customized reports. These functions are provided directly by the XML Web Service in wsolap. asmx through a set of web methods. The OLAP. js script file contains a set of client-side JavaScript Functions. the user interface uses these functions to hook up with the preceding web methods. These JavaScript Functions delegate most requests to Web services by calling these methods on the client. The preceding functions are implemented in a DHTML behavior file WebService. HTC. In the OLAP. js file, the user interface calls the preceding web method using a set of client-side JavaScript Functions. Access the web service. These are all done in a DHTML behavior file WebService. HTC.

Figure 7 Web-based OLAP report design program

This example program is called olapreport. It is developed using Visual Studio. NET 1.1 and uses foodmart 2000 data warehouse as its OLAP data source, and uses the sales Cube as its data member. Foodmart 2000 is an example database installed with SQL Server 2000 Analysis Services.
If you have downloaded and installed the example project file, you must complete the following steps to ensure its normal operation. First, change the ole db connection string and the application will use it to access the OLAP data source. The connection string is stored in the web. config file as the custom settings of the application.

<Deleetask>
<Add key = "olapconnectionstring" value = "provider = msolap.2 ";
Data Source = https: // SERVERNAME; initial catalog = foodmart 2000>
</Appsettings>
 

Note that this connection string does not contain Security Trust (such as user ID and password). These trust is required only when security rules are implemented in Analysis Server. As described above, we encourage you to add security rules. Once you are ready, simply add this trust to the end of the connection string.
To make the connection string work, do not forget to use HTTP to access the Analysis Server. The configuration steps are also described before this article. The project uses http: // localhost/OLAP as its data source. Therefore, you need to create a virtual directory named OLAP under the default web site and set msolap. copy the ASP file to the virtual directory. Simply put, you can replace the data source value with your machine name, omitting the leading https: // part. This will allow connections to standard OLAP data sources, rather than HTTP connections. This can be done for demonstration purposes. However, if you do not enable HTTP access, you cannot expose the Analysis Server to users outside the network.
Finally, you need to set reference to the owc10 COM component in the olapreport project before proceeding to the network. (usually, the setup project is responsible for this, but it is always useful to know this step ). The procedure is as follows: Open Solution Explorer, right-click the project icon, and select Add reference from the pop-up menu. After the dialog box appears, switch to the com tag and find the component named Microsoft Office XP Web Components (owc10.dll. Click Select and OK. Visual Studio. NET automatically generates a series of packaging files for this component and adds the owc10 project reference.

Connect controls and data sources

The three features of the application are now implemented in sequence. First, we will discuss pathway 1-how to establish a connection between the owc control of the client and the OLAP data source. Use the set connection button on the user interface to implement this function. This button calls the JavaScript function initializepivottable and uses the Multidimensional Dataset name as the parameter. In this example, the sales cube is used.
Javascript then calls the initializepivottablexml web method (implemented in the DHTML behavior called initializepivottablexml web, see the above content ). This web method connects to the OLAP data source and generates the xmldata of the receivttable control. Its output is processed by the oninitializepivottableresult JavaScript function.Figure 8Is JavaScript code, whileFigure 9Is the code of the web method. The code in these two examples is not only simple, but also reusable. Through simple steps, we can use these functions to generate custom xmldata on the Web server and transmit it to the client's retrittable.
Second, when establishing a connection, we effectively use the blank Report of the sales cube to initialize the owc control. The blank report fills the field list with field information and measurement values in the multi-dimensional dataset. It drag and drop fields into the row and column fields and drag measures to the totals field, you can create custom reports in the notebook tTable. Each time you drag and drop a field to the PivotTable, the latter sends a query request to the data source, and then obtains more query results from the data source. Data exchange is managed independently by the web service. It is used only when the control establishes an initial connection with the data source. Figure 2 shows the data exchange channel pathway 2.

Dynamic generation of customized PivotTable reports

Now let's program a customized report to go deep into the PivotTable object model. The custom report we want to create allows users to select two cities and compare the store sales of drinks product family. We use the same method as pathway 1, which means we call the web method to generate xmldata that describes the report. Then, we assign the xmldata returned by the web method to the xmldata attribute of the control. The interface provides two drop-down lists of cities and a load M Report button (see figure 7 ). This button activates a JavaScript function named loadcustomreport, which accepts two cities from the web form as input parameters and then calls the loadcustompivottablereport web method to generate xmldata for Custom reports. Finally, a JavaScript processing function accepts xmldata from the web method and assigns the xmldata attribute to the embedded javastcontrol.Figure 10Is the code of the web method.
The loadcustompivottablereport method uses a large number of optional tTable attributes (see figure 5 ). Note the following:

Use the insertfieldset method to insert a suitable pivotfieldset in the row and column axes.

Use the includedmembers attribute to differentiate the distinct tfields in the table. The value of the includedmembers attribute is an array of variables, corresponding to the object array in. net.

The isincluded attribute allows or disables the specified effectfield in the effectfieldset.

The inserttotal method inserts an appropriate measurement value into the totals mesh.

The numberformat attribute defines the format of the metric value. This property accepts the format code (such as "0. ###") or predefined format, such as "currency" or "percent"

Save and obtain customized reports

Since we have already gone through the report generation process, we do not want to repeat this job every time a new report is generated. Fortunately, the data used to copy reports is stored in the xmldata attribute. Therefore, it is easy to save a report. You only need to save the xmldata attribute to a permanent storage medium, such as a relational database. Loading a report is just as simple as obtaining the saved xmldata and re-binding it to the mongottable. This feature is shown in pathway 3 in Figure 2.
The Web Service of an application can be used to save and obtain the xmldata of a custom report. In this example, we save xmldata in a text file named olapreport1.xml. The program interface provides two buttons: Save Report and load saved report. The Save Report button calls JavaScript to send the xmldata Of The PivotTable control to the following web method:

 
Function savereport (){
// Purpose: Call web service method to save the report
VaR strreportxmldata = frm1.20.ttable1. xmldata;
VaR icallid = service. svcolap. callservice (onsavereportresult,
''Savereport '', strreportxmldata, ''olapreport1. xml
}
 

This web method is followed by writing xmldata into a file. The load saved report button calls a JavaScript function to complete reverse work, as shown in Figure 11. This function calls a web method to open a text file, obtain xmldata, and then return it to bind it to the PivotTable control.

Use owc events

Owc 10 provides several useful events for our use in client event processing. You can use VBscript or JavaScript to write event handlers. The event processing format in VBScript is as follows:

 
Sub <objectname >_< eventname>
 

The syntax in Javascript is as follows:

 
<Script language = "JavaScript" event = "eventname" for = "objectname">
 

The example program demonstrates how to capture query and commandexecute events. For event processing code, see Figure 12. When a query event is activated, the program fills in the event log in the left-side text box on the web page. After the commandexecute event is activated, we first check whether it is a refresh command. If yes, the program fills in the event date and Event Log in the text box.

 Bind a chart to the PivotTable control

In most cases, graphical data representation is very valuable for user testing. Fortunately, owc provides the chartspace control, which provides graphical functions. To improve customized reports, we use a bar chart to represent data. Although this seems to be an arduous task, the chartspace control can greatly help us solve this problem. One of the useful features of the chartspace control is that it can bind another control, such as a PivotTable to its data source. It can also display the current PivotTable view in graphic format. The final result is that the changes in the PivotTable view are automatically displayed in the chartspace control.
In this example, after a customized PivotTable report is generated, you only need to assign the PivotTable value to the datasource attribute of the chartspace control.

Frm1.chartspace1. Clear ();
Frm1.chartspace1. datasource = frm1.20.ttable1;

// Set the chart type to 3 dbar.
Frm1.chartspace1. Charts (0). type = 50;
 

Call the clear method before assigning values to the datasource property to clear previous chart instances. The chartspace control provides many custom attributes, including the type attribute. It accepts a chartcharttypeenum value to define the chart format.

 Create custom Group

Finally, let's take a look at owc 10's most important new feature-the function of creating custom groups. Currently, our reports group sales metric values based on the time field. In this example, the time is grouped by year, month, or day. If you do not want to use the grouping mode defined in the Multidimensional Dataset in custom reports, you can use the custom grouping function in the owc 10 control. The apply M grouping button calls a JavaScript function, which calls the applycustomgrouping web method. The input parameter is the xmldata of the currently loaded project. This web method loads a PivotTable control and calls the addcustomgroupfield method to define a custom group. In this example, it takes half a year to group. For the code, see Figure 13.
Note that we add a custom group ("customgroup1") with two members ("1 sthalf" and "2 ndhalf") to cover the entire year. Call the addcustomgroupmember method to add the preceding custom members. When the function is executed on the web page, the chartspace control can automatically reflect the custom grouping function to the bar chart table-this is also an advantage of using Office Web Components 10.
There is also a problem related to custom groups. Xmldata does not store data related to custom group names. If you save xmldata (as previously executed by the Save Report function), then load the stored xmldata to the saved tTable, and customize the title bar of the group ("1 sthalf", "2 ndhalf ") it is displayed in guids format. Despite this, the owc 10 component has been greatly improved compared with previous versions, and thus becomes a very effective tool for designing web-based OLAP reports.

 HTTP client requirements

Since we have built a Web-based OLAP report design program, to use this program, each Web user must perform the following configuration. 1. Each web user must install Office XP on his/her computer. Or, he must have the right to access the Office XP authorized on a server, because the owc component is released together with Office XP.
2. Each web user must use Internet Explorer 5.0 or later. Because web programs use Web Service DHTML behavior, they must be supported by Internet Explorer 5.0 or later. In addition, the owc component of the client is a Microsoft COM component and can only run in a Microsoft browser. (We also know that some other browsers also provide plug-ins that support the COM control on the client, but we have not tested owc in these browsers ).
3. Each web user must correctly install the ole db provider, especially Microsoft ole db provider for OLAP services 8.0 or later. This supplier is installed with distributed release tTable services, which includes a set of DLL files and requires support from Microsoft Data Access Components (MDAC. PivotTable service can be installed from the \ msolap \ install \ PTS directory of the SQL Server 2000 CD-ROM. You can choose to install ptslite.exe or ptsfull.exe. The latter installs the tTable service and MDAC, while the former only installs the tTable service. If you are not sure which version you need, install ptsfull.exe. Because only Microsoft Analysis Services can automatically install the service, you also need to install the PivotTable service on each Web Client. You can use the Excel external tTable Wizard to check whether the external tTable service has been installed on the Web Client.
4. Each Web Client must add the web domain of the application to the trusted site list of its IE. Because the application resides in a different domain than the client, the owc javasttable control must access the OLAP data source through cross-origin each time. Therefore, the web domain of the program must be placed in the trusted site of the client. The specific configuration method is as follows: Open ie -- Click Tools | options -- Select security label -- click trusted icon -- click sites.
You can also take another approach: provide a registration file for the customer to automatically update the customer's trusted site. The following is an example of a registration key:

 
[HKEY_CURRENT_USER \ Software \ Microsoft \ Windows \ CurrentVersion \ Internet
Settings \ zonemap \ Domains \ mycompany.com] "HTTPS" = DWORD: 00000002
 

Note: The registration file must run on different users on the same client separately, because it only updates the registration key value under HKEY_CURRENT_USER. For example, if two domain users share the same host, they must log in with their respective accounts and run the registration files respectively.