MicroStrategy ONE

Use Auto SQL to Create Freeform SQL Tables

MicroStrategy ONE Update 10 introduced the ability to use auto SQL to create Freeform tables as a preview feature. Starting in MicroStrategy 2021 Update 11, this feature is shipped out-of-the-box.

Using Auto SQL to create Freeform SQL tables is supported in MicroStrategy Cloud environments and starting in MicroStrategy ONE (March 2024), Auto SQL is supported on the MicroStrategy Cloud for Government platform. You can only use this feature in Workstation.

Auto SQL is an AI-integrated tool within the schema editor designed for efficient SQL generation and editing. By interpreting natural language inputs, it can help produce and modify complex Freeform SQL table definitions. When presented with an existing SQL statement, Auto SQL provides a clear and concise summary of its functionality. Additionally, for databases like Snowflake that require specific SQL writing conventions, it offers optimization recommendations to enhance performance and compliance.

Access Auto SQL

Auto SQL is part of the MicroStrategy AI add-on bundle and is available for MicroStrategy Cloud Environment (MCE) customers starting in MicroStrategy ONE Update 11 (September 2023). Only users and user groups with the Use Auto Assistant and ML Visualizations privilege, as well as an Administrator set of privileges required to edit the schema and Freeform SQL can access Auto SQL.

  1. Open Workstation.

  2. In the Navigation pane, click  Schemas.

  3. Right-click the schema in which you want to create a custom logical table and choose Edit.

  4. In the left pane, select a data source from Data Catalog.

  5. Connect to your data source.

  6. Click Add free-form SQL.

    The Auto SQL appears in the top right corner.

  7. Right-click a table and choose Add to the Chat to send specified tables to Auto SQL. If necessary, you can multi-select multiple tables.

    This step is highly recommended, as large schemas may not be compatible with the maximum token limit for the service.

  8. Enter your question.

    Auto SQL generates the SQL for you.

  9. Use the Insert button to automatically send generated SQL into the Freeform SQL input field in the Schema Editor.

  10. Verify the generated SQL and make an necessary adjustments, if necessary.

  11. Click Execute SQL. View the results in the table preview.

Best Practices for Using Auto SQL

  • Know your data model Auto SQL doesn't know any details about the structure of your tables and data, it helps to combine the SQL. Understand your data model to fully leverage Auto SQL capabilities. Tables are picked by their name, not the content, meaning you need to understand if the object is sourced from a correct table.

  • Narrow down Use Add to the Chat and provide tables to the conversation to increase generated SQL quality and response time. Vast schemas can quickly cross the maximum available question size.

  • Be Specific Provide as many details as possible for your input. Try using the names of columns that exist in your database.

  • Iterate Build incremental query, add new fields, and give more details on your question—with corrections and adjustments, you can get a good SQL structure that may serve as a starting point for you to continue.

  • Ensure data quality Auto SQL does not check if columns are empty or nulls. If your prompt returns no rows, verify the quality of data and filtering conditions.

Known Limitations

  • Big data models Due to the constraints of the Large Language Model, vast schemas comprised of multiple tables and namespaces are unable to be processed all at once by Auto SQL. Reduce the number of connected namespaces and add tables to a chat to provide specific tables needed for your SQL.

  • Google Big Query The Schema Editor only retrieves tables by default in Data Catalog. Therefore, Auto SQL does not receive a full set of column names. This is a limitation of the current design. Verify if your table names are correct before executing the SQL.

Use Auto SQL to Explain SQL

Imagine the convenience of having a personal tutor by your side, ready to help you anytime you're delving into a query. Auto SQL does just that for you! If you've previously crafted an intricate SQL query and find yourself in a muddle trying to recall its logic months later, Auto SQL comes to the rescue. Simply open your Freeform SQL table and it swiftly breaks down its structure, explaining each component in plain language. You can also use it to understand any SQL code you have, by pasting it into Auto SQL.

Whether it's decoding JOIN operations, dissecting WHERE clauses, or clarifying aggregate functions, Auto SQL offers a quick refresher, ensuring you're always in the loop.

Understand a Freeform SQL Table

  1. Select a Freeform SQL table and click SQL at the bottom.

  2. Click Edit SQL.

  3. Open Auto SQL to automatically get a description of the SQL table.

Explain SQL

  1. Above Auto SQL's input field, click Explain. The input is pre-populated with a formula.

  2. Copy your SQL to the input field and hit Enter.

  3. Auto SQL analyzes the SQL and provides a concise summary of your query.

Use Auto SQL to Optimize your Query

Unleash top-tier performance with our SQL optimizer! Whether you're working with Snowflake or another data source, the quality of your SQL plays a pivotal role in query speed. Input your code, and our tool swiftly identifies inefficiencies, offering refined queries for optimal results. Navigate complex datasets confidently, ensuring every query runs smoothly and efficiently. Elevate your analytics with precision-tuned SQL!

  1. Click Optimize above Auto SQL's input field. The input is pre-populated with a formula. Notice that Auto SQL automatically recognizes your database connection.

  2. Copy your SQL in the input field and hit Enter.

  3. Auto SQL analyzes your SQL and provides you with guidance to improve your query.

 

Related Topics

Auto Telemetry