Retail Store Analysis Dashboard (Superset)

Connecting to MySQL

1. In the Add New Data Source screen, click Create Connection in the MySQL tile.
2. In the drawer that opens, enter the following information in the respective fields:
   • Name: mysql
   • Connection URL: jdbc:mysql://<ip-address>:<port>
   • Connection User: myaccount
   • Connection Password: moi123
   • Enable Local Snapshot Table: Select the check box
     TIP

     When Enable Local Snapshot Table is selected, the system caches remote table data to accelerate queries on the tables. The cache is active for the duration of the configured TTL or until the remote tables in the data source are altered.
   • Click Connect. Upon successful connection, the system returns the following message:

     Successfully added data source "mysql".

Connecting to SQL Server

1. In the Add New Data Source screen, click Create Connection in the SQL Server tile.
2. In the drawer that opens, enter the following information in the respective fields:
   • Name: mssqlret2
   • Connection URL: jdbc:sqlserver:<ip-address>:<port>;database=retailstore
   • Connection User: myaccount
   • Connection Password: moi123
   • Enable Local Snapshot Table: Select the check box
     TIP

     When Enable Local Snapshot Table is selected, the system caches remote table data to accelerate queries on the tables. The cache is active for the duration of the configured TTL or until the remote tables in the data source are altered.
   • Click Connect. Upon successful connection, the system returns the following message:

     Successfully added data source "mssqlret2".

Connecting to Hive

1. In the Add New Data Source screen, click Create Connection in the Hive tile.
2. In the drawer that opens, enter the following information in the respective fields:
   • Name: hiveview
   • Hive Metastore: file
   • Hive Metastore Catalog Dir: file:///data/shared/tmpmetastore
   • In Optional Fields, search for the following fields and add the specified values:
     • Hive Max Partitions Per Writers: 10000
     • Hive Temporary Staging Directory Enabled: Unselect
     • Hive Allow Drop Table: Select
   • Enable Local Snapshot Table: Select the check box
     TIP

     When Enable Local Snapshot Table is selected, the system caches remote table data to accelerate queries on the tables. The cache is active for the duration of the configured TTL or until the remote tables in the data source are altered.
   • Click Connect. Upon successful connection, the system returns the following message:

     Successfully added data source "hiveview".

1. In the left navigation bar, select Data Engineering > Data Catalog.
2. On the Data Catalog page, click the dropdown next to the mysql and mssqlret2 data sources to expose the available schemas in those data sources.
3. Select schemas for each of the data sources:
   • For the mysql data source, select the retailstore schema.
   • For the mssqlret2 data source, select the dbo schema.
4. In the All Datasets section, click the filter icon to open the Filters drawer.
5. Use the filter to identify and select the following data sets in the selected schemas:
   • For the dbo schema, filter for and select the following datasets:
     • call_center
     • catalog_sales
     • data_dim
     • item
   • For the retailstore schema, filter for and select the following datasets:
     • customer
     • customer_address
     • customer_demographics
6. After you select all the data sets, click Apply.
7. Click Selected Datasets (button that is displaying the number of selected data sets).
8. In the drawer that opens, click Query Editor. Depending on the number of selected data sets, you may have to scroll down to the bottom of the drawer to see the Query Editor button.

1. In the Query Editor, click + to Add Worksheet.
2. Run the following command to create a new schema, such as hiveview.demoschema, for example:

   create schema if not exists hiveview.demoschema;

3. Run a query to create a new view from a federated query against the selected data sets, for example:

   create view hiveview.demoschema.qf_retailstore_view as select * from mssqlret2.dbo.catalog_sales cs
   inner join mssqlret2.dbo.call_center cc on cs.cs_call_center_sk = cc.cc_call_center_sk
   inner join mssqlret2.dbo.date_dim d on cs.cs_sold_date_sk = d.d_date_sk
   inner join mssqlret2.dbo.item i on cs.cs_item_sk = i.i_item_sk
   inner join mysql.retailstore.customer c on cs.cs_bill_customer_sk = c.c_customer_sk
   inner join mysql.retailstore.customer_address ca on c.c_current_addr_sk = ca.ca_address_sk
   inner join mysql.retailstore.customer_demographics cd on c.c_current_cdemo_sk = cd.cd_demo_sk
   

4. Click Run. When the query completes, the status, Finished, displays.

• DATABASE: Presto
• SCHEMA: <your_schema>
• SEE TABLE SCHEMA: <your_view>

This tutorial uses the retailstore schema and qf_retailstore_view.

METRICS

1. Click into the METRICS field (located on the DATA tab). A metrics window opens.
2. Select the Simple tab.
3. Click the edit icon and enter a name for the metric, such as SUM(cs_net_paid).
4. In the Column field, select cs_net_paid.
5. In the Aggregate field, select SUM.
6. Click Save.

FILTERS

1. Click into the FILTERS field (located on the DATA tab).
2. In the window that opens, select the CUSTOM SQL tab.
3. Select the WHERE filter and enter the following:

   NULLIF(ca_state, '') IS NOT NULL

4. Click Save.

DIMENSIONS

1. Drag and drop the ca_state column into the DIMENSIONS field.
2. Click into the BREAKDOWNS column.
3. In the window that opens, select the SIMPLE tab and select the cc_name column.
4. Click Save.

SORT BY

1. Click into the SORT BY field.
2. In the window that opens, select the SIMPLE tab and enter cs_net_paid as the COLUMN and SUM as the AGGREGATE.
3. Click Save.