MicroStrategy ONE

Create a Freeform SQL Cube

Starting in MicroStrategy ONE (June 2024), you can create freeform SQL cubes that requires you to first define a valid SQL statement. You must also map objects to the data returned by your SQL statement.

Prerequisites

  • You must have the Use Freeform SQL Editor and Create schema objects privileges to access and use the Freeform SQL Editor.

  • To use, create, and edit freeform SQL cubes, you must have the following privileges:

    • Use Intelligent Cube Editor

    • Use analytics

    • Create application objects

    • Modify the list of Report objects (use object browser)

    • Use Freeform SQL Editor

  • If there is no database instance defined in the metadata, the Freeform SQL Editor can not load and a message appears.

  • Database instances are available for selection only if they have been set up correctly for use with Freeform SQL.

    1. Right-click a project and choose Properties.

    2. Navigate to the Data Sources tab.

    3. Click Add Data Source.

Create a Freeform SQL Cube

  1. Open the Workstation window.
  2. In the top Navigation pane, click File and choose New Freeform SQL Cube.
  3. Select a project and click OK. The Freeform SQL Editor appears.
  4. Select a database instance for the data source to access using freeform SQL.
  5. In the top-right pane, type your SQL query.

  6. Click Execute SQL.

  7. In the bottom-right pane, map the columns of the SQL statement to your attributes and metrics that will be used in the cube.

    When you map columns, it is important to follow the same sequence of the columns as they appear in the SQL statement. If you do not follow the same sequence, the cube will fail.

    Ensure the number of mappings is the same as the number of columns in the SQL statement. For example, if your SQL statement lists 10 columns to retrieve data, you should map them to 10 attributes and metrics.

    For each attribute, you must map a column to the ID form.

  8. Click Save and Open Cube.

  9. You can define the cube using the objects of the query results. You must use all query results.

    For more information on defining cubes, see Create Intelligent Cubes

  10. Click Publish.

  11. If there is a cube cache, a Cube Cache toggle appears in the top-right of the cube editor. If this toggle is on, the editor will display data as it appears in the cache in the SQL View and Data Preview. If you switch this toggle off, the SQL View displays the latest SQL from the current cube definition and Data Preview displays Publish. If you save new changes in the cube editor, the Cube Cache toggle should be off.