The MicroStrategy REST API is a RESTful application that uses HTTP requests such as POST, GET, and DELETE. It is designed to help developers build data-driven client applications quickly and easily. It does this by providing light-weight JSON data that is easy to consume because it includes raw data without direct formatting. Developers can use the MicroStrategy REST API programmatically in their own code, or in a tool like curl.
This R package was designed to give data scientists a way to easily extract data from MicroStrategy cubes and reports, and create new in-memory datasets within MicroStrategy.
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)
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
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
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()
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)
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()
Finally, terminate session with the server using the close() function.
conn$close()