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:
To validate that the REST API server is running, in your web browser, visit
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() 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
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
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
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