1. Access the required credentials and parameters from the Experience Platform Query Service UI.

   1. Navigate to your Experience Platform sandbox.

   2. Select Queries from the left rail.

   3. Select Credentials tab in the Queries interface.

   4. Select prod:cja from the Database dropdown menu.

      

2. Start Power BI Desktop.

   1. From the main interface, select Get data from other sources.
   2. In the Get Data dialog:
      
      1. Search for and select PostgreSQL database.
      2. Select Connect.
   3. In the PostgreSQL database dialog:
      
      1. Use to copy and paste the Host and Port values from the Experience Platform Query Expiring Credentials panel, separated by : as the value for Server. For example: examplecompany.platform-query.adobe.io:80.
      2. Use to copy and paste the Database value from the Experience Platform Query Expiring Credentials panel. Add ?FLATTEN to the value that you paste. For example, prod:cja?FLATTEN.
      3. Select DirectQuery as the Data connectivity mode.
      4. Select OK.
   4. In the PostgreSQL database - Database dialog:
      
      1. Use to copy the Username and Password values from the Experience Platform Query Expiring Credentials panel in the User name and Password fields. If you are using a non-expiring credential, use the password of your non-expiring credential.
      2. Ensure that the dropdown menu for Select which level to apply these settings to is set to the Server that you have defined earlier.
      3. Select Connect.
   5. In the Navigator dialog, the data views are retrieved. This retrieval can take some time. Once retrieved, you see the following in Power BI Desktop.
      
      1. Select public.cc_data_view from the list in the left panel.
      2. You have two options:
         1. Select Load to continue and finish the setup.
         2. Select Transform Data. You see a dialog where you can optionally apply transformations as part of the configuration.
            
            • Select Close & Apply.
   6. After a while, public.cc_data_view is displayed in the Data pane. Select to show dimensions and metrics.
      

To FLATTEN or not

Power BI Desktop supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.

FLATTEN parameter	Example	Supported	Remarks
None	prod:cja
?FLATTEN	prod:cja?FLATTEN		Recommended option to use!
%3FFLATTEN	prod:cja%3FFLATTEN		Power BI Desktop displays error: We couldn’t authenticate with the credentials provided. Please try again.

More information

• Prerequisites

• Credentials guide

• Connect Power BI to Query Service.

1. Access the required credentials and parameters from the Experience Platform Query Service UI.

   1. Navigate to your Experience Platform sandbox.

   2. Select Queries from the left rail.

   3. Select Credentials tab in the Queries interface.

   4. Select prod:cja from the Database dropdown menu.

      

2. Start Tableau.

   1. Select PostgreSQL from the left rail underneath To a Server. If not available, select More… and select PostgreSQL from the Installed Connectors.
      

   2. In the PostgreSQL dialog, in the General tab:
      

      1. Use to copy and paste the Host from the Experience Platform Query Expiring Credentials panel to the Server.
      2. Use to copy and paste the Port from the Experience Platform Query Expiring Credentials panel to the Port.
      3. Use to copy and paste the Database from the Experience Platform Query Expiring Credentials panel to the Database. Add %3FFLATTEN to the value that you paste. For example: prod:cja%3FFLATTEN.
      4. Select Username and Password from the Authentication dropdown menu.
      5. Use to copy and paste the Username from the Experience Platform Query Expiring Credentials panel to the Username.
      6. Use to copy and paste the Password from the Experience Platform Query Expiring Credentials panel to the Password. If you are using a non-expiring credential, use the password of your non-expiring credential.
      7. Ensure that Require SSL is checked.
      8. Select Sign In.

      You see a Progressing Request dialog while Tableau Desktop validates the connection.

   3. In the main window, you see in the Data Source page, in the left pane:

      • The name of the connection, underneath Connections.
      • The name of the database, underneath Database.
      • A list of tables, underneath Table.
        
      1. Drag the cc_data_view entry and drop the entry on the main view that reads Drag tables here.

   4. The main window displays details of the cc_data_view data view.
      

To FLATTEN or not

Tableau Desktop supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.

FLATTEN parameter	Example	Supported	Remarks
None	prod:cja
?FLATTEN	prod:cja?FLATTEN
%3FFLATTEN	prod:cja%3FFLATTEN		Recommended option to use. Note, %3FFLATTEN is URL-encoded version of ?FLATTEN.

More information

• Prerequisites

• Credentials guide

• Connect Tableau Desktop to Query Service.

1. Access the required credentials and parameters from the Experience Platform Query Service UI.

   1. Navigate to your Experience Platform sandbox.

   2. Select Queries from the left rail.

   3. Select Credentials tab in the Queries interface.

   4. Select prod:cja from the Database dropdown menu.

      

2. Log in to Looker

   1. Select Admin from the left rail.

   2. Select Connections.

   3. Select Add Connection.

   4. In the Connect your database to Looker screen.

      
      1. Enter a Name for your connection, for example Example Looker Connection.
      2. Ensure All Projects is selected as the Connection Scope.
      3. Select PostgreSQL 9.5+ as the Dialect.
      4. Use to copy and paste the Host value from the Experience Platform Query Expiring Credentials panel, as the value for Host. For example: examplecompany.platform-query.adobe.io.
      5. Use to copy and paste the Port value from the Experience Platform Query Expiring Credentials panel, as the value for Port. For example: 80.
      6. Use to copy and paste the Database value from the Experience Platform Query Expiring Credentials panel as the value for Database. Add %3FFLATTEN to the value that you paste. For example, prod:cja%3FFLATTEN.
      7. Use to copy and paste the Username value from the Experience Platform Query Expiring Credentials panel as the value for Username.
      8. Use to copy and paste the Password value from the Experience Platform Query Expiring Credentials panel as the value for Password.
      9. Select Expand all at Optional Settings.
      10. Set Max connections per node to 5.
      11. Ensure SSL is enabled.
      12. Select Test to test the connection. You should see a banner appear at the top of the screen with a message like Success, can connect JDBC ….
      13. Select Connect to establish and save the connection.

   5. You see the new connection in the Connections interface.

   6. Select ← from Admin to go to main navigation in the left rail.

   7. Select Develop.

   8. Select Projects.

   9. Select New Model in LookML Projects.

   10. To ensure you do not affect other users. select Enter Development Mode, when prompted.

   11. In the Create Model experience:

       1. In ➊ Select Database Connection:
          1. Select your database connection in Select database connection. For example: example_looker_connection.
          2. Name your project in Create a new LookML Project for this model. For example: example_looker_project.
          3. Select Next.
       2. In ➋ Select Tables:
          1. Select public and then ensure your Customer Journey Analytics data view is selected. For example: cc_data_view.
          2. Select Next.
       3. In ➌ Select Primary Keys:
          1. Select Next.
       4. In ➍ Select Explores to Create:
          1. Ensure you select your view. For example: cc_data_view.view.
          2. Select Next.
       5. In ➎ Enter Model Name:
          1. Name your model. For example: example_looker_model.
       6. Select Complete and Explore Data.

   You are redirected to the Explore interface of Looker, ready to explore the data.

To FLATTEN or not

Looker supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.

FLATTEN parameter	Example	Supported	Remarks
None	prod:cja
?FLATTEN	prod:cja?FLATTEN
%3FFLATTEN	prod:cja%3FFLATTEN		Recommended option to use. Note, %3FFLATTEN is URL-encoded version of ?FLATTEN.

More information

• Prerequisites

• Credentials guide

1. Access the required credentials and parameters from the Experience Platform Query Service UI.

   1. Navigate to your Experience Platform sandbox.

   2. Select Queries from the left rail.

   3. Select Credentials tab in the Queries interface.

   4. Select prod:cja from the Database dropdown menu.

      

2. Ensure you have set up a dedicated Python virtual environment for running your Jupyter Notebook environment.

3. Ensure you have installed the required libraries in your virtual environment:

   • ipython-sql: pip install ipython-sql.
   • psycopg2-binary: pip install psycopg-binary.
   • sqlalchemy: pip install sqlalchemy.

4. Start Jupyter Notebook from your virtual environment: jupyter notebook.

5. Create a new notebook, or download this sample notebook.

6. In your first cell, enter and execute:

   %config SqlMagic.style = '_DEPRECATED_DEFAULT'
   

7. In a new cell, enter the config parameters for your connection. Use to copy and paste values from the Experience Platform Query Expiring Credentials panel to the values required for the config parameters. For example:

   import ipywidgets as widgets
   from IPython.display import display
   
   config_host = widgets.Text(description='Host:', value='example.platform-query-stage.adobe.io',
                           layout=widgets.Layout(width="600px"))
   display(config_host)
   config_port = widgets.IntText(description='Port:', value=80,
                              layout=widgets.Layout(width="200px"))
   display(config_port)
   config_db = widgets.Text(description='Database:', value='prod:cja',
                         layout=widgets.Layout(width="300px"))
   display(config_db)
   config_username = widgets.Text(description='Username:', value='EC582F955C8A79F70A49420E@AdobeOrg',
                               layout=widgets.Layout(width="600px"))
   display(config_username)
   config_password = widgets.Password(description='Password:', value='***',
                                   layout=widgets.Layout(width="600px"))
   display(config_password)
   

8. Execute the cell.

9. Use to copy and paste the password from the Experience Platform Query Expiring Credentials panel to the Password field in Jupyter Notebook.

   

10. In a new cell, enter the statements to load the SQL extension, the required library and connect with Customer Journey Analytics.

    %load_ext sql
    from sqlalchemy import create_engine
    %sql postgresql://{config_username.value}:{config_password.value}@{config_host.value}:{config_port.value}/{config_db.value}?sslmode=require
    

    Execute the shell. You should see no output but the cell should execute without any warning.

    

11. In a new call, enter the statements to get a list of available data views based on the connection.

    %%sql
    SELECT n.nspname as "Schema",
       c.relname as "Name",
       CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
       pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
    FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('v','')
       AND n.nspname <> 'pg_catalog'
       AND n.nspname !~ '^pg_toast'
       AND n.nspname <> 'information_schema'
       AND pg_catalog.pg_table_is_visible(c.oid)
       AND c.relname NOT LIKE '%test%'
       AND c.relname NOT LIKE '%ajo%'
    ORDER BY 1,2;
    

    Execute the shell. You should see output simular to the screenshot below.

    

    You should see the cc_data_view in the list of data views.

To FLATTEN or not

Jupyter Notebook supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.

FLATTEN parameter	Example	Supported	Remarks
None	prod:cja
?FLATTEN	prod:cja?FLATTEN
%3FFLATTEN	prod:cja%3FFLATTEN		Recommended option to use. Note, %3FFLATTEN is URL-encoded version of ?FLATTEN.

More information

• Prerequisites

• Credentials guide

1. Access the required credentials and parameters from the Experience Platform Query Service UI.

   1. Navigate to your Experience Platform sandbox.

   2. Select Queries from the left rail.

   3. Select Credentials tab in the Queries interface.

   4. Select prod:cja from the Database dropdown menu.

      

2. Start RStudio.

3. Create a new R Markdown file, or download this example R markdown file.

4. In your first chunk, enter the following statements between ```{r} and ```. Use to copy and paste values from the Experience Platform Query Expiring Credentials panel to the values required for the various parameters, like host, dbname, and user. For example:

   library(rstudioapi)
   library(DBI)
   library(dplyr)
   library(tidyr)
   library(RPostgres)
   library(ggplot2)
   
   host <- rstudioapi::showPrompt(title = "Host", message = "Host", default = "orangestagingco.platform-query-stage.adobe.io")
   dbname <- rstudioapi::showPrompt(title = "Database", message = "Database", default = "prod:cja?FLATTEN")
   user <- rstudioapi::showPrompt(title = "Username", message = "Username", default = "EC582F955C8A79F70A49420E@AdobeOrg")
   password <- rstudioapi::askForPassword(prompt = "Password")
   

5. Run the chunk. You are prompted for Host, Database, and User. Simply accept the values you have provided as part of the previous step.

6. Use to copy and paste the password from the Experience Platform Query Expiring Credentials panel to the Password dialog prompt in RStudio.

   

7. Create a new chunk and enter the following statements between ``` {r} and ```.

   con <- dbConnect(
      RPostgres::Postgres(),
      host = host,
      port = 80,
      dbname = dbname,
      user = user,
      password = password,
      sslmode = 'require'
   )
   

8. Run the chunk. You should see no output if the connection is successful.

9. Create a new chunk and enter the following statements between ``` {r} and ```.

   views <- dbListTables(con)
   print(views)
   

10. Run the chunk. You should see character(0) as the only output.

11. Create a new chunk and enter the following statements between ``` {r} and ```.

    glimpse(dv)
    

12. Run the chunk. You should see output simular to the screenshot below.

    

To FLATTEN or not

RStudio supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.

FLATTEN parameter	Example	Supported	Remarks
None	prod:cja
?FLATTEN	prod:cja?FLATTEN		Recommended option to use.
%3FFLATTEN	prod:cja%3FFLATTEN

More information

• Prerequisites

• Credentials guide

1. In the Data pane:

   1. Select daterangeday.
   2. Select ∑ occurrences.

   You see a table displaying the occurrences for the current month. For better visibility, enlarge the visualization.

2. In the Filters pane:

   1. Select the daterangeday 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. You can use the calendar icon to pick and select dates.
   4. Select Apply filter.

   You see the table updated with the applied daterangeday filter.

3. In the Visualizations pane, select the Line chart visualization.

   A line chart visualization replaces the table while using the same data as the table. Your Power BI Desktop should look like below.

   

4. On the Line chart visualization:

   1. Select .
   2. From the context menu, select Show as a table.

   The main view is updated to show both a line visualization and a table. Your Power BI Desktop should look like below.

   

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

   1. Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.

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

   3. In the Filter [Daterange] dialog, select Range of dates and specify a period of 01/01/2023 - 01/02/2023.

      

   4. Drag and drop Daterangeday from the Tables list in the Data pane and drop the entry in the field next to Columns.

      • Select Day from the Daterangeday dropdown menu, so that the value is updated to DAY(Daterangeday).

   5. Drag and drop Occurrences from the Tables (Measure Names) list in the Data pane and drop the entry in the field next to Rows. The value is automatically converted to SUM(Occurrences).

   6. Modify Standard to Entire View from the Fit dropdown menu in the toolbar.

      Your Tableau Desktop should look like below.

      

2. Select Duplicate from the Sheet 1 tab context menu to create a second sheet.

3. Select Rename from the Sheet 1 tab context menu to rename the sheet to Graph.

4. Select Rename from the Sheet 1 (2) tab context menu to rename the sheet to Data.

5. Ensure that the Data sheet is selected. In the Data view:

   1. Select Show me at the top right and select Text table (upper left top visualization) to modify the content of the Data view to a table.

   2. Select Swap Rows and Columns from the toolbar.

   3. Modify Standard to Entire View from the Fit dropdown menu in the toolbar.

      Your Tableau Desktop should look like below.

      

6. Select the New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:

   1. Drag and drop the Graph sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.

   2. Drag and drop the Data sheet from the Sheets shelf below the Graph sheet onto the Dashboard 1 view.

   3. Select the Data sheet in the view and modify Entire View to Fix Width.

      Your Tableau Desktop should look like below.

      

1. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
2. Select + Filter underneath Filters.
3. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
4. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2023/02/01.
5. From the Cc Data View section in the left rail,
   1. Select ‣ Daterange Date, then Date from the list of DIMENSIONS.
   2. Select Count underneath MEASURES in the left rail (at the bottom).
6. Select Run.
7. Select ‣ Visualization to display the line visualization.

You should see a visualization and table similar as shown below.

1. Enter the following statements in a new cell.

   import seaborn as sns
   import matplotlib.pyplot as plt
   data = %sql SELECT daterangeday AS Date, COUNT(*) AS Events \
             FROM cc_data_view \
             WHERE daterange BETWEEN '2023-01-01' AND '2023-02-01' \
             GROUP BY 1 \
             ORDER BY Date ASC
   df = data.DataFrame()
   df = df.groupby('Date', as_index=False).sum()
   plt.figure(figsize=(15, 3))
   sns.lineplot(x='Date', y='Events', data=df)
   plt.show()
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk.

   ## Daily Events
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2023-02-01") %>%
      group_by(daterangeday) %>%
      count() %>%
      arrange(daterangeday, .by_group = FALSE)
   ggplot(df, aes(x = daterangeday, y = n)) +
      geom_line(color = "#69b3a2") +
      ylab("Events") +
      xlab("Date")
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

Power BI does not understand how to handle date-time fields, so dimensions like daterangehour and daterangeminute are not supported.

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

   1. Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.

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

   3. In the Filter [Daterange] dialog, select Range of dates and specify a period of 01/01/2023 - 02/01/2023.

      

   4. Drag and drop Daterangehour from the Tables list in the Data pane and drop the entry in the field next to Columns.

      • Select More > Hours from the Daterangeday dropdown menu, so that the value is updated to HOUR(Daterangeday).

   5. Drag and drop Occurrences from the Tables (Measure Names) list in the Data pane and drop the entry in the field next to Rows. The value is automatically converted to SUM(Occurrences).

   6. Modify Standard to Entire View from the Fit dropdown menu in the toolbar.

      Your Tableau Desktop should look like below.

      

2. Select Duplicate from the Sheet 1 tab context menu to create a second sheet.

3. Select Rename from the Sheet 1 tab context menu to rename the sheet to Graph.

4. Select Rename from the Sheet 1 (2) tab context menu to rename the sheet to Data.

5. Ensure that the Data sheet is selected. In the Data view:

   1. Select Show me at the top right and select Text table (upper left top visualization) to modify the content of the Data view to a table.

   2. Drag HOUR(Daterangeday) from Columns to Rows.

   3. Modify Standard to Entire View from the Fit dropdown menu in the toolbar.

      Your Tableau Desktop should look like below.

      

6. Select New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:

   1. Drag and drop the Graph sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.

   2. Drag and drop the Data sheet from the Sheets shelf below the Graph sheet onto the Dashboard 1 view.

   3. Select the Data sheet in the view and modify Entire View to Fix Width.

      Your Dashboard 1 view should look like below.

      

1. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
2. Select + Filter underneath Filters.
3. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
4. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2023/01/02.
5. From the Cc Data View section in the left rail,
   1. Select ‣ Daterangehour Date, then Time from the list of DIMENSIONS.
   2. Select Count underneath MEASURES in the left rail (at the bottom).
6. Select Run.
7. Select ‣ Visualization to display the line visualization.

You should see a visualization and table similar as shown below.

1. Enter the following statements in a new cell.

   import seaborn as sns
   import matplotlib.pyplot as plt
   data = %sql SELECT daterangehour AS Hour, COUNT(*) AS Events \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2023-01-02' \
               GROUP BY 1 \
                ORDER BY Hour ASC
   df = data.DataFrame()
   df = df.groupby('Hour', as_index=False).sum()
   plt.figure(figsize=(15, 3))
   sns.lineplot(x='Hour', y='Events', data=df)
   plt.show()
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk.

   ## Hourly Events
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2023-01-02") %>%
      group_by(daterangehour) %>%
      count() %>%
      arrange(daterangehour, .by_group = FALSE)
   ggplot(df, aes(x = daterangehour, y = n)) +
      geom_line(color = "#69b3a2") +
      ylab("Events") +
      xlab("Hour")
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

1. In the Data pane:

   1. Select daterangemonth.
   2. Select ∑ occurrences.

   You see a table displaying the occurrences for the current month. For better visibility, enlarge the visualization.

2. In the Filters pane:

   1. Select the daterangemonth 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 1/1/2024. You can use the calendar icon to pick and select dates.
   4. Select Apply filter.

   You see the table updated with the applied daterangemonth filter.

3. In the Visualizations pane:

   1. Select the Line chart visualization.

   A line chart visualization replaces the table while using the same data as the table. Your Power BI Desktop should look like below.

   

4. On the Line chart visualization:

   1. Select .
   2. From the context menu, select Show as a table.

   The main view is updated to show both a line visualization and a table. Your Power BI Desktop should look like below.

   

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

   1. Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.

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

   3. In the Filter [Daterange] dialog, select Range of dates and specify a period of 01/01/2023 - 01/01/2024.

      

   4. Drag and drop Daterangeday from the Tables list in the Data pane and drop the entry in the field next to Columns.

      • Select MONTH from the Daterangeday dropdown menu, so that the value is updated to MONTH(Daterangeday).

   5. Drag and drop Occurrences from the Tables (Measure Names) list in the Data pane and drop the entry in the field next to Rows. The value is automatically converted to SUM(Occurrences).

   6. Modify Standard to Entire View from the Fit dropdown menu in the toolbar.

      Your Tableau Desktop should look like below.

      

2. Select Duplicate from the Sheet 1 tab context menu to create a second sheet.

3. Select Rename from the Sheet 1 tab context menu to rename the sheet to Graph.

4. Select Rename from the Sheet 1 (2) tab context menu to rename the sheet to Data.

5. Ensure that the Data sheet is selected. In the Data view:

   1. Select Show me at the top right and select Text table (upper left top visualization) to modify the content of the Data view to a table.

   2. Drag MONTH(Daterangeday) from Columns to Rows.

   3. Modify Standard to Entire View from the Fit dropdown menu in the toolbar.

      Your Tableau Desktop should look like below.

      

6. Select New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:

   1. Drag and drop the Graph sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.

   2. Drag and drop the Data sheet from the Sheets shelf below the Graph sheet onto the Dashboard 1 view.

   3. Select the Data sheet in the view and modify Entire View to Fix Width.

      Your Tableau Desktop should look like below.

      

1. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
2. Select + Filter underneath Filters.
3. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
4. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2024/01/01.
5. From the left Cc Data View rail,
   1. Select ‣ Daterangemonth Date, then Month from the list of DIMENSIONS.
   2. Select Count underneath MEASURES in the left rail (at the bottom).
6. Select Run.
7. Select ‣ Visualization to display the line visualization.

You should see a visualization and table similar as shown below.

1. Enter the following statements in a new cell.

   import seaborn as sns
   import matplotlib.pyplot as plt
   data = %sql SELECT daterangemonth AS Month, COUNT(*) AS Events \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2024-01-01' \
               GROUP BY 1 \
               ORDER BY Month ASC
   df = data.DataFrame()
   df = df.groupby('Month', as_index=False).sum()
   plt.figure(figsize=(15, 3))
   sns.lineplot(x='Month', y='Events', data=df)
   plt.show()
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk.

   ## Hourly Events
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2023-01-02") %>%
      group_by(daterangehour) %>%
      count() %>%
      arrange(daterangehour, .by_group = FALSE)
   ggplot(df, aes(x = daterangehour, y = n)) +
      geom_line(color = "#69b3a2") +
      ylab("Events") +
      xlab("Hour")
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

1. In the Data pane:

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

   You see an empty table displaying only the column headers for the selected element. For better visibility, enlarge the visualization.

2. In the Filters pane:

   1. Select the 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.

   You see the table updated with the applied daterange filter.

3. In the Visualization pane:

   1. Use to remove daterange from Columns.
   2. Drag and drop Sum of purchases_revenue underneath Sum of purchases in Columns.

4. On the Table visualization:

   1. Select Sum of purchase_revenue to sort the product names in descending purchase revenue order. Your Power BI Desktop should look like below.
   

5. In the Filters pane:

   1. Select product_name is (All).
   2. Set Filter type to Top N.
   3. Define the filter to Show items Top 10 By value.
   4. Drag and drop purchase_revenue into By value Add data fields here.
   5. Select Apply filter.

   You see the table updated with values for purchase revenue in sync with the Freeform table visualization in Analysis Workspace.

6. In the Visualizations pane:

   1. Select the Line and stacked column chart visualization.

   A line and stacked column chart visualization replaces the table while using the same data as the table.

7. Drag and drop purchases onto Line y-axis in the Visualizations pane.

   The line and stacked column chart is updated. Your Power BI Desktop should look like below.

   

8. On the Line and stacked column chart visualization:

   1. Select .
   2. From the context menu, select Show as a table.

   The main view is updated to show both a line visualization and a table.

   

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

   1. Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.

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

   3. In the Filter [Daterange] dialog, select Range of dates and specify a period of 01/01/2023 - 31/12/2023. Select Apply and OK.

      

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

   5. Drag and drop Purchases from the Tables (Measure Names) list in the Data pane and drop the entry in the field next to Rows. The value is automatically converted to SUM(Purchases).

   6. Drag and drop Purchase Revenue from the Tables (Measure Names) list in the Data pane and drop the entry in the field next to Columns and left from SUM(Purchases). The value is automatically converted to SUM(Purchase Revenue).

   7. To order both charts in descending purchase revenue order, hover over the Purchase Revenue title and select the sort icon.

   8. To limit the number of entries in the charts, select SUM(Purchase Revenue) in Rows and from the dropdown menu select Filter.

   9. In the Filter [Purchase Revenue] dialog select Range of values and enter appropriate values. For example: 1,000,000 - 2,000,000. Select Apply and OK.

   10. To convert the two bar charts to a dual combination chart, select SUM(Purchases) in Rows and from the dropdown menu, select Dual Axis. The bar charts transform into a scatter plot.

   11. To modify the scatter plot to a bar chart:

       1. Select SUM(Purchases) in the Marks area and select Line from the dropdown menu.
       2. Select SUM(Purchase Revenue) in the Marks area and select Bar from the dropdown menu.

   Your Tableau Desktop should look like below.

   

2. Select Duplicate from the Sheet 1 tab context menu to create a second sheet.

3. Select Rename from the Sheet 1 tab context menu to rename the sheet to Data.

4. Select Rename from the Sheet 1 (2) tab context menu to rename the sheet to Graph.

5. Ensure that the Data sheet is selected.

   1. Select Show me at the top right and select Text table (upper left top visualization) to modify the content of the two charts to a table.
   2. To order purchase revenue in descending order, hover over Purchase Revenue in the table and select .
   3. Select Entire View from the Fit dropdown menu.

   Your Tableau Desktop should look like below.

   

6. Select New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:

   1. Drag and drop the Graph sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.
   2. Drag and drop the Data sheet from the Sheets shelf below the Graph sheet onto the Dashboard 1 view.
   3. Select the Data sheet in the view and modify Entire View to Fix Width.

   Your Dashboard 1 view should look like below.

   

1. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
2. Select + Filter underneath Filters.
3. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
4. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2024/01/01.
5. From the ‣ Cc Data View section in the left rail, select Product Name.
6. From the ‣ Custom Fields section in the left rail:
   1. Select Custom Measure from the + Add dropdown menu.
   2. In the Create custom measure dialog:
      1. Select Purchase Revenue from the Field to measure dropdown menu.
      2. Select Sum from the Measure type dropdown menu.
      3. Enter a custom field name for Name. For example: Purchase Revenue.
      4. Select the Field details tab.
      5. Select Decimals from the Format dropdown menu and ensure 0 is entered in Decimals.
         
      6. Select Save.
   3. Select Custom Measure once more from the + Add dropdown menu. In the Create custom measure dialog:
      1. Select Purchases from the Field to measure dropdown menu.
      2. Select Sum from the Measure type dropdown menu.
      3. Enter a custom field name for Name. For example: Sum of Purchases.
      4. Select the Field details tab.
      5. Select Decimals from the Format dropdown menu and ensure 0 is entered in Decimals.
      6. Select Save.
   4. Both fields are automatically added to the Data view.
7. Select + Filter to add another Filters and to limit the data.
8. In the Add Filter dialog, select ‣ Custom Fields, then Purchase Revenue.
9. Make the appropriate selections and enter the proposed values, so the filter reads is between inclusive 1000000 AND 2000000.
10. Select Run.
11. Select ‣ Visualization to display the line visualization.
12. Select Edit in Visualization to update the visualization. In the popup dialog:
    1. Select the Series tab.
    2. Scroll down to see Purchases and change the Type to Line.
    3. Select the Y tab.
    4. Drag Purchases from the **Left 1 ** container to where it reads Drag series here to create a new left axis. This action creates a Left 2 container.
       
    5. Select next to Edit to hide the popup dialog

You should see a visualization and table similar as shown below.

1. Enter the following statements in a new cell.

   import seaborn as sns
   import matplotlib.pyplot as plt
   data = %sql SELECT product_name AS `Product Name`, SUM(purchase_revenue) AS `Purchase Revenue`, SUM(purchases) AS `Purchases` \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2024-01-01' \
               GROUP BY 1 \
               LIMIT 10;
   df = data.DataFrame()
   df = df.groupby('Product Name', as_index=False).sum()
   plt.figure(figsize=(15, 3))
   sns.barplot(x='Purchase Revenue', y='Product Name', data=df)
   plt.show()
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk.

   library(tidyr)
   
   ## Single dimension ranked
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2024-01-01") %>%
      group_by(product_name) %>%
      summarise(purchase_revenue = sum(purchase_revenue), purchases = sum(purchases)) %>%
      arrange(product_name, .by_group = FALSE)
   dfV <- df %>%
      head(5)
   ggplot(dfV, aes(x = purchase_revenue, y = product_name)) +
      geom_col(position = "dodge") +
      geom_text(aes(label = purchase_revenue), vjust = -0.5)
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

1. To ensure the date range apply to all visualizations, drag and drop daterangeday from the Data pane on to Filters on this page.

   1. Select the daterangeday is (All) from Filters on this page.
   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.

2. In the Data pane:

   1. Select datarangeday.
   2. Select product_category.
   3. Select product_name.
   4. Select ∑ purchase_revenue
   5. Select ∑ purchases

3. To modify the vertical bar chart to a Table, ensure you have the table selected and select Matrix from the Visualizations pane.

   • Drag product_name from Columns and drop the field underneath product_category in Rows in the Visualization pane.

4. To limit the number of displayed products within the table, select product_name is (All) in the Filters pane.

   1. Select Advanced filtering.
   2. Select Filter type Top N Show items Top 15 By Value.
   3. Drag purchases from the Data pane onto the Add data fields here.
   4. Select Apply filter.

5. To improve readability, select View from the top menu, and select Page View > Actual size and resize the table visualization.

6. To break down each category in the table, select + at the product category level. Your Power BI Desktop should look like below.

   

7. Select Home from the top menu, and select New visual. A new visual is added to your report.

8. In the Data pane:

   1. Select product_category.
   2. Select product_name.
   3. Select purchase_revenue.

9. To modify the visual, select the bar chart and select Treemap from the Visualizations pane.

10. Ensure that product_category is listed underneath Category, and product_name is listed underneath Details in the Visualizations pane.

    Your Power BI Desktop should look like below.

    

11. Select Home from the top menu, and select New visual. A new visual is added to your report.

12. In the Data pane:

    1. Select product_category.
    2. Select purchase_revenue.
    3. Select purchase.

13. In the Visualizations pane:

    1. To modify the visualization, select Line and stacked column chart.
    2. Drag sum_of_purchases from Column y-axis to Line y-axis.

14. In the report, reshuffle the individual visualizations.

    Your Power BI Desktop should look like below.

    

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

   1. Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.

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

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

      Your Tableau Desktop should look like below.

      

   4. Drag Product Category and drop next to Columns.

   5. Drag Purchase Revenue and drop next to Rows. The value changes to SUM(Purchase Revenue).

   6. Drag Purchases and drop next to Rows. The value changes to SUM(Purchases).

   7. Select SUM(Purchases) and from the dropdown menu select Dual Axis.

   8. Select SUM(Purchases) in Marks and select Line from the dropdown menu.

   9. Select SUM(Purchase Revenue) in Marks and select Bar from the dropdown menu.

   10. Select Entire View from the Fit menu.

   11. Select the Purchase Revenue title in the chart and ensure that the purchase revenue is in ascending order.

       Your Tableau Desktop should look like below.

       

2. Rename the current Sheet 1 sheet to Category.

3. Select New Worksheet to create a new sheet, and rename it to Data.

   1. Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.

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

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

   4. Drag Purchase Revenue from Data pane to Columns. The value changes to SUM(Purchase Revenue).

   5. Drag Purchase from Data pane to Columns, next to Purchase Revenue. The value changes to SUM(Purchases).

   6. Drag Product Category from the Data pane to Rows.

   7. Drag Product Name from the Data pane to Rows, next to Product Category.

   8. To change the two horizontal bars to a table, select Text Table from Show Me.

   9. To limit the number of products, select Purchases in Measure Values. From the dropdown menu, select Filter.

   10. In the Filter [Purchases] dialog, select At least and enter 7000. Select Apply and OK.

   11. Select Fit Width from the Fit dropdown menu.

       Your Tableau Desktop should look like below.

       

4. Select New worksheet to create a new sheet and rename it to Treemap.

   1. Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.

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

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

   4. Drag Purchase Revenue from the Data pane to Rows. The values changes to SUM(Purchase Revenue).

   5. Drag Purchase from the Data pane to Rows, next to Purchase Revenue. The value changes to SUM(Purchases).

   6. Drag Product Category from the Data pane to Columns.

   7. Drag Product Name from the Data pane to Columns.

   8. To change the two vertical bar charts to a treemap, select Treemap from Show Me.

   9. To limit the number of products, select Purchases in Measure Values. From the dropdown menu, select Filter.

   10. In the Filter [Purchases] dialog, select At least and enter 7000. Select Apply and OK.

   11. Select Fit Width from the Fit dropdown menu.

       Your Tableau Desktop should look like below.

       

5. Select New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:

   1. Drag and drop the Category sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.
   2. Drag and drop the Treemap sheet from the Sheets shelf underneath the Category sheet on the Dashboard 1 view.
   3. Drag and drop the Data sheet from the Sheets shelf underneath the Treemap sheet on the Dashboard 1 view.
   4. Resize each of the sheets in the view.

   Your Dashboard 1 view should look like below.

   

1. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
2. Select + Filter underneath Filters.
3. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
4. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2024/01/01.
5. From the ‣ Cc Data View section in the left rail:
   1. Select Product Category.
   2. Select Product Name.
6. From the ‣ Custom Fields section in the left rail:
   1. Select Custom Measure from the + Add dropdown menu.
   2. In the Create custom measure dialog:
      1. Select Purchase Revenue from the Field to measure dropdown menu.
      2. Select Sum from the Measure type dropdown menu.
      3. Enter a custom field name for Name. For example: Sum of Purchase Revenue.
      4. Select the Field details tab.
      5. Select Decimals from the Format dropdown menu and ensure 0 is entered in Decimals.
         
      6. Select Save.
   3. Select Custom Measure once more from the + Add dropdown menu. In the Create custom measure dialog:
      1. Select Purchases from the Field to measure dropdown menu.
      2. Select Sum from the Measure type dropdown menu.
      3. Enter a custom field name for Name. For example: Sum of Purchases.
      4. Select the Field details tab.
      5. Select Decimals from the Format dropdown menu and ensure 0 is entered in Decimals.
      6. Select Save.
   4. Both fields are automatically added to the Data view.
7. In the Filters section, select + Filter. In the Add Filter dialog. Select ‣ Custom Fields, then Purchase Revenue.
8. Select is > and enter 800000 to limit the results.
9. Select Run.
10. Select ‣ Visualization to display the line visualization.
11. Select Edit in Visualization to update the visualization. In the popup dialog:

    1. Select the Plot tab.

    2. Scroll down and select Edit Chart Config.

    3. Modify the JSON in Chart Config (Override) like in the screenshot below, and then select Preview.

       

    4. Select Apply.

    5. Select next to Edit to hide the popup dialog

You should see a visualization and table similar as shown below.

1. Enter the following statements in a new cell.

   import seaborn as sns
   import matplotlib.pyplot as plt
   data = %sql SELECT product_category AS `Product Category`, product_name AS `Product Name`, SUM(purchase_revenue) AS `Purchase Revenue`, SUM(purchases) AS `Purchases` \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2024-01-01' \
               GROUP BY 1, 2 \
               ORDER BY `Purchase Revenue` DESC \
               LIMIT 10;
   df = data.DataFrame()
   df = df.groupby(['Product Category', 'Product Name'], as_index=False).sum()
   plt.figure(figsize=(8, 8))
   sns.scatterplot(x='Product Category', y='Product Name', size='Purchase Revenue', sizes=(10, 200), hue='Purchases', palette='husl', data=df)
   plt.show()
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk.

   ## Multiple dimensions ranked
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2024-01-01") %>%
      group_by(product_category, product_name) %>%
      summarise(purchase_revenue = sum(purchase_revenue), purchases = sum(purchases), .groups = "keep") %>%
      arrange(desc(purchase_revenue), .by_group = FALSE)
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

1. To ensure the date range apply to all visualizations, drag and drop daterangeday from the Data pane on to Filters on this page.

   1. Select the daterangeday is (All) from Filters on this page.
   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.
   4. Select Apply filter.

2. In the Data pane:

   1. Select datarangeday.
   2. Select ∑ cm_product_name_count_distinct, which is the calculated metric defined in Customer Journey Analytics.

3. To modify the vertical bar chart to a Table, ensure you have the chart selected and select Table from the Visualizations pane.

   Your Power BI Desktop should look like below.

   

4. Select the table visualization. From the context menu, select Copy > Copy visual.

5. Paste the visualization using ctrl-v. The exact copy of the visualization overlaps the original one. Move it to the right in the report area.

6. To modify the copied visualization from a table to a card, select Card from Visualizations.

   Your Power BI Desktop should look like below.

   

Alternatively, you can use the count distinct functionality from Power BI.

1. Select the product_name dimension.

2. Apply the Count (Distinct) function on the product_name dimension in Columns.

   

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

   1. Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto 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 - 31/1/2023. Select Apply and OK.

   4. Drag Cm Product Name Count Distinct to Rows. The value changes to SUM(Cm Product Name Count Distinct). This field is the calculated metric that you have defined in Customer Journey Analytics.

   5. Drag Daterangeday and drop next to Columns. Select Daterangeday and from the dropdown menu select Day.

   6. To modify the lines visualization to a table, select Text Table from Show Me.

   7. Select Swap Rows and Columns from the toolbar.

   8. Select Fit Width from the Fit dropdown menu.

      Your Tableau Desktop should look like below.

      

2. Select Duplicate from the Sheet 1 tab context menu to create a second sheet.

3. Select Rename from the Sheet 1 tab context menu to rename the sheet to Data.

4. Select Rename from the Sheet 1 (2) tab context menu to rename the sheet to Card.

5. Ensure you have selected the Card view.

6. Select DAY(Daterangeday) and from the dropdown menu select Month. The value changes to MONTH(Daterangeday).

7. Select SUM(Cm Product Name Count Distinct) in Marks and from the dropdown menu select Format.

8. To change the font size, in the Format SUM(CM Product Name Count Distinct) pane, select Font within Default and select 72 for the font size.

9. To align the number, select Automatic next to Alignment and set Horizontal to centered.

10. To use whole numbers, select 123.456 next to Numbers and select Number (Custom). Set Decimal places to 0.

    Your Tableau Desktop should look like below.

    

11. Select New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:

    1. Drag and drop the Card sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.
    2. Drag and drop the Data sheet from the Sheets shelf underneath the Card sheet on the Dashboard 1 view.

    Your Dashboard 1 view should look like below.

    

Alternatively, you can use the count distinct functionality from Tableau Desktop.

1. Use Product Name instead of Cm Product Name Count Distinct.

2. Apply Measure > Count (Distinct) on Product Name in Marks.

   

1. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
2. Select + Filter underneath Filters.
3. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
4. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2023/02/01.
5. From the ‣ Cc Data View section in the left rail:
   1. Select Daterange Date, then Date.
   2. Select Aggregate ‣ Count Distinct from the ⋮ More context menu on Product Name.
      
6. Select Run.
7. Select ‣ Visualization and select 6︎⃣ from the toolbar to display a Single value visualization.

You should see a visualization and table similar as shown below.

1. Enter the following statements in a new cell.

   data = %sql SELECT COUNT(DISTINCT(product_name)) AS `Product Name` \
      FROM cc_data_view \
      WHERE daterange BETWEEN '2023-01-01' AND '2023-02-01';
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk.

   ## Count Distinct
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2023-02-01") %>%
      summarise(product_name_count_distinct = n_distinct(product_name))
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

1. In the Data pane:

   1. Select daterangemonth.
   2. Select daterangeName.
   3. Select ∑ occurrences.

   You see a visualization displaying Error fetching data for this visual.

2. In the Filters pane:

   1. Select the daterangeName is (All) from Filters on this visual.
   2. Select Basic filtering as the Filter type.
   3. Underneath the Search field, select Last Year 2023, which is the name of your date range defined in Customer Journey Analytics.
   4. Select to remove daterangeName from Columns.

   You see the table updated with the applied daterangeName filter. Your Power BI Desktop should look like below.

   

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

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

   2. In the Filter [Daterange Name] dialog ensure Select from list is selected, and select Last Year 2023 from the list. Select Apply and OK.

   3. Drag Daterangemonth entry from the Tables list and drop the entry in the field next to Rows. Select Daterangemonth and select Month. The value changes to MONTH(Daterangemonth).

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

   5. Select Text Table from Show Me.

   6. Select Swap Rows and Columns from the toolbar.

   7. Select Fit Width from the Fit dropdown menu.

      Your Tableau Desktop should look like below.

      

1. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
2. Select + Filter underneath Filters.
3. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Name.
4. Specify the Cc Data View Daterange Name filter as is and select Last Year 2023 from the list of values.
5. From the ‣ Cc Data View section in the left rail:
   1. Select Daterange Month, then Month.
   2. Select Count underneath MEASURES in the left rail (at the bottom).
6. Select Run.
7. Select ‣ Visualization.

You should see a visualization and table similar as shown below.

1. Enter the following statements in a new cell.

   data = %sql SELECT daterangeName FROM cc_data_view;
   style = {'description_width': 'initial'}
   daterange_name = widgets.Dropdown(
      options=[d for d, in data],
      description='Date Range Name:',
      style=style
   )
   display(daterange_name)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

3. Select Fishing Products from the dropdown menu.

4. Enter the following statements in a new cell.

   import seaborn as sns
   import matplotlib.pyplot as plt
   data = %sql SELECT daterangemonth AS Month, COUNT(*) AS Events \
               FROM cc_data_view \
               WHERE daterangeName = '{daterange_name.value}' \
               GROUP BY 1 \
               ORDER BY Month ASC
   df = data.DataFrame()
   df = df.groupby('Month', as_index=False).sum()
   plt.figure(figsize=(15, 3))
   sns.lineplot(x='Month', y='Events', data=df)
   plt.show()
   display(data)
   

5. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk. Ensure you use the appropriate date range name. For example, Last Year 2023.

   ## Monthly Events for Last Year
   df <- dv %>%
      filter(daterangeName == "Last Year 2023") %>%
      group_by(daterangemonth) %>%
      count() %>%
      arrange(daterangemonth, .by_group = FALSE)
   ggplot(df, aes(x = daterangemonth, y = n)) +
      geom_line(color = "#69b3a2") +
      ylab("Events") +
      xlab("Hour")
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

1. In the Data pane:
   1. Select daterange.
   2. Select filterName.
   3. Select product_name.
   4. Select ∑ occurrences.

You see a visualization displaying Error fetching data for this visual.

1. In the Filters pane:

   1. Select filterName is (All) from Filters on this visual.
   2. Select Basic filtering as the Filter type.
   3. Underneath the Search field, select Fishing Products, which is the name of the existing filter defined in Customer Journey Analytics.
   4. Select daterange is (All) from Filters on this visual.
   5. Select Advanced filtering as the Filter type.
   6. Define the filter to Show items when the value is on or after 1/1/2023 And is before 2/1/2023.
   7. Select to remove filterName from Columns.
   8. Select to remove daterange from Columns.

   You see the table updated with the applied filterName filter. Your Power BI Desktop should look like below.

   

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

   1. Drag the Filter Name entry from the Tables list in the Filters shelf.

   2. In the Filter [Filter Name] dialog ensure Select from list is selected, and select Fishing Products from the list. Select Apply and OK.

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

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

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

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

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

   8. Select Text Table from Show Me.

   9. Select Fit Width from the Fit dropdown menu.

      Your Tableau Desktop should look like below.

      

1. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
2. Select + Filter underneath Filters.
3. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
4. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2023/02/01.
5. Select + Filter underneath Filters to add another filter.
6. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Filter name.
7. Ensure is the selection for the filter.
8. Select Fishing Products from the list of possible values.
9. From the ‣ Cc Data View section in the left rail:
   1. Select Product Name.
   2. Select Count underneath MEASURES in the left rail (at the bottom).
10. Select Run.
11. Select ‣ Visualization.

You should see a visualization and table similar as shown below.

1. Enter the following statements in a new cell.

   data = %sql SELECT filterName FROM cc_data_view;
   style = {'description_width': 'initial'}
   filter_name = widgets.Dropdown(
      options=[d for d, in data],
      description='Filter Name:',
      style=style
   )
   display(filter_name)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

3. Select Fishing Products from the dropdown menu.

4. Enter the following statements in a new cell.

   import seaborn as sns
   import matplotlib.pyplot as plt
   data = %sql SELECT product_name AS `Product Name`, COUNT(*) AS Events \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2023-02-01' \
                  AND filterName = '{filter_name.value}' \
               GROUP BY 1 \
               LIMIT 10;
   df = data.DataFrame()
   df = df.groupby('Product Name', as_index=False).sum()
   plt.figure(figsize=(15, 3))
   sns.barplot(x='Events', y='Product Name', data=df)
   plt.show()
   display(data)
   

5. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk. Ensure you use the appropriate filter name. For example, Fishing Products.

   ## Dimension filtered by name
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2023-02-01" & filterName == "Fishing Products") %>%
      group_by(product_name) %>%
      count() %>%
      arrange(desc(n), .by_group = FALSE)
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

1. Select Home from the menu, then select Refresh from the toolbar. You need to refresh the connection to pick up the new filter you just defined in Customer Journey Analytics.

2. In the Data pane:

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

You see a visualization displaying Error fetching data for this visual.

1. In the Filters pane:

   1. Select filterName is (All) from Filters on this visual.
   2. Select Basic filtering as the Filter type.
   3. Select daterange is (All) from Filters on this visual.
   4. Select Advanced filtering as the Filter type.
   5. Define the filter to Show items when the value is on or after 1/1/2023 And is before 2/1/2023.
   6. Select Basic filter as the Filter type for product_category, and select Hunting from the list of possible values.
   7. Select to remove filterName from Columns.
   8. Select to remove daterange from Columns.

   You see the table updated with the applied product_category filter. Your Power BI Desktop should look like below.

   

Tableau Desktop does not support fetching the dynamic list of product categories from Customer Journey Analytics. Instead, this use case uses the newly created filter for Hunting Products and use the filter name critetia.

1. In the Data Source view, underneath Data, from the context menu on cc_data_view(prod:cja%3FFLATTEN), select Refresh. You need to refresh the connection to pick up the new filter you just defined in Customer Journey Analytics.

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

   1. Drag the Filter Name entry from the Tables list in the Filters shelf.

   2. In the Filter [Filter Name] dialog ensure Select from list is selected, and select Hunting Products from the list. Select Apply and OK.

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

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

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

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

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

   8. Select Text Table from Show Me.

   9. Select Fit Width from the Fit dropdown menu.

      Your Tableau Desktop should look like below.

      

1. In the 1. In the Explore interface of Looker, refresh your connection. Select Clear cache and refresh.
2. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
3. Select + Filter underneath Filters.
4. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
5. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2023/02/01.
6. Select + Filter underneath Filters to add another filter.
7. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Product Category.
8. Ensure is as the selection for the filter.

Lookes does not show the list of possible values for Product Category.

1. Enter the following statements in a new cell.

   data = %sql SELECT DISTINCT product_category FROM cc_data_view WHERE daterange BETWEEN '2023-01-01' AND '2024-01-01';
   style = {'description_width': 'initial'}
   category_filter = widgets.Dropdown(
      options=[d for d, in data],
      description='Product Category:',
      style=style
   )
   display(category_filter)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

3. Select Hunting from the dropdown menu.

4. Enter the following statements in a new cell.

   import seaborn as sns
   import matplotlib.pyplot as plt
   data = %sql SELECT product_name AS `Product Name`, COUNT(*) AS Events \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2023-02-01' \
               AND product_category = '{category_filter.value}' \
               GROUP BY 1 \
               ORDER BY Events DESC \
               LIMIT 10;
   df = data.DataFrame()
   df = df.groupby('Product Name', as_index=False).sum()
   plt.figure(figsize=(15, 3))
   sns.barplot(x='Events', y='Product Name', data=df)
   plt.show()
   display(data)
   

5. Execute the cell. You should see output similar to the screenshot below.

   

1. Enter the following statements between ```{r} and ``` in a new chunk. Ensure you use an appropriate category. For examplee, Hunting.

   ## Dimension 1 Filtered by Dimension 2 value
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2023-02-01" & product_category == "Hunting") %>%
      group_by(product_name) %>%
      count() %>%
      arrange(desc(n), .by_group = FALSE)
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

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 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.

   

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.

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

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.

      

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.

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

1. In the Explore interface of Looker, refresh your connection. Select Clear cache and refresh.
2. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
3. Select + Filter underneath Filters.
4. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
5. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2023/02/01.
6. From the ‣ Cc Data View section in the left rail, select Product Name.
7. From the ‣ Custom Fields section in the left rail:
   1. Select Custom Measure from the + Add dropdown menu.
   2. In the Create custom measure dialog:
      1. Select Purchase Revenue from the Field to measure dropdown menu.
      2. Select Sum from the Measure type dropdown menu.
      3. Enter a custom field name for Name. For example: Sum of Purchase Revenue.
      4. Select the Field details tab.
      5. Select Decimals from the Format dropdown menu and ensure 0 is entered in Decimals.
         
      6. Select Save.
8. Ensure you select ↓ (Descending, Sort Order: 1) on the Purchase Revenue column.
9. Select Run.
10. Select ‣ Visualization.

You should see a visualization and table similar as shown below.

The query generated by Looker using the BI extension is including ORDER BY, which implies that the sort is executed through Looker and the BI extension.

-- Looker Query Context '{"user_id":6,"history_slug":"fc83573987b999306eaf6e1a3f2cde70","instance_slug":"71d4667f0b76c0011463658f45c3f7a3"}'
SELECT
    cc_data_view."product_name"  AS "cc_data_view.product_name",
    COALESCE(SUM(CAST(( cc_data_view."purchase_revenue"  ) AS DOUBLE PRECISION)), 0) AS "purchase_revenue"
FROM
    "public"."cc_data_view" AS "cc_data_view"
WHERE ((( cc_data_view."daterange"  ) >= (DATE_TRUNC('day', DATE '2024-01-31')) AND ( cc_data_view."daterange"  ) < (DATE_TRUNC('day', DATE '2023-02-01'))))
GROUP BY
    1
ORDER BY
    2 DESC
FETCH NEXT 500 ROWS ONLY

1. Enter the following statements in a new cell.

   data = %sql SELECT product_name AS `Product Name`, SUM(purchase_revenue) AS `Purchase Revenue`, SUM(purchases) AS `Purchases` \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2023-02-01' \
               GROUP BY 1 \
               ORDER BY `Purchase Revenue` DESC \
               LIMIT 5;
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

The query is excuted by the BI extension as defined in Jupyter Notebook.

1. Enter the following statements between ```{r} and ``` in a new chunk.

   ## Dimension 1 Sorted
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2023-02-01") %>%
      group_by(product_name) %>%
      summarise(purchase_revenue = sum(purchase_revenue), purchases = sum(purchases), .groups = "keep") %>%
      arrange(desc(purchase_revenue), .by_group = FALSE)
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

The query generated by RStudio using the BI extension is including ORDER BY, which implies that the order is applied through RStudio and the BI extension.

SELECT
  "product_name",
  SUM("purchase_revenue") AS "purchase_revenue",
  SUM("purchases") AS "purchases"
FROM (
  SELECT "cc_data_view".*
  FROM "cc_data_view"
  WHERE ("daterange" >= '2023-01-01' AND "daterange" < '2023-02-01')
) AS "q01"
GROUP BY "product_name"
ORDER BY "purchase_revenue" DESC
LIMIT 1000

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 to remove daterange from Columns.

   Your Power BI Desktop should look like below.

   

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.

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

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.

         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.

         

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

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.

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

1. In the Explore interface of Looker, refresh your connection. Select Clear cache and refresh.
2. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.
3. Select + Filter underneath Filters.
4. In the Add Filter dialog:
   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      
5. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2024/01/01.
6. From the ‣ Cc Data View section in the left rail:
   1. Select Product Name.
   2. Select Count underneath MEASURES in the left rail (at the bottom).
7. Ensure you select ↓ (Descending, Sort Order: 1) on the Purchase Revenue column.
8. Ensure you select ↓ (Descending, Sort Order: 1) on the Purchase Revenue column.
9. Select Run.
10. Select ‣ Visualization.

You should see a visualization and table similar as shown below.

The query generated by Looker using the BI extension is including FETCH NEXT 5 ROWS ONLY, which implies that the limit is executed through Looker and the BI extension.

-- Looker Query Context '{"user_id":6,"history_slug":"a8f3b1ebd5712413ca1ae695090f70db","instance_slug":"71d4667f0b76c0011463658f45c3f7a3"}'
SELECT
    cc_data_view."product_name"  AS "cc_data_view.product_name",
    COUNT(*) AS "cc_data_view.count"
FROM
    "public"."cc_data_view" AS "cc_data_view"
WHERE ((( cc_data_view."daterange"  ) >= (DATE_TRUNC('day', DATE '2023-01-31')) AND ( cc_data_view."daterange"  ) < (DATE_TRUNC('day', DATE '2024-01-01'))))
GROUP BY
    1
ORDER BY
    2 DESC
FETCH NEXT 5 ROWS ONLY

1. Enter the following statements in a new cell.

   data = %sql SELECT product_name AS `Product Name`, COUNT(*) AS Events \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2023-02-01' \
               GROUP BY 1 \
               ORDER BY `Events` DESC \
               LIMIT 5;
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

The query is excuted by the BI extension as defined in Jupyter Notebook.

1. Enter the following statements between ```{r} and ``` in a new chunk.

   ## Dimension 1 Limited
   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange < "2024-01-01") %>%
      group_by(product_name) %>%
      count() %>%
      arrange(desc(n), .by_group = FALSE) %>%
      head(5)
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

The query generated by RStudio using the BI extension is including LIMIT 5, which implies that the limit is applied through RStudio and the BI extension.

SELECT "product_name", COUNT(*) AS "n"
FROM (
  SELECT "cc_data_view".*
  FROM "cc_data_view"
  WHERE ("daterange" >= '2023-01-01' AND "daterange" < '2024-01-01')
) AS "q01"
GROUP BY "product_name"
ORDER BY "n" DESC
LIMIT 5

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_distinct 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]).
   

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 in the table visualization.

   Your Power BI Desktop should look like below.
   

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

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

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]).
      
   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.

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

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))

The Customer Journey Analytics objects are available in the Explore interface. And are retrieved as part of setting up your connection, project, and model in Looker. For example, cc_data_view. The name of the view 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 listed as DIMENSION in the Cc Data View left rail. The dimension is defined in your Customer Journey Analytics data view. For example, dimension Product Name in Customer Journey Analytics has a DIMENSION Product Name, which is the name for the dimension in Looker.
Date range dimensions from Customer Journey Analytics, like Day, Week, Month, and more are available as Daterangeday Date, Daterangeweek Date, Daterangemonth Date, and more. When you use a date range dimension, you have to select an appropriate definition of date or time. For example, Year, Quarter, Month, Date.

Metrics
Metrics from Customer Journey Analytics are listed as DIMENSION in in the Cc Data View left rail. For example, metric Purchase Revenue in Customer Journey Analytics has a DIMENSION Purchase Revenue. To actually use as a metric, create a custom measure field as shown in the examples above, or use the shortcut on a dimension. For example, ⋮, select Aggregate, and then select Sum…

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 Looker, 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 Looker.

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
Looker provides custom transformation functionality using custom field builders, as shown above. As an example, you want to execute the Single dimension ranked use case with product names in lower case.

1. From the ‣ Custom Fields section in the left rail:
   1. Select Custom Dimension from the + Add dropdown menu.
   2. Enter lower(${cc_data_view.product_name}) in the Expression text area. You are assisted with the correct syntax when you start to type Product Name.
      
   3. Enter product name as the Name.
   4. Select Save.

You should see a similar table as shown below.

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

SELECT
    LOWER((cc_data_view."product_name")) AS "product_name",
    COALESCE(SUM(CAST(( cc_data_view."purchase_revenue"  ) AS DOUBLE PRECISION)), 0) AS "sum_of_purchase_revenue",
    COALESCE(SUM(CAST(( cc_data_view."purchases"  ) AS DOUBLE PRECISION)), 0) AS "sum_of_purchases"
FROM public.cc_data_view  AS cc_data_view
WHERE ((( cc_data_view."daterange"  ) >= (DATE_TRUNC('day', DATE '2023-01-01')) AND ( cc_data_view."daterange"  ) < (DATE_TRUNC('day', DATE '2024-01-01'))))
GROUP BY
    1
ORDER BY
    2 DESC
FETCH NEXT 500 ROWS ONLY

The Customer Journey Analytics objects (dimensions, metrics, filters, calculated metrics, and date ranges) are available as part of the embedded SQL queries you construct. See earlier examples.

Custom transformations

1. Enter the following statements in a new cell.

   data = %sql SELECT LOWER(product_category) AS `Product Category`, COUNT(*) AS EVENTS \
               FROM cc_data_view \
               WHERE daterange BETWEEN '2023-01-01' AND '2024-01-01' \
               GROUP BY 1 \
               ORDER BY `Events` DESC \
               LIMIT 5;
   display(data)
   

2. Execute the cell. You should see output similar to the screenshot below.

   

The query is excuted by the BI extension as defined in Jupyter Notebook.

The Customer Journey Analytics components (dimensions, metrics, filters, calculated metrics, and date ranges) are available as similar named objects in the R language. Refer to the components using the component See earlier examples.

Custom transformations

1. Enter the following statements between ```{r} and ``` in a new chunk.

   df <- dv %>%
      filter(daterange >= "2023-01-01" & daterange <= "2024-01-01") %>%
      mutate(d2=lower(product_category)) %>%
      group_by(d2) %>%
      count() %>%
      arrange(d2, .by_group = FALSE)
   print(df)
   

2. Run the chunk. You should see output similar to the screenshot below.

   

The query generated by RStudio using the BI extension is including lower, which implies that the custom transformation is executed by RStudio and the BI extension.

SELECT "d2", COUNT(*) AS "n"
FROM (
  SELECT "cc_data_view".*, lower("product_category") AS "d2"
  FROM "cc_data_view"
  WHERE ("daterange" >= '2023-01-01' AND "daterange" <= '2024-01-01')
) AS "q01"
GROUP BY "d2"
ORDER BY "d2"
LIMIT 1000

Comparison

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

Icon	Customer Journey Analytics visualization	Power BI Desktop visualization
	Area	Area chart, stacked area chart and 100% area chart
	Bar	Clustered column chart
	Bar stacked	Stacked column chart and 100% stacked column chart
	Bullet
	Cohort table
	Combo	Line and stacked column chart and Line and clustered column chart
	Donut	Donut chart
	Fallout	Funnel.
	Flow	Decomposition tree?
	Freeform table	Table and Matrix
	Histogram
	Horizontal bar	Clustered bar chart
	Horizontal bar stacked	Stacked bar chart and 100% stacked bar chart
	Journey canvas	Decomposition tree
	Key metric summary
	Line	Line chart
	Scatter	Scatter chart
	Section header	Text box
	Summary change	Card
	Summary number	Card
	Text	Text box
	Treemap	Treemap
	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.

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

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

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

Comparison

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

Icon	Customer Journey Analytics visualization	Power BI Desktop visualization
	Area	Area Chart
	Bar	Bar Chart
	Bar stacked
	Bullet	Bullet Graph
	Cohort table
	Combo	Combination Charts
	Donut
	Fallout
	Flow
	Freeform table	Text Table
	Histogram	Histogram
	Horizontal bar	Bar Chart
	Horizontal bar stacked	Bar Chart
	Journey canvas
	Key metric summary
	Line	Line Chart
	Scatter	Scatter Plot
	Section header
	Summary change
	Summary number
	Text
	Treemap	Treemap
	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.

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

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

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.

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.

Comparison

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

Icon	Customer Journey Analytics visualization	Power BI Desktop visualization
	Area	Area Chart
	Bar	Bar Chart
	Bar stacked	Bar Chart
	Bullet	Bullet Graph
	Cohort table
	Combo	Customizing visualizations
	Donut	Donut
	Fallout	Funnel
	Flow	Sankey
	Freeform table	Table
	Histogram
	Horizontal bar	Bar Chart
	Horizontal bar stacked	Bar Chart
	Journey canvas
	Key metric summary
	Line	Line Chart
	Scatter	Scatterplot
	Section header
	Summary change	Single Value
	Summary number	Single Value
	Text	Single Value
	Treemap	Treemap
	Venn Diagram	Venn Diagram

Comparing the visualization capabilities of matplotlib.pyplot, the state-based interface to matplotlib, is beyond the purpose of this article. See examples above for inspiration and the matplotlib.pyplot documentation.

Comparing the visualization capabilities of ggplot2, the data visualization package in R, is beyond the purpose of this article. See examples above for inspiration and the ggplot2 documentation.

• 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 daterangeX 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 the 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.