BI tools
note prerequisites
PREREQUISITES
Ensure you have validated a successful connection, can list data views, and use a data view for the BI tool for which you want to try out this use case.
tabs
Power BI Desktop

  1. In the Data pane:

    1. Select daterange.
    2. Select product_namr.
    3. Select ∑ purchase_revenue.
    4. Select ∑ purchases.

  2. In the Filters pane:

    1. Select daterange is (All) from Filters on this visual.
    2. Select Advanced filtering as the Filter type.
    3. Define the filter to Show items when the value is on or after 1/1/2023 And is before 2/1/2023.

  3. In the Visualizations pane:

    1. Select CrossSize75 to remove daterange from Columns.
    2. Drag Sum of purchase_revenue to the bottom of Column items.

  4. In the report, select Sum of purchase_revenue to sort the table in descending order of purchase revenue.

    Your Power BI Desktop should look like below.

    Power BI Desktop Using Date Range Names To Filter {modal="regular"}

The query executed by Power BI Desktop using the BI extension is not including a sort statement. The lack of a sort statement implies that the sort is executed client side.

code language-sql 
select "_"."product_name",
    "_"."a0",
    "_"."a1"
from
(
    select "rows"."product_name" as "product_name",
        sum("rows"."purchases") as "a0",
        sum("rows"."purchase_revenue") as "a1"
    from
    (
        select "_"."daterangeName",
            "_"."daterange",
            "_"."filterId",
            "_"."filterName",
            "_"."timestamp",
            "_"."affiliate_name",
            "_"."affiliate_url",
            "_"."commerce.order.priceTotal",
            "_"."customer_city",
            "_"."customer_region",
            "_"."daterangeday",
            "_"."daterangefifteenminute",
            "_"."daterangefiveminute",
            "_"."daterangehour",
            "_"."daterangeminute",
            "_"."daterangemonth",
            "_"."daterangequarter",
            "_"."daterangesecond",
            "_"."daterangethirtyminute",
            "_"."daterangeweek",
            "_"."daterangeyear",
            "_"."hitdatetime",
            "_"."page_name",
            "_"."page_url",
            "_"."product_category",
            "_"."product_name",
            "_"."product_short_review",
            "_"."product_subCategory",
            "_"."referrer_url",
            "_"."search_engine",
            "_"."search_keywords",
            "_"."store_city",
            "_"."store_name",
            "_"."store_region",
            "_"."store_type",
            "_"."timepartdayofmonth",
            "_"."timepartdayofweek",
            "_"."timepartdayofyear",
            "_"."timeparthourofday",
            "_"."timepartminuteofhour",
            "_"."timepartmonthofyear",
            "_"."timepartquarterofyear",
            "_"."timepartweekofyear",
            "_"."cm_session_end_rate_defaultmetric",
            "_"."cm_session_person_defaultmetric",
            "_"."cm_session_start_rate_defaultmetric",
            "_"."cm_timespent_person_defaultmetric",
            "_"."cm_timespent_session_defaultmetric",
            "_"."cm_product_name_count_distinct",
            "_"."ad_views",
            "_"."adobe_sessionends",
            "_"."adobe_sessionstarts",
            "_"."adobe_timespent",
            "_"."exchange_buybacks",
            "_"."exchange_cost",
            "_"."exchange_purchases",
            "_"."exchange_revenue",
            "_"."occurrences",
            "_"."page_views",
            "_"."product_quantity",
            "_"."product_reviews",
            "_"."product_views",
            "_"."purchase_revenue",
            "_"."purchases",
            "_"."visitors",
            "_"."visits"
        from "public"."cc_data_view" "_"
        where "_"."daterange" < date '2023-02-01' and "_"."daterange" >= date '2023-01-01'
    ) "rows"
    group by "product_name"
) "_"
where not "_"."a0" is null or not "_"."a1" is null
limit 1000001
Tableau Desktop

  1. Select the Sheet 1 tab at the bottom to switch from Data source. In the Sheet 1 view:

    1. Drag Daterange entry from the Tables list in the Filters shelf.

    2. In the Filter Field [Daterange] dialog, select Range of Dates and select Next >.

    3. In the Filter [Daterange] dialog, select Range of dates, and select 01/01/2023 - 1/2/2023. Select Apply and OK.

    4. Drag Product Name from the Tables list and drop the entry in the field next to Rows.

    5. Drag Purchases entry from the Tables list and drop the entry in the field next to Columns… The value changes to SUM(Purchases).

    6. Drag Purchase Revenue entry from the Tables list and drop the entry in the field next to Columns, next to SUM(Purchases). The value changes to SUM(Purchase Revenue).

    7. Select Text Table from Show Me.

    8. Select Fit Width from the Fit dropdown menu.

    9. Select the Purchase Revenue column header and sort the table on this column in descending order.

      Your Tableau Desktop should look like below.

      Tableau Desktop Sort {modal="regular"}

The query executed by Tableau Desktop using the BI extension is not including a sort statement. The lack of this sort statement implies that the sort is executed client side.

code language-sql 
SELECT CAST("cc_data_view"."product_name" AS TEXT) AS "product_name",
  SUM("cc_data_view"."occurrences") AS "sum:occurrences:ok",
  SUM("cc_data_view"."purchase_revenue") AS "sum:purchase_revenue:ok",
  SUM("cc_data_view"."purchases") AS "sum:purchases:ok"
FROM "public"."cc_data_view" "cc_data_view"
WHERE (("cc_data_view"."daterange" >= (DATE '2023-01-01')) AND ("cc_data_view"."daterange" <= (DATE '2023-02-01')))
GROUP BY 1

Limits

In this use case, you want to report on the top 5 occurrences of product names during 2023.

Customer Journey Analytics

An example Limit panel for the use case:

Customer Journey Analytics Limit panel {modal="regular"}

BI tools
note prerequisites
PREREQUISITES
Ensure you have validated a successful connection, can list data views, and use a data view for the BI tool for which you want to try out this use case.
tabs
Power BI Desktop

  1. In the Data pane:

    1. Select daterange.
    2. Select product_name.
    3. Select ∑ occurrences.

  2. In the Filters pane:

    1. Select daterange is (All) from Filters on this visual.
    2. Select Relative date as the Filter type.
    3. Define the filter to Show items when the value is in the last 1 calendar years.
    4. Select Apply filter.
    5. Select product_name is (All) from Filters on this visual.
    6. Select Top N as the Filter type.
    7. Select Show Items Top 5 By value.
    8. Drag and drop ∑ occurrences from the Data pane and drop it on Add data fields here.
    9. Select Apply filter.

  3. In the Visualization pane:

    • Select CrossSize75 to remove daterange from Columns.

    Your Power BI Desktop should look like below.

    Power BI Desktop Using Date Range Names To Filter {modal="regular"}

The query executed by Power BI Desktop using the BI extension is including a limit statement but not the one expected. The limit to the top 5 occurrences is enforced by Power BI Desktop using explicit product name results.

code language-sql 
select "_"."product_name",
    "_"."a0"
from
(
    select "rows"."product_name" as "product_name",
        sum("rows"."occurrences") as "a0"
    from
    (
        select "_"."daterangeName",
            "_"."daterange",
            "_"."filterId",
            "_"."filterName",
            "_"."timestamp",
            "_"."affiliate_name",
            "_"."affiliate_url",
            "_"."commerce.order.priceTotal",
            "_"."customer_city",
            "_"."customer_region",
            "_"."daterangeday",
            "_"."daterangefifteenminute",
            "_"."daterangefiveminute",
            "_"."daterangehour",
            "_"."daterangeminute",
            "_"."daterangemonth",
            "_"."daterangequarter",
            "_"."daterangesecond",
            "_"."daterangethirtyminute",
            "_"."daterangeweek",
            "_"."daterangeyear",
            "_"."hitdatetime",
            "_"."page_name",
            "_"."page_url",
            "_"."product_category",
            "_"."product_name",
            "_"."product_short_review",
            "_"."product_subCategory",
            "_"."referrer_url",
            "_"."search_engine",
            "_"."search_keywords",
            "_"."store_city",
            "_"."store_name",
            "_"."store_region",
            "_"."store_type",
            "_"."timepartdayofmonth",
            "_"."timepartdayofweek",
            "_"."timepartdayofyear",
            "_"."timeparthourofday",
            "_"."timepartminuteofhour",
            "_"."timepartmonthofyear",
            "_"."timepartquarterofyear",
            "_"."timepartweekofyear",
            "_"."cm_session_end_rate_defaultmetric",
            "_"."cm_session_person_defaultmetric",
            "_"."cm_session_start_rate_defaultmetric",
            "_"."cm_timespent_person_defaultmetric",
            "_"."cm_timespent_session_defaultmetric",
            "_"."cm_product_name_count_distinct",
            "_"."ad_views",
            "_"."adobe_sessionends",
            "_"."adobe_sessionstarts",
            "_"."adobe_timespent",
            "_"."exchange_buybacks",
            "_"."exchange_cost",
            "_"."exchange_purchases",
            "_"."exchange_revenue",
            "_"."occurrences",
            "_"."page_views",
            "_"."product_quantity",
            "_"."product_reviews",
            "_"."product_views",
            "_"."purchase_revenue",
            "_"."purchases",
            "_"."visitors",
            "_"."visits"
        from "public"."cc_data_view" "_"
        where (("_"."product_name" in ('Saltwater Monofilament Line', 'Pop-Up Beach Tent', 'Instant Pop-Up Tent', 'Envelop Sleeping Bag', 'Waterproof Tackle Bag')) and "_"."daterange" < date '2024-01-01') and "_"."daterange" >= date '2023-01-01'
    ) "rows"
    group by "product_name"
) "_"
where not "_"."a0" is null
limit 1000001
Tableau Desktop

  1. Select the Sheet 1 tab at the bottom to switch from Data source. In the Sheet 1 view:

    1. Drag Daterange entry from the Tables list in the Filters shelf.

    2. In the Filter Field [Daterange] dialog, select Range of Dates and select Next >.

    3. In the Filter [Daterange] dialog, select Relative dates, select Years, and select Previous years. Select Apply and OK.

    4. Drag Product Name from the Tables list to Rows.

    5. Drag Occurrences entry from the Tables list and drop the entry in the field next to Columns… The value changes to SUM(Occurrences).

    6. Select Text Table from Show Me.

    7. Select Fit Width from the Fit dropdown menu.

    8. Select Product Name in Rows. Select Filter from the dropdown menu.

      1. In the Filter [Product Name] dialog, select the Top tab.

      2. Select By field: Top 5 by Occurrences Sum.

      3. Select Apply and OK.

        AlertRed You notice that the table disappears. Selecting the top 5 product names by occurrences does not work properly using this filter.

      4. Select the Product Name in the Filter shelf and from the dropdown menu select Remove. The table reappears.

    9. Select SUM(Occurrences) in the Marks shelf. Select Filter from the dropdown menu.

      1. In the Filter [Occurrences] dialog, select At least.

      2. Enter 47.799 as the value. This value ensures that only the top 5 items are shown in the table. Select Apply and OK.

        Your Tableau Desktop should look like below.

        Tableau Desktop Limits {modal="regular"}

As shown above, this query executed by Tableau Desktop, when defining a Top 5 occurrences filter on product names, fails.

code language-sql 
SELECT CAST("cc_data_view"."product_name" AS TEXT) AS "product_name",
  SUM("cc_data_view"."occurrences") AS "sum:occurrences:ok"
FROM "public"."cc_data_view" "cc_data_view"
  INNER JOIN (
  SELECT CAST("cc_data_view"."product_name" AS TEXT) AS "product_name",
    SUM("cc_data_view"."occurrences") AS "$__alias__0"
  FROM "public"."cc_data_view" "cc_data_view"
  GROUP BY 1
  ORDER BY 2 DESC,
    1 ASC
  LIMIT 5
) "t0" ON (CAST("cc_data_view"."product_name" AS TEXT) = "t0"."product_name")
WHERE (("cc_data_view"."daterange" >= (TIMESTAMP '2023-01-01 00:00:00.000')) AND ("cc_data_view"."daterange" < (TIMESTAMP '2024-01-01 00:00:00.000')))
GROUP BY 1

The query executed by Tableau Desktop, when defining a Top 5 filter on occurrences, is shown below. The limit is not visible in the query and applied client side.

code language-sql 
SELECT CAST("cc_data_view"."product_name" AS TEXT) AS "product_name",
  SUM("cc_data_view"."occurrences") AS "sum:occurrences:ok"
FROM "public"."cc_data_view" "cc_data_view"
WHERE (("cc_data_view"."daterange" >= (TIMESTAMP '2023-01-01 00:00:00.000')) AND ("cc_data_view"."daterange" < (TIMESTAMP '2024-01-01 00:00:00.000')))
GROUP BY 1

Transformations

You want to understand the transformations of Customer Journey Analytics objects like dimensions, metrics, filters, calculated metrics, and date ranges by the various BI tools.

Customer Journey Analytics
In Customer Journey Analytics, you define in a data view, which and how components of your datasets are exposed as dimensions and metrics. That definition of dimension and metrics is exposed to the BI tools using the BI extension.
You use components like Filters, Calculated metrics, and Date ranges as part of your Workspace projects. These components are also exposed to the BI tools using the BI extension.
BI tools
note prerequisites
PREREQUISITES
Ensure you have validated a successful connection, can list data views, and use a data view for the BI tool for which you want to try out this use case.
tabs
Power BI Desktop

The Customer Journey Analytics objects are available in the Data pane and are retrieved from the table you have selected in Power BI Desktop. For example, public.cc_data_view. The name of the table is the same as the External ID that you have defined for your data view in Customer Journey Analytics. For example, data view with Title C&C - Data View and External ID cc_data_view.

Dimensions
Dimensions from Customer Journey Analytics are identified by the Component ID. The Component ID is defined in your Customer Journey Analytics data view. For example, dimension Product Name in Customer Journey Analytics has a Component ID product_name, which is the name for the dimension in Power BI Desktop.
Date range dimensions from Customer Journey Analytics, like Day, Week, Month, and more are available as daterangeday, daterangeweek, daterangemonth, and more.

Metrics
Metrics from Customer Journey Analytics are identified by the Component ID. The Component ID is defined in your Customer Journey Analytics data view. For example, metric Purchase Revenue in Customer Journey Analytics has a Component ID purchase_revenue, which is the name for the metric in Power BI Desktop. A ∑ indicates metrics. When you use a metric in any visualization, the metric is renamed to Sum of metric.

Filters
Filters that you define in Customer Journey Analytics are available as part of the filterName field. When you use a filterName field in Power BI Desktop, you can specify which filter to use.

Calculated metrics
Calculated metrics that you define in Customer Journey Analytics are identified by the External ID you have defined for the calculated metric. For example, calculated metric Product Name (Count Distinct) has External ID product_name_count_distinct and is shown as cm_product_name_count_distinc t in Power BI Desktop.

Date ranges
Date ranges that you define in Customer Journey Analytics are available as part of the daterangeName field. When you use a daterangeName field, you can specify which date range to use.

Custom transformations
Power BI Desktop provides custom transformation functionality using Data Analysis Expressions (DAX). As an example, you want to execute the Single dimension ranked use case with product names in lower case.

  1. In the report view, select the bar visualization.

  2. Select product_name in the Data pane.

  3. Select New column in the toolbar.

  4. In the formula editor, define a new column named product_name_lower, like product_name_lower = LOWER('public.cc_data_view[product_name]).
    Power BI Desktop Transformation to Lower {modal="regular"}

  5. Ensure you select the new product_name_lower column in the Data pane instead of the product_name column.

  6. Select Report as Table from More in the table visualization.

    Your Power BI Desktop should look like below.
    Power BI Desktop Transformation Final {modal="regular"}

The custom transformation result in an updates to SQL queries. See the use of the lower function in the SQL example below:

code language-sql 
select "_"."product_name_lower",
    "_"."a0",
    "_"."a1"
from
(
    select "rows"."product_name_lower" as "product_name_lower",
        sum("rows"."purchases") as "a0",
        sum("rows"."purchase_revenue") as "a1"
    from
    (
        select "_"."daterange" as "daterange",
            "_"."product_name" as "product_name",
            "_"."purchase_revenue" as "purchase_revenue",
            "_"."purchases" as "purchases",
            lower("_"."product_name") as "product_name_lower"
        from
        (
            select "_"."daterange",
                "_"."product_name",
                "_"."purchase_revenue",
                "_"."purchases"
            from
            (
                select "daterange",
                    "product_name",
                    "purchase_revenue",
                    "purchases"
                from "public"."cc_data_view" "$Table"
            ) "_"
            where ("_"."daterange" < date '2024-01-01' and "_"."daterange" >= date '2023-01-01') and ("_"."product_name" in ('4G Cellular Trail Camera', '4K Wildlife Trail Camera', 'Wireless Trail Camera', '8-Person Cabin Tent', '20MP No-Glow Trail Camera', 'HD Wildlife Camera', '4-Season Mountaineering Tent', 'Trail Camera', '16MP Trail Camera with Solar Panel', '10-Person Family Tent'))
        ) "_"
    ) "rows"
    group by "product_name_lower"
) "_"
where not "_"."a0" is null or not "_"."a1" is null
limit 1000001
Tableau Desktop

The Customer Journey Analytics objects are available in the Data side bar whenever you work in a sheet. And are retrieved from the table that you have selected as part of the Data source page in Tableau. For example, cc_data_view. The name of the table is the same as the External ID that you have defined for your data view in Customer Journey Analytics. For example, data view with Title C&C - Data View and External ID cc_data_view.

Dimensions
Dimensions from Customer Journey Analytics are identified by the Component name. The Component name is defined in your Customer Journey Analytics data view. For example, dimension Product Name in Customer Journey Analytics has a Component name Product Name, which is the name for the dimension in Tableau. All dimensions are identified by Abc.
Date range dimensions from Customer Journey Analytics, like Day, Week, Month, and more are available as Daterangeday, Daterangeweek, Daterangemonth, and more. When you use a date range dimension, you have to select an appropriate definition of date or time to apply to that date range dimension from the dropdown menu. For example, Year, Quarter, Month, Day.

Metrics
Metrics from Customer Journey Analytics are identified by the Component Name. The Component Name is defined in your Customer Journey Analytics data view. For example, metric Purchase Revenue in Customer Journey Analytics has a Component Name Purchase Revenue, which is the name for the metric in Tableau. All metrics are identified by #. When you use a metric in any visualization, the metric is renamed to Sum(metric).

Filters
Filters that you define in Customer Journey Analytics are available as part of the Filter Name field. When you use a Filter Name field in Tableau, you can specify which filter to use.

Calculated metrics
Calculated metrics that you define in Customer Journey Analytics are identified by the Title you have defined for the calculated metric. For example, calculated metric Product Name (Count Distinct) has Title Product Name (Count Distinct) and is shown as Cm Product Name Count Distinct in Tableau.

Date ranges
Date ranges that you define in Customer Journey Analytics are available as part of the Daterange Name field. When you use a Daterange Name field, you can specify which date range to use.

Custom transformations
Tableau Desktop provides custom transformation functionality using Calculated Fields. As an example, you want to execute the Single dimension ranked use case with product names in lower case.

  1. Select Analysis > Create Calculated Field from the main menu.

    1. Define Lowercase Product Name using the function LOWER([Product Name]).

      Tableau Calculated Field {modal="regular"}

    2. Select OK.

  2. Select the Data sheet.

    1. Drag Lowercase Product Name from Tables and drop the entry in the field next to Rows.
    2. Remove Product Name from Rows.

  3. Select Dashboard 1 view.

Your Tableau Desktop should look like below.

Tableau Desktop after transformation {modal="regular"}

The custom transformation result in an updates to SQL queries. See the use of the LOWER function in the SQL example below:

code language-sql 
SELECT LOWER(CAST(CAST("cc_data_view"."product_name" AS TEXT) AS TEXT)) AS "Calculation_1562467608097775616",
  SUM("cc_data_view"."purchase_revenue") AS "sum:purchase_revenue:ok",
  SUM("cc_data_view"."purchases") AS "sum:purchases:ok"
FROM "public"."cc_data_view" "cc_data_view"
WHERE (("cc_data_view"."daterange" >= (DATE '2023-01-01')) AND ("cc_data_view"."daterange" <= (DATE '2023-12-31')))
GROUP BY 1
HAVING ((SUM("cc_data_view"."purchase_revenue") >= 999999.99999998999) AND (SUM("cc_data_view"."purchase_revenue") <= 2000000.00000002))

Visualizations

You want to understand how the visualizations, available in Customer Journey Analytics, can be similarly created using the available visualizations in the BI tools.

Customer Journey Analytics
Customer Journey Analytics has a number of visualizations. See Visualizations for an introduction and an overview of all possible visualizations.
BI tools
tabs
Power BI Desktop

Comparison

For most Customer Journey Analytics visualizations, Power BI Desktop offers equivalent experiences. See the table below.

table 0-row-3 1-row-3 2-row-3 3-row-3 4-row-3 5-row-3 6-row-3 7-row-3 8-row-3 9-row-3 10-row-3 11-row-3 12-row-3 13-row-3 14-row-3 15-row-3 16-row-3 17-row-3 18-row-3 19-row-3 20-row-3 21-row-3 22-row-3 23-row-3 1-align-center 5-align-center 9-align-center 13-align-center 17-align-center 21-align-center 25-align-center 29-align-center 33-align-center 37-align-center 41-align-center 45-align-center 49-align-center 53-align-center 57-align-center 61-align-center 65-align-center 69-align-center 73-align-center 77-align-center 81-align-center 85-align-center 89-align-center 93-align-center
Icon Customer Journey Analytics visualization Power BI Desktop visualization
GraphArea Area Area chart, stacked area chart and 100% area chart
GraphBarVertical Bar Clustered column chart
GraphBarVertical Bar stacked Stacked column chart and 100% stacked column chart
GraphBullet Bullet
TextNumbered Cohort table
Combo Combo Line and stacked column chart and Line and clustered column chart
GraphDonut Donut Donut chart
ConversionFunnel Fallout Funnel.
GraphPathing Flow Decomposition tree?
ViewTable Freeform table Table and Matrix
GraphHistogram Histogram
GraphBarHorizontal Horizontal bar Clustered bar chart
GraphBarHorizontalStacked Horizontal bar stacked Stacked bar chart and 100% stacked bar chart
Branch3 Journey canvas Decomposition tree
KeyMetrics Key metric summary
GraphTrend Line Line chart
GraphScatter Scatter Scatter chart
PageRule Section header Text box
MoveUpDown Summary change Card
123 Summary number Card
Text Text Text box
ModernGridView Treemap Treemap
Type Venn

Drill down

Power BI supports a drill mode to explore in-depth details on certain visualizations. In the example below, you analyze purchase revenue for product categories. From the context menu of a bar representing a product category, you can select Drill down.

Power BI drill down {modal="regular"}

Drill down updates the visualization with purchase revenue for products within the selected product category.

Power BI drill up {modal="regular"}

The drill down results in the following SQL query that uses a WHERE clause:

code language-sql 
select "_"."product_category" as "c25",
    "_"."product_name" as "c26",
    "_"."a0" as "a0"
from
(
    select "_"."product_category",
        "_"."product_name",
        "_"."a0"
    from
    (
        select "_"."product_category",
            "_"."product_name",
            "_"."a0"
        from
        (
            select "rows"."product_category" as "product_category",
                "rows"."product_name" as "product_name",
                sum("rows"."purchase_revenue") as "a0"
            from
            (
                select "_"."product_category",
                    "_"."product_name",
                    "_"."purchase_revenue"
                from "public"."cc_data_view" "_"
                where ("_"."daterange" >= date '2023-01-01' and "_"."product_category" = 'Fishing') and "_"."daterange" < date '2024-01-01'
            ) "rows"
            group by "product_category",
                "product_name"
        ) "_"
        where not "_"."a0" is null
    ) "_"
) "_"
order by "_"."product_category",
        "_"."product_name"
limit 1001
Tableau Desktop

Comparison

For most Customer Journey Analytics visualizations, Tableau offers equivalent experiences. See the table below.

table 0-row-3 1-row-3 2-row-3 3-row-3 4-row-3 5-row-3 6-row-3 7-row-3 8-row-3 9-row-3 10-row-3 11-row-3 12-row-3 13-row-3 14-row-3 15-row-3 16-row-3 17-row-3 18-row-3 19-row-3 20-row-3 21-row-3 22-row-3 23-row-3 1-align-center 5-align-center 9-align-center 13-align-center 17-align-center 21-align-center 25-align-center 29-align-center 33-align-center 37-align-center 41-align-center 45-align-center 49-align-center 53-align-center 57-align-center 61-align-center 65-align-center 69-align-center 73-align-center 77-align-center 81-align-center 85-align-center 89-align-center 93-align-center
Icon Customer Journey Analytics visualization Power BI Desktop visualization
GraphArea Area Area Chart
GraphBarVertical Bar Bar Chart
GraphBarVertical Bar stacked
GraphBullet Bullet Bullet Graph
TextNumbered Cohort table
Combo Combo Combination Charts
GraphDonut Donut
ConversionFunnel Fallout
GraphPathing Flow
ViewTable Freeform table Text Table
GraphHistogram Histogram Histogram
GraphBarHorizontal Horizontal bar Bar Chart
GraphBarHorizontalStacked Horizontal bar stacked Bar Chart
Branch3 Journey canvas
KeyMetrics Key metric summary
GraphTrend Line Line Chart
GraphScatter Scatter Scatter Plot
PageRule Section header
MoveUpDown Summary change
123 Summary number
Text Text
ModernGridView Treemap Treemap
Type Venn

Drill down

Tableau supports drill mode through hierarchies. In the example below, you create a hierarchy when you select the Product Name field within Tables and drag it on top of Product Category. Then, from the context menu of a bar representing a product category, you can select + Drill down.

Tableau drill down {modal="regular"}

Drill down will update the visualization with purchase revenue for products within the selected product category.

Tableau drill up {modal="regular"}

The drill down results in the following SQL query that is using a GROUP BY clause:

code language-sql 
SELECT CAST("cc_data_view"."product_category" AS TEXT) AS "product_category",
  CAST("cc_data_view"."product_name" AS TEXT) AS "product_name",
  SUM("cc_data_view"."purchase_revenue") AS "sum:purchase_revenue:ok"
FROM "public"."cc_data_view" "cc_data_view"
WHERE (("cc_data_view"."daterange" >= (TIMESTAMP '2023-01-01 00:00:00.000')) AND ("cc_data_view"."daterange" < (TIMESTAMP '2024-01-01 00:00:00.000')))
GROUP BY 1,
  2

The query does not limit the results to the selected product category; only the visualization shows the selected product category.

Tableau drill up {modal="regular"}

Alternatively, you can create a drill down dashboard where one visual is the result of the selection in another visual. In the example below, the Product Categories visualization is used as a filter to update the Product Names table. This visualization filter is client-only and does not result in an additional SQL query.

Tableau visualization filter {modal="regular"}

Caveats

Each of the supported BI tools has some caveats in working with the Customer Journey Analytics BI extension.

BI tools
tabs
Power BI Desktop

  • Power BI Desktop Advanced date range filtering is exclusive. For your end date, you need to select one past the day you want to report on. For example is on or after 1/1/2023 and before 1/2/2023.

  • Power BI Desktop defaults to Import when you create a connection. Please ensure you use Direct Query.

  • Power BI Desktop exposes data transformations through Power Query. Power Query primarily works with Import type connections so a many transformations that you apply like date or string functions throw an error saying you need to switch to an Import type connection. If you need to transform data at query time, you should use derived dimensions and metrics so Power BI doesn’t need to do the transforms itself.

  • Power BI Desktop does not understand how to handle date-time type columns so the daterange X dimensions like daterangehour and daterangeminute are not supported.

  • Power BI Desktop by default tries to make multiple connections using up more Query Service sessions. Go in to the Power BI settings for your project and disable parallel queries.

  • Power BI Desktop does all sorting and limiting client-side. Power BI Desktop also has different semantics for top X filtering that includes tied values. So you cannot create the same sorting and limiting as you can do in Analysis Workspace.

  • Earlier versions of the Power BI Desktop October 2024 release break PostgreSQL data sources. Ensure you use the version mentioned in this article.
Tableau Desktop

  • Tableau Desktop Range of Dates filtering is exclusive. For your end date, you need to select one past the day you want to report on.

  • By default, when you add a date or date-time dimension like Daterangemonth to the rows of a sheet, Tableau Desktop wraps the field in a YEAR() function. To get what you want, you need to select that dimension and from the dropdown menu select the date function you want to use. For example, change Year to Month when you are trying to use Daterangemonth.

  • Limiting results to the Top X is not obvious in Tableau Desktop. You can limit the results explicitly or using a calculated-field and the INDEX() function. Adding a Top X filter to a dimension generates complex SQL using an inner-join that is not supported.
recommendation-more-help