Accelerating Dashboards with Reflections

This tutorial guides you through accelerating updates to a dashboard by creating a reflection. Reflections organize and optimize the data close to Dremio Sonar’s query engine, using techniques such as pre-computation, columnarization, compression, sorting, partitioning, and data aggregation.

In this tutorial, you will:

1. Modify a dashboard in Tableau. You will note the amount of time it takes for the dashboard to be updated, establishing a baseline against which to measure the time updates take after they are accelerated by a reflection.
2. Create an aggregation reflection.
3. Modify the dashboard again to see how much faster it is updated and view the jobs that updated the dashboard.

Prerequisites

• Ensure that you are using Tableau.

• Download the workbook that includes the dashboard that you will use in this tutorial. This dashboard is built on the Samples.”samples.dremio.com”.NYC-taxi-trips-iceberg dataset that is in an Amazon S3 bucket in Apache Iceberg table format (330+ million rows).

• Before you begin, sign up for Dremio Cloud and create your first project. When you're done signing up, you are logged in to your organization and can start the tutorial by adding your first Sonar project.

• Sign up for Dremio Cloud and verify that you meet the prerequisites for configuring a Sonar project. When you're done signing up, you are logged in to your organization and can start the tutorial by adding your first Sonar project.

Step 1. Modify the Dashboard in Tableau

Before you create a reflection, modify worksheets and make note of the time it takes for the dashboard to update so you can compare when a reflection is in place.

1. Open the NYC-Trips-Dashboard.twbx workbook in Tableau.
2. Sign into Dremio Cloud through Tableau. If your user account has access to more than one organization, sign into the organization where you added the Samples source.
3. In the workbook, open the worksheet Average Fare and Average Tip.
4. Modify the sheet to show the average fare and average tip per year. Note that the update takes about twelve seconds.
5. Modify the sheet to show the averages per month. Note that this update, too, takes about twelve seconds.
6. Change the time aggregation on the sheets Average and Sum Total Amount and Number of Rides by Month. Note that the updates also each take about twelve seconds.

The dashboard updates are fast but users expect instantaneous updates. Create a reflection to optimize the response time.

Step 2. Create an Aggregation Reflection

An aggregation reflection is a type of reflection that pre-aggregates data, so aggregation does not need to happen every time a query is run on a dataset.

1. On the Datasets page, go to Sources > Object Storage > Samples in the panel on the left.

2. On the right, click samples.dremio.com.

3. Hover over the row for NYC-taxi-trips-iceberg and click to the right.

4. Click the Reflections tab at the top of the page.

5. Click the toggle switch in the Aggregation Reflections bar. The columns from the table are pre populated under Dimensions and Measures. Dimensions are qualitative values (such as names, dates, or geographical data). Measures are numeric, quantitative values that you can measure.

6. Under Dimensions, remove the passenger_count field.

7. Click Save. The green circle with the checkmark indicates that the reflection is ready for use.

8. To the right of the name of the reflection, click History to view the two successive jobs that create the reflection and wait for them in finish in a few seconds. As each job runs, a spinner appears in front of it. When each job ends, the spinner is replaced by a white checkmark in a green circle. When both checkmarks appear, the reflection is ready to accelerate updates to the dashboard.

   note

   You can also create reflections using SQL by adding a reflection to a dataset.

Step 3. Modify the Dashboard Again to See Faster Update Times

Now that the acceleration has been created, modify the worksheets again to how much faster updates to the dashboard are.

1. Open the NYC-Trips-Dashboard.twbx workbook in Tableau. If prompted, sign into Dremio Cloud from Tableau.

2. In the workbook, open the worksheet Average Fare and Average Tip.

3. Modify the sheet to show the average fare and average tip per quarter. Note that the change occurs instantaneously.

4. Modify the sheet to show the averages per year. Note that this change, too, occurs instantaneously.

5. Change the time aggregation on the sheets Average and Sum Total Amount and Number of Rides by Month. Note that these changes also occur instantaneously

6. To verify that the updates to the dashboard were accelerated by the reflection, open the Jobs page by clicking in the side navigation bar in the Dremio Console.

   The jobs that updated the dashboard all show a purple lightning bolt to indicate that they were accelerated by the reflection.

   note

   If you are using a project shared with other users, filter the list of jobs by your username by selecting your username from the User dropdown at the top fo the page.

Wrap-up and Next Steps

You've seen how a reflection can accelerate updates to a dashboard, and you've created an aggregation reflection in the Dremio console. Now you're ready to take the next steps:

• Learn about the different types of reflections, and the best practices for creating them.
• Try creating reflections for some of your workloads to optimize performance and cost. See Creating and Editing Reflections for the steps of using the Dremio console. See ALTER TABLE for the SQL commands. See Reflection for the REST API.