A OLAP MDX interface

RMDX	R Documentation

RMDX - XML/A OLAP interface, specifically Mondrian, but should support others eg: SAP HANA

Description

create a connection object for accessing an XML/A OLAP service. This could be Mondrian, SAP Hana, or MS-SQL Server.

RMDX establishes an XML/A connection object to a specific MDX compliant XML/A server instance. There are two styles of passing the connection parameters:

- RMDX('conn.yml') where the name of A YAML encoded file containing RMDX connection parameters is passed in

- RMDX(url='http://localhost:8080/pentaho/Xmla', userid='joe', password='password')

The individual connection parameters are:

url - the base URL for the XML/A service
userid - XML/A service user name
password - user password
debug - TRUE - turn on debug
curlopts - list() of options to pass to curl - currently only support httpauth (for values see: https://curl.haxx.se/libcurl/c/CURLOPT_HTTPAUTH.html, and https://stackoverflow.com/questions/26255964/r-negotiate-authentication-with-rcurl-or-httr)

There maybe some problems with SSL negotiation - especially if you are using self-signed certs. The following Curl options may help - especially downgrading SSL to v3 (this must also be made available on the server):

options(RCurlOptions = list(ssl.verifypeer = FALSE, sslversion=3, verbose = TRUE, cainfo = "/etc/ssl/certs/some-certificate.pem", followlocation = TRUE, timeout = 100, useragent = "myApp in R"))

Usage

RMDX(...)

Arguments

`url`	the base URL for the XML/A service eg: `http://localhost:8080/pentaho/Xmla`
`userid`	XML/A service user name
`password`	user password

Details

Once a connection object has been obtained, then the follwoing functions can be applied to interrogate the server:

olapsources enables the lookup of datasource names required as input to all other functions eg: olapsources(conn). A data.frame is returned with the data source details of the connected server.

olapcatalogs(conn, datasource) - list the catalogs for a data source

olapcubes(conn, datasource, catalog) - list the cubes for a catalog within a data source

cubedimensions(conn, datasource, catalog, cube) - list the dimensions for a cube cubelevels(conn, datasource, catalog, cube) - list the levels for a cube cubemeasures(conn, datasource, catalog, cube) - list the measures for a cube

cubeexplore(conn, datasource, catalog, schema, cube) - list alternative schema information for a cube

mdxquery(conn, datasource, catalog, query) - execute an MDX query and get a data.frame back

Function arguments:

conn - an object of class RMDXConnector created with RMDX(...). print() or str() conn to inspect connection details
datasource - a string that indicates the name of the DataSourceInfo of the required call context
catalog - a string that indicates the name of the Catalog
cube - a string that indicates the name of the Cube interrogated
query - a string that representing the MDX query to execute within the datasource/catalog context

Value

Returns an RMDXConnector object that you can then use to pass to olapsources, olapcatalogs, olapcubes, cubedimensions, cubemeasures and mdxquery.

Note

Rd is a pain so for future reference - http://cran.r-project.org/doc/manuals/r-release/R-exts.html#Rd-format .

Author(s)

Piers Harding

References

MDX Reference

XML/A References

Examples

## Not run: 
# Connecting to Pentaho BI Server 5.x

# full named parameter specification
conn <- RMDX(url='http://localhost:8080/pentaho/Xmla', userid='piers', password='password')

or

# connect using parameters
conn <- RMDX('http://localhost:8080/pentaho/Xmla', 'Admin', 'password')

or

# Use a YAML encoded parameter file
conn <- RMDX("conn.yml")
# conn.yml looks like:
# url: http://localhost:8080/pentaho/Xmla
# userid: Admin
# password: password

# list the data sources for a server
olapsources(conn)

# list the catalogs for a data source
olapcatalogs(conn, 'Pentaho')

# list the cubes for a catalog within a data source
olapcubes(conn, 'Pentaho', 'SampleData')

# list the dimensions, levels and measures for a cube
cubedimensions(conn, 'Pentaho', 'SampleData', 'Quadrant Analysis')
cubelevels(conn, 'Pentaho', 'SampleData', 'Quadrant Analysis')
cubemeasures(conn, 'Pentaho', 'SampleData', 'Quadrant Analysis')

# execute an MDX query and get a data.frame back
r <- mdxquery(conn, 'Pentaho', 'SampleData', 'SELECT
    NON EMPTY {Hierarchize({{[Measures].[Actual], [Measures].[Budget], [Measures].[Variance]}})} ON COLUMNS,
    NON EMPTY CrossJoin([Department].[Department].Members, [Positions].[Positions].Members) ON ROWS
    FROM [Quadrant Analysis]')

## End(Not run)

piersharding/RMDX documentation built on May 17, 2022, 8:42 p.m.