phpOLAPi - Connecting to OLAP databases from PHP

For MDX, the query language for multidimensional databases, there is no such thing as ODBC for SQL - no wide spread standard cross-platform way to interact with the OLAP cubes inside. Instead many OLAP databases provide webservices based on the XMLA standard (XML for Analysis). While being pretty powerful, XMLA has a really complex syntax. phpOLAPi provides OOP-style interface to work with XMLA webservices.

Requirements

phpOLAPi needs an active XMLA webservice to work with. Here is some information concerning XMLA support for common OLAP databases:

• Mondrian has XMLA built in by default
• Microsoft SQL Server Analysis Services (SSAS) requires some additional setup in IIS as described here.
• SAP BI (BW) supports XMLA via BI XMLA Connector
• Oralce OLAP should support XMLA via the commerical Simba MDX driver
• IBM Cognos, Jedox, Infor BI, Essbase, icCube, Kyvos, SAS and Microstrategy should support XMLA according to the OLAP server comparison on Wikipedia

Installing phpOLAPi

composer require kabachello/phpolapi

Connection setup

<?php
require_once 'vendor/autoload.php';
use phpOLAPi\Xmla\Connection\Connection;
use phpOLAPi\Xmla\Connection\Adaptator\SoapAdaptator;

// for Mondrian
$connection = new Connection(
    new SoapAdaptator('http://localhost:8080/mondrian/xmla'), 
    [
        'DataSourceInfo' => 'Provider=Mondrian;DataSource=MondrianFoodMart;'
        'CatalogName' => 'FoodMart',
        'schemaName' => 'FoodMart'
    ]
);

// for Microsoft SQL Server Analysis Services
$connection = new Connection(
    new SoapAdaptator('http://localhost/olap/msmdpump.dll', 'username', 'password'),
    [
        'DataSourceInfo' => null,
        'CatalogName' => 'Adventure Works DW 2008R2 SE'
    ]
);

Runing MDX queries from PHP

// Connect as shown above
$connection = ...

// Execute MDX statement
$resultSet = $connection->statement("
	SELECT [Measures].MEMBERS ON COLUMNS FROM [Adventure Works] 
");

// Transform to associative array
$renderer = new \phpOLAPi\Renderer\AssocArrayRenderer($resultSet);
$array = $renderer->generate();

MDX query builder

Instead of writing an MDX query manually, you can use the included query builder to build it in OOP-style.

use phpOLAPi\Mdx\Query;

$query = new Query("[Sales]");
$query->addElement("[Measures].[Unit Sales]", "COL");
$query->addElement("[Measures].[Store Cost]", "COL");
$query->addElement("[Measures].[Store Sales]", "COL");
$query->addElement("[Gender].[All Gender].Children", "COL");
$query->addElement("[Promotion Media].[All Media]", "ROW");
$query->addElement("[Product].[All Products].[Drink].[Alcoholic Beverages]", "ROW");
$query->addElement("[Promotion Media].[All Media].Children", "ROW");
$query->addElement("[Product].[All Products]", "ROW");
$query->addElement("[Time].[1997]", "FILTER");

$connection = ...

$resultSet = $connection->statement(
	$query->toMdx()
);

Exploring the schema

$connection = ...

$cube = $connection->findOneCube(null, array('CUBE_NAME' => 'Sales'));

// Cube data
$cube->getName(); // 'Sales'
// ...

// Measures
foreach ($cube->getMeasures() as $measure) {
	$measure->getCaption();
	// ...
}

// Dimensions
foreach($cube->getDimensionsAndHierarchiesAndLevels() as $dimention) {
	if($dimention->getType() != 'MEASURE') {
		$dimention->getCaption();
		// ...
		
		// Hierarchies
		foreach ($dimention->getHierarchies() as $hierarchy) {
			$hierarchy->getCaption();
			// ...
			
			// Hierarchy levels
			foreach ($hierarchy->getLevels() as $level) {
				$level->getCaption();
				// ...
			}
		}
	}
}

Using custom renderers for the ResultSet

The result of a query is a ResultSet instance, which mimics the (very complex) structure of an XMLA response. Renderers help extract the actual data, which is burried deep in the XML. In the first example above we used the AssocArrayRenderer to transform the ResultSet into an associative array. But there are also other renderers and you can also build your own!

use phpOLAPi\Renderer\Table\HtmlTableRenderer;
use phpOLAPi\Renderer\Table\CsvTableRenderer;
use phpOLAPi\Renderer\AssocArrayRenderer

$connection = ...

$resultSet = $connection->statement("
	SELECT	
		{ 
			[Measures].[internet Sales Amount],
			[Measures].[Internet Order Quantity] 
		} ON COLUMNS,
		{
			[Date].[Calendar].[Calendar Year].[CY 2006],
			[Date]. [Calendar].[Calendar Year].[CY 2007] 
		} ON ROWS
	FROM
	    [Adventure Works]
	WHERE
	    ([Customer].[Customer Geography].[Country].[Australia])

");

// Associative array (similar to the result of SQL queries)
$array = (new AssocArrayRenderer($resultSet))->generate();
var_dump($array);

// HTML table
$tableRenderer = new HtmlTableRenderer($resultSet);
echo $tableRenderer->generate();

// CSV
header("Content-type: application/vnd.ms-excel"); 
header("Content-disposition: attachment; filename=\"export.csv\"");
$csv = new CsvTableLayout($resultSet);
print($csv->generate()); 

Credits & License

phpOLAPi is a fork of phpOLAP by Julien Jacottet, which is unfortunately not maintained anymore. Both libraries are available under the MIT license.

Alternatives

Depending on the OLAP database, there may be other ways to connect from PHP. However, in most cases, they are a bit hackish: for example, in case of Microsoft SSAS there is a way to embed MDX statements in SQL queries to execute the via the sqlsrv PHP extension.