BI 300: Dashboard Authoring with Data Distiller Query Pro Mode

This tutorial goes through the steps of building a dashboard using SQL Chart Authoring, Drillthroughs and Global Filters.

Introduction

In this tutorial, we will explore how to create powerful, enterprise-grade dashboards that rival the best-in-class dashboards found in traditional business intelligence (BI) tools. The goal is to demonstrate that you don't need to rely on expensive BI vendors or invest extra resources for users to build and consume insights effectively. By leveraging SQL as the foundation for creating these charts, we can unlock the full potential of your data without the need for additional software or tools.

The tutorial is designed with two key audiences in mind: the data team and the business users. The data team will focus on building foundational charts, ensuring that they include the necessary filters and dimensions to provide flexibility. This allows business users to perform deep, drill-down analyses and gain actionable insights directly from the dashboards. This approach streamlines the workflow, enabling teams to quickly create interactive and insightful dashboards without having to leave the Data Distiller environment.

By the end of the tutorial, you'll have the skills to create sophisticated dashboards that empower business users to make data-driven decisions—without the complexity and cost typically associated with external BI platforms.

Why SQL for Chart Authoring?

Data Distiller natively supports SQL which continues to be a powerful tool for marketing analysts because it allows them to access and manipulate large datasets directly, without relying on pre-built reports or static dashboards. With SQL, marketing analysts can:

1. Create Custom Metrics: What we have seen is that marketing data often requires complex calculations, such as lifetime value, churn rates, or multi-touch attribution. Almost with every customer we have worked with, we have seen that they use SQL to define these metrics precisely, tailor them to specific campaigns, and adjust them as needed.

2. Data Exploration: SQL allows marketing analysts to dig into granular data, performing detailed segmentation, cohort analysis, and cross-channel performance reviews, giving a more complete picture of customer behavior.

3. Faster Decision-Making: By querying data in Adobe Experience Platform Data Lake directly, marketing analysts can quickly respond to changing trends and real-time campaign performance, reducing the dependency on other teams or fixed reporting schedules. So, this is a good skill to have on your team.

4. Flexibility: SQL provides the flexibility to create dynamic, customizable reports that can be adapted for different stakeholders, from top-level summaries for executives to detailed insights for campaign managers. You are not restricted by how you build the data layer or the authoring layer.

Overall, SQL empowers marketing analysts to go beyond basic analytics, driving more data-driven decisions and optimizing marketing strategies with precision. And with LLM-powered chat assistants, learning SQL or debugging SQL is no longer a challenge!

Pro Tip: While that creative companion (yes, your trusty chat assistant) is here to help, make sure you fully understand what it suggests before using it. And remember, never send any of your company's sensitive data or queries to the bot! Instead, get savvy by crafting examples using dummy data. Stay sharp and secure!

Metrics are the lifeblood of a marketing team's campaigns, and no two companies are alike. Neither are their metrics.

One of the main challenges with using drag-and-drop interfaces to create charts is the lack of flexibility when it comes to defining custom metrics. While these interfaces are convenient, they often fall short in handling more complex calculations. On the other hand, SQL provides unmatched flexibility in metric definition. For instance, if you want to visualize a trailing 30-day average using a rolling window for each date, achieving this in a typical drag-and-drop dashboard interface would be nearly impossible. You would likely need to recompute the entire metric at the ETL layer instead.

Prerequisites

Follow the steps to create your first table in the accelerated store here:

BI 200: Create Your First Data Model in the Data Distiller Warehouse for Dashboarding

Code Check

1. Once you have completed the Prerequisites, make sure you have executed the following queries in sequence:

-- Create the Database in Accelerated Store
CREATE DATABASE testexample WITH (TYPE=QSACCEL, ACCOUNT=acp_query_batch);

-- Create the Schemas on the Database
CREATE schema testexample.lookups;
CREATE schema testexample.facttables;

-- Create the Tabbles
CREATE TABLE testexample.facttables.crm_table AS
       SELECT
          cast(null as int)    purchase,
          cast(null as string)   country_code
WHERE false;

CREATE TABLE testexample.lookups.country_lookup AS
       SELECT
          cast(null as string) country_code,
          cast(null as string) country_name
       WHERE false;

-- Define a key relationship between the country code and the lookup
ALTER TABLE crm_table ADD CONSTRAINT FOREIGN KEY (country_code) REFERENCES country_lookup(country_code) NOT enforced;

-- Rename the data models to make them user friendly names in Query Pro Mode
ALTER MODEL testexample.facttables RENAME TO test_purchase;
ALTER MODEL testexample.lookups RENAME TO test_country;

-- Hydrate the Tables
INSERT INTO testexample.facttables.crm_table (country_code, purchase)
(SELECT country, purchase_amount FROM purchases_dataset_crm_data);

INSERT INTO testexample.lookups.country_lookup(country_code, country_name)
(select country_code, country from(
select count(*), country_code, country from country_codes
group by country_code, country));

-- Data Exploration Queries
SELECT * FROM testexample.facttables.crm_table;
SELECT * FROM testexample.lookups.country_lookup;

2. Make sure that the Database dropdown is set to prod:all in the top right corner and save it as a Query Template

Make sure that you choose prod: all in the database dropdown.

Data Distiller Charts Primer

Anatomy of a Data Distiller Chart

1. Navigate to Dashboards->Create Dashboard->Add Widget to explore what charts do.

2. Here are the key concepts to keep in mind when building a chart in Data Distiller Dashboards:

   1. There are 5 key chart types available under the Marks section that cover most of the visualization needs for business users:

      1. Line Chart (aka Trend Chart): Ideal for visualizing data trends over time, helping users track changes, growth, or patterns.

      2. Donut Chart (aka Pie Chart): Useful for showing parts of a whole, making it easy to visualize percentage breakdowns or categorical distributions.

      3. Bar Chart: Effective for comparing quantities across different categories, making it versatile for a variety of data comparisons.

      4. Table: Allows users to display raw data in a structured format, perfect for detailed reports or numeric summaries.

      5. Big Number Chart (aka KPI Chart): Highlights a single key metric, providing an at-a-glance view of important figures like revenue, user growth, or performance metrics.

      These chart types cover a wide range of use cases, from tracking trends and showing proportions to comparing values and summarizing key performance indicators (KPIs).

   2. Most charts feature an X and Y axis where you can assign attributes from the Attributes panel. These attributes are typically categorized into three types:

      • Metrics (123): These represent numerical values, such as sales or revenue, and are often used for calculations or aggregations.

      • Dimensions (ABC): These are categorical fields, such as names, categories, or locations, that represent groupings or categories of data.

      • Dates (calendar icon): These are the date fields that you have defined as columns in your tables in the Accelerated Store.

      The X and Y axes allow you to plot data using these attributes, with metrics usually plotted on the Y-axis (vertical) and dimensions/dates on the X-axis (horizontal), enabling you to visualize patterns, trends, or distributions across different categories.

   3. Both the X and Y axes in Data Distiller have built-in aggregation functions like MIN, MAX, SUM, and AVG to easily summarize data for visualization. However, when using Data Distiller Query Pro Mode with SQL authoring, these built-in functions become less necessary because you would typically handle the aggregation directly in your SQL query. This gives you greater control over how data is processed and presented, allowing you to fine-tune your calculations and ensure the desired outcome without relying on the built-in functions provided by the visualization tool.

   4. There is a third visualization dimension called Color that can enhance the richness of the chart. Adding an attribute to the Color dimension in a bar chart will create a stacked bar graph, where the bars are segmented or "sliced" by that attribute. This allows you to break down the values within each bar into subcategories, providing more detailed insights within the same chart. The use of color helps to visually distinguish between different segments, making it easier to interpret the data and see how different groups or categories contribute to the total value.

3. On the right-hand side, you'll find the chart properties panel, where you can customize various aspects of the chart. This includes:

   • Naming the Chart: You can assign a title or label to the chart for easy identification.

   • Legend Placement: You can control the position of the chart's legend (e.g., top, bottom, left, or right) to improve readability.

   • Naming Visualization Dimensions: You can rename the labels for the X and Y axes to better represent the data being displayed.

   These customization options help tailor the chart to your specific needs, ensuring clarity and improving the overall presentation of the data.

4. Hierarchical Design: Each dashboard supports a maximum of 10 charts to ensure performance and encourages users to design their dashboards in a more hierarchical manner. If there are charts that provide detailed information, they should be placed in separate dashboards that can be accessed through Data Distiller Drillthroughs. This approach promotes clarity and efficient organization by allowing users to navigate from high-level summaries to more detailed views without overcrowding the main dashboard.

5. All charts have the following when deployed on a dashboard:

   1. ViewMore: Shows you the tabular data with pagination along with the chart. You can download the results as a CSV file.

   2. ViewSQL: Shows the underlying SQL behind the chart. You can copy the SQL or execute it in the Data Distiller Query Pro Mode Editor.

   3. Drillthrough: If a dashboard is attached as a Drillthrough dashboard for a chart, it becomes accessible for users. They can drill down into the linked dashboard directly from the chart, allowing for deeper exploration of the data.

6. Export as PDF: All dashboards also include an Export to PDF feature, which generates a single-page PDF version of the dashboard for manual email distribution or archival purposes.

Chart summary - when to use what and why

1. Chart Summary: The table above provides a helpful guide to understanding different types of visualizations available in dashboards, along with when and how to use them. Here's a more detailed description for each type of chart to make it easier to understand:

   1. Big Number (KPI Chart): This chart is used to display a single, important number. It's ideal for showing key performance indicators (KPIs), such as total sales or total website traffic. It gives you a high-level, easy-to-read snapshot of a key metric.

      1. X and Y Axes: Since this chart only shows one big number, there’s no need for X or Y axes. The metric is placed on Value dimension.

         1. Color: No color is needed because it’s just one number.

         2. Example: Think of this as showing the total web traffic for your site in big, bold numbers.

   2. Line Chart (Trend Chart): This chart is great for showing trends over time. For example, if you want to see how web traffic changes week by week, a line chart will plot those changes over time.

      1. X Axis: The X axis represents time, like days, months, or years. This allows you to see how things change over a period.

         1. Y Axis: The Y axis shows a metric, like the number of visitors or revenue, so you can track how it changes over time.

         2. Color: You can add color to represent different categories (e.g., showing different lines for various traffic sources like social media, email campaigns, etc.).

         3. Example: A line chart could show web traffic trends for the top 5 marketing channels over the last year.

   3. Bar Chart: Bar charts are used for comparing categories side by side. For example, you might want to compare the web traffic from different countries or the sales of different products.

      1. X Axis: The X axis represents categories, such as different countries or product names.

         1. Y Axis: The Y axis shows the value of a metric, such as the number of visitors or total sales.

         2. Color: Adding a color dimension allows you to break down each bar further. For example, you could split the traffic from each country into different sources, like social media, direct traffic, and search engines.

         3. Example: A bar chart could show how much traffic each country is generating, with colors dividing the traffic sources for each country.

   4. Donut Chart (Pie Chart): Donut or pie charts are used for showing parts of a whole. They allow you to visualize how much each segment contributes to the total.

      1. X and Y Axes: There are no axes on a donut chart because the chart itself shows proportions. The metric is placed on Value dimension.

         1. Color: Each segment of the donut is colored differently to represent a category, making it easy to see how much each category contributes.

         2. Example: A donut chart could show the percentage of traffic that comes from different marketing channels, with each channel represented by a different color.

   5. Table: Tables display detailed data in a grid format, allowing you to see raw numbers in a structured way. This is great when you need to present multiple data points for comparison.

      1. X and Y Axes: Tables don’t use traditional X and Y axes. Instead, they have headers at the top, which label each column (e.g., date, traffic source, number of visitors).

         1. Color: Tables don’t use color for individual cells

         2. Special Note: A table will only display the 5 first columns when placed in a dashboard. It will display upto 20 columns when viewed in the View More mode. Each page in ViewMore mode will have 500 rows. There is Download CSV option that lets you download the 500 rows on that page with all the underlying dimensions that were there in the table (no restrictions on the 5 or 20 dimensions that are being visualized)

         3. Example: A table could display detailed web traffic numbers for each channel and date, allowing users to drill down into the data.

What Chart to Use When

• Big Number (KPI): A simple, large number showing an important metric, like total web traffic.

• Line Chart (Trend): Tracks changes over time, showing trends in metrics like traffic or sales.

• Bar Chart: Compares different categories (e.g., countries, products) using bars.

• Donut Chart (Pie): Shows how different categories contribute to a total, using a donut-shaped chart.

• Table: Presents raw, detailed data in a grid for in-depth analysis.

Build Bar Chart: Country By Revenue

1. In the AEP UI, navigate to Dashboards->Create Dashboard

Navigate to dashboards inventory page

1. Click Create Dashboard. Name the dashboard as QueryProModeDemo. Choose Query Pro Mode. Click Save.

Choose Query Pro Mode

1. Minimize the left navigation bar and click on Add Widget

Add widget

1. Click Enter SQL

Click on Enter SQL

1. In the Query Pro Mode Editor, choose the Data Model titled test_purchase and execute the following query:

SELECT * FROM testexample.facttables.crm_table 

Execute a simple query

1. Click Select to select the results of this query on which we will build the chart. On the next screen, add the attributes purchase to the X and country_code to Y axis. Choose the bar chart and do the labeling as shown in the diagram below.

   1. Name of the chart: Country by Revenue

   2. Y Axis Label: Country

   3. X Axis Label: Revenue (US Dollars)

Chart creation

1. Save and Close the chart. Now resize the chart on the dashboard by dragging the bottom right corner.

Chart added to the dashboard

1. Resize the chart on the dashboard. Then click Save. Then Click Cancel to exit the Edit Mode.

Click Cancel to exit the Edit Mode

1. Click on Edit and then Add Widget

Add a new widget.

Build a Table: Federated Queries Across Data Models

1. Let us now execute a query across the two data models. We have looks up in test_country data model and the raw purchase data in test_purchase.

The Data Distiller Query Pro Mode Editor you are using for chart creation cannot access tables in the AEP Data Lake due to a 60-second timeout limit for chart queries. If a query takes longer to run because of the latency in reading from the data lake, it will time out. This is one of the reasons why the Accelerated Store was built—to mitigate these latencies caused by the data infrastructure layer. While data lakes are excellent for storing large volumes of data, running exploration queries that can take minutes, and executing batch jobs to create new datasets, they are not optimized for dashboarding. In contrast, the Accelerated Store's data layer is designed to efficiently serve queries for dashboards.

However, in the main Data Distiller Query Pro Mode Editor within the AEP UI (i.e., Queries → Create Query), queries can be executed across both the AEP Data Lake and the Accelerated Store in a federated manner. These queries can read from and write to both stores without the 60-second timeout restriction, although they may time out after 10 minutes. This flexibility allows for longer-running queries that can return results in minutes, avoiding the limitations present in chart creation.

1. Copy paste and execute the following query with the data model chosen as test_country

SELECT sum(purchase) AS purchase, country_name FROM testexample.facttables.crm_table 
INNER JOIN testexample.lookups.country_lookup ON testexample.facttables.crm_table.country_code = testexample.lookups.country_lookup.country_code
GROUP BY country_name;

It is required that you list the full path like the way it was done above for chart authoring i.e. testexample.lookups.country_lookup and testexample.facttables.crm_table.country_code

1. The editor should look like this:

Final result after joining the two tables.

1. Click Select to select the results of this query on which we will build the chart. Choose Table under Marks

Choose Table as the Option

1. Click on purchase attribute and add it to Header 1. Rename the Header name as Revenue and choose Sortable that allows you to sort the rows by that table in the View More mode.

Choose the columns in the table.

1. Click Add Header to create Header 2 and add the next attribute country_name to Header 2.

Choosing the next column.

1. Name the Header 2 to Country.

Country column is added.

1. Change the order of thee columns by using the Up Arrow. Make minor adjustments to Revenue Label- Change it to Revenue (US Dollars). Also, rename the table.

Make adjustments to the table you created.

1. Resize the chart using the bottom right arrow:

Adjust the length and width of the table to make it look visually appealing.

1. Click on Save. Then Click Cancel.

2. Click on the View More mode in table by clicking on the ellipsis. You can sort the columns and even resize the columns. There is a Download CSV option that lets you download 500 rows per page. You cannot download the whole table as a CSV.

View More gives you more columns and data to view than the table in the dashboard.

A table will only display the first 5 columns when placed in a dashboard. It will display upto 20 columns when viewed in the View More mode. Each page in ViewMore mode will have 500 rows. There is Download CSV option that lets you download the 500 rows on that page with all the underlying dimensions that were there in the table (no restrictions on the 5 or 20 dimensions that are being visualized)

Add Big Number Charts

1. As an exercise, go through the same steps as outlined before to create a Big Number Chart using the SQLs show below.

Be careful to choose the test_purchase data model in the dropdown each time you build a chart below otherwise you will be getting errors.

1. Total Revenue (US Dollars)

SELECT SUM(purchase) AS Total_Revenue
FROM testexample.facttables.crm_table;

1. Operating Countries:

SELECT COUNT(DISTINCT country_code) AS Number_of_Countries
FROM testexample.facttables.crm_table;

1. Transaction Volume

SELECT COUNT(purchase) AS Transaction_Volume
FROM testexample.facttables.crm_table;

1. You should be able to drag and resize the Big Number charts. Your final dashboard should look like this:

Creating the Big Charts

Improve Bar Chart Readability

1. Our bar chart automatically handles certain aspects of the visualization, but it doesn't sort the data and will truncate the results - which is all the more reason to use a table as we did. As a dashboard designer, it’s essential to monitor both the data and the visualization closely to ensure that what is being displayed accurately reflects the underlying data.

2. Our chart has bars that are not sorted. This creates issues for our business users who would like to see things sorted.

3. Click Edit the Dashboard. Click Edit the bar chart. And make sure you click on the pencil icon to access the Data Distiller Query Pro Mode Editor

Data Distiller Query Pro Mode access is through the pencil icon

1. Make the smallest changes to the SQL. Let us do the aggregation ahead of time and do the sorting in the result set that is feeding the bar chart itself.

SELECT SUM(purchase) as purchase, country_code from testexample.facttables.crm_table 
GROUP by country_code
ORDER BY purchase DESC

1. You should see the following preview of the chart which makes it hard to parse the bars as they are so close:

The long tail that we typically see in data.

1. So we now have to do a tradeoff for readability. We have the detailed numbers in the table chart that we created that can show the long tail. Wee are now going to truncate the results to show just the right amount of the long tail.

SELECT SUM(purchase) as purchase, country_code from testexample.facttables.crm_table 
GROUP by country_code
ORDER BY purchase DESC
LIMIT 20

1. Now the chart with the renaming Top 20 Countries by Revenue will look like the following:

Top 20 countries by revenue

7. Just make sure that you have Aggregation chosen as None on the X and the Y Axis:

Aggregations should be marked as none

Improve Dashboard Readability and Navigation

1. Let us rearrange the dashboard with charts following a pattern - summaries at top via Big Number charts, then more detail through Bar Charts and finally the detailed data via Tables.

2. The dashboard should look like this:

Dashboard readibility has improved

1. We need to make some more adjustments to make this dashboard appealing and interesting

   1. We need to create a new summary donut chart that compares the percentage contribution of the Top 5 Countries By Revenue against the rest.

   2. The detailed table that we have now should be made available as a Data Distiller Drill Through on the bar chart so that a user who wants to truly dig into the data can see the raw data.

   3. Notice how I adjust my charting requirements and seamlessly adapt to the new ones without needing to perform ETL or overhaul the entire project. Additionally, the metric mentioned in (1) is somewhat arbitrary.

Revenue Contribution Donut Chart

1. Top 5 Countries Contribution vs. Rest: First, let us go back to the main Data Distiller Query Pro Mode Editor and prototype the query there:

SELECT 
    (top_5_purchase / total_revenue) * 100 AS top_5_percentage,
    (rest_purchase / total_revenue) * 100 AS rest_percentage
FROM (
    SELECT 
        SUM(CASE WHEN rank <= 5 THEN purchase ELSE 0 END) AS top_5_purchase,
        SUM(CASE WHEN rank > 5 THEN purchase ELSE 0 END) AS rest_purchase
    FROM (
        SELECT 
            country,
            SUM(purchase_amount) AS purchase,
            RANK() OVER (ORDER BY SUM(purchase_amount) DESC) AS rank
        FROM 
            purchases_dataset_crm_data
        GROUP BY 
            country
    ) AS ranked_countries
) AS purchases_data
CROSS JOIN (
    SELECT 
        SUM(purchase_amount) AS total_revenue
    FROM 
        purchases_dataset_crm_data
) AS total_data

1. You should be in this editor and prototyping your results against the raw dataset in the AEP Data Lake. Go to AEP's Left Navigation bar and navigate to Queries->Create Query. You can see that the top 5 countries contribute around 44% of total revenue.

Go to Left Navigation bar and navigate to Queries->Create Query

1. Important: If you look at the structure of the query, there is a window function (RANK), several SUM aggregations and even a CROSS JOIN happening at the same time. When I execute this query, it takes about 20 seconds. Remember that the Accelerated Store can process 4 queries at a time. If I start overloading the system with a heavy duty query like this one, it can cause the timeout of one or many charts. Let us go ahead and create a table in the Accelerated Store using the similar SQL code which should be easy. Open up the Data Distiller main editor and add the following queries and execute them in sequence (highlight the query and run selected query button next to the run button)

2. Create the table underneath the fact tables as this is also a fact. Make sure that you choose the Decimal(10,2) as the data type:

CREATE TABLE testexample.facttables.contribution_table AS
       SELECT
          cast(null as Decimal(10,2))    top_5_countries,
          cast(null as  Decimal(10,2))    rest_countries
WHERE false;

DECIMAL(10,2) refers to a fixed-point or exact numeric data type with a precision of 10 digits and a scale of 2 digits. Here's what that means:

• Precision (10): This is the total number of significant digits that can be stored. In this case, the total number of digits is 10.

• Scale (2): This indicates the number of digits to the right of the decimal point. In this case, 2 digits are reserved for the decimal portion.

1. Insert the data from the AEP Data lake query into this table:

INSERT INTO testexample.facttables.contribution_table (top_5_countries, rest_countries)
(SELECT 
    (top_5_purchase / total_revenue) * 100 AS top_5_percentage,
    (rest_purchase / total_revenue) * 100 AS rest_percentage
FROM (
    SELECT 
        SUM(CASE WHEN rank <= 5 THEN purchase ELSE 0 END) AS top_5_purchase,
        SUM(CASE WHEN rank > 5 THEN purchase ELSE 0 END) AS rest_purchase
    FROM (
        SELECT 
            country,
            SUM(purchase_amount) AS purchase,
            RANK() OVER (ORDER BY SUM(purchase_amount) DESC) AS rank
        FROM 
            purchases_dataset_crm_data
        GROUP BY 
            country
    ) AS ranked_countries
) AS purchases_data
CROSS JOIN (
    SELECT 
        SUM(purchase_amount) AS total_revenue
    FROM 
        purchases_dataset_crm_data
) AS total_data);

1. Verify that the table has been hydrated:

SELECT * FROM testexample.facttables.contribution_table

The results should be returned very fast:

The ETL does work.

Notice that the dimensions are displayed as column names, which isn't ideal for creating charts. This is a common issue you may face. In the next section, we'll work on resolving this. After all, Query Pro Mode allows us to quickly reshape data on the fly. However, keep in mind that this may come with a slight performance impact.

1. Now go into the Data Distiller Query Pro Mode in Dashboards and make sure you choose test_purchase as the data model and execute the following query:

SELECT 'Top 5' AS category, top_5_countries AS value
FROM testexample.facttables.contribution_table
UNION ALL
SELECT 'Rest 5' AS category, rest_countries AS value
FROM testexample.facttables.contribution_table;

The code in the editor

1. The donut chart authoring should look like this:

Chart authoring for the SQL result set

1. The dashboard after you have saved it should look like this:

The dashboard should look like this.

Enable Data Distiller Drillthroughs

1. Rearrange the dashboard so that the bar and the donut charts are side by side. Save the dashboard.

Summary metrics are side by side

1. Open up a new AEP tab and navigate to Dashboards. Create a new dashboard called Detailed Revenue by Country . In the Query Pro Mode, type the following query. You should be able to retrieve the query from ViewSQL in the previous QueryProModeDemo dashboard

ViewSQL gives you the Query Pro Mode SQL that you can reuse across charts.

Here is the code to copy and use. Make sure that the data model chosen is test_purchase in the data model dropdown:

SELECT sum(purchase) AS purchase, country_name FROM testexample.facttables.crm_table 
INNER JOIN testexample.lookups.country_lookup ON testexample.facttables.crm_table.country_code = testexample.lookups.country_lookup.country_code
GROUP BY country_name;

Query copy pasted with the test_purchase as the data model

1. Configure the table as you did before (this should be fast)

Recreating thhe table

1. Rearrange the dashboard and save it

The detailed dashboard that has the most information

1. Go back to the QueryProMode dashboard and edit the bar chart titled Top 20 Countries by Revenue. Enable Drillthrough and choose the dashboard Detailed Revenue by Country from the alphabetical list

Enabling Drillthroughs for drill-down dashboards

1. Click on the ellipsis for the Top 20 Countries by Revenue bar chart.

Accessing the Drillthrough hyperlink

1. You should see the drill-down dashboard (which is pretty cool). If you click on the parent dashboard, you should be able to go back up. You can create as many drillthroughs you like and can figure which direct you want to go.

Parent dashboard is available in the top left corner. Clicking on it will take you one level up.

1. Go back to the QueryProModeDemo dashboard and delete the table there. At this point, you have a high-level summary dashboard with drilldowns.

Global Filters

A new request from the business users is that they want to apply filters to three specific Big Number chart widgets shown below, while leaving the others unaffected.

Global filters should apply to these widgets only.

1. In the QueryProModeDemo dashboard, click on Add Filter->Global Filter

Access the Global Filter

1. Conceptually, a global filter applies a user-selected value to a chart query, assuming the relevant column exists in the underlying table. For instance, if we want to filter the queries by country name, we would need to translate the country_name to country_code and apply it to the three chart queries that use the testexample.facttables.crm_table table. However, displaying country_code on the dashboard is not user-friendly. To improve the user experience, we can allow users to select a country name, and internally, this selection would be mapped to the corresponding country_code in the testexample.facttables.crm_table table.

It turns out that the answer to our problems is in the testexample.lookups.country_lookup that has the mapping.

SELECT concat('''', country_code, '''') AS Id, 
country_name AS value
FROM testexample.lookups.country_lookup

Observe the format of the table that is being created - it has an id column which is the column that will be passed as strings internally to the testexample.facttables.crm_table tableand country name values in the value column that the business user will select on the dashboard

1. This is how the results should look like:

Generating a global filter on testexample.lookups.country_lookup table.

1. Click Next. Name the filter as Country_Code and do not choose any default values:

Name the filter but do not choose default values.

1. Click Select. You should now see the Global Filter icon appear in the dashboards.

Global filter icon appears in the dashboard.

1. Click on the Global Filter icon to open a pop-up. Select any values, and you’ll notice that none of the charts update. This is because the global filter hasn’t been linked to the charts yet. We still need to specify which charts should have the global filter applied. Additionally, for the filter to work, the relevant column must exist in the table that the chart’s query is based on.

Global Filter dropdown in the dashboard

Charts are unaffected because the global filter has not been attached to the 3 charts.

1. Let us open the Big Number chart for Total Revenue (US Dollars). Open the editor and add the following code:

SELECT SUM(purchase) AS Total_Revenue
FROM testexample.facttables.crm_table
WHERE country_code IN ($country_code_filter);

We have just added $country_code_filter as a runtime parameter.

1. Press the play button and you should see a screen that looks like this. The query should fail and you should navigate to Query Parameters

Query parameters are not set

1. Choose the value of 'AF' to test if the filter works:

Choosing a parameter for testing the query to see if it will give the correct result wheen thee Global Filter is applied.

1. The result should look like:

Results returned as part of passing a parameter to a query.

1. Copy the following filter code:

WHERE country_code IN ($country_code_filter)

Tip: If there are multiple parameters, they can be combined in any filter condition within the chart. The global filter simply passes the column values into the chart, acting as runtime parameters for the query. The WHERE clause can then use these values in any combination that SQL allows. As a result, each global filter can be interpreted uniquely depending on the chart’s context.

1. Click Select. Then enable the Global Filter and bind the Country_Code Global Filter ( which was a collection of country_code id and country_name value pairs) to the country_code_filter parameter in the chart query. Thus, values defined in Country_Code Global Filter can now be chosen by the business user. When they are chosen, the values are sent as country_code_filter to the chart query.

Enable the Global Filter for this chart and bind it to the country_code_filter parameter

1. Click on Save and Close. Do the same operation on all the other charts. Make sure you add the following to their chart query and remove any semicolons:

WHERE country_code IN ($country_code_filter)

1. Now apply the Global Filter with the value of Japan

Apply the global filter with a value of Japan

1. You should get the following dashboard with the filtered charts at the top:

The three charts at the top that are attached to the Global Filter update.

1. If you want to change the Global Filter icon and click the Global Filter Edit icon

You can modify the Global Filter you have created.

Recap: We started by creating an id-based table for the Global Filter. The values in this table represent the options users can select from the filter dropdown. The filter will only apply to tables that contain the corresponding id values in one of their columns. For the charts, the query needs to be parameterized by adding a filter condition on that column (using the IN clause for multiple values). This parameter must be linked to the Global Filter's idin the chart configuration. When a user selects a value from the Global Filter, the corresponding id is passed to all the charts in the dashboard. Only charts that can accept that id as a parameter will apply the filter condition to the query, refining the results accordingly.

Pushdown Filter in Drillthroughs

1. The children of a dashboard, on which a Drillthrough is applied, can have the Global Filter applied to them as long as they are bound to the same filter."This means that the Global Filter will affect all child elements in the dashboard if they are properly connected to the filter, even when Drillthrough actions are involved.

2. We want to be able to apply the Global Filter in Total Revenue (US Dollars) chart below to be pushed down (propagated down) to the dashboard below:

Total Revenue (US Dollars) has a Drillthrough dashboard

The Global Filter is not applied to the underlying dashboard underneath.

3. Let us examine the query behind Detailed Revenue by Country dashboard:

SELECT 
      sum(purchase) AS purchase, country_name 
FROM 
      testexample.facttables.crm_table 
INNER JOIN 
      testexample.lookups.country_lookup 
      ON testexample.facttables.crm_table.country_code = testexample.lookups.country_lookup.country_code
GROUP BY 
country_name

4. Observe that country_code is already present in the tables being joined except that we have not added it. Let us add it and add our $country_code_filter parameter:

SELECT 
    SUM(purchase) AS purchase, country_name 
FROM 
    testexample.facttables.crm_table 
INNER JOIN 
    testexample.lookups.country_lookup 
    ON testexample.facttables.crm_table.country_code = testexample.lookups.country_lookup.country_code 
WHERE 
    testexample.facttables.crm_table.country_code IN ($country_code_filter) 
GROUP BY 
country_name;

1. You need to create a brand new filter, as there is currently no option to select from a library of Global Filters. Make sure to give it the exact same name as the one previously used in the Global Filters section
2. You should be able to see that the filters are propagated down:

Filters are propagated down

Date Filter

1. The Date Filteris very similar to the Global Filter except that the table of filter values is predefined. It is in the DATE format i.e YYYY-MM-DD. There are also presets defined as well.

2. The Date Filter will use the following format in SQL fragment:

WHERE registration_date BETWEEN CAST('$START_DATE' AS DATE) AND CAST('$END_DATE' AS DATE)

What the above means is that the query is filtering results to include only rows where a specific date column falls within the range of two dates, $START_DATE and $END_DATE. Here's a breakdown:

• BETWEEN: This keyword is used to check if a value falls within a specified range, inclusive of both the start and end values.

• CAST('$START_DATE' AS DATE): Converts the value of the variable $START_DATE into a DATE data type.

• CAST('$END_DATE' AS DATE): Similarly, this converts the value of the $END_DATE variable into a DATE data type.

Thus, the SQL fragment is checking if a particular date field in the query lies within the inclusive range of $START_DATE and $END_DATE once they are converted to the DATE type. Like Global Filters, we have two parameters -$START_DATE and $END_DATEthat we will bind to the Date Filter.

For example, if $START_DATE is '2021-01-01' and $END_DATE is '2021-12-31', the query would return rows where the date column is between January 1, 2021, and December 31, 2021, inclusive.

1. Let us first create a table that has the names of customers who registered as a loyalty member:.Make sure you execute each of the SQL queries by selecting the query in the editor and using the Run Selected Query option:

-- Explore the Registration Table--
SELECT first_name, last_name, registration_date FROM purchases_dataset_crm_data;

-- Create a New Table in the Accelerated Store --
CREATE TABLE testexample.facttables.registration_table AS
   SELECT
      cast(null as string) first_name,
      cast(null as string) last_name,
      cast(null as DATE) registration_date
   WHERE false;


--Insert data into this new table --
INSERT INTO testexample.facttables.registration_table(first_name, last_name, registration_date)
(SELECT first_name, last_name, TO_DATE(registration_date, 'MM/dd/yyyy') AS DATE_KEY FROM purchases_dataset_crm_data);

You should see the following:

Create the registration table

1. Create a table with the following SQL in the QueryProMode dashboard:

SELECT * FROM testexample.facttables.registration_table
ORDER BY registration_date DESC

Observe that if you choose the ORDER BY and keep flipping the DESC/ASC, you can find out the date range: 2020-01-01 and 2021-12-30.

SQL query for the table authoring

Table creation

1. Resize and rearrange the dashboard to show the table:

Registration table is added to the dashboard.

1. Click on Add Filter and choose Date Filter:

Choose Date Filter

1. Configure the Date Filter to choose Dec 31, 2021 as the default date as we have data for 2 years i.e. 2020 and 2021

Choose the options in Date Filter

Choose Dec 31, 2021 as the default date.

1. The dashboard should look like this and note that the date has the format that is just the reverse of the DATE format.

Date Filter added to the dashboards.

1. If you open up the Registration Table chart, you will see two new parameters:

$START_DATE and $END_DATE are the two parameters

1. Add this SQL fragment for the Date Filter. Input the parameters for @START_DATE as 1/1/2020 and $END_DATE as 12/31/2021.

SELECT * 
FROM testexample.facttables.registration_table
WHERE registration_date BETWEEN CAST('$START_DATE' AS DATE) AND CAST('$END_DATE' AS DATE)
ORDER BY registration_date ASC;

Choose the date parameters

1. Bind the Date Filters as shown in the figure below:

Parameters in the SQL query are availlable for binding

1. The Date Filter should be. You can just type the dates if you need to:

Date Filter settings

1. Results of the Date Filter query are shown below:

Date Filter keeps changing the results based on the filter values.

Final Remarks

In conclusion, this tutorial shows that you can create powerful, interactive dashboards using SQL without relying on expensive BI tools. By empowering both data teams and business users to collaborate within the Data Distiller environment, you can streamline the process of building insightful, data-driven dashboards. This approach eliminates the need for external BI platforms, reducing complexity and cost while still delivering high-quality, actionable insights for decision-making.