Query Playground

Kyvos offers a Query Playground feature that enables you to browse semantic models using popular query languages, such as SQL and MDX. You can write and execute the queries directly without relying on a visualization layer or third-party tools. This makes it easier to extract data for downstream applications, validate data in a semantic model, or debug query performance. Additionally, users can export querying data in CSV and JSON format.

When a semantic model is selected, Kyvos Copilot assists users in writing MDX queries by auto-correcting the MDX expression based on the name and syntax of dimensions and measures. The history of the last 30 days is stored and can be filtered by category or custom text. You can also reuse previous inputs and outputs to create new expressions. To create new expressions, users can reuse previous inputs and outputs automatically by clicking the Use me option. Alternatively, they can manually fill in the input by clicking the Copy to Clipboard option and paste it into the Input Prompt box to regenerate the output. For more information on how to use Kyvos Copilot to create MDX expressions, see the Creating automated MDX expressions using Kyvos Copilot section.

To navigate to Query Playground, click the Toolbox and select Utilities > Query Playground. The Query Playground page is displayed in a new window.

Note

Use Spark SQL to execute SQL queries and MDX to handle multidimensional queries, with the correct syntax for each query type.
The query editor has an IntelliSense feature that assists with keywords, functions, and semantic model element names.
You can execute queries on any browsable and accessible semantic model within the system.
Kyvos Copilot is accessible when a semantic model is selected for MDX querying.
You can preview the results of a query in tabular format with a limited number of records. You can preview maximum 10,000 rows. By default, the preview is available for 300 rows. If you want to view more details, you need to export data, as mentioned in this section.
You can export data in JSON/CSV format for complete data extracts with configurable export settings.
If you remove the semantic model, the selected semantic model and metadata fields will be cleared.
Only supported queries will execute successfully (select etc.).

Selecting Semantic Model

When selecting a semantic model for SQL and MDQ queries, ensure the following:

MDX: To write MDX queries, you need to select a semantic model. After selecting the semantic model, the dimensions and measures are displayed that are used for calculations.
SQL: To write SQL queries, selecting a semantic model is not mandatory as SQL queries run directly on the Query Engine. However, if you select a semantic model, you must write a query about it. After selecting a semantic model, the dimensions and measures are displayed. Expanding the dimensions or measures displays the columns used for calculations. To view the hierarchical details of a column, you need to hover over it. The details of the column will be displayed.

Executing query on Query Playground

To execute a query on Query Playground, perform the following steps.

Click the Toolbox and select Utilities > Query Playground. The Query Playground page is displayed in a new window.
To execute a query, select one of the following:
- Spark SQL: To execute SQL queries with or without selecting a semantic model, start typing your queries in the editor. As you enter text, suggested statements, functions, operators, and so on are displayed. Click an item to auto-fill it.
  The following image displays how to execute an SQL query without selecting a semantic model.
  The following image displays how to execute an SQL query by selecting a semantic model.
- MDX: To execute an MDX query, click the Select Semantic Model link. The Select Semantic Model dialog will appear. The semantic model's dimension and measure will be displayed in the left pane. Using the Kyvos Copilot, you can also create MDX expressions for calculated measures in semantical models and Kyvos worksheets.
  For further details on how to use Kyvos Copilot for MDX queries, see the section Creating expression using Kyvos Copilot section.
Click Execute. You will view a Preview of the executed query.

Following are the common actions for SQL and MDX Query:

Query Status: Click the option to monitor the status of the query.
Clear Query: Click the option to cancel the query.
Open Semantic Model: Click the link to view the semantic model in detail. The semantic model will open in a new window.
Remove: Click the link to cancel the semantic model.
Export: Click the option to export the data to JSON or CSV format, as mentioned below.

Exporting Data to JSON or CSV

You can export data to JSON or CSV format.

To export data to JSON or CSV, perform the following steps.

On the Query Playground dialog box, click Export Data.
The Export Data dialog is displayed.
Select file format as JSON or CSV. By default, the JSON file format is selected.
- JSON: Click the option to export the data in JSON format.
- CSV: Click this option to export the data in a CSV (comma-separated values) file, which allows data to be saved in a table-structured format. By default, the information for CSV is filled. You can change the values as needed.
  The following options will be displayed when exporting the data to JSON or CSV format.
  - Zipped: Select this check box to export the data in a zipped format. If you clear this option, the JSON or CSV file is downloaded without the zipped folder.
  - Keep Measure formatting: Select this check box to format the larger-digit measure while exporting the data for JSON or CSV. If you clear this option, the measure in the exported JSON or CSV file will not be formatted.
  - Include Header (for CSV only): Select this check box to include the column header while exporting data in the CSV file format.