tutorials/superset/03-retail-dashboard-supersets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retail Store Analysis Dashboard (Superset)\n",
    "\n",
    "This tutorial provides an end-to-end workflow example for a retail store analysis scenario in HPE\n",
    "Ezmeral Unified Analytics (EzUA) Software using EzPresto and Superset.\n",
    "\n",
    "In this case, a data analyst working for a major retail company aims to visualize data sets from\n",
    "MySQL, SQL Server, and Hive data sources using Superset. The analyst logs into HPE EzUA and\n",
    "establishes connections to MySQL, SQL Server, and Hive data sources. Following this, the analyst\n",
    "executes a federated query across the data sets and then creates a view based on this query. They\n",
    "then access this view through Superset and utilize it to visualize the data in a bar chart,\n",
    "ultimately creating a comprehensive dashboard. This dashboard provides a fully customized overview\n",
    "of the retailer's operations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table of Contents\n",
    "\n",
    "- [Checking the Data Sources](#cheking-the-data-sources)\n",
    "- [Select the Datasets and Create a View](#select-data-sets-and-create-a-view)\n",
    "- [Connect to the Presto Database](#connect-to-the-presto-database)\n",
    "- [Add the View to Superset and Create a Chart](#add-the-view-to-superset-and-create-a-chart)\n",
    "- [Specify Query Conditions to Visualiza Resulrs in the Chart](#specify-query-conditions-to-visualize-results-in-the-chart)\n",
    "- [Create a Superset Dashboard and Add the Chart](#create-a-superset-dashboard-and-add-the-chart)\n",
    "- [Monitor Queries](#monitor-queries)\n",
    "- [Conclusion](#conclusion)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking Data Sources\n",
    "\n",
    "In the sidebar navigation menu of HPE EzUA, click `Data Engineering > Data Sources`. Confirm you\n",
    "have the following Data Sources:\n",
    "\n",
    "- `mysql`\n",
    "- `mssql_ret2`\n",
    "- `hiveview`\n",
    "\n",
    "> If you do not have these data sources, please follow the first tutorial notebook, \"Data Source\n",
    "> Connectivity and Exploration\". \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Select Data Sets and Create a View\n",
    "\n",
    "Next, you create a new view for our retail dashboard. You first select the data sources and datasets\n",
    "you wish to work with. Then, you run a federated query against the selected data sets and create a\n",
    "view from the query. From these queries, you create an example view named `qf_retailstore_view`:\n",
    "\n",
    "1. In the sidebar navigation menu in HPE Ezmeral Unified Analytics, select `Data Engineering > Data Catalog`.\n",
    "1. On the `Data Catalog` page, click the dropdown next to the `mysql` and `mssql_ret2` data sources\n",
    "   to expose the available schemas in those data sources.\n",
    "1. Select schemas for each of the data sources:\n",
    "    - For the `mysql` data source, select the `retailstore` schema.\n",
    "    - For the `mssql_ret2` data source, select the `dbo` schema.\n",
    "1. In the `All Datasets` section, click the `filter` icon to open the `Filters` drawer.\n",
    "1. Use the filter to identify and select the following data sets in the selected schemas:\n",
    "    - For the `dbo` schema, filter for and select the following datasets:\n",
    "        * `call_center`\n",
    "        * `catalog_sales`\n",
    "        * `data_dim`\n",
    "        * `item`\n",
    "    - For the `retailstore` schema, filter for and select the following datasets:\n",
    "        * `customer`\n",
    "        * `customer_address`\n",
    "        * `customer_demographics`\n",
    "1. After you select all the data sets, click `Apply`.\n",
    "1. Click `Selected Datasets` (button that is displaying the number of selected data sets).\n",
    "1. In the drawer that opens, click `Query Editor`. Depending on the number of selected data sets,\n",
    "   you may have to scroll down to the bottom of the drawer to see the `Query Editor` button.\n",
    "\n",
    "\n",
    "Now, let's query the datasets and create a view:\n",
    "\n",
    "1. In the `Query Editor`, click `+` to `Add Worksheet`.\n",
    "1. Run the following command to create a new schema, such as `hiveview.demoschema`, for example:\n",
    "\n",
    "    ```sql\n",
    "    create schema if not exists hiveview.demoschema;\n",
    "    ```\n",
    "1. Run a query to create a new view from a federated query against the selected data sets, for\n",
    "   example:\n",
    "\n",
    "    ```sql\n",
    "    create view hiveview.demoschema.qf_retailstore_view as select * from mssql_ret2.dbo.catalog_sales cs\n",
    "    inner join mssql_ret2.dbo.call_center cc on cs.cs_call_center_sk = cc.cc_call_center_sk\n",
    "    inner join mssql_ret2.dbo.date_dim d on cs.cs_sold_date_sk = d.d_date_sk\n",
    "    inner join mssql_ret2.dbo.item i on cs.cs_item_sk = i.i_item_sk\n",
    "    inner join mysql.retailstore.customer c on cs.cs_bill_customer_sk = c.c_customer_sk\n",
    "    inner join mysql.retailstore.customer_address ca on c.c_current_addr_sk = ca.ca_address_sk\n",
    "    inner join mysql.retailstore.customer_demographics cd on c.c_current_cdemo_sk = cd.cd_demo_sk\n",
    "    ```\n",
    "1. Click `Run`. When the query completes, the status `\"Finished\"` will display."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connect to the Presto Database\n",
    "\n",
    "Complete the following steps to connect Superset to the Presto database for access to your data\n",
    "sources and data sets in HPE EzUA. Once connected to the Presto database, you can access your data\n",
    "sets in HPE EzUA from Superset.\n",
    "\n",
    "To connect to the Presto database, you need the connection URI. You can get the URI from your HPE\n",
    "EzUA administrator. To open Superset, in the left navigation pane of HPE Ezmeral Unified Analytics\n",
    "Software, select `BI Reporting > Dashboards`. Supersets opens in a new tab.\n",
    "\n",
    "In Supersets, perform the following:\n",
    "\n",
    "1. Select `Settings > Database Connections`.\n",
    "1. Click `+ DATABASE`.\n",
    "1. In the `Connect a database` window, select the `Presto` tile.\n",
    "1. Enter the SQLALCHEMY URI provided by your administrator.\n",
    "1. Test the connection.\n",
    "1. If the test was successful, click `Connect`.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add the View to Superset and Create a Chart\n",
    "\n",
    "Complete the following steps to import the view you created in HPE EzUA and create a bar chart. This\n",
    "tutorial demonstrates how to import the view `qf_retailstore_view`. To open Superset, in the left\n",
    "navigation pane of HPE Ezmeral Unified Analytics Software, select `BI Reporting > Dashboards`.\n",
    "Supersets opens in a new tab.\n",
    "\n",
    "In Superset, perform the following:\n",
    "\n",
    "1. Click the `Datasets` tab.\n",
    "1. Click `+ DATASET`.\n",
    "1. In the Add Dataset window, select the following options:\n",
    "    - `DATABASE:` Presto\n",
    "    - `SCHEMA:` `<your_schema>`\n",
    "    - `SEE TABLE SCHEMA:` `<your_view>`\n",
    "\n",
    "    > This tutorial uses the `retailstore` schema and `qf_retailstore_view`.\n",
    "\n",
    "1. Click `ADD DATASET AND CREATE CHART`.\n",
    "1. In the `Create a New Chart` window, select `Bar Chart`.\n",
    "1. Click `CREATE NEW CHART`.\n",
    "1. Enter a name for the chart (e.g \"Retail Store View\")."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![title](images/03a.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Specify Query Conditions to Visualize Results in the Chart\n",
    "\n",
    "In Superset, charts visualize data based on the query conditions that you specify. The charts\n",
    "created in Superset automatically generate queries that Superset passes to the SQL query engine.\n",
    "Superset visualizes the query results in the chart. Try applying query conditions to visualize your\n",
    "data.\n",
    "\n",
    "The following steps demonstrate how query conditions were applied to visualize data in the resulting\n",
    "example bar chart (shown in Step 4). First, enter the specified query parameters in the following\n",
    "fields:\n",
    "\n",
    "- `METRICS`\n",
    "    1. Click into the `METRICS` field (located on the `DATA` tab). A metrics window opens.\n",
    "    1. Select the `Simple` tab.\n",
    "    1. Click the `edit` icon and enter a name for the metric, such as `SUM(cs_net_paid)`.\n",
    "    1. In the `Column` field, select `cs_net_paid`.\n",
    "    1. In the `Aggregate` field, select `SUM`.\n",
    "    1. Click `Save`.\n",
    "- `FILTERS`\n",
    "    1. Click into the `FILTERS` field (located on the `DATA` tab).\n",
    "    1. In the window that opens, select the `CUSTOM SQL` tab.\n",
    "    1. Select the `WHERE` filter and enter the following: `NULLIF(ca_state, '') IS NOT NULL`\n",
    "    1. Click `Save`.\n",
    "- `DIMENSIONS`\n",
    "    1. Drag and drop the `ca_state` column into the `DIMENSIONS` field.\n",
    "    1. Click into the `BREAKDOWNS` column.\n",
    "    1. In the window that opens, select the `SIMPLE` tab and select the `cc_name` column.\n",
    "    1. Click `Save`.\n",
    "- `SORT BY`\n",
    "    1. Click into the `SORT BY` field.\n",
    "    1. In the window that opens, select the `SIMPLE` tab and enter `cs_net_paid` as the `COLUMN` and\n",
    "       `SUM` as the `AGGREGATE`.\n",
    "    1. Click `Save`.\n",
    "\n",
    "Next, click `CREATE CHART`. The bar chart displays results when the query finishes processing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![title](images/03b.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Click `Save` to save the chart. In the `Save Chart` window that opens, do not enter or select a\n",
    "dashboard. Click `Save` to continue."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create a Superset Dashboard and Add the Chart\n",
    "\n",
    "Complete the following steps to create a new dashboard and add your chart to the dashboard. This\n",
    "tutorial adds the Retail Store View chart to a dashboard named Retail Store Analysis Dashboard.\n",
    "\n",
    "To create a new dashboard and add your visualized data:\n",
    "\n",
    "1. In Superset, click on the `Dashboards` tab.\n",
    "1. Click `+ DASHBOARD`.\n",
    "1. Enter a name for the dashboard (e.g \"Retail Store Analysis Dashboard\")\n",
    "1. Drag and drop your chart into the dashboard.\n",
    "1. Click `Save` to save the dashboard."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![title](images/03c.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Monitor Queries\n",
    "\n",
    "You can monitor queries generated through Superset from the EzPresto endpoint. You can access the\n",
    "EzPresto endpoint in the EzPresto tile in the Applications & Frameworks space in HPE EzUA.\n",
    "\n",
    "Complete the following steps to monitor the query that the chart generates:\n",
    "\n",
    "1. Return to the HPE EzUA dashboard.\n",
    "1. In the sibebar navigation menu, select `Applications & Frameworks`.\n",
    "1. Under the `Data Engineering` tab, click the `EzPresto endpoint` in the `EzPresto` tile. The\n",
    "   EzPresto interface will open in a new tab.\n",
    "1. In the `Query Details` section, verify that `Finished` is selected. Selected options have a\n",
    "   visible checkmark. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![title](images/03d.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see the query that ran to populate the Retail Store View bar chart in the Retail Store\n",
    "Analysis Dashboard. Click on the `Query ID` to see the query details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![title](images/03e.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To see a visualized query plan and metadata for the query, click `Live Plan` and hover over\n",
    "different areas of the visualized plan. You can also click on various parts of the visualized plan\n",
    "to zoom in on details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conclusion\n",
    "\n",
    "You have completed this tutorial series. This series demonstrated the integration of the HPE EzUA\n",
    "SQL query engine (EzPresto) with Superset to visualize the results of a query on data sets made\n",
    "available through the default Presto database connection. This tutorial also showed you how to\n",
    "monitor queries from the EzPresto Cluster Monitoring tool."
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}