MicroStrategy REST API interface for R

More resources

• Learn more about the MicroStrategy REST API
• MicroStrategy REST API Demo environment

Connecting to the REST API server

To create a connection to the MicroStrategy Intelligence Server, you’ll need to obtain the address of the REST API server, typically following this format: https://yourMicroStrategyEnvironment.com/MicroStrategyLibrary/api.

To validate that the REST API server is running, in your web browser, visit https://yourMicroStrategyEnvironment.com/MicroStrategyLibrary/api-docs.

The Connection$new() function requires the URL to the REST API server, your username and password, and the name of the project which contains the data you are looking for. By default, the Connection$new() function expects your MicroStrategy username and password. If you use LDAP to access MicroStrategy, provide the optional argument login_mode=16 to the Connection$new() function. Connection$new() returns a list of environment connection data which is in turn used in subsequent requests to the API server.

library(mstrio)

conn <- Connection$new(base_url = 'https://demo.microstrategy.com/MicroStrategyLibrary/api',
                       username = username,
                       password = password,
                       project_name = 'MobileDossier',
                       login_mode = 8)

Extracting data from an existing cube

Now that we’ve logged in, let’s get some data! We can do this with the Cube$to_dataframe() function. Provide your connection object and the identifier of the cube containing the data. You can get the ID by navigating to the cube within MicroStrategy Web, right-clicking on the cube of interest, and selecting ‘properties.’ Alternatively, you can use MicroStrategy Developer in a similar manner. Cube$to_dataframe() will return a R dataframe with the cube data.

cube <- Cube$new(connection = conn, cube_id = '5E2501A411E8756818A50080EF4524C9')
cube$to_dataframe()
cube_data <- cube$dataframe

Extracting data from an existing report

If the data we need is contained in a report, we can extract it using Report$to_dataframe() function. Like Cube$to_dataframe(), this will return a dataframe containing the data from all rows.

report <- Report$new(connection = conn, report_id = '873CD58E11E8772BA1CD0080EF05B984')
report$to_dataframe()
report_data <- report$dataframe

Creating a new cube

You can create a new in-memory cube from a dataframe in R. This is accomplished with the Dataset class. You’ll need to specify a name for your dataset and a name for the table that will contain the data. You can add tables by calling add_table() multiple times. Among others, the Dataset object will hold the dataset_id and folder_id.

dat <- iris[1:50, ]

# note: column names in MicroStrategy cannot have a period (".")
names(dat) <- gsub("[[:punct:]]", "_", names(dat))

# this creates the dataset, and returns the dataset object, which you can use to update the dataset later
my_dataset <- Dataset$new(connection=conn, name="IRIS_Upload")

# add one or more tables to the dataset
my_dataset$add_table(name = "IRIS_Upload",
                     data_frame = dat,
                     update_policy = "add")
my_dataset$create()

Adding or modifying data in a cube

You can also modify data within a dataset. This is helpful if the data changes from day to day and you wish to add new data to a pre-existing dataset for analysis in MicroStrategy. To accomplish this, use the Dataset$update() function. Note that you’ll need to first instantiate a Dataset object using the the dataset_id of the target dataset. The update_policy parameter controls the update behavior. Currently supported update operations are add, update, upsert, and replace.

dat <- iris[51:150, ]

# note: column names in MicroStrategy cannot have a period (".")
names(dat) <- gsub("[[:punct:]]", "_", names(dat))

# Initialise the existing dataset using the `id` param.
my_dataset <- Dataset$new(connection = conn, id = my_dataset$dataset_id)

# update one or more tables to the dataset
my_dataset$add_table(name = "IRIS_Upload",
                     data_frame = dat,
                     update_policy = "add")

# push the new data to the MicroStrategy Environment
my_dataset$update(auto_publish=TRUE)

Mapping R data types to MicroStrategy metrics and attributes

When creating a dataset from a R dataframe, numeric columns (e.g. double, numeric, integer) are treated as metrics and non-numeric columns (e.g. strings, factors, date and time stamps) are treated as attributes. To override this default and specify columns to create specifically as an attribute or metric, an example being an integer that’s used as an attribute to join or link two data sets, you can provide a vector of metrics and attributes in the to_attribue and to_metric parameters.

dat <- iris

# note: column names in MicroStrategy cannot have a period (".")
names(dat) <- gsub("[[:punct:]]", "_", names(dat))

# create two new columns - one formatted as a numeric, and another formatted as a string
dat$integer_attribute <- as.integer(row.names(dat))
dat$integer_metric <- row.names(dat)

# create a new dataset, but this time, specifically instructs MicroStrategy to reflect these two new columns as a metric and attribute, respectively
# you can map multiple columns at once with to_metric = c("A", "B", "C")
new_dataset <- Dataset$new(connection=conn, name="IRIS")
my_dataset$add_table(name = "IRIS_Upload",
                     data_frame = dat,
                     update_policy = "add",
                     to_metric = c("integer_metric"),
                     to_attribute = c("integer_attribute"))
my_dataset$create()

Ending the session

Finally, terminate session with the server using the close() function.

conn$close()