Comm 3220: DM4DM

Goal

Creating a BI dashboard 

Learning how to use Power BI in a realistically complex environment

Context

You are a recently-hired analyst at Wide World Importers (WWI), a global trading company that sells novelty goods to domestic businesses.  Bea I. Power, your CEO, believes that she needs a better handle on her company revenue data and has purchased a license for Power BI.  As a proof-of-concept, she would like to build a dashboard to monitor sales by quarter and product category.

Many of your colleagues have passed on the opportunity to create the first BI dashboard for WWI, claiming various forms of allergy to learning new technologies. Not you. You gave Ms. Power your trademark "I'm on it, Boss...." and got the job.  This is your chance to shine. 

Task

Access the database called WWImportersDB on the f-sg6m-s4 server and build a dashboard to analyze sales at WWI.

The hard part of this exercise is to figure out where is the data, what it means, and how to make Power Bi work as desired. If you know what you are doing, this assignment will take about two hours. Depending on your familiarity with the tools, expect to spend more than that to complete it.
Give yourself plenty of time. Take a break if you start feeling like punching your computer screen. You will succeed! (eventually)

When done, save the Power Bi file as "MP10 YourLastname YourFirstName.pbix", drop it in your BOX folder and pat yourself in the back. Another job well done!

Context (continued)

To get some advice on how to proceed, you decide to pay a visit to Steve Quinn Leheman (his friends call him 'SQL' for short).  Steve is the Chief Data Officer at WWI, and except for being a die-hard fan of Star Wars and his habit of calling you 'my young Jedi', he is super helpful.  You find him in his office.

"Yes! Our DB contains over 50 tables. You will have to figure out which ones you need for the dashboard. Power Bi can create ERDs for you. If you know how to read them, they are a great help, my young Jedi!"

"The tables are organized into schemas, which are ways to group them by adding an extra name (schemaname.tablename).   These data dictionaries will help you understand what is going on with the schemas.  There is much technical info that is not very useful, but you will find the definition of the schemas and the tables. There is no much info on the fields, but do not worry, my young Jedi, I will help you!"

"Let's start with the good news: you will not have to work too hard on cleaning the data.  My team at WWI has already done a pretty good job. We sure work smarter than those jokers at Nolex..."  (Steve worked at Nolex before joining WWI and really dislikes their data practices, such as sending reports as pdfs...).

"Here: last week I sketched a couple of screens for the dashboard and showed them to Bea.  She loved them! Try to match their look as closely as you can...not all numbers there are accurate" said Steve while handing you two sketches (one and two) "... and  this file has our logo."

"The dashboard will have two pages. One for revenue analysis and the other for gross profits analysis. Always use the invoice data, not the order data. They are more reliable. Figure out what extended price and unit price mean.  In our reporting we use pre-tax numbers."

You realize that the advice will save you hours of work and start taking notes furiously.

"When you create the visuals, use filters to limit the time ranges: some span several years, other just 2016." 

"The gauge is easy to set up: sum of the revenue for 2016, plus two fixed parameters: the target and the max. Easy peasy!"

"The revenue-by-category is a little more complex. Here at WWI we call product categories 'StockGroups'.  It gets tricky because each stockItem (i.e., each product we sell) can belong to more than one category. So for instance our toy USB Missile Launcher belongs to "Novelty items", "Computing novelties", and "USB novelties."  So this toy gets counted three times when we compute the revenue for those three categories. I do not think this is a great idea, but this is how Bea set it up."

"The key is to create a merged table.  This is how you do it: 1)  duplicate the SaleInvoiceLines table; 2) Start from that and using Power Bi and Power Query merge tables until you get a table that contains both the StockItemId and the StockGroupNames.  Delete extra columns that you will not need, as well as the links to tables that Power Query sets up for you: you need to simplify the table or it will take forever to join.  Once you are done, charting revenue by category will be super easy."

"..."

"You look concerned, my young Jedi! Here: I have some really good news for you: the second screen is very very similar to the first.  Gross profit is just the difference between revenue and cost. That data has already been calculated. Look for it: it is right there!"

Just as you are about to leave his office, he gives you one more advice.

"I like to double-check my results by running queries: if I get the same results I feel good about the accuracy of my dashboard.  You might prefer to work on the queries first, and the dashboard later. Or do the opposite.  Choose whatever approach feels easier for you."

"Thank you, SQL!"

Criteria

Interactive filters must work, numbers must be correct, the dashboard must match as closely as possible the sketches above.