I quote DS42: "Can we have more content on SQL please?"
A question well placed where the current marketplace is keen to have people with SQL skills.
So here we go, day 3 of dashboard week and it's time to practice our SQL skills some more.
You'll be investigating a fraction of the imaginary company called, MoVer, who manages, sells and distributes products. It is a large retail company who has multiple stores located nation-wide and also sells products through catalogues and the internet.
We've been given full access to the Snowflake database on which MoVer operates. It contains multiple fact and dimension tables that track records of customer purchases and returns from any sales channel, promotion sale process, warehouse inventory levels and maintains customer profiles.
Snowflake and Tableau work well together, however, in order to reduce costs, MoVer would like to start working with data extracts that are updated daily rather than racking up costs by using live connections
Hence, careful planning of data aggregations, joins and calculations are to be carried out using SQL before they can bring it into Tableau.
Today we'd like you to interrogate a part of the business and as it turns out MoVer appreciate any scope you can take to look at a section of their business. Due to time and resources available they have never been able to get to quick answers, one of the big sticking points is the sheer size of the data. The fact tables contain over 500 billion rows all together!
Requirements for today:
- use Snowflake worksheets to interrogate the data and prepare your SQL queries
- DB (SNOWFLAKE_SAMPLE_DATA) SCHEMA (TPCDS_SF100TCL)
- Use LIMIT to prevent the query of billions of rows until it's necessary!
- Use the New Custom SQL function underneath the tables in Tableau after connecting to the database schema to create your table(s)
- Relate multiple tables (or pre-join them in the queries) where you see fit
- Build out your insights using a combination of sheets and dashboards, put together into story pages (yes the 'new story' icon on the bottom middle of tableau desktop.
Make sure to narrow down to a very specific business element to interrogate, for instance:
- Find the top 10 selling Store Sales products (in terms of revenue) and investigate which demographics contribute towards those sales most. Take those demographic categories and determine what other products they tend to buy with those top 10 products.
- Look at the latest year's revenue for Catelog Sales and determinate the top item brand(s) or category(ies) for each Call Center. Dive into their Monthly Sales for each Call Centre and determine which months deviate more than 10% of the monthly average sales for that year.
- Pick the Web Sale Returns of a defined period of time (latest year for instance) and investigate whether there are any connections between web pages, ship modes, warehouses to the reason of return (if there was any).
- Scope out your own business element you think will be something that's of interest for this business.
Page 18 to 31 of this document contains schema's that will support you on deciding a route to interrogate as well as help out with the rest of your endeavour.
By 4pm today you are to present your:
- Scope
- Plan (and reflections on the Plan)
- SQL Worksheets
- Tableau Story
- Blog
You will have 5 minutes to present this.
Good luck!