MP10: World Wide Importers

The C-suite wants a modern dashboard to monitor the business

Goal

Learn how to create a BI dashboard for a realistically complex environment. Acquiring practice with Power BI.

Context

Your consultant/analyst career has now led you to Wide World Importers (WWI), a global trading company that imports and sells novelty goods to domestic businesses.
Bea I. Power, your CEO, believes that she needs a better handle on her company data. She purchased a few Power BI licenses to build a series of dashboards to monitor profits and revenues.

As a proof-of-concept, she would like to build a dashboard to monitor sales. However, 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 WWImportersDB on the f-sg6m-s4 server and build a dashboard to analyze sales at WWI.

The hardest 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. Bottom line: 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 PowerBI file as "MP10 YourLastname.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 Lehman (his friends call him 'SQL' for short). Steve is the Chief Data Officer at WWI. Except for being a die-hard fan of Star Wars and for his cringe-worhty 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 your dashboard, my young Jedi! Power BI can create ERDs for you. If you know how to read them, they are a great help!
In larger DBs, the tables are often 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 our schemas. There is much technical info that is not very useful, but if you search carefully you will find the definition of the schemas and the tables."

"So, let's start with the good news: you will not have to work too hard on cleaning the data. My team here 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 sloppy data practices, such as sending reports as pdfs...).

"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... but remember: numbers there are not accurate" said Steve while handing you two sketches (one and two) "... and this is 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 always use pre-tax numbers unless Bea tells us differently."

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 visuals span several years, other just 2016."

"The gauge is easy to set up: use the 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 tricky because each 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 it gets counted three times when we compute the revenue for those three categories.  Bottom line: the financial metrics computed as a sum of categories and the metrics computed as a sum of products will not match.  I do not think this is a great idea, but this is how Bea set it up. Keep it that way."

"If you get stuck and one of your visuals does not seem to be working in sync with the others, fear not! It is probably because one of the filters in the ERD is set to 'single' rather than 'both.'   Go to the ERD, and figure out which are the relationships involved in joining the tables you need to join to get your visual to work.  Then click on their relationship and change the cross-filter direction to 'both'.  However, do not do it indiscriminately, or Power BI will get confused.  Flip one, then check if the visual works, then flip another and so on."

"..."

"You look concerned, my young Jedi. No worries, you got this! Here: I have some more good news for you: the second screen is very very similar to the first. You will be done in no time."

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 on the source database: 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."

"Thank you, SQL!"

Criteria

The dashboards should look as much as possible like the two sketches, including the requested colors.  Make them look professional: well-aligned items and borders, well-organized fonts, matching colors, matching formats, etc. All components of the reports must be active visuals.  All data must be accurate.

 $ figures: one decimal for all, except for the cards on top left of the screens who have 2 for the number and 0 for the min/max/target.

No need to create a web or mobile version.
No need to add the SQL queries you may optionally use to check your dashboards.

Uploading error message

If you get this error when you upload your MP10: "The page was not displayed because the request entity is too large" do the following:
1. Open Power BI with your miniproject
2. Go to Power Query and delete all tables that contain the word "Archive", "Website", and "VehicleTemperatures"
3. Save your file.   Now it is much smaller and will upload. It needs to be less than 30MB.

---

 IThank you for a great semester. I enjoyed working with you and observe you acquire foundational data management skills that I believe will be useful in your next job. Keep in touch!