How to use olapR

The olapR library provides a simple R style API for generating and validating MDX queries against an Analysis Services cube. olapR does not provide APIs for all MDX scenarios, but it does cover the most use cases including slice, dice, drilldown, rollup, and pivot scenarios in N dimensions. You can also input a direct MDX query to Analysis Services for queries that cannot be constructed using the olapR APIs.

Workflow for using olapR

Load the library.

Create a connection string pointing to a MOLAP cube on Analysis Services.

Verify you have read access on the cube

Use the connection string on a connection.

Verify the connection using the explore function.

Set up a query by submitting an MDX query string or by building a query structure.

Execute the query and verify the result.

To execute an MDX query on an OLAP Cube, you need to first create a connection string (olapCnn) and validate using the function OlapConnection(connectionString). The connection string must have a Data Source (such as localhost) and a Provider (MSOLAP).

After the connection is established, you can either pass in a fully defined MDX query, or you can construct the query using the Query() object, setting the query details using cube(), axis(), columns(), slicers(), and so forth.

Finally, pass the olapCnn and query into either executeMD or execute2D to get a multidimensional array or a data frame back.

MDX concepts

MDX is the query language for multidimensional OLAP (MOLAP) cubes containing processed and aggregated data stored in structures optimized for data analysis and exploration. Cubes are used in business and scientific applications to draw insights about relationships in historical data. Internally, cubes consist of mostly quantifiable numeric data, which is sliced along dimensions like date and time, geography, or other entities. A typical query might roll up sales for a given region and time period, sliced by product category, promotion, sales channel, and so forth.

Cube data can be accessed using various operations:

Slicing - Taking a subset of the cube by picking a value for one dimension, resulting in a cube that is one dimension smaller.

Dicing - Creating a subcube by specifying a range of values on multiple dimensions.

Drill-Down/Up - Navigate from more general to more detailed data ranges, or vice versa.

Roll-up - Summarize the data on a dimension.

Pivot - Rotate the cube.

MDX queries are similar to SQL queries but, because of the flexibility of OLAP databases, can contain up to 128 query axes. The first four axes are named for convenience: Columns, Rows, Pages, and Chapters. It's also common to just use two (Rows and Columns), as shown in the following example:

Using an AdventureWorks OLAP cube from the multidimensional cube tutorial, this MDX query selects the internet sales count and sales amount and places them on the Column axis. On the Row axis it places all possible values of the "Product Line" dimension. Then, using the WHERE clause (which is the slicer axis in MDX queries), it filters the query so that only the sales from Australia matter. Without the slicer axis, we would roll up and summarize the sales from all countries.