Debugging Calculation Views with the Debug Query Mode

Objectives

After completing this lesson, you will be able to:

  • Debug calculation views with the Debug Query Mode

Introducing the Debug Query Mode

To examine the runtime behavior of a calculation view when it is called by a query, you use the Debug Query mode. In Debug Query mode, you can interrogate each node in the calculation view to see how the runtime SQL is generated.

When you switch to Debug Query mode, a generic query is automatically created to call the calculation view. This generated query simulates a reporting tool or application that is sending a query to the calculation view. The generated query requests all columns of the calculation view and does not have filters. In other words, the generated query requests everything from the calculation view. However, you can modify the generated query if you wish, for example, to remove columns or add filters to simulate a user requesting different views of the data.

When you execute the generated query on the calculation view, you can then view the SQL that is generated for each node of the calculation view, and even execute the SQL at each individual node to display the interim results. This helps to ensure that your calculation view is behaving as you would expect, or perhaps to investigate a performance or output issue that has been reported. By stepping through the nodes one by one, examining the SQL, you can check if the expected query is generated. For example, you could see how a WHERE clause is added to the query when a filter is needed, or check how a PLACEHOLDER is behaving when using an input parameter to pass a value.

Using Debug Query mode, you can even navigate to the lowest level of the entire modeled stack. This means that you do not have to start the Debug Query mode in separate calculation views, you simply begin debugging at the top of the model and continue debugging to the lowest level.

One of the most useful things that you can discover when using Debug Query mode is to see how each node is pruned of columns and even complete data sources under various query conditions. For example, this is a great way of testing if union or join pruning is working as you would expect by running queries that request different data sets.

Debug Query Mode

You can reset the top-level debug query in the Semantics node at any time using the reset button, which is adjacent to the Execute button.

Note

Remember that the Debug Query mode calls the active version of the calculation view. This means that if you have made changes to the calculation view but not yet rebuilt it, then you switch on Debug Query mode, the debug results will not reflect those latest changes.

Work with Query Debug Mode

Query Debug Mode

Watch this video to learn about Query Debug Mode.

Log in to track your progress & complete quizzes