SAS/ACCESS Software for Relational Databases: Reference

Accessing OLE DB for OLAP Data

Overview

The SAS/ACCESS interface to OLE DB provides a facility for accessing OLE DB for OLAP data. You can specify a Multidimensional Expressions (MDX) statement through the SQL Procedure Pass-Through Facility to access the data directly, or create a PROC SQL view of the data. Note that you must pass an MDX statement which specifies a two-axis "flattened" data set. Attempting to return a data set with more than two axes will result in an error. Refer to the Microsoft Data Access Components Software Developer's Kit for details on MDX syntax.

Note: This implementation provides read-only access to OLE DB for OLAP data. You cannot update or insert data with this facility. [cautionend]

Using the SQL Procedure Pass-Through Facility

The main difference between normal OLE DB access using SQL Pass-Through and this implementation for OLE DB for OLAP is the use of additional identifiers to pass MDX statements to the OLE DB for OLAP data. These identifiers are:

MDX::: identifies MDX statements that return a "flattened" data set from the multidimensional data
MDX_DESCRIBE::: identifies MDX statements that return detailed column information.

An MDX_DESCRIBE:: identifier is used to obtain detailed information on each returned column. During the process of "flattening" multidimensional data into a two-axis data set, OLE DB for OLAP builds column names from each level of the given dimension. For example, for OLE DB for OLAP multidimensional data that contains CONTINENT, COUNTRY, REGION, and CITY dimensions, you could build a column with the following name:

[NORTH AMERICA].[USA].[SOUTHEAST].[ATLANTA]

This name cannot be used as a SAS variable name because it has more than 32 characters. For this reason, the SAS/ACCESS engine for OLE DB creates a column name based on a shortened description, in this case, ATLANTA. However, since there could be an ATLANTA in some other combination of dimensions, you might need to know the complete OLE DB for OLAP column name. Using the MDX_DESCRIBE:: identifier returns a SAS data set that contains the SAS name for the returned column and its corresponding OLE DB for OLAP column name:

SASNAME          MDX_UNIQUE_NAME

ATLANTA          [NORTH AMERICA].[USA].[SOUTHEAST].[ATLANTA]
CHARLOTTE        [NORTH AMERICA].[USA].[SOUTHEAST].[CHARLOTTE]
   .                        .
   .                        .
   .                        .

If two or more SASNAMEs are identical, a number is appended to the end of the second and later instances of the name; for example, ATLANTA, ATLANTA0, ATLANTA1, and so on. Also, depending on the value of the VALIDVARNAME= system option, illegal characters are converted to underscores in the SASNAME.

Syntax

This facility uses the following general syntax. For more information on SQL Pass-Through syntax, see SQL Procedure Pass-Through Facility.

PROC SQL <options>;

CONNECT TO OLEDB (<options>);

<non-SELECT SQL statement(s)>

SELECT column-identifier(s) FROM CONNECTION TO OLEDB

( MDX:: | MDX_DESCRIBE:: <MDX statement>)

<other SQL statement(s)>
;

MDX::

identifies the following statement as an MDX statement that requests data from the multidimensional data. The MDX statement is passed through to the provider, and the resulting "flattened" data set is returned to SAS software.

MDX_DESCRIBE::

identifies a request for detailed information about the column names for the data set that would be returned by the MDX statement. The returned data set contains two varaibles:

SASNAME, containing the SAS names for the columns that would be returned by the MDX statements
MDX_UNIQUE_NAME, containing the fully-described OLE DB for OLAP column identifier

Examples

The following code uses SQL Pass-Through to pass an MDX statement to a Microsoft SQL Server Decision Support Services (DSS) Cube. The provider used is the Microsoft OLE DB for OLAP provider named MSOLAP.

proc sql noerrorstop;
   connect to oledb (provider=msolap prompt=yes);
   select * from connection to oledb
      ( MDX::select {[Measures].[Units Shipped],
             [Measures].[Units Ordered]} on columns,
             NON EMPTY [Store].[Store Name].members on rows
              from Warehouse );

See the Microsoft Data Access Components Software Developer's Kit for details on MDX systax.

The CONNECT statement requests prompting for connection information, which facilitates the connection process (especially with provider properties). The MDX:: prefix identifies the statement within the parentheses that follows the MDX statement sytax, and is not an OLAP-specific SQL statement. Partial output from this query might look like this:

Store               Units Shipped         Units Ordered

Store6              10,647                11,699
Store7              24,850                26,223
   .                   .                      .
   .                   .                      .
   .                   .                      .

You can use the same MDX statement with the MDX_DESCRIBE:: identifier to see the full description of each column:

 proc sql noerrorstop;
 connect to oledb (provider=msolap prompt=yes);
 select * from connection to oledb
    ( MDX_DESCRIBE::select {[Measures].[Units Shipped],
                    [Measures].[Units Ordered]} on columns,
                    NON EMPTY [Store].[Store Name].members on rows
                    from Warehouse );

The next example creates a view of the OLAP data, which is then accessed using the PRINT procedure:

proc sql noerrorstop;
  connect to oledb(provider=msolap
                   props=('data source'=sqlserverdb 
                      'user id'=myuserid password=mypassword));
  create view work.myview as  
     select * from connection to oledb 
        ( MDX::select {[MEASURES].[Unit Sales]} on columns,
               order(except([Promotion Media].[Media Type].members,
               {[Promotion Media].[Media Type].[No Media]}),
               [Measures].[Unit Sales],DESC) on rows
             from Sales )
;
  
proc print data=work.myview; 
run;

In this example, full connection information is provided in the CONNECT statement, so the user is not prompted. The PROC SQL view can be used in other PROC SQL statements, the DATA step, or in other procedures, but you cannot modify (that is, insert, update, or delete a row in) the view's underlying multidimensional data.

Chapter Contents
Previous
Next
Top of Page