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

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