| In order to execute the sample application included on the book's Web site at http://www.awprofessional.com/title/0321246268, you will need to install files needed for communicating with Analysis Services. You will also need to apply certain database changes utilizing a SQL script, available for download from the book's Web site. Communicating with Analysis Services Old Method Analysis Services, bundled with SQL Server 2000, was released prior to Visual Studio.NET and therefore is not natively supported by the .NET framework. Initially, communication with Analysis Services and .NET was done using COM integration and the Microsoft Active Data Objects Multidimensional (ADOMD) library. To do this, you had to add a COM reference to the ADOMD library. Unfortunately, this sometimes introduced problems and limitations to the process of communicating with Analysis Services. One such problem involved the use of C# with ADOMD. Since C# does not support omitting parameters, as Visual Basic does, you would be forced to set values for all parameters. If the potential values for these parameters changed during releases of ADOMD, this could cause your program to behave improperly. New Method The ADOMD.NET SDK was introduced as a means of consistently communicating with multidimensional sources of data like Analysis Services. It is a standard .NET data provider that utilizes interfaces from the System.Data and System.Data.Common namespaces. It offers an object model similar to the one used by ADO.NET. For instance, it uses a connection object to connect with the data source. It then has a command object for issuing commands and other objects to store the results of data queries. Tip Readers interested in learning more about Analysis Services may want to refer to the following Web site, which is hosted by the development lead for the Microsoft Analysis Services engine, Mosha Pasumansky: http://www.mosha.com/msolap This site contains many links to technical information involving current and upcoming releases of SQL Server.
It communicates with standard data sources over the Web using the XML for Analysis specification 1.1. Multidimensional data can be queried and returned in an object similar to the ADO.NET DataReader. Connections are made using ADOMDConnection objects, and query parameters are passed using ADOMDCommand objects. ADOMD.NET also offers an object known as the CellSet that is similar to the ADO.NET dataset object. Refer to Table 6.1 for a listing of the main objects offered by ADOMD.NET. There are essentially three ways of retrieving data: Table 6.1. The topmost objects used in the ADOMD.NET SDK. This SDK was designed specifically for communicating with multidimensional data sources and Visual Studio.NET.Object Name | Description |
|---|
ADOMDConnection | Represents a connection to a multidimensional data source. This can include a data cube or a processed mining model. It will accept a connection string similar to the one used to connect to a SQL Server database. The difference is that ADOMD.NET uses the XML for Analysis specification which is optimized for the Web. A valid connection string used to communicate with the SavingsMart database defined on Analysis Server is, "Provider=MSOLAP;Data Source=http://localhost/xmla/msxisapi.dll;InitialCatalog=SavingsMart;" | ADOMDCommand | Used to specify an mdx statement or query to run against Analysis Services. It operates similarly to the command object used by ADO.NET and accepts parameters. It can be used to return CellSet, DataReader, or XMLReader objects. It can also be used to execute NonQuery and Scalar methods. | ADOMDDataReader | Just as with ADO.NET, the DataReader object is the fastest and most efficient way of returning data. It returns data using either the Execute or ExecuteReader method of the command object. | CellSet | A CellSet is a multidimensional result set that runs across more than one dimension or axis. This is necessary when returning data from a multidimensional (mdx) query of a data cube. A CellSet is disconnected and useful for caching purposes, but is also the most resource intensive. |
Of the three methods, the XMLReader, which returns raw XML, is the least resource intensive. In order to reproduce results using the sample application for this chapter, you will need to install both the XML for Analysis specification 1.1 and the ADOMD.NET SDK. To do this, perform the following steps: 1. | If you have not already installed the Microsoft XML Parser (MSXML) 4.0 or later, you must install it first by executing the msxml.msi file, available for download from the Microsoft Web site.
| 2. | Initiate installation for the XML for Analysis specification 1.1 by executing the file named XMLASDK.exe, available for download from the Microsoft Web site.
| 3. | Installing the ADOMD.NET SDK 8.0, the last step, is accomplished by executing the Setup.exe file, also available for download from the Microsoft Web site.
|
Tip When installing the XML for Analysis SDK, make sure you check the Enable HTTP Unsecure checkbox. By default the XML for Analysis Service Provider will attempt to use a secure HTTPS connection with Secure Sockets Layer (SSL).
Applying Database Changes In addition to installing the SDK, you will need to apply database changes in order to execute the sample code for this chapter. This is done by opening Query Analyzer and executing the SQL script named DBChanges.sql file, available for download from the book Web site. This script will add four new stored procedures and three new tables to the SavingsMart database. Refer to Table 6.2 for a listing of these changes. Table 6.2. Database changes included in the DBChanges.sql file available on the book's Web site. These changes will allow us to execute the code in the LoadSampleData project included with this chapter. Note that each time a database restore is performed, the DBChanges.sql script will also need to be executed.Object Name | Description |
|---|
ShipmentSchedules | Table that stores values attained from querying the mining model Analyze Shipments. This represents the predicted days between shipments and quantity for each vendor and store. | ProductAvailabilityTracking | Table written to during execution of the revised GeneratePurchases routine. A record will be written to it every time an attempt is made to purchase a product not available. This table will allow management to review the success of applying the mining-model predictions. | ServiceParms | Table used by the Windows service named Windows-Svc. Only one record will be stored in this table, and it will be used by the Windows service to identify when the mining model was last processed. It is possible to alter the code not to use this table and instead select a different method for scheduling the Windows service. | GetStoresAndVendors | Stored procedure that returns one result set containing all stores and another containing all vendors. This will be used to populate a data set used by the PopulateShipmentSchedulesTable routine. | InsertShipmentSchedule | Stored procedure used to insert records into the newly created ShipmentSchedules table. | CheckShipmentSchedule | Stored procedure executed by the revised Generate-Purchases routine. It is used to determine whether an order needs to be generated for the store and date being processed. It compares the value of Days-BetweenShipments in the ShipmentSchedules table against the last time an order was processed for each vendor of that store. If the number of days exceeds the predicted value, an order is generated for all products of that vendor. | InsertPurchaseDetailNew | Stored procedure similar to the InsertPurchaseDetail stored procedure used in the LoadSampleData project from Chapter 5. This new stored procedure is smaller because it does not need to check threshold values on every purchase. Instead it just checks to see if the product is available at that store and if it is not, it writes a record to the newly created ProductAvailabilityTracking table. |
Note Readers who did not build the SavingsMart database in Chapter 5 must first attach a database file available for download from the book's Web site. The Version1database files are named SavingsMart.mdf and SavingsMart.ldf. This file contains data generated for all five stores in the year 2001. 1. | Copy the SavingsMart.mdf and SavingsMart.ldf files from the book's Web site to a local directory on the server where Microsoft SQL Server is installed.
| 2. | Open SQL Server's Enterprise Manager.
| 3. | Right-click the Databases node and click All Tasks. . . and Attach Database.
| 4. | From the Attach Database dialog, browse to the directory where you copied the database files in step 1 and select the SavingsMart.mdf file. Click OK.
| 5. | Click OK to attach the database.
| 6. | To see the newly added database, you will need to click Action and Refresh.
|
|
