---
title: "MicroStrategy REST API interface for R"
author: "Scott Rigney"
date: "`r Sys.Date()`"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{MicroStrategy REST API interface for R}
  %\VignetteEngine{knitr::knitr}
  %\VignetteEncoding{UTF-8}
---

```{r, include=FALSE}
library(mstrio)
```

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.

### More resources
- [Learn more about the MicroStrategy REST API](https://www2.microstrategy.com/producthelp/Current/RESTSDK/Content/topics/REST_API/REST_API.htm)
- [MicroStrategy REST API Demo environment](https://demo.microstrategy.com/MicroStrategyLibrary/api-docs/index.html)

## 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.

```{r, include=FALSE}
username <- "demouser"
password <- "demopass"
```

```{r, eval=FALSE}
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.

```{r, eval=FALSE}
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.

```{r, eval=FALSE}
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`.

```{r, eval=FALSE}
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`.

```{r, eval=FALSE}
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.

```{r, eval=FALSE}
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.

```{r, eval=FALSE}
conn$close()
```