BI extension use cases

Power BI Desktop

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.

More information

• Prerequisites

• Credentials guide

• Connect Power BI to Query Service.

Tableau Desktop

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.

More information

• Prerequisites

• Credentials guide

• Connect Tableau Desktop to Query Service.

Looker

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.

More information

• Prerequisites

• Credentials guide

Jupyter Notebook

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.

More information

• Prerequisites

• Credentials guide

RStudio

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.

More information

• Prerequisites

• Credentials guide

Power BI Desktop

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.

   

Tableau Desktop

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.

      

Looker

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.

Jupyter Notebook

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.

   

RStudio

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 Desktop

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

Tableau Desktop

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.

      

Looker

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.

Jupyter Notebook

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.

   

RStudio

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.

   

Power BI Desktop

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.

   

Tableau Desktop

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.

      

Looker

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.

Jupyter Notebook

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.

   

RStudio

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.