KB485674: Create Super Cube from Excel file and update it with a script in MicroStrategy Workstation

Action

1. Prepare an Excel file which you want to use as a data source and save it in CSV format.
2. Open Workstation, log into your environment and click plus icon which is located next to Scripts.

1. Start your script from importing Connection object from

   mstrio.connection

   module which is necessary to create connection to Strategy environment.

1. From

   mstrio.project_objects.datasets.super_cube

   module import

   SuperCube.

1. Import Pandas analysis toolkit using

   import pandas as pd

   to be able to read an Excel file.

1. Create following four new variables that will be used as Connection parameters (names can differ):   
   
   base_url – assign Strategy Library URL as a value 
   username – assign a username with sufficient privileges granted as a value 
   password – assign a user password as a value  
   project_name – assign as a value a name of a project in which a Super Cube should be created  
   
   Your script should look similar to the below: 

1. Create

   Connection

   object using parameters specified above: 

1. Load your CSV file into a DataFrame providing full path, i.e. 

1. Create a new variable and assign SuperCube with two parameters inside – connection and name, i.e. 

1. Use

   SuperCube.add_table()

   with 3 parameters inside to define a name of a table, a data frame source and an update policy. As it is required to define an update policy, for a new cube use

   update_policy="replace"

1. Use

   SuperCube.create()

   method to create a Super Cube. Suppose you want to put it in a specific folder, define

   folder_id

   parameter inside  
   
   Note: From default Super Cube is being published automatically. If you do not want to publish it at this stage, add

   auto_publish

   parameter inside

   SuperCube.create

   method and set it to False 

1. Save and run the script. You should get a message that your Super Cube has been created and published successfully.
   
   Suppose you came across any issues, check if your script looks like the below:

1. To update the newly created Super Cube, create a new script starting from repeating steps 3-7
2. Load an updated CSV file into a DataFrame providing full path  
   
   Note: number of columns should be the same as in the original file  

1. Create a new variable and assign

   SuperCube

   with two parameters inside –

   connection

   and

   id

   . Use ID of the original cube (the one you created), i.e.  

1. Similar to step 10, use SuperCube.add_table() with 3 parameters inside to define a name of a table (keep the one from the original cube), a data frame source and an update policy. 
   
   This time there are couple of options that can be applied for an update policy: 

• add - insert entirely new row of data 
• update - update existing metrics values
• upsert - insert entirely new row of data and simultaneously update metrics & attributes values 
• replace - truncates and replaces the data (number of columns cannot change) 

1. Use

   SuperCube.update()

   method to update your Super Cube.

1. Save and run the script to accomplish the update process. Your script should look similar to the below: