Create a Python Enabled Transaction Dashboard

Starting in MicroStrategy ONE (March 2024), you can create Python enabled transaction dashboards in MicroStrategy Cloud environments and on the MicroStrategy Cloud for Government platform. To be create and execute a Python-enabled transaction dashboard, you must:

• Make sure the environment supports the usage of the Python executor

  This feature is supported on the Microstrategy Cloud Environment platform. Contact MicroStrategy Support to enable this feature.

• Choose a data source with a public endpoint for writing back data changes

• Update the metadata to the latest version

• Create a transaction enabled dashboard

• Create a custom runtime and download all necessary Python packages as part of that runtime

• Create a Python script with the required data manipulation actions

• Configure the dashboard with the associated script

Get started by creating a transaction enabled dashboard and then follow the steps below.

• Create a Custom Runtime

• Create a Custom Python Script

• Configure the Dashboard with the Associated Python Script

• Use Python Transactions for Data Operations

• System Configuration Recommendations

• Known Limitations

Create a Custom Runtime

The Manage Runtimes privilege is required. Users with this privilege can download third party packages, so only vetted analysts should have this privilege.

See Create and Edit Python Runtimes for more information.

Users with the Manage Runtime privilege can view and manage currently existing runtimes and create new ones. All environments have a default runtime. When creating a runtime users can changes its name, description, addresses it can connect to, packages installed on it, and pick a size for script execution. The size of the runtime instance determines the resources (CPU and memory) available to each script execution associated with that runtime.

1. Open Workstation and connect to an environment.

2. In the Navigation pane, click Monitors.

   All environments have a default runtime name of (Default) MicroStrategy.

   

   Default runtimes include the latest versions of both Python and mstrio packages, as well as a default instance size and default network access configuration. Users can use the mstrio package to access MicroStrategy data using the default runtime.

   Users cannot change the configuration of the default runtime. If a user wants to customize the instance size, network access, or package configuration, they must create a new runtime.

3. In the left pane, click next to Runtime.

   

4. Enter a Runtime Name and Description. Newly created runtimes use the latest Python Version. For existing runtimes, you can upgrade to the latest Python version. The default Runtime Instance Size is 0.2 vCPU 0.5 GB of memory.

   

5. In Network Access, enter the network with allowed access from the Python runtime. The format must follow Classless Inter-Domain Routing (CIDR) format, such as 141.193.213.21/32.

6. Click in the lower left to add a new package to be installed in the Python runtime. If the package version is not provided, the latest version is installed. Use the format: [package name][whitespace][package version].

   Examples:

   • requests 2.28.1

   • requests

   

7. Click Add.

8. Packages typically support connectivity to the data source to write back data changes, support manipulations with MicroStrategy APIs, or connect to other applications such as Microsoft Teams. Click the Pypi link to find the exact package name and version to download.

9. Click Add to save the runtime and wait for the installation to finish. Refresh the page to check the status.

   

Some helpful tips:

• Make sure to hit Enter after entering each field (Network access, package name and version) before clicking Add or OK to confirm the change.

• You can add more than one package to one runtime.

• After object migration from one environment to another, re-add packages in the new environments to get the runtime ready. The runtime does not work without this additional step.

• By default, Everyone has a View ACL for a runtime.

Create a Custom Python Script

1. Open Workstation and connect to an environment.

2. In the Navigation pane, click next to Scripts.

3. Open the script settings to change the script type to Transaction and select the appropriate Python runtime.

   

   

4. Start creating a script by selecting a template from the gallery and modifying it or start from scratch.

   

5. Variables in a script typically map to columns in the dashboard grid. When defining variables the Column parameter is turned on by default. Turn this off for non-transactional variables, such as if the variable is for internal use, like connecting to a data source.

   

6. For the data source connection password, select the non-transactional type variable, Secret.

   

7. Consider including a system prompt variable to record users' logins and see who is making changes to the data.

   

8. The default action types are select, insert, and update. To create new action types in addition to these, such as combining both select and update, select Transaction Type in Variable type. In a single Python transaction script, only one variable can be set as Transaction Type.

9. For a variable that maps to the primary key of a table in a write back data source, you do not want the consumer to modify the primary key. In this situation, you should disable the Editable parameter.

10. For columns in the data source that do not allow NULL values, mark this variable as required by turning on the Required parameter.

11. If a variable is not Required, authors can choose whether or not a column can be left empty, depending on what the database allows.

12. For columns that you want to users to edit, turn on the Editable parameter.

    

13. Only certified transaction Python scripts can be used for the transaction operations, so you should certify the script once it is ready for production use. The group of users that will consume Python transactional grids should have the View ACL for this script.

Best Practices

• Learn from a sample code template.

• For data sources supporting atomic transactions, pick the package to support the atomic transaction when preparing the runtime. In the Python script, add sections to handle atomic transaction.

• Use parameterized queries when applicable. Queries executed in such a way are not susceptible to injection attacks because the query and parameters are sent over for execution separately.

• Since the Python transaction script creator and transaction dashboard author may not be the same person, ensure parity between the script and dashboard. This includes the definition of the data source table to write back, mapping relationships between the variables defined in the Python script and the dashboard transaction grid object, which object is editable, and so on.

• Detailed descriptions of what the Python script supports (update data, insert new data, select rows to delete or insert to a different table, send Microsoft Teams messages, send emails, and so on) should be included in the script. The dashboard author can retrieve the description of the Python script from the Dashboard Editor. Only a person with proper privileges and ACLs can open Python scr

Configure the Dashboard with the Associated Python Script

1. Python based transactions are designed to work with modern grids. Select a grid, open the Format panel, and click Transaction Options .

   

2. Click Use Python.

   

3. Select action types and provide a customized action name, button name, and so on.

   • Action types are the actions that you want to allow the consumer to perform on this dashboard. At least one action type must be selected.

   • Give the action a meaningful name, describe what the action can do, and give the button a name.

     Example:

     • Action Name Update Customer Age

     • Description Please update the age of the customer in the Customer-Age column.

     • Button Name Submit

   • You can combine actions. For example, you can modify existing data and add additional rows of data at the same time. Choose action names that reflect this behavior accordingly.

   

4. Click Choose Python Script to retrieve all transaction Python scripts from the project that the dashboard is in. By default, only certified scripts appear. Use the filter to navigate the list and find the target script.

   

5. After selecting a script, the mapping section appears. The selected script name appears with an information icon to show the script description. In the upper right of this section, an icon to replace the selected script and an icon to refresh the script list from the metadata is provided. Variables requiring data are marked with an asterisk (*). In these cases, mapping must be provided to save the configuration.

   Action Types

   • Allow Modifying data in grid The Editable option is enabled. Authors can select the variable that they want consumers to be able to modify.

   • Adding data to grid The New Data Entry option is selected and you can select the variables you want consumers to be able to insert.

   • Take action on selected rows The Editable and New Data Entry options are both disabled. In these cases, the row number is passed to the script. The script determines what action is taken on that row. For example, you can use this action to delete a row from the grid.

   

6. To configure the input controls, see Transactions in Grids.

7. Click to add more actions and configure the message to display after submission.

   

8. Click Done to complete your transaction and Save the dashboard.

Best Practices

• Depending on the data source for writing back data changes, usually the SQL based one is the first choice. If the data source is web services, Python is the preferred option.

• For more complex workflows that combine data changes with actions like sending emails, updating Slack, and so on, use Python scripts.

• We recommend a one to one mapping relationship between the script variable and grid objects for easy input control configuration and to define whether the grid object is editable or not.

Use Python Transactions for Data Operations

1. In-line editing is not supported for Python based transactions, so all data modifications are done in bulk mode. Click to start modifying data.

   

2. Both selection and data modification are supported for a single action.

   

3. For actions that support selection, modification, or data insertion, click Add data to provide new data. When you are finished, click Add.

   

4. Click Submit.

System Configuration Recommendations

Containerized environments allow you to run scripts directly in a MicroStrategy environment without the need to leave a computer running. You can save your scripts in these environments and access them anywhere within Workstation, as well as provide access to other administrators. Currently, the support of server-side script execution is turned on for containerized environments only.

You can improve the performance of Python transaction requests in the following ways:

• Adjust Runtime Instance Size

• Adjust Runtime Settings

Adjust Runtime Instance Size

1. In the Navigation pane, click Monitors.

2. In the left pane, click Runtime.

3. In the grid, right-click the runtime and choose Edit.

4. Adjust the Runtime Instance Size.

Why should I change the runtime size?

The default runtime size may not be sufficient for your script's memory needs. By increasing the runtime size, you can ensure that your script has enough memory to run without errors or crashes. For example, when using a script to create a data source, it is best to use larger runtimes, since a script used to create tables could load them into memory.

What are the risks involved in using a large environment?

A large containerized environment can also consume a lot of memory, which can lead to performance issues on the host machine and potentially cause the container to crash. MicroStrategy containers have limited memory for Python runtimes. This is currently limited to 6 vCPU and 20 GB of memory for containerized environments. It's important to regularly assess the size and complexity of your container environment and make sure it aligns with your performance, security, and maintenance requirements. It is best to run administrative scripts on smaller runtimes to reduce resource usage.

Adjust Runtime Settings

1. In the Navigation pane, click Monitors.

2. In the left pane, click Runtime.

3. In the grid, right-click the runtime and choose Get Info.

4. In the left pane, click All Settings.

   

5. Adjust the runtime settings.

   Script timeout (sec) Timeout setting for Python script execution. The default setting is 600 seconds.

   Default Pod instance number The default count of a cached Python execution instance.

   Python execution instance If a Python script is running based on a Python runtime, a Python execution instance is created and can be cached according to the Default Pod instance number and Maximum Pod instance number (total run scripts limits) settings.

   Maximum Pod instance number (total run script limits) The maximum number of Python execution instances that can be created based on the runtime.

Runtime Default Pod instance number	Runtime Maximum Pod instance number (total run scripts limits)	Recommendation
1	1	For casual users with low usage and concurrency that are sensitive to response time. Since the Python executor pod is always up, the pod warm up time can be reduced, but the overall cost is higher. One pod is on at all times.
1	N>1	For casual users with low usage and some concurrency, but not very sensitive to response time. Since one Python executor pod is always up and up to N-1 pods can be up on request, this configuration can support more concurrency cases. Some users have a good response time; others may experience the pod warn up time. One pod is always up and up to N-1 pods can be started and up at a specific time then stopped after being idle for more than two minutes
X>0	= Default Pod instance number	For serious users with more concurrent usage that are sensitive to response time. The price is higher since there will be X runtime pods up at all times.

Known Limitations

Performance

The current logic for the Python remote execution is:

• The customized package install for a specific runtime is on the persist volumes, when the executor pod is spawned, and the persistent volume is mounted to the Python executor's container.

• The main process of the Python service is responsible for request and response handling. The actual script execution is launched by the subprocess. As a result, each time the Python scripts are executed, the Python interpreter attempts to load the used package into the subprocess's memory.

Since there are still some delays when the container imports the data in the persist volume, so even if the pod is already warmed up, there is some extra time cost for the subprocess to run the scripts.

Data Source Connections

For private on-prem data sources, if there is no network connection between the Python executor pod container and the private data source, then Python transactions are not supported. In general, data sources with public end points can be used.

Related Articles

KB486432: Best practices for mapping Python script variables and grid fields for Python transaction configurations

KB486431: Sync up Python script modifications with Python transaction configurations in a dashboard