Accessing OLE DB for OLAP Data

The main difference between normal OLE DB access using the SQL pass-through facility and the implementation for OLE DB for OLAP is the use of these additional identifiers to pass MDX statements to the OLE DB for OLAP data:

An MDX_DESCRIBE:: identifier is used to obtain detailed information about each returned column. During the process of flattening multidimensional data, 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 this 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 SASNAME values 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 value.

This facility uses the following general syntax. For more information about SQL pass-through facility syntax, see SQL Pass-Through Facility.

The following code uses the SQL pass-through facility 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 about MDX syntax.

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 syntax, and is not an SQL statement that is specific to OLAP. 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 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.