Accessing OLAP Cubes from SAS: SQL Pass-Through Facility for OLAP

Overview

The SQL Pass-Through facility for OLAP enables a SAS user to connect to an OLAP server and execute queries against OLAP data within the PROC SQL environment. PROC SQL establishes a connection to an OLAP server by using the PROC SQL CONNECT statement.
After a connection is made to the OLAP server, multiple queries can be submitted by using the OLAP multidimensional expressions (MDX) query language. These queries are run against existing OLAP cubes. While using the SQL Pass-Through facility, you can use the MDX DDL statement to create and delete named sets and calculated members, scoped either globally, or for the current server connection. A PROC SQL query is then closed after all observations (rows) of data are returned. To disconnect from the server, you must submit the PROC SQL DISCONNECT statement.

Conversion Issues

Overview

OLAP cube data is multidimensional and flexible in regard to data name lengths and restrictions. However, when PROC SQL sends a query to the OLAP server, data is returned in a flattened, tabular format that contains rows (observations) and columns (variables).
The SAS OLAP Server has unique naming conventions that specify valid column names, lengths, and types. Column names that are returned from the SAS OLAP Server can contain characters (periods, spaces, brackets) and can be unrestrained in length. Moreover, OLAP data types can be variable-length strings, floating-point numbers, or integers. This differs from SAS data set naming conventions, and some conversion is necessary.

VALIDVARNAME

The SQL Pass-Through facility supports the existing SAS option VALIDVARNAME. You can specify the VALIDVARNAME option to control variable names. The current default setting for VALIDVARNAME is V7, and variable names can be a maximum of 32 characters in length. Each variable must start with a letter or the underscore character and can contain letters, underscores, and numbers. Uppercase and lowercase letters are also allowed.
When converting column names to SAS variable names, the SQL Pass-Through facility for OLAP will do the following:
  • truncate the column name to the maximum size that is allowed
  • replace any invalid characters with an underscore
  • use a numeric suffix to differentiate between duplicate variable names that are generated during the data conversion
Note: For additional information about naming restrictions for the SAS OLAP Server, see Naming Guidelines and Rules for the SAS OLAP Server .
Note: For further information about the VALIDVARNAME= system option, see “VALIDVARNAME=System Option” and “Names in the SAS Language” in the SAS Language Reference: Dictionary.

Data Types

OLAP query results that contain member names or strings are converted to a fixed length CHAR type. All OLAP numeric types are converted to standard SAS numeric types (8-byte floating point). Missing values are handled by standard SAS conventions.

Specifying Member Names

When specifying member names, single quotation marks and double quotation marks are uniquely processed by the SQL Pass-Through facility. The following exceptions occur when using quotation marks in member names.
A member name contains a single quotation mark.
If a member name in the OLAP query contains a single quotation mark (for example, Adam's), the entire query must be enclosed in double quotation marks when passed to the SQL Pass-Through facility. This ensures that the single quotation mark is maintained in the query text. The double quotation marks are then removed before the OLAP query is processed on the OLAP server.
Note: Macro variables are not expanded within strings that contain single quotation marks. This includes MDX statements enclosed in single quotation marks.
A member name contains double quotation marks.
If a member name in the OLAP query contains double quotation marks, the entire query must be enclosed in single quotation marks when passed to the SQL Pass-Through facility. This ensures that the double quotation marks are maintained in the query text. The single quotation marks are then removed before the OLAP query is processed on the OLAP server.
Here is an example of the SQL Pass-Through facility with quotation marks. The member name Adam's requires the double quotation marks around the SELECT statement. 
proc sql;
connect to olap (host=localhost port=5451 user=user pass=pass);
         select * from connection to olap (
              "select [A].[All A].[M].[Adam's] on Rows,
                 crossjoin([Measures].defaultMember, [B].defaultMember)
                 on Columns
                   from [QuoteTest]"
       );
disconnect from olap;
quit;

SQL Pass-Through Example

In the following example, PROC SQL connects to the SQL Pass-Through facility for OLAP to create a new data set named temp, which contains all the variables that are returned from the multidimensional expression (MDX) defined in the SELECT query. The OLAP server returns query results in a tabular format known as a flattened rowset. The table rows become the observations of the output data set, and the table columns become the variables. After all the rows are returned, PROC SQL closes the query. The server connection is terminated when the program encounters a DISCONNECT statement or when the PROC SQL step ends.
Note: Because the OLAP server does not impose the same restrictions on column names, types, and lengths that SAS imposes on data sets, some conversion might be required. 
100        proc sql;
101           connect to olap (host=localhost service=olap1);
102           create table temp as select * from connection to olap
103           (
104              select { dealers.dealer.members } on 0,
105                     { [cars].[Car].members,
106                       [cars].[Color].members } on 1
107              from mddbcars
108           );
109          disconnect from olap;
110          quit;

PROC SQL Syntax

Overview

CONNECT TO OLAP|OLEDB
establishes a connection to an OLAP server. This statement is required. Specify OLAP to connect to a SAS OLAP server. Specify OLEDB to connect to all other OLEDB for OLAP-compliant servers. When connecting to an OLAP server, implicit connection is not supported. Therefore the CONNECT TO OLAP statement is required. However, when connecting to an OLEDB for OLAP compliant server, implicit connection is supported. For further information about the CONNECT statement, see “Pass-Through Facility for Relational Databases” in the SAS/ACCESS for Relational Databases: Reference.
DISCONNECT FROM OLAP | OLEDB
ends the connection to the OLAP server.
EXECUTE (MDX DDL statement) BY OLAP | OLEDB
specifies an MDX DDL statement for creating or deleting calculated members or named sets. This option is executed by the SAS OLAP Server. For further information, see “Basic MDX DDL Syntax” in the SAS OLAP Server: MDX Guide.
SELECT . . . FROM CONNECTION TO OLAP | OLEDB (MDX statement);
specifies the MDX query that is sent to the connected SAS OLAP server.

Connecting Directly to a SAS OLAP Server

When connecting directly to a SAS OLAP server, you specify a SAS OLAP server in the CONNECT statement. For example: 
CONNECT TO olap (host=localhost service=olap1);
You can also submit MDX commands to query SAS OLAP cubes and create various temporary OLAP components that can be used during a PROC SQL session including named sets and drill-through paths.
Here are the connection options for a SAS OLAP Server that is started with bridge protocol:
HOST=machine-name
specifies either the DNS name or the IP address of the machine that is hosting the OLAP server.
PORT=port-number | SERVICE=service-name
either the port-number or service-name is required. The port-number specifies the numeric value of the port on which the OLAP server resides. The service-name is used to look up the port number of the machine that is hosting the OLAP server.
USER=userid
a string that specifies the user's identification for the specified SAS OLAP server. If included, this option is enclosed with parentheses with the required arguments and any options.
PASS=password
a string that specifies the password for the user who is identified with the USER= option. If included, this option is enclosed with parentheses with the required arguments and any options.

Connecting to an OLAP Server Using the OLEDB Protocol

When connecting to OLE DB for OLAP data the SAS/ACCESS interface to OLE DB is used. With this approach you specify OLEDB in the CONNECT statement. For example: 
CONNECT TO oledb (provider=msolap prompt=yes);
With this approach, you can create a PROC SQL view of the data or specify MDX statements to access the OLE DB for OLAP data directly. In this approach the MDX statements can be used for read-only access of OLE DB for OLAP data. For further information about the CONNECT statement, see “Syntax for the Pass-Through Facility for Relational Databases” in the SAS/ACCESS for Relational Databases: Reference. Also see “Accessing OLE DB for OLAP Data” in the SAS/ACCESS 9.1 Supplement for OLE DB (SAS/ACCESS for Relational Databases).

Overview

Note: For detailed information about PROC SQL syntax, see “Overview of the Pass-Through Facility” in the SAS/ACCESS for Relational Databases: Reference and “Syntax for Remote SQL Pass-Through (RSPT) Facility” in the SAS/SHARE User's Guide.

Specifying User Authentication Information

When you use the SQL Pass-Through facility to connect to an OLAP server, you can choose how the user authentication information is identified. You can choose to store the user information in the PROC SQL statement or enter the information at the time of connection to an OLAP server.
  • Enter the user information with the connect statement. – The user name and password are included and stored in the PROC SQL syntax. For example: 
    connect to olap (host=localhost port=5451 user="user" pass="pass");
  • Enter the user information at the time of connection to an OLAP server. – Authentication with an OLAP server occurs at the time of connection and is accomplished with Integrated Windows authentication. You do not have to store a user name or password in the connection string. For example: 
    connect to olap (host=localhost port=5451);
    This option works in two instances:
    1. If both the OLAP server and the SAS client are started with the -SSPI option, then authentication is attempted using Integrated Windows authentication. This requires that both the OLAP server and the user's computer be Windows PCs that have access to a shared domain server. This usually occurs when both machines are on the same network.
    2. If the –SSPI option is not specified on either the client or the OLAP server, or if the Integrated Windows authentication fails, an attempt is then made to contact a metadata server to create a one-time password. This is possible when the OLAP server and the client use the same metadata server. The access information for the metadata server can either pre-exist in a SAS profile or otherwise be queried using a dialog box at runtime.
Note: For more information about Integrated Windows authentication, see the SAS Intelligence Platform: Security Administration Guide.
Copyright © SAS Institute Inc. All rights reserved.