Lesson 3: Programming with ADO MD Objects

In Lesson 4 of Chapter 10, "Introduction to Programming with ADO MD Objects," you learned how to use the Catalog object to connect to a multidimensional data provider and how to use the CubeDef object to access the metadata in a cube. In this lesson, you will learn how to retrieve a multidimensional dataset by using the Cellset object. You will then learn about MDX data definition language (DDL) extensions for OLE DB for OLAP. You can use these statements with ADO MD to create and populate local cube files.

After this lesson, you will be able to:

• Write ADO MD code to retrieve multidimensional data
• Write ADO MD code and use MDX DDL extensions to create a local cube

Estimated lesson time: 120 minutes

Retrieving Multidimensional Data

ADO MD provides extensions to the ADO object model for OLE DB for OLAP. Figure 12.6 shows how the ADO Connection object connects ADO MD objects to multidimensional data sources. When you have connected to these data sources, you can retrieve metadata that describes the multidimensional structure of the data and you can access the data in the data source. You retrieve multidimensional data into the Cellset object. This process is similar to retrieving relational data into an ADO Recordset object.

It is possible to retrieve multidimensional data into a recordset. In this case, the multidimensional data provider automatically flattens the multidimensional data into a tabular rowset. To do this, open a Recordset specifying an MDX query as the Source and a connection to an OLAP data source. You can also retrieve metadata into a Recordset by using the OpenSchema method of the ADO Connection object.

NOTE

---

SQL Server Books Online does not include documentation of the ADO MD object model. The ADO MD object model is documented in the ADO MD Programmer s Reference, which is included in the ADO version 2.x documentation. This documentation should be located in C:\Program Files\Common Files\System\ado\ado2xx.CHM on computers that have the Microsoft Data Access Components installed.

Figure 12.6 ADO MD objects provide access to multidimensional data

The following sections discuss how to populate a cellset by using an MDX query and how to retrieve individual cell values.

The Cellset Object

The Cellset object represents the n-dimensional dataset (cellset) returned by an MDX SELECT statement. Each cell stores the value found at an intersection of the members of the cellset dimensions. The Cellset object operates as an n-dimensional array. Figure 12.7 shows the Cellset object and the hierarchy of subordinate objects that provide access to multidimensional datasets.

Figure 12.7 The Cellset object and subordinate objects

Unlike the way you work with a traditional SQL result set, you do not scroll through the cellset dimensions to retrieve cell values, and there is no current item in the cellset. All of the cell values are available when the MDX query finishes, and you address these values directly in the Cellset.

Properties and Methods

The Cellset object contains several properties and methods that you will use when you work with cellsets. The most common ones are listed in the following table:

Property or Method	Description
ActiveConnection property	Set this property to an existing ADO Connection object or to an ADO MD connection string.
Source property	Set this property equal to an MDX SELECT statement. After the cellset is open, this property contains the SELECT statement used to generate the current cellset.
Open method	Use this method to execute a query on the server after you have set the ActiveConnection and Source properties.
Close method	Use the Close method to release the resources of the cellset object.
FilterAxis property	This read-only property is an Axis object that lists the members of the slicer dimensions referenced in the WHERE clause. You can use this property to determine how the cube was sliced to get the current cellset. The filter axis is not contained in the Axes collection.
State property	The State property has two values, 1 (adStateOpen) or 0 (adStateClosed). The value of this property indicates whether the Cellset is open or closed.

Example

This Visual Basic example shows you how to open a cellset. It first opens a catalog by setting the ActiveConnection property of a Catalog object to a valid connection string, which opens the connection to the OLAP Server. Then the Source property of the Cellset object is set to an MDX SELECT query, and the ActiveConnection property of the Cellset object is set to the ActiveConnection of the Catalog object. Finally, the code calls the Open method of the Cellset object, which retrieves the result of the MDX SELECT query into the Cellset object.

 Dim cat As New ADOMD.Catalog  Dim cst As New ADOMD.Cellset cat.ActiveConnection = "Provider=MSOLAP;Data Source=Localhost;" & _     "Initial Catalog=Foodmart;"  cst.Source = "SELECT [Product].Children ON COLUMNS, " & _     "CrossJoin([Year].Members, [Gender].Children) ON ROWS " & _     "FROM  [sales] " & _      "WHERE ([Unit Sales])"  Set cst.ActiveConnection = cat.ActiveConnection  cst.Open 

Retrieving Cellset Data

Retrieving a cellset returns both the cellset metadata and individual cell values.

Using Axes, Positions, and Members

A Cellset object contains an Axes collection, each Axis in the Axes collection contains a Positions collection, and each Position in the Positions collection contains a Members collection. These collections define the characteristics of the returned data.

Axes Collection

The Axes collection is a collection of Axis objects. Each Axis object represents an axis that projects the members of one or more dimensions as defined in an MDX SELECT statement.

Positions Collection

The Positions collection is a collection of Position objects. Each Position object represents a point (tuple) along an axis of the cellset. Each position on the axis represents a unique combination of the dimension members specified in the MDX SELECT statement that was used to create the cellset.

Members Collection

The Members collection is a collection of Member objects. Each Member object represents a member of a position (tuple). There will be as many members per position as there are dimensions on the axis.

Example

If the following MDX SELECT statement is used to open a cellset, the COLUMNS axis will have three positions and the Members collection for each position will contain one Member object: Drink, Food, or Non-Consumable. The ROWS axis will have four positions corresponding to the cross-product of the year and gender dimensions. The Members collection for the first position will contain 1997 and F; the second position will contain 1997 and M; the third position will contain 1998 and F; and the fourth position will contain 1998 and M.

 SELECT [Product].Children ON COLUMNS,   CROSSJOIN([Year].Members, [Gender].Children) ON ROWS  FROM Sales 

Accessing an Individual Cell with the Item Method

You can access an individual cell using the Item method of the Cellset object.

Using the Item Method of the Cellset Object

The Item method is the default method for the Cellset object. When you return a cell using the Item method, you must specify the cell in one of three ways:

• By using a list of axis position numbers in the form Cellset. Item (x, y, z, ). The positions on an axis are all numbered incrementally from zero to one less than the number of positions on the axis.
• By using a list of names of the members for each position. Within an axis, the members must be listed in increasing order of dimension nesting that is, the member of the outermost dimension comes first, followed by the members of the inner dimensions. Specifying a cell by using member names is not supported by the MSOLAP provider.
• By ordinal position within the cellset. Each cell has a unique ordinal value in the cellset. For example, in a cellset with eight columns, the ordinal value of the first cell in the cellset is zero, the ordinal value of the last cell in the first row is 7, and the ordinal value of the first cell in the second row is 8.

Although you can think of the Cellset object as an n-dimensional array, the cells are actually stored internally in row-major order in a linear array. Each cell, therefore, has a unique index coordinate in the internal array; this is the ordinal value of the cell. You can find the index of the cell through the Ordinal property on a Cell object.

Typically, you will use a list of axis coordinates such as (x, y, z, ). Each Position object has an Ordinal property that identifies its position on the axis. You can use this value to indicate which group of cells along that axis you want. By combining the Ordinal property values from all the Position objects, you can specify an individual cell.

Example

This Visual Basic example iterates through the Positions and Members collections in the ROWS and COLUMNS Axis objects and prints the caption of each member of each position and the cell value for the intersection points of the axes.

Dim cat As New ADOMD.Catalog  Dim cst As New ADOMD.Cellset  Dim XPos As ADOMD.Position  Dim YPos As ADOMD.Position  Dim mbr As ADOMD.Member cat.ActiveConnection = "Provider=MSOLAP;Data Source=Localhost;" & _     "Initial Catalog=Foodmart;"  cst.Source = "SELECT [Product].Children ON COLUMNS, " & _     "CrossJoin([Year].Members, [Gender].Children) ON ROWS " & _     "FROM  [sales] " & _     "WHERE ([Unit Sales])"  Set cst.ActiveConnection = cat.ActiveConnection  cst.Open 'Print the header for each column (Member names)  Debug.Print ,  For Each XPos In cst.Axes(0).Positions          For Each mbr In XPos.Members         Debug.Print mbr.Caption,          Next  Next  Debug.Print 'Now process the rows  For Each YPos In cst.Axes(1).Positions     'Print the row headers (Member names)     For Each mbr In YPos.Members                  Debug.Print mbr.Caption & " ";     Next          Debug.Print ,     'Now print the row values          For Each XPos In cst.Axes(0).Positions         Debug.Print cst.Item(XPos.Ordinal, YPos.Ordinal).Value,          Next          Debug.Print  Next 

The results that are printed in the Visual Basic immediate pane when you run this code are as follows:

               Drink         Food          Non-Consumable  1997 F         12202         94814         24542  1997 M         12395         97126         25694  1998 F        Null          Null          Null  1998 M        Null          Null          Null 

In this example, you can use the position numbers and the ordinal number methods to address the cell that contains the food sales to males in 1997 as follows:

 cst.Item(1, 1)  OR  cst.Item(4) 

Retrieving Cell Values

You can use either of the following properties to retrieve cell values:

• Use the Value property to access the contents of a cell. This is the default property and is a variant data type.
• Use the FormattedValue property to obtain the value of the Value property, formatted for display. For example, if the Value property returns 123, the formatted value for a money data type will be $123.00.

Example

This Visual Basic example iterates through the Positions and Members collections in the ROWS and COLUMNS Axis objects and prints the caption of each member of each position and the cell value for the intersection points of the axes. In this example, the FormattedValue property is used to retrieve the cell values. Notice the difference between the results of this example and the previous example, which used the Value property to retrieve cell values.

Dim cat As New ADOMD.Catalog  Dim cst As New ADOMD.Cellset  Dim XPos As ADOMD.Position  Dim YPos As ADOMD.Position  Dim mbr As ADOMD.Member cat.ActiveConnection = "Provider=MSOLAP;Data Source=Localhost;" & _     "Initial Catalog=Foodmart;"  cst.Source = "SELECT [Product].Children ON COLUMNS, " & _     "CrossJoin([Year].Members, [Gender].Children) ON ROWS " & _     "FROM  [sales] " & _          "WHERE ([Unit Sales])"  Set cst.ActiveConnection = cat.ActiveConnection  cst.Open 'Print the header for each column (Member names)  Debug.Print ,  For Each XPos In cst.Axes(0).Positions     For Each mbr In XPos.Members         Debug.Print mbr.Caption,          Next  Next  Debug.Print  'Now process the rows  For Each YPos In cst.Axes(1).Positions     'Print the row headers (Member names)     For Each mbr In YPos.Members         Debug.Print mbr.Caption & " ";          Next          Debug.Print ,     'Now print the row values     For Each XPos In cst.Axes(0).Positions         Debug.Print cst.Item(XPos.Ordinal,  YPos.Ordinal).FormattedValue,           Next           Debug.Print  Next 

The results that are printed in the Visual Basic immediate pane when you run this code are as follows:

             Drink         Food         Non-Consumable                1997 F        12,202.00     94,814.00     24,542.00       1997 M        12,395.00     97,126.00     25,694.00       1998 F                                                    1998 M                                                   

Exercise 1: Retrieving Data from a Cube (Using VBA in Excel 2000)

In this exercise, you will use ADO MD to retrieve a slice of data from a cube stored in the OLAP database. You will use an MDX SELECT statement to create a cellset that contains data from the OLAP Server. In the next exercise, you will perform the same operations by using ADO MD and Visual Basic Scripting Edition (VBScript) running in ASP. You can do both exercises or choose only one of the two. The C:\SQLDW\Exercise\Ch12\ADO12_Ans.XLS file is a completed answer file for this exercise.

To declare variables

In this procedure, you will declare Cellset and Axis variables used to retrieve data from the OLAP database.

1. Open the C:\SQLDW\Exercise\Ch12\ADO12_Start.XLS Excel file. You may be prompted to enable or disable macros. You need to enable macros because you will be writing VBA code in the Excel workbook.
2. On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor.
3. In Module1, locate the comment labeled ***First Edit. In the space provided, type the following code to declare the Cellset object:

Dim cst as ADOMD.Cellset

4. Locate the comment labeled ***Second Edit. In the space provided, type the following code to declare the Axis variables used to read the cell data stored along the axis points for the data returned from the cube:

 Dim Y_axis As ADOMD.Axis  Dim X_axis As ADOMD.Axis  Dim i As Integer  Dim j As Integer 

To create and populate a cellset

In this procedure, you will create a new instance of the Cellset object and populate it with the results of an MDX query.

1. Locate the comment labeled ***Third Edit. In the space provided, type the following code to create a new Cellset object and set the connection to the OLAP Server:

Set cst = CreateObject("ADOMD.Cellset") cst.ActiveConnection = connstr 

2. Open the C:\SQLDW\Exercise\Ch12\MDXQuery.TXT text file in Notepad.
3. Copy the contents to the Windows Clipboard.
4. Locate the comment labeled ***Fourth Edit. In the space provided, paste the contents of the text file to set the Source property of the Cellset object.

Make sure that you format the MDX query string properly using line continuation characters or place the entire string on one line.

5. Locate the comment labeled ***Fifth Edit. In the space provided, type the following code to populate the cellset:

cst.Open

To read the contents of the cellset

In this procedure, you will examine code that uses Axis variables to navigate through the contents of a cellset and display the results on an Excel spreadsheet.

1. Locate the comment ***First Review. Examine the code. These local variables are used to scroll through the results in the cellset.

 Set X_axis = cst.Axes(0)  Set Y_axis = cst.Axes(1)

2. Locate the comment labeled ***Second Review. Examine the code. In this case, cellset data is stored at the Member level of the object model. This code scrolls through each position and reads the data stored at that location. The FormattedValue property returns a text string representing the formatted display of a Cell value.

 '***********************************************  'Scroll through each axis position collection  'to read the member objects containing the data  'and write the information to the worksheet.  '*********************************************** For i = 0 To X_axis.Positions.Count - 1      Cells(1, i + 2) = X_axis.Positions(i).Members(0).Caption      For j = 0 To Y_axis.Positions.Count - 1                        '*****************************************************           'Read the hierarchy level member at the Cell Position                      '*****************************************************          Cells(j + 3, 1) = Y_axis.Positions(j).Members(0).Caption                   '**********************************************                   'Read the Cell Value for the Axis Intersection                    '**********************************************               Cells(j + 3, i + 2) = cst(i, j).FormattedValue               Next j       Next i 

To review and execute the procedure

1. Save the workbook.
2. Review the completed procedure.
3. Position your cursor inside the subroutine GetData and run the code.
4. Switch to the Excel spreadsheet and examine the results.

Exercise 2: Retrieving Data from a Cube (Using VBScript in ASP)

In this exercise, you will use ADO MD and Visual Basic Scripting Edition (VBScript) to connect to the Northwind_DSS OLAP database and read cube data from the Sales cube by using an MDX query. In the previous exercise, you performed the same operations by using Visual Basic for Applications (VBA) running in Excel 2000. You can do both exercises or choose only one of the two. The C:\SQLDW\Exercise\Ch12\CubeData_Ans.ASP file is a completed answer file for this exercise.

To insert the code to return the data in a Web page

In this procedure, you will modify the C:\SQLDW\Exercise\Ch12\CubeData.ASP file to connect to your local server and set the MDX query to be processed.

1. Use Notepad to open the C:\SQLDW\Exercise\Ch12\CubeData.ASP file. This file contains a partially complete Active Server Page.
2. Locate the comment ***First Edit***. Insert the following statement to set the value of cst.Source to the MDX statement:

 cst.Source = "With Member [Measures].[Total Discounted Sales] As " _   & "'([Measures].[Line Item Total] - [Measures].[Line Item  Discount])', " _    & "Format = '$#.00'" & Chr(13) & Chr(10) _    & "Select {[Measures].[Total Discounted Sales]} ON COLUMNS, " _    & "[Product].[Beverages].Children ON ROWS " _    & "FROM Sales " _    & "WHERE [Customer].[USA].[WA].[Seattle].[White Clover Markets]" 

3. On the File menu, click Save so that the new changes will take effect.

To review and execute the procedure

1. Right-click the C:\SQLDW\Exercise\Ch12 folder in Windows NT Explorer, and select Sharing on the pop-up menu.
2. Select the Web Sharing tab, and then select Share this folder. The Edit Alias dialog box will be displayed. If the Edit Alias dialog box is not displayed, click the Add button to open it.
3. In the Edit Alias dialog box, set the Alias to ADOMD12 and ensure that the Read and Scripts check boxes are checked. Click the OK button twice to close the Edit Alias and the Ch12 properties dialog boxes.
4. To view the Web page, open Microsoft Internet Explorer and navigate to http://localhost/ADOMD12/CubeData.ASP.

How to Create a Local Cube

You can create a local cube to be stored on a computer other than the computer running the OLAP Server.

Creation of local cubes is accomplished through PivotTable Service, which is an OLE DB client to an OLAP Server. PivotTable Service also provides stand-alone OLE DB provider capabilities for local cube creation when not connected to an OLAP Server.

The ability to create and query a local cube allows greater flexibility for analyzing data. You can

• Download cube slices from OLAP Server to a local computer other than the OLAP Server
• Create a completely new cube from any relational database management system (RDBMS)

ADO MD does not contain object methods to build local cubes. You create local cubes through PivotTable Service by using MDX data definition language (DDL) statements embedded in ADO MD code to define the structure and populate the cube. The PivotTable Service uses this code to build the cube. You can execute these statements as part of a custom client application.

When you create local cubes, you should consider the following:

• Provide generous space for local cubes.

This allows the client tools to have workspace for calculation and presentation of the data.

• Consider available bandwidth.

Local area networks (LANs) provide sufficient bandwidth for transfer of data from the OLAP Server to the local PivotTable Service, but wide area networks (WANs) usually do not. As a result, it is recommended that you configure remote users so that they do not automatically update their local data cubes whenever they connect.

TIP

---

You can open local cube files in the MDX Sample Application. To do so, simply specify the full pathname of the local cube file instead of a server name in the Server textbox when you connect to the data source.

Building a Connection String

To create a local cube, you must use a connection string to open a connection. The cube is created and populated when you open the connection. The connection string contains special embedded information that PivotTable Service uses to build the local cube. The information embedded in the connection string includes the MDX CREATE CUBE statement. The parameters that you must specify in the connection string are listed in the following table.

Parameter	Description
Provider	The name of the OLE DB provider. For OLAP Services, this is MSOLAP.
Location	The location of the local cube file.
Source_DSN	The connection string for the source of the data that will be used to populate the local cube.
CreateCube	The MDX CREATE CUBE statement that defines the logical structure of the cube.
InsertInto	The MDX INSERT INTO statement that populates the cube with data.

Notice that the CreateCube (without a space) connection string parameter specifies the CREATE CUBE (with space) statement and that the InsertInto (without a space) connection string parameter specifies the INSERT INTO (with a space) statement.

CREATE CUBE Statement

The purpose of the CREATE CUBE statement is to define a new cube structure. This statement shares much of the syntax and semantics of SQL-92 syntax and shares the semantics of the CREATE TABLE statement. However, the CREATE CUBE statement contains syntax specific to data cubes.

A cube is defined as a collection of measures, each having an aggregate function, and a set of dimensions, each containing one or more levels. There may be special dimensions, such as time dimensions, and special levels, such as ALL and various time units.

The partial syntax of the CREATE CUBE statement is

CREATE CUBE <cube name> (

DIMENSION <dimension name> [TYPE TIME],

<hierarchy def> [<hierarchy def>...]

[{, DIMENSION <dimension name> [TYPE TIME],

<hierarchy def> [<hierarchy def>...]}...] ,

MEASURE <measure name> <measure function def> [<measure format def>] [<measure type def>]

[{, MEASURE <measure name> <measure function def> [<measure format def>] [<measure type def>] }...]

[,COMMAND <expression>]

[,COMMAND <expression>...]

)

<hierarchy def> ::= [HIERARCHY <hierarchy name>,] <level def> [,<level def>...]

<level def> ::= LEVEL <level name> [TYPE <level type>] [<level format def>] [<level options def>]

<level type> ::= ALL | YEAR | QUARTER | MONTH | WEEK | DAY | DAYOFWEEK | DATE | HOUR | MINUTE | SECOND

<level format def> ::= FORMAT_NAME <expression> [FORMAT_KEY <expression>]

<level options def> ::= OPTIONS (<option_list>)

<option_list> :: = UNIQUE | SORTBYNAME | SORTBYKEY [,<option_list>]

<measure function def> ::= FUNCTION SUM | MIN | MAX | COUNT

<measure format def> ::= FORMAT <expression>

<measure type def> ::= TYPE DBTYPE_I1 | DBTYPE_I2 | DBTYPE_I4 | DBTYPE_I8 | DBTYPE_UI1 | DBTYPE_UI2 | DBTYPE_UI4 | DBTYPE_UI8 | DBTYPE_R4 | DBTYPE_R8 | DBTYPE_CY | DBTYPE_DECIMAL | DBTYPE_NUMERIC | DBTYPE_DATE

In the DIMENSION clause of the CREATE CUBE statement, the name given to a level of TYPE ALL applies the specified name to the ALL member rather than the ALL level; the ALL level will always have the name (All). For example, the clause LEVEL [All Customers] TYPE ALL will create a level named (All) containing a single member named [All Customers]; there will be no [All Customers] level.

Example

This example creates a cube named LocalSales. The cube has two dimensions, named Time and Product. Each dimension has a number of levels. The cube has a single measure, named Quantity. A Visual Basic example that uses this CREATE CUBE statement is shown in the next section that explains the INSERT INTO statement.

 CREATE CUBE LocalSales (      DIMENSION Time TYPE TIME,      LEVEL Year TYPE YEAR,           LEVEL Quarter TYPE QUARTER,           LEVEL Month TYPE MONTH,           DIMENSION Product,           LEVEL [All Products] TYPE ALL,           LEVEL [Product Name],           MEASURE [Quantity] FUNCTION SUM) 

INSERT INTO Statement

The INSERT INTO statement binds a logical cube to an existing relational view and populates this cube with dimension members and data. Each level and each measure in a cube is derived from a column in a SELECT statement that is embedded in the INSERT INTO statement.

The partial syntax of the INSERT INTO statement is as follows:

INSERT INTO <cube-name> (<target-element-list>)

[<options-clause>]

[BIND (<simple-column-name>[,<simple-column-name>])]

SELECT <columns-list>

FROM <tables-list>

[ WHERE <where-clause> ]

The parts of this syntax are as follows:

<target-element-list> ::= <target-element>[, <target-element-list>]

<target-element> ::= [<dim-name>.[<hierarchy-name>.]]<level-name> | <time-dim-name>| [Measures.]<measure-name>| SKIPONECOLUMN

<level-name> ::= <simple-level-name> | <simple-level-time>.NAME | <simple-level-time>.KEY

<time-dim-name> ::= <dim-name-type-time> | <dim-name-type-time>.NAME | <dim-name-type-time>.KEY

<options-clause> ::= OPTIONS <options-list>

<options-list> ::= <defer-options> | < analysis-options> [, <options-list>]

<defer-options> ::= DEFER_DATA | ATTEMPT_DEFER

<analysis-options> ::= PASSTHROUGH | ATTEMPT_ANALYSIS

<columns-list> ::= <column-name> [AS <alias-name>]| <alias name> <column-name> [, < columns-list> ]

<tables list> ::= <table-name> [ [AS] <table-alias>] [, <tables list>]

<where clause> ::= <where-condition> [AND <where-clause>]

<where condition> ::= <join-constraint> | <application constraint>

<join-constraint> ::= <column-name> = <column-name>

<application-constraint> ::= (. . .)| NOT (. . .)| (. . .) OR (. . .) | (. . .) AND (. . .)

When populating a local cube by using the INSERT INTO statement, consider the following facts and guidelines:

• The INSERT INTO statement binds a logical cube to an existing relational data source and populates this cube with dimension members and data.
• The items listed in an INSERT INTO statement are level and measure names. Names can be fully qualified by using the dimension name or by using the keyword Measures.
• Each level and measure in the local cube derives data from a column in the SELECT clause. The columns listed in the SELECT clause provide data for items in the <target> clause with a one-to-one correspondence.
• If a column listed in the SELECT clause does not have a related member in the <target> clause, use the SKIPONECOLUMN keyword in the <target> clause as a placeholder for the unused column.
• A dimension that holds time data is specified by placing the TYPE TIME keyword after the name of the dimension in the CREATE CUBE statement. The time dimension is then used to correlate the entire dimension with a single column in the source table that contains data in date/time format. The levels of TYPE <level-type> identified for the time dimension in the CREATE CUBE statement will cause the time information to be extracted from the source column specified in the SELECT statement.
• The WHERE clause of an INSERT INTO statement can contain both data restrictions (application constraints) and join constraints. You should specify which elements in the WHERE clause indicate data restrictions as opposed to joins. Expressions between parentheses are used to restrict the dataset. This code indicates a join condition:

 Sales.Product_ID = Products.Product_ID AND  Sales.Customer_ID = Customers.Customer_ID 

While this code indicates a data restriction:

 (Product.Price < 100 AND Product.Category = 1)

Defer and Analysis Options

When the INSERT INTO statement is processed, the defer and analysis options determine how the PivotTable Service retrieves the data.

The defer options determine when the data is retrieved; data can be retrieved when the INSERT INTO statement is processed and stored in the local cube (MOLAP storage mode), or data retrieval can be deferred until a user queries the cube (ROLAP storage mode).

The analysis options determine how the SELECT statement is processed. The PivotTable Service can parse the SELECT query and create a set of queries to retrieve the data. Otherwise, the PivotTable Service can pass the SELECT query directly to the source database; this is called a passthrough. The set of queries that is generated by the PivotTable Service is often more efficient than the specified query. The PivotTable Service cannot always successfully parse the query; in this case, it will pass the query directly to the source database.

The following matrix shows how the PivotTable Service retrieves data in each possible circumstance based on whether the SELECT query can be parsed and what options are specified.

	Neither Analysis Option Specified	PASS-THROUGH	ATTEMPT_ANALYSIS
Neither defer option specified Parse succeeds.	MOLAP	MOLAP using passthrough	MOLAP
Neither defer option specified. Parse fails.	Error	n/a	MOLAP using passthrough
DEFER_DATA Parse succeeds.	ROLAP	Error	ROLAP
DEFER_DATA Parse fails.	Error	n/a	Error
ATTEMPT_DEFER Parse succeeds.	ROLAP	MOLAP using passthrough	ROLAP
ATTEMPT_DEFER Parse fails.	MOLAP using passthrough	n/a	MOLAP using passthrough

Example

In this example, the LocalSales cube is populated with data retrieved from the Sales cube.

 INSERT INTO LocalSales (     Time.Year,          Time.Quarter,          Time.Month,          Product.[Product Name],          Measures.Quantity)          SELECT Sales.[Time:Year],         Sales.[Time:Quarter],                  Sales.[Time:Month],                  Sales.[Product:Product Name],                  Sales.[Measures:Line Item Quantity]                  From Sales 

Example of Creating a Local Cube

This example demonstrates how to use MDX with ADO MD to create a local cube that is a subset of a server-side cube. The example is written in the VBScript language. The example demonstrates building the connection string by setting the values of a number of shorter strings and then concatenating these strings. This method simplifies the code and makes it more readable. The local cube is created when the connection is opened with the Open method call.

 Dim cn  Dim strProvider  Dim strLocation  Dim strDSN  Dim strSourceDSN   Dim strCreateCube   Dim strInsertInto   Dim strConnect Set cn = CreateObject("ADODB.Connection") strProvider = "Provider=MSOLAP;"  strLocation = "Location=C:\LocalSalesCube.cub;"  strDSN = "Provider=MSOLAP;Data Source=LocalHost;" _     & "Initial Catalog=Northwind_DSS"  strSourceDSN = "Source_DSN=""" & strDSN & """;"  strCreateCube = "CreateCube=CREATE CUBE LocalSales(" _          & "DIMENSION Time TYPE TIME, " _          & "LEVEL Year TYPE YEAR, " _          & "LEVEL Quarter TYPE QUARTER, " _          & "LEVEL Month TYPE MONTH, " _          & "DIMENSION Product, " _          & "LEVEL [All Products] TYPE ALL, " _          & "LEVEL [Product Name], " _          & "MEASURE [Quantity] FUNCTION SUM);"  strInsertInto = "InsertInto=INSERT INTO LocalSales (" _          & "Time.Year, " _          & "Time.Quarter, " _          & "Time.Month, " _          & "Product.[Product Name], " _          & "Measures.Quantity) " _          & "SELECT Sales.[Time:Year], " _           & "Sales.[Time:Quarter], " _          & "Sales.[Time:Month], " _          & "Sales.[Product:Product Name], " _          & "Sales.[Measures:Line Item Quantity] " _          & "From Sales" strConnect = strProvider & strLocation & strSourceDSN _     & strCreateCube & strInsertInto  cn.ConnectionString = strConnect  cn.Open 

In this example, the connection string that is built is as follows. (It is one long string; the line breaks are used here simply to fit the string onto a printed page.) Notice the five parts of the string indicated by the boldfaced parameter keywords.

Provider=MSOLAP;
Location=C:\LocalSalesCube.cub;
Source_DSN="Provider=MSOLAP;Data Source=LocalHost;Initial
Catalog=Northwind_DSS";
CreateCube=CREATE CUBE LocalSales(DIMENSION Time TYPE TIME,
LEVEL Year TYPE YEAR, LEVEL Quarter TYPE QUARTER,
LEVEL Month TYPE MONTH,
DIMENSION Product,
LEVEL [All Products] TYPE ALL, LEVEL [Product Name],
MEASURE [Quantity] FUNCTION SUM);
InsertInto=INSERT INTO LocalSales (Time.Year, Time.Quarter,
Time.Month, Product.[Product Name], Measures.Quantity) SELECT
Sales.[Time:Year], Sales.[Time:Quarter], Sales.[Time:Month],
Sales.[Product:Product Name], Sales.[Measures:Line Item Quantity] From
Sales

REFRESH CUBE

To refresh a data cube, use the following syntax.

REFRESH CUBE <cubename>

You can use the REFRESH CUBE statement when connected to a local cube to rebuild the local cube. The local cube is rebuilt using the same information that was used to create the local cube. You can also use the REFRESH CUBE statement when connected to OLAP Services; in this case the REFRESH CUBE statement causes the local cache to be refreshed. To execute the REFRESH CUBE statement, create a connection to the local cube or to OLAP Services and use the Connection object s Execute method to execute the REFRESH CUBE statement.

Example

In this VBScript example, the code connects to the LocalSales cube and refreshes the local cube.

 Dim cn  Dim strProvider  Dim strLocation  Dim strConnect Set cn = CreateObject("ADODB.Connection") strProvider = "Provider=MSOLAP;"  strLocation = "Location=C:\LocalSalesCube.cub;" strConnect = strProvider & strLocation  cn.ConnectionString = strConnect  cn.Open  cn.Execute "REFRESH CUBE LocalSales" 

Exercise 3: Creating and Querying a Local Cube

In this exercise, you will use MDX and ADO to take a slice of a server cube and build a local cube to use for offline analysis. You will use an Active Server Page as the vehicle for executing the script that builds the local cube.

To set the source and destination data sources for the local cube

1. Open C:\SQLDW\Exercise\Ch12\BuildLocalCube.ASP.
2. Locate the comment labeled ***First Edit*** and type the following line of code to set the value of the destination string:

destination = _       "Provider=MSOLAP;Location=C:\SQLDW\Exercise\Ch12\LocalSalesCube.cub" 

3. Locate the comment labeled ***Second Edit*** and type the following line of code to set the value of the SourceDSN string:

 SourceDSN = "Provider=MSOLAP;" _          & "Data Source=LOCALHOST;Initial Catalog=Northwind_DSS" 

To specify the local cube definition

In this procedure, you will examine a CREATE CUBE statement used to define the local cube. The cube will contain two dimensions, Time and Product, and a single measure, Quantity.

Locate the comment labeled ***First Review*** and examine the CREATE CUBE statement.

To specify the local cube contents

In this procedure, you will examine an INSERT INTO statement used to populate the local cube.

Locate the comment labeled ***Second Review*** and examine the INSERT INTO statement.

To build a connection string to connect to the server and process the local cube

In this procedure, you will write a connection string that combines the destination, source, CREATE CUBE, INSERT INTO information to connect to the OLAP Server through the PivotTable Service and then build the local cube.

Locate the comment labeled ***Third Edit*** and type the following line of code to set the value of the connection string as follows:

 ConnectionString = destination & ";" & source & ";" & _      CreateCubeStr & ";" & InsertInto 

To save the file and process the script

1. Review the script on the .ASP page.
2. On the File menu, click Save.
3. Start Microsoft Internet Explorer and navigate to http://localhost/ADOMD12/BuildLocalCube.ASP.

You should receive notification that the local cube was created successfully.

4. To make sure that the cube was created, open Windows Explorer and locate the C:\SQLDW\Exercise\Ch12\LocalSalesCube.CUB file.

To connect to the new local cube

• Use Excel 2000 to create a pivot table using the new local cube.
• Start Microsoft Internet Explorer and navigate to http://localhost/ADOMD12/UseLocalCube.HTM.

ADO MD Object Usage Summary

Each of the ADO MD objects has a specific purpose, but they can be grouped according to the overall tasks that they perform. Use the following guidelines to help you determine which ones are right for your application.

Use Decision Support Objects (DSO) to Create Server-based Cubes

DSO is the only interface that you can use to programmatically create cubes on the server. See Lesson 3, "OLAP Services and PivotTable Architecture," in Chapter 10, Data Analysis Tools and Architecture."

Use the Catalog Object to Connect to the Data Store

When you want to retrieve cube metadata, use the Catalog object to connect to the database because the ADO Connection object does not provide access to the cube metadata. The ADO MD Catalog object recognizes the cubes as special objects and populates the CubeDefs collection automatically.

Use the CubeDef Object and Its Contained Objects to Read Cube Schema

The CubeDef, Dimension, Hierarchy, and Level objects refer to the schema of the cube, and you can use them to determine the overall structure. You cannot use them to create or modify cubes.

Use the Cellset Object and Its Contained Objects to Read Cube Data

The Cellset object is designed to receive a multidimensional dataset from an MDX query; therefore, use it instead of the ADO Recordset object when you want to process multidimensional data. Use the Axis, Position, and Member objects to return cellset data. Use the Cellset object s Item method to retrieve individual cells. Use the Cell object s Value or FormattedValue properties to return the value of an individual cell.

Use MDX Statements to Create Local Cubes

Use the MDX CREATE CUBE and INSERT INTO statements in the connection string when opening an ADO connection to create local cubes using the PivotTable Service.

Lesson Summary

ADO MD provides an easy-to-use object model to retrieve multidimensional datasets. The Cellset, Axis, Position, Member, and Cell objects and their associated collections make it possible to navigate and manipulate all of the nested levels of the highly complex datasets returned by MDX queries.

MDX provides the CREATE CUBE and INSERT INTO statements that you can specify in a connection string when opening a connection to create a new local cube. You can use this functionality to create local cubes in your custom applications. Local cubes are available on a user s local hard drive for offline analysis using any PivotTable Service client such as Excel 2000 or custom ADO MD-based applications.