Publish Microstrategy MTDI Super Cube from S3 Parquet file - mstrio Python API

 

We need a Python IDE to run this. Currently, some of the Python packages (ex: boto3) are not supported in Microstrategy workstation scripts. So, we can't run this in Microstrategy workstation scripts.

Logic: 

1. Install necessary python packages 
2. Import packages (boto3, mstrio, pandas)
3. Establish connection to S3 bucket and read the parquet file
4. Establish connection to Microstrategy Rest server (Library)
5. Create cube

We can achieve the same using MSTR Data Import UI. But, in order to connect it to S3 Bucket in MSTR UI, you need "list all buckets" access. In API, we can specify the particular bucket name and parquet file to create this cube.

Sample Script:

# Install boto3 and mstrio packages. boto3 package is to read files from S3 bucket. mstrio package to connect and create objects in Microstrategy

#pip install boto3

#pip install mstrio

#Import necessary packages

from mstrio.connection import Connection

from mstrio.project_objects.datasets.super_cube import SuperCube, list_super_cubes

import boto3

import pandas as pd

import io

# Connect to S3 bucket and read parquet file

buffer = io.BytesIO()

s3 = boto3.resource('s3',aws_access_key_id='xxx',aws_secret_access_key='xxx')

object=s3.Object('S3 Bucket name','Parquet file name')

object.download_fileobj(buffer)

df=pd.read_parquet(buffer)

#df

#Connect it to Microstrategy project

#base_url = "MSTR REST server Link (Library)"

base_url = "http://servername:8080/MicroStrategyLibrary/api"

username = "xxx"

password = "xxxx"

conn = Connection(base_url, username, password, project_name="{Mstr Project name}", login_mode=1)

conn.connect()

#Create MTDI Super cube

cube = SuperCube(conn, name="Test_cube_py")

cube.add_table(name="test", data_frame=df, update_policy="replace", to_attribute=["column1", "column2"])

#Column1 & Column2 - Make sure you have the same column name available in the Parquet file & Dataframe

cube.create()