Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Exploratory Seminar #39 - How to Use Parameters

Exploratory Seminar #39 - How to Use Parameters

The Parameter feature is one of the strong features in Exploratory. It makes your chart, analytics, dashboard, notes, slides, and data interactive in a very flexible way. In this seminar, Kan will introduce the basics and demonstrate how to use the Parameter in the following scenarios.

* Parameterize Data
* Parameterize Chart
* Parameterize Dashboard
* Parameterize Calculations
* Parameterize SQL Queries

19fc8f6113c5c3d86e6176362ff29479?s=128

Kan Nishida
PRO

March 25, 2021
Tweet

Transcript

  1. EXPLORATORY Online Seminar #39 3/24/2021 (Wed) 11AM PT How to

    Use Parameter - SQL, Filter, & Calculation, Dashboard -
  2. Kan Nishida CEO/co-founder Exploratory Summary In Spring 2016, launched Exploratory,

    Inc. to democratize Data Science. Prior to Exploratory, Kan was a director of product development at Oracle leading teams to build various Data Science products in areas including Machine Learning, BI, Data Visualization, Mobile Analytics, Big Data, etc. While at Oracle, Kan also provided training and consulting services to help organizations transform with data. @KanAugust Speaker
  3. Vision Everyone will be making data informed decisions.

  4. Mission Democratize Data Science

  5. 5 Questions Communication Data Access Data Wrangling Visualization Analytics (Statistics

    / Machine Learning) Data Analysis Data Science Workflow
  6. 6 Questions Communication (Dashboard, Note, Slides) Data Access Data Wrangling

    Visualization Analytics (Statistics / Machine Learning) Data Analysis ExploratoryɹModern & Simple UI
  7. EXPLORATORY Online Seminar #39 3/24/2021 (Wed) 11AM PT How to

    Use Parameter - SQL, Filter, & Calculation, Dashboard -
  8. 1. Parameterize Data 2. Parameterize Chart 3. Parameterize Dashboard 4.

    Parameterize SQL 5. Parameterize Calculation Agenda
  9. 1. Parameterize Data 2. Parameterize Chart 3. Parameterize Dashboard 4.

    Parameterize SQL 5. Parameterize Calculation Agenda
  10. This is Sales data. Each row represent each order of

    a product from a given customer.
  11. I have filtered the data to keep only North America

    customers.
  12. And, I have created a Top 100 North American Customers

    list based on the sales amount.
  13. And, I have published this data to share with other

    team mates.
  14. 14 Me Team Mates Exploratory Server Publish

  15. Now, my team mates can open the data page in

    their web browsers and download the data.
  16. But, here is a problem…

  17. 17 Me Team Mates I want data for Europe. I

    want data for Africa. I want data for Asia. Exploratory Server
  18. 18 We can Parameterize the Data!

  19. 19 This data uses this Filter step to filter the

    data by a ‘Market is USCA’ condition.
  20. Instead of ‘hard-coding’ the Market value, we can use Parameter

    to make it dynamic. You can create a new parameter from here.
  21. Create a ‘List of Values (Single Select)’ this time.

  22. Select the same data frame and the step before the

    Filter step to get a list of values from the Market column.
  23. Once the parameter is created, select the new parameter ‘Market’.

  24. Now, click the Parameter button to open the Parameter pane.

  25. Select a value from the dropdown and click on the

    Run button. It will filter the data to keep only the ‘Europe’ market data.
  26. Republish to update the data at Exploratory Server.

  27. Now, you see the Parameter button!

  28. In order to use the Parameter, you want to turn

    on the ‘Use Parameter’ mode first.
  29. Just like we did in Exploratory Desktop, select a value

    and click the Run button!
  30. Once the data is returned, then you can click on

    the ‘Download’ button to download the data if you want.
  31. 31 Me Team Mates I want data for Europe. I

    want data for Africa. I want data for Asia. Exploratory Server By parameterizing the data, everyone can get their own preferred data set. Publish
  32. Parameter is not only for Data.

  33. 1. Parameterize Data 2. Parameterize Chart 3. Parameterize Dashboard 4.

    Parameterize SQL 5. Parameterize Calculation Agenda
  34. Again, we have a sales data with a Filter step

    that is parameterized.
  35. Now, I have created a chart to show Sales Amount

    by Product Sub-Category.
  36. Since the data for this chart is coming from the

    step with the parameter, you can change the parameter to update the data shown in the chart.
  37. For example, we can select ‘Asia Pacific’ to show only

    the data for Asia Pacific.
  38. Now, I have created another chart, but this time it

    is pinned to the Source step, that is before the Filter step.
  39. So, if I changed the parameter the data in this

    chart wouldn’t change.
  40. 1. A Quick Introduction to Parameter with Filter 2. Parameterize

    Data 3. Parameterize Chart 4. Parameterize Dashboard 5. Parameterize SQL 6. Parameterize Calculation
  41. Now, I have created a dashboard with the previously created

    2 charts.
  42. When we click on the Parameter button, we can see

    the ‘Market’ parameter, which is automatically added.
  43. It adds all the parameters that are used to generate

    the data for the charts. This time, the bar chart (left side) is the one using the Parameter.
  44. Now, I want to add a parameter to show the

    Line chart to show the sales trend for either Returned orders or Not Returned orders.
  45. Create a Logical type parameter which has TRUE or FALSE

    values.
  46. Select ‘Set all values when no value is selected’. This

    is convenient when you want to return all the rows by not selecting any value for this parameter.
  47. Create a Chart filter because we want only this chart

    to have this filtered data.
  48. Select ‘Is In’ operator and select the ‘Returned’ parameter.

  49. The default is ‘All’, which means both TRUE and FALSE.

  50. Now, when you open the dashboard again, you will see

    the new parameter ‘Returned’ being automatically added.
  51. When you change the Returned parameter value, it will update

    only the line chart (right side), not the bar chart.
  52. 52 Me Team Mates I want data for Europe. I

    want data for Africa. I want data for Asia. Exploratory Server I can publish the dashboard to Exploratory Server to share with my team mates. And, everyone can see the data of their interest shown in the dashboard by using the Parameter! Publish
  53. 53 You can flexibly design the Dashboard with Parameters.

  54. 54 This parameter is used in the Filter step that

    effects all the charts.
  55. 55 This parameter is used in a Chart Filter that

    effects only this chart.
  56. 56 This parameter is used in a Analytics Filter that

    effects only this chart that is from ‘Time Series Forecasting with Prophet’.
  57. 57 All you need is to add parameters to Step,

    Chart, and Analytics. Then all the required parameters will show up automatically in the Parameter pane.
  58. 1. Parameterize Data 2. Parameterize Chart 3. Parameterize Dashboard 4.

    Parameterize SQL 5. Parameterize Calculation Agenda
  59. 59 Team Mates I want data for Europe. I want

    data for Africa. I want data for Asia. Exploratory Server By parameterizing the data, everyone can get their own preferred data set.
  60. 60 Team Mates I want data for Europe. I want

    data for Africa. I want data for Asia. Exploratory Server These parameters are used in the Filter step of the Data Wrangling steps inside Exploratory.
  61. 61 Team Mates I want data for Europe. I want

    data for Africa. I want data for Asia. Exploratory Server But the data is originally coming from a database. σʔλ
  62. 62 Team Mates I want data for Europe. I want

    data for Africa. I want data for Asia. Exploratory Server Instead of getting all the data into Exploratory and doing the filtering inside Exploratory, can’t we make the database do the filtering? σʔλ
  63. 63 Team Mates I want data for Europe. I want

    data for Africa. I want data for Asia. Exploratory Server Yes, you can! You can parameterize SQL! σʔλ SQL
  64. 64 This is Sales data from MySQL database.

  65. 65 I’m using this SQL query to get the sales

    data from a database.
  66. 66 We want to parameterize this SQL query.

  67. 67 Let’s start with ‘Category’.

  68. 68 Click the Parameter button.

  69. 69 Click ‘Setup Parameter’ button to open the Parameter Config

    dialog.
  70. 70 Create a ‘Category’ parameter.

  71. 71 This time, select ‘List of Values (Multi Select)’ input

    type so that we can select multiple categories.
  72. 72 Enter the parameter values manually.

  73. 73 If you want to get the values dynamically from

    the database you can create another data frame with a SQL query, then use the data frame for the list of values.
  74. 74 You can type ‘@‘, which will show you a

    list of the parameters to pick from.
  75. 75 Once you select the ‘Category’ parameter you can click

    ‘Refresh’ button in the parameter pane. This will show the Category parameter.
  76. 76 By selecting the categories and clicking on the Run

    button, it will submit an updated SQL query to the database and get the updated data set accordingly.
  77. 77 Now, we want to make the ‘Order Date’ filter

    to be parameterized.
  78. 78 Create a Data type parameter.

  79. 79 Select ‘Specific Date’ and type a date as the

    default value.
  80. 80 Add the ‘From’ parameter to the SQL query.

  81. 81 Again, once you click on the Refresh button, you’ll

    see the From parameter.
  82. 82 You can use the Date Picker to select a

    specific date, which will update the SQL query’s condition and get an appropriate data.
  83. 83 You can import the query result as a data

    frame into Exploratory.
  84. 84 Click the Parameter button to open the Parameter pane.

  85. 85 You can update the parameter values and click the

    Run button. This will send an update SQL query and get the data back from the database.
  86. 1. Parameterize Data 2. Parameterize Chart 3. Parameterize Dashboard 4.

    Parameterize SQL 5. Parameterize Calculation Agenda
  87. I have published this chart. It shows the ratio of

    orders from the repeated purchase customers.
  88. How do you define ‘Repeated Customers’?

  89. 89 Orders from Repeated Customers The 2nd or further orders.

    Orders from New Customers 1st time order.
  90. 90 Some people have different definitions of ‘Repeat Customers’. More

    than twice! More than 3 times! More than 4 times!
  91. Let’s parameterize the ‘Repeat Customer’ definition!

  92. 92 Here is how the chart is created.

  93. 93 Each customer orders 1 or more times.

  94. The ‘Nth Order’ column shows how many times a given

    customer have ordered.
  95. The ‘Repeat Order’ column looks at the ‘Nth Order’ column

    value and see if it is greater than 2 or not.
  96. Currently, it is hard-coded as 2, but we want to

    make it dynamic with Parameter.
  97. Create a Number type parameter.

  98. Update the calculation by adding the ‘Repeat_Criteria’ parameter inside the

    condition.
  99. Now you see a slider, which you can use to

    set the Repeat Criteria value.
  100. Now, only the rows whose ‘Nth Order’ is 3 or

    more are labeled ‘TRUE’.
  101. Go back to the chart.

  102. Let’s assign ‘Market’ column to Repeat By to separate the

    chart into each market.
  103. Republish it.

  104. Now you see the ‘Repeat Criteria’ parameter.

  105. That’s it for today! 105

  106. EXPLORATORY Online Seminar #40 3/31/2021 (Wed) 11AM PT Introduction to

    Various Filter Types
  107. None
  108. Information Email kan@exploratory.io Website https://exploratory.io Twitter @ExploratoryData Seminar https://exploratory.io/online-seminar

  109. Q & A 109

  110. EXPLORATORY 110