Skip to Content

Seeing Calculation View Results

0 %
Seeing Calculation View Results

Seeing Calculation View Results

Generating select statements for results and visualization

You will learn

  • How to generate a SQL Select Statement for a Calculation View
  • How to Visualize Queries
QR code


  • Tutorials: “Adding Our Union Calculation View into the Converted Data Model” and “Migrate Records in Related Tables Using Stored Procedure”

Step 1: Seeing Results

We will now generate a SQL select statement for our AV_ORDERS calculation view. It will be editted to query all rows between 6 months and 18 months ago. Since ORDERS_CS contains all data from the current year and ORDERS_DT contains all data older than a year, the result set will span both the in-memory table instance and the extended table instance.

We will then visualize the plan, and show again that data is being queried from both in-memory and extended storage.

Before we begin, open a SQL console, and run our Migrate_Aged_Orders() function created from our previous tutorial, Migrate Records in Related Tables Using Stored Procedure. This will ensure that all data older than one year is moved from ORDERS_CS into ORDERS_DT.

Click execute, to run the statement.

Call Migrate_Aged_Orders()
CALL "TPCH"."Migrate_Aged_Orders"();

Now, right click on the calculation view AV_ORDERS and click Generate Select SQL.

Generate Select SQL

The result:

Result of select sql

We will add a WHERE statement, so the query only returns results between 6 to 18 months ago. This is done to ensure that it is collecting data from both ORDERS_CS and ORDERS_DT. Add the following between the FROM and GROUP BY lines:

Where statement

Click execute.


The results should look like the following image:

Where Results
Log on to answer question
Step 2: Visualizing Queries

We can also visualize the query, by going back to the SQL tab, right clicking, and selecting Visualize Plan > Execute.

Visualize Plan

After it executes, navigate to the Executed Plan tab.

Executed Plan

On the bottom panel, navigate to the Operator List tab, and search for the operator name ES.

Operator List

Double click one of the results, and it will zoom into the selected operator. As you can see by the Remote Row Scan box, the query accessed the extended table.

Remote Row Scan
Which operator name do you search for?

Next Steps

Back to top