Run MDX queries on OLAP Cubes via ODBC or PHP sqlsrv extension

Microsoft SQL server provides the option to access so-called "linked servers" from within an SQL query. In particular, such a linked server can be an OLAP cube from the Analysis Services. There are two possibilities to do this:

1. Via OPENQUERY() - where you configure a linked server first using a special SQL command or the SQL Management Studio. Then you can use this server in your queries.
2. Via OPENROWSET() - where the connection is configured and established within each SQL query separately.

Both functions take a statement in a language compatible to the target database as an argument. In our case, this will be MDX (multidimensional expressions). In a sence, it's a subquery based on another language, that can be used within your SQL. This implies, that you can actually join data from your relational SQL database right onto the MDX result set, which is, of course, a huge advantage! 

Which one of the above methods to choose, largely depends on your specific needs and on the configuration of the SQL server. Thus, OPENROWSET() will often be unavailable for security reasons, while linked servers to be used via OPENQUERY() can be centrally configured by the DB administrator and then accessed by less priviledged users.

Connect to your SQL Server database

It does not really matter, how or which SQL database you connect to. However, you must use Windows Authentification. Here is an example for a connection through ODBC.

// Establish a connection via ODBC
if (! $conn = odbc_connect('Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;')) {
    thorw new \RuntimeException('Cannot connecto to SQL Server: ' . odbc_errormsg($conn));
}

Alternatively, you can establish a connection via Microsofts PHP extension sqlsrv and than do the following replacements in the subsequent code examples:

• odbc_exec -> sqlsrv_query
• odbc_errormsg($conn) -> sqlsrv_errors()[0]['message']

// Establish a connection via sqlsrv_connect()
if (! $conn = sqlsrv_connect('myserver:myport', ['Database' => 'myDataBase'])) {
    thorw new \RuntimeException('Cannot connecto to SQL Server: ' . sqlsrv_errors()[0]['message']);
}

Run MDX via OPENQUERY

OPENQUERY lets you execute a query on a previously defined linked server. Thus, we need to add a linked server first. You can either add it via SQL Server Management Studio or using the SQL command below. The following example does everything needed including dropping the linked server after the query had been performed.

// Add a linked server
$initLinkedServer = <<<SQL

EXEC sp_addlinkedserver 
    @server='EXAMPLE', 
    @srvproduct='',
    @provider='MSOLAP', 
    @datasrc='', 
    @catalog='Adventure Works DW'

SQL;

if (! $result = odbc_exec($conn, $initLinkedServer)) {
        throw new \Exception('Cannot add linked server: ' . odbc_errormsg($conn));
    }
}

// Perform an MDX query via OPENQUERY
$result = odbc_exec($conn, "
SELECT * FROM OPENQUERY(
    EXAMPLE,
    'SELECT [Measures].MEMBERS ON COLUMNS FROM [Adventure Works]'
)");

// drop linked server
odbc_exec($conn, "EXEC sp_dropserver @server='EXFOLAP'");

Of course, in a production environment, you should not add/drop the linked server for every query, but rather add it once beforehand. 

Run MDX via OPENROWSET

In contrast to OPENQUERY(), OPENROWSET() takes the connection configuration options as arguments, so the connection to the cube will be initialized for every query separately.

// Perform MDX query via OPENROWSET
$result = odbc_exec($conn, "
    SELECT a.* FROM OpenRowset(
        'MSOLAP',
        'DATASOURCE=myOlapServer; Initial Catalog=Adventure Works DW;',
        'SELECT [Measures].MEMBERS ON COLUMNS FROM [Adventure Works]'
    ) as a
");

Authentication

Obviously, the main drawback of this approach is being restricted to Windows Authentication. Your PHP server must connecto to the SQL database using a Windows user account - ideally the one of the current user and not that running the PHP server. The latter is easier, but makes it impossible to use personalized access rights in the database server.

Technically speaking, what you want to do is impersonate your users. Depending on your server stack there are multiple ways to achieve this, but, unfortunately, none seems to be really straight forward. Here are a couple of starting points:

• If PHP runs on IIS, fastcgi.impersonate seems a valid option.
• If you are using Apache, here is a helpful discussion on stack overflow.

Unfortunately, Analysis Services generally only allow access via Windows Authentication. The only way around it seems to be using XMLA over HTTP, where you can map basic HTTP authentication credentials to a Windows user account.