KB486704: Example of importing data from Cube or Report to dataframe and save them back to SuperCube

Software Engineer • MicroStrategy

This Knowledge Base article offers practical examples and a comprehensive guide for importing and exporting data within MicroStrategy using a Jupyter Notebook. It details four distinct workflows that can be executed through the Jupyter plugin interface, covering the processes of connecting to a MicroStrategy project, importing data from cubes and reports into pandas DataFrames, exporting data from a DataFrame to a new SuperCube, and updating an existing SuperCube with modified data.

Introduction

The script below has been created in Jupyter Notebook with Strategy 2021 Update 12 and mstrio-py 11.4.3.101 but it’s compatible also with 11.3.12.101.
Please note that you should replace placeholder values such as <username>, <password>, <created_cube_ID> with actual values snd update environment url to correct one.
Additionally, the script assumes that the mstrio-py library is installed and that you have the necessary permissions to perform these actions in the Strategy environment.
The script shows fours basic workflows that Jupyter connector was able to achieve:

Import data from selected cube to DataFrame - listing available cubes, selecting one, applying filters, and importing the data into a pandas DataFrame.
Import data from selected report do DataFrame - listing available reports, selecting one, applying filters, and importing the data into a pandas DataFrame.
Export local DataFrame to new SuperCube - defining a new DataFrame and exporting it to a new SuperCube in Strategy, certifying the cube afterward.
Update cube table - modifying a DataFrame and updating an existing SuperCube with the new data.

Workflows 1. and 2. are independent.
Workflow 3. depends on data imported in workflow 1.
Workflow 4. depends on workflow 3.
Note - Color formatting has been added in the code below (http://hilite.me/). Code copied from here might not work due to indentation issues. In that case, use attached file with sample code. Attached is the Jupyter Notebook file.

Script


# # Example of importing data from Cube or Report to dataframe and save them back to SuperCube
# ### Defining connection details

from mstrio.connection import Connection
from mstrio.project_objects.datasets import list_all_cubes, OlapCube, SuperCube
from mstrio.project_objects import Report, list_reports
from mstrio.users_and_groups import User
import pandas as pd
from time import sleep

base_url = 'https://env-XXXXXX.customer.cloud.Strategy.com/MicroStrategyLibrary/'
username = '<username>'
password = '<password>'
project_name = 'Strategy Tutorial'

connection = Connection(base_url, username, password, project_name)

# ## 1. Import data from selected cube to DataFrame workflow

# Listing available cubes and picking one to import to dataframe

# Define owner for listing function filtering
admin = User(connection, name='Administrator')
# List available Cubes filtered to owner - 'Administrator'
admin_cubes = list_all_cubes(connection, owner=admin)
# List available Cubes (limited to 10 results)
cubes = list_all_cubes(connection, limit=10)
for cube in cubes:
    print(cube)

# Selecting sample OlapCube ID for later use
sample_cube_id = '42FF415D4E162846C87D4FAD8B26BF4E'

# Check and select metrics and attributes to filter Cube data

# Check available attributes and metrics of a Cube 
sample_cube = OlapCube(connection, id=sample_cube_id)
attributes = sample_cube.attributes
metrics = sample_cube.metrics
print(f"Attributes: {attributes}\nMetrics: {metrics}")
# Choose attributes and metrics to use when applying filters on Cube
call_center_attribute = attributes[0].get('id')
category_attribute = attributes[1].get('id')

profit_metric = metrics[0].get('id')
profit_margin_metric = metrics[1].get('id')

# Display and select attribute elements to use when applying filters on Cube

# First publish Cube it will be needed to display attributes elements and before applying filters on Cube
sample_cube.publish()
sleep(5)
# Display attributes elements
sample_cube.attr_elements

# Select attributes elements values for filter based on previous cell output
# For attribute 'Category' we selected 3 elements IDs that corespond to: Electronics, Movies, Music categories
att_elements_filter = ["8D679D3711D3E4981000E787EC6DE8A4:2",
                       "8D679D3711D3E4981000E787EC6DE8A4:3",
                       "8D679D3711D3E4981000E787EC6DE8A4:4"]

# Apply picked earlier filters on Cube, export to dataframe and display first 5 rows

# Filter which attributes and metrics to use in a dataframe
sample_cube.apply_filters(
    attributes=[call_center_attribute, category_attribute],
    metrics=[profit_metric, profit_margin_metric],
    attr_elements=att_elements_filter
)
# Create a dataframe from a Cube
cube_dataframe = sample_cube.to_dataframe()
# Display resulting dataframe
cube_dataframe


# ## 2. Import data from selected report do DataFrame workflow

# Listing available Reports and picking one to import to dataframe

# List available Cubes filtered to owner - 'Administrator'
sample_admin_reports = list_reports(connection, owner=admin)

# List available Reports (limited to 10)
sample_reports = list_reports(connection, limit=10)
for report in sample_reports:
    print(report)
    
# Selecting sample report for later steps
sample_report_id = '759C0B5340E0AD6FAEDFF19D4DBC3488'

# Check and select metrics and attributes to filter Report data

# Check available attributes and metrics of a Report
sample_report = Report(connection, id=sample_report_id)
attributes = sample_report.attributes
metrics = sample_report.metrics
print(f"Attributes: {attributes}\nMetrics: {metrics}")

# Choose attributes and metrics
category_attribute = attributes[0].get('id')
subcategory_attribute = attributes[1].get('id')

cost_metric = metrics[0].get('id')
profit_metric = metrics[1].get('id')

# Apply picked earlier filters on Report, export to dataframe and display first 5 rows

# Filter which attributes and metrics to use in a dataframe
sample_report.apply_filters(
    attributes=[category_attribute, subcategory_attribute],
    metrics=[cost_metric, profit_metric],
)

# Create a dataframe from a Report
report_dataframe = sample_report.to_dataframe()
# Display resulting dataframe
report_dataframe


# ## 3. Export local DataFrame to new SuperCube workflow

# Define new dataframe object

# new local dataframe sales_df definition
sales = {
    "store_id": [1, 2, 3],
    "category": ["TV", "Books", "Accessories"],
    "sales": [400, 200, 100],
    "sales_fmt": ["$400", "$200", "$100"]
}
sales_df = pd.DataFrame(sales, columns=["store_id", "category", "sales", "sales_fmt"])

# Define new SuperCube named 'new_cube' and add first table with 'sales_df' dataframe

# new_cube definition
mstr_dataset = SuperCube(connection, name="new_cube", description="new cube with exported dataframes")

# Adding sales_df dataframe as a first table, mapping attributes and metrics
# This is initial operation so update policy is 'replace'
mstr_dataset.add_table(
  name="sales_df",
  data_frame=sales_df,
  update_policy="replace",
  to_metric=["store_id","sales"],
  to_attribute=["category","sales_fmt"]
)

# Add previous 'cube_dataframe' as second table of this 'new_cube'

# Display cube_dataframe to map properly numeric values to metrics and non-numeric to attributes
cube_dataframe.head()

# Adding second table as cube_dataframe, mapping attributes and metrics
# This is initial operation so update policy is 'replace'
mstr_dataset.add_table(
  name="cube_dataframe",
  data_frame=cube_dataframe,
  update_policy="replace",
  to_metric=["Profit Margin","Profit"],
  to_attribute=["Call Center","Category"]
)

# Create already defined SuperCube, certify it and publish

# Trigger SuperCube create ( fetching local data with server)
mstr_dataset.create(folder_id="E28806A611E5B55F088C0080EF555BA1", force="True")
mstr_dataset.certify()


# ## 4. Update cube table workflow

# Drop last two rows from cube_dataframe

# Number of rows to drop
n = 2 
# Dropping last n rows of dataframe
cube_dataframe.drop(cube_dataframe.tail(n).index,inplace = True)
# Display current cube_dataframe
cube_dataframe

# Update 'new_cube' table with 'cube_dataframe'

# list cubes when name includes 'new_cube'
list_all_cubes(connection, name='new_cube')

# Save ID of Cube that we created in prevoius steps
new_cube_ID = '<created_cube_ID>'

# Initialise 'new_cube'
new_cube = SuperCube(connection, id=new_cube_ID)

# Add local dataframe 'cube_dataframe' as new table with update policy set to 'update'
# To update table name of added table must match aready exisitng table in this case it is 'cube_dataframe' 
new_cube.add_table(name="cube_dataframe", data_frame=cube_dataframe, update_policy="update")

# Trigger SuperCube update ( fetching local data with server)
new_cube.update()

Comment

0 comments

Details

Knowledge Article

Published:

May 30, 2024

Last Updated:

May 30, 2024

Introduction

Import data from selected cube to DataFrame - listing available cubes, selecting one, applying filters, and importing the data into a pandas DataFrame.

Import data from selected report do DataFrame - listing available reports, selecting one, applying filters, and importing the data into a pandas DataFrame.

Export local DataFrame to new SuperCube - defining a new DataFrame and exporting it to a new SuperCube in Strategy, certifying the cube afterward.

Update cube table - modifying a DataFrame and updating an existing SuperCube with the new data.

Script

# # Example of importing data from Cube or Report to dataframe and save them back to SuperCube # ### Defining connection details from mstrio.connection import Connection from mstrio.project_objects.datasets import list_all_cubes, OlapCube, SuperCube from mstrio.project_objects import Report, list_reports from mstrio.users_and_groups import User import pandas as pd from time import sleep base_url = 'https://env-XXXXXX.customer.cloud.Strategy.com/MicroStrategyLibrary/' username = '<username>' password = '<password>' project_name = 'Strategy Tutorial' connection = Connection(base_url, username, password, project_name) # ## 1. Import data from selected cube to DataFrame workflow # Listing available cubes and picking one to import to dataframe # Define owner for listing function filtering admin = User(connection, name='Administrator') # List available Cubes filtered to owner - 'Administrator' admin_cubes = list_all_cubes(connection, owner=admin) # List available Cubes (limited to 10 results) cubes = list_all_cubes(connection, limit=10) for cube in cubes: print(cube) # Selecting sample OlapCube ID for later use sample_cube_id = '42FF415D4E162846C87D4FAD8B26BF4E' # Check and select metrics and attributes to filter Cube data # Check available attributes and metrics of a Cube sample_cube = OlapCube(connection, id=sample_cube_id) attributes = sample_cube.attributes metrics = sample_cube.metrics print(f"Attributes: {attributes}\nMetrics: {metrics}") # Choose attributes and metrics to use when applying filters on Cube call_center_attribute = attributes[0].get('id') category_attribute = attributes[1].get('id') profit_metric = metrics[0].get('id') profit_margin_metric = metrics[1].get('id') # Display and select attribute elements to use when applying filters on Cube # First publish Cube it will be needed to display attributes elements and before applying filters on Cube sample_cube.publish() sleep(5) # Display attributes elements sample_cube.attr_elements # Select attributes elements values for filter based on previous cell output # For attribute 'Category' we selected 3 elements IDs that corespond to: Electronics, Movies, Music categories att_elements_filter = ["8D679D3711D3E4981000E787EC6DE8A4:2", "8D679D3711D3E4981000E787EC6DE8A4:3", "8D679D3711D3E4981000E787EC6DE8A4:4"] # Apply picked earlier filters on Cube, export to dataframe and display first 5 rows # Filter which attributes and metrics to use in a dataframe sample_cube.apply_filters( attributes=[call_center_attribute, category_attribute], metrics=[profit_metric, profit_margin_metric], attr_elements=att_elements_filter ) # Create a dataframe from a Cube cube_dataframe = sample_cube.to_dataframe() # Display resulting dataframe cube_dataframe # ## 2. Import data from selected report do DataFrame workflow # Listing available Reports and picking one to import to dataframe # List available Cubes filtered to owner - 'Administrator' sample_admin_reports = list_reports(connection, owner=admin) # List available Reports (limited to 10) sample_reports = list_reports(connection, limit=10) for report in sample_reports: print(report) # Selecting sample report for later steps sample_report_id = '759C0B5340E0AD6FAEDFF19D4DBC3488' # Check and select metrics and attributes to filter Report data # Check available attributes and metrics of a Report sample_report = Report(connection, id=sample_report_id) attributes = sample_report.attributes metrics = sample_report.metrics print(f"Attributes: {attributes}\nMetrics: {metrics}") # Choose attributes and metrics category_attribute = attributes[0].get('id') subcategory_attribute = attributes[1].get('id') cost_metric = metrics[0].get('id') profit_metric = metrics[1].get('id') # Apply picked earlier filters on Report, export to dataframe and display first 5 rows # Filter which attributes and metrics to use in a dataframe sample_report.apply_filters( attributes=[category_attribute, subcategory_attribute], metrics=[cost_metric, profit_metric], ) # Create a dataframe from a Report report_dataframe = sample_report.to_dataframe() # Display resulting dataframe report_dataframe # ## 3. Export local DataFrame to new SuperCube workflow # Define new dataframe object # new local dataframe sales_df definition sales = { "store_id": [1, 2, 3], "category": ["TV", "Books", "Accessories"], "sales": [400, 200, 100], "sales_fmt": ["$400", "$200", "$100"] } sales_df = pd.DataFrame(sales, columns=["store_id", "category", "sales", "sales_fmt"]) # Define new SuperCube named 'new_cube' and add first table with 'sales_df' dataframe # new_cube definition mstr_dataset = SuperCube(connection, name="new_cube", description="new cube with exported dataframes") # Adding sales_df dataframe as a first table, mapping attributes and metrics # This is initial operation so update policy is 'replace' mstr_dataset.add_table( name="sales_df", data_frame=sales_df, update_policy="replace", to_metric=["store_id","sales"], to_attribute=["category","sales_fmt"] ) # Add previous 'cube_dataframe' as second table of this 'new_cube' # Display cube_dataframe to map properly numeric values to metrics and non-numeric to attributes cube_dataframe.head() # Adding second table as cube_dataframe, mapping attributes and metrics # This is initial operation so update policy is 'replace' mstr_dataset.add_table( name="cube_dataframe", data_frame=cube_dataframe, update_policy="replace", to_metric=["Profit Margin","Profit"], to_attribute=["Call Center","Category"] ) # Create already defined SuperCube, certify it and publish # Trigger SuperCube create ( fetching local data with server) mstr_dataset.create(folder_id="E28806A611E5B55F088C0080EF555BA1", force="True") mstr_dataset.certify() # ## 4. Update cube table workflow # Drop last two rows from cube_dataframe # Number of rows to drop n = 2 # Dropping last n rows of dataframe cube_dataframe.drop(cube_dataframe.tail(n).index,inplace = True) # Display current cube_dataframe cube_dataframe # Update 'new_cube' table with 'cube_dataframe' # list cubes when name includes 'new_cube' list_all_cubes(connection, name='new_cube') # Save ID of Cube that we created in prevoius steps new_cube_ID = '<created_cube_ID>' # Initialise 'new_cube' new_cube = SuperCube(connection, id=new_cube_ID) # Add local dataframe 'cube_dataframe' as new table with update policy set to 'update' # To update table name of added table must match aready exisitng table in this case it is 'cube_dataframe' new_cube.add_table(name="cube_dataframe", data_frame=cube_dataframe, update_policy="update") # Trigger SuperCube update ( fetching local data with server) new_cube.update()