Skip to main content Skip to navigation
Mauricio Featherman Mauricio Featherman Ph.D

Data Base, Data Modeling and Data Visualization Short-Course

Syllabus: Data-Modeling-Short-Course-Syllabus

 

Data management, data analysis and data visualization has never been more important for business managers to understand and leverage. Fortunately many software providers have recently innovated in this area to allow non-IT workers to gain access to advanced functionality with just a few clicks of the mouse. One software shaking up the analytics industry in 2017 is PowerBI from Microsoft. PowerBI is free, it is easy to learn and use, and it is VERY powerful.

In this class we explore data management, data analysis and data visualization using PowerBI. Students are given common business problems and the training needed to build the dataset needed to analyze the problem. In a hands-on format, supplemented by explanation of the concepts, students perform the data analysis and visualization, resulting in web-published dashboards that monitor business problems and display insights. Students analyze business problems, conduct descriptive analysis and provide their own managerial recommendations. It is very common for students to learn concepts, perspectives and methodologies in class that they can leverage on the job the very next day.

While we use Microsoft’s PowerBi because it is free and easy to install on windows machines, we also go back in time a bit to demonstrate similar PowerPivot data management technologies in Excel to build a database and reporting system. The content of this course requires no pre-requisite. We start by pulling data and datasets together and advance to writing powerful DAX code to add metric that are then visualized on dashboards. This is a great time to be taking a data modeling course!

Set-up – please install Power Bi by clicking here. If you have a MAC please borrow a windows machine for the course. If you need to use a MAC then there is considerable setup. First purchase, download and install the program Parallels – this partitions the hard drive. Next install a copy of windows. Finally from Microsoft’s website, locate, download and install PowerBI desktop. There is no MAC version of PowerBI, rather try the PowerBIPro 60 day trial (it must have data modeling).

Please try to install Power BI Desktop and start experimenting and watching training videos before the class starts. Here is the link for the video series shown below. There are > 100 short videos, look for solutions that help you solve current business problems.

Please also arrive class with a Windows based notebook computer with Excel 2013 or Excel 2016 installed. In Excel please select File | New | blank workbook, then select File | Add-ins | hen change the selection in the Manage: list to Manage Com Add-ins and then press Go. See if Microsoft Office Powerpivot and Power Query, and Power View are available to be selected.  If you see these three options, your Excel is the right version. If you do not see these three options please install a different version of Excel (maybe professional or Office 365). While the majority of our hands-on time is in the free version of PowerBI, we will cover the similar tools in Excel as many course participants enjoy working in Excel.

Core Principals of the course:

  • Please dedicate the two weeks of class to as much reading, watching videos, and experimenting with Excel or PowerBI desktop as you can find time. Ask questions when you do not understand something, work with classmates and investigate solutions to problems.
  • Please attend every class. You can arrive one hour early to the classroom for personal consultation or to participate in a homework lab.  Your professor will serve as your class’ software consultant. Please spend considerable time outside class completing assignments and installing software.
  • On the first day of class, bring a notebook computer to class that has Excel 2013 or Excel 2016 installed. Be sure you have the Powerpivot, Power query and Powerview add-ins pre-loaded. Also install PowerBI desktop. we will focus PowerBI which can be installed in five minutes.
  • During hands-on sessions each class period, please help your classmates to understand and finish the content.
  • Students are encouraged to use their own business or personal data in the data analysis and data analysis sessions. You can solve business data management, analysis and visualization problems for your company as part of the class assignments and final project.
  • Here is an example of a live dahboard https://app.powerbi.com/groups/me/reports/c6e142a0-be35-4f2d-ae42-e648534d4ed8/ReportSection
  • Click here for live Dashboard

This course had the following components:

  • Coverage of relational database design, theory and implementation using demonstrations in Microsoft Access, SQL Server Management Studio
  • Coverage of ETL theory and data warehouse design including star schemas and cloud computing
  • Coverage of ETL processes using the Power Query tools inside Excel 2013, Excel 2016 and PowerBI. This section includes data transformations, data cleaning, and the filtering out of unneeded rows and columns
  • Data Modeling using Excel 2013, Excel 2016 and PowerBI – including calculation of derived data, KPI’s, calculataed measures, hierarchies, and slicers
  • Data visualization using pivot tables, pivot charts, maps, and conditional formatting in Excel, Powerview and PowerBI
  • Advanced data modeling and calculations using the DAX programming language

Course reading materials

Course Schedule

 

Day 1 – Saturday 7.22.17 – 6 hours (9am – 4pm)
Textbook chapters 1 & 2 Introducing PowerBI and self-service BI

This is the dashboard we created the first 2 days of class – Day1 Dashboard

Here is a link to a guided learning module to show data modeling and making charts

Assignment #1
Think about the data you use to do your work. Make a list of the tables & columns that you commonly use. Include their data types (integer, number, text, date, yes/no, time) and any constraints on the data.

Create the first version of your database star schema. Place transactional data (numerical data) in one table. Place the categorical data in the other tables. Draw the lines to connect the data table. Don’t worry no one will see your companies data.

Think about the metrics that you measure. Identify the top 5 metrics that you use to run your business. Identify where the data is coming from for these metrics, and write what the formula is in English (or Excel formula).

Here is the dashboard from the first 2 days of class – Day1 Dashboard

 

Day 2 – Sunday 7.23.17 –  6hours (9am – 4pm)
Textbook chapters 3 & 4 – Intro to calculated columns – pulling fact table data into a dimension table. Making reports, visualizing data. Performing a frequency and monetary analysis of sales data.

Here is a list of the calculated columns for day 2 and 3 – Intro to Calcuated columns


Assignment #1 Due

Assignment #2
Choose one country from the data schema and .pbix file created in class (you can use the day1 dashboard file above and import data from this AWNew file. Perform an analysis of what sub-categories and models are selling well/or poorly. Identify states/provinces that are improving/weakening. Use line charts and column charts to get an overview of the data, then make an additional chart with slicer. Take screenshots of your dashboard pages and place the cropped images into a word processing document. Add your managerial analysis – 5 page minimum.

Add a paragraph in the email you use to submit your assignment. Discuss what slicers are needed to analyze your top 5 metrics outlined in Assignment #1.

 

Day 3 – Monday 7.24.17 – 3 hours (6:30pm – 9:30 pm)
Textbook chapter 5 – More on data modeling. Building calculated columns in a fact table that can be filtered using different dimension levels. Use of hierarchies and drill down analysis. Performing a profitability analysis for product lines. Creating maps and line charts including analysis of the full data set over time.

Assignment #2 Due

Assignment #3
Using the AdventureWorks database (the AW_New data we uploaded) perform an analysis of what color bicycles (using the 3 different types of bicycles) are popular (or should be discontinued) in what regions/countries. Make recommendations as to what color bicycles should be produced more (for what country) and produced less (for what country).Take screenshots of your dashboard pages and place the cropped images into a word processing document. Add your managerial analysis – 5 page minimum.

 

Day 4 – Tuesday 7.25.17 – 3 hours (6:30pm – 9:30 pm)
Textbook chapter 5 – More on data modeling, using data and calcualted columns from the time dimension such as Age = DATEDIFF(DimCustomer[BirthDate], TODAY(), YEAR)Learning how to download, clean and use government data (Introducing the Seattle Police DataSet.). Here is the .csv file.

Be sure to change the datatype for the latitude and longitude fields.

Seattle_Police_Department_Police_Report_Incidents 7-25-17

Here is an example of a map drawn with a map – seattlepd

Assignment #4/5
This assignment double counted as the analysis needs to be in-depth. Using the Seattle Police Data (or similar data from a different city), choose one category of crime and do an analysis using the following sections a) time analysis – is the crime level (Count) in this category getting better or worse over time? b) when is the crime occurring? What day of the month, week, or time of the day? are there seasonal patterns? c) Where is the crime occurring? what parts of the city? Google the latitude/longitude an include a screenshot of a street-level picture of the locations where the crime is occurring. Take screenshots of your dashboard pages and place the cropped images into a word processing document. Add your managerial analysis – 5 page minimum.

Day 5 – Wednesday 7.26.17 – rest



Day 6 – Thursday 7.27.17  – 3 hours (6:30pm – 9:30 pm)
Textbook chapters 6 &7 – Transforming data, More custom columns and introducing measures.

Assignment #3 Due

Contoso PowerBI file used for demonstration – Contoso Sales PBI Distributed

Date DAX Formulas – Date and time DAX

Day 7 – Friday 7.28.17 – 3 hours (6:30pm – 9:30 pm)
Advanced DAX Material and custom data visualizations

Assignment #6 – Using the Contoso pbix file perform the following analysis. The Contoso management is looking to expand in-store sales in Asia! They want to know what product categories to promote and expand in what countries. Management is looking for your recommendations as to what countries are experiencing (or are poised to experience) good growth. Use # of transactions (in store traffic), # units, $ revenue, and profitability as the criteria for your analysis. Further what category of product is selling well (or is experiencing growth) in each country?

The Contoso management has limited funds for promotion and purchasing of stock to be sent to each country so the generation of a list of product categories (and perhaps specific products or models)  to send to each candidate country is important.

Here are some useful formulas to get you started.

NumberOrders = COUNTROWS(DISTINCT(Sales[SalesKey]))
NumberCountries = DISTINCTCOUNT(Geography[RegionCountryName])

 

Day 8 – Saturday 7.29.17 – 6 hours (9am – 4pm)
More charting capabilities, data manamegement and work on Final projects.  

Data Management – unpivoting Data. Click here to see NYC Data – Select Citywide Misdemeanor Offenses 2000-2016
misdemeanor-offenses-2000-2016 – Excel Working

Downloading several data files from a folder – Country Data Files

 

Assignment #4/5 Due (assignment double counted).

Day 9 – Sunday 7.30.17 – 6 hours (9am – 4pm)

Advanced DAX Material, scattergram charts, Present final projects
Ass
ignment #6, 7, 8 plus published dashboard of final project due by 8.15.17

Scattergrams and Bubble charts in PowerBI

Categorizing records in Groups

Video training materials

Data Files:

Deomonstrations:

  • Create Mfr and products table in Access. Show with no relationship, then create the one-to-many relationship.
  • Data Modeling – Relational Data ModelImplementing Data Models in PowerBI and Excel

1. Data Extraction  – Cloud, Databases, Access, Flat files, Excel Power Query or Data Extration with Power BI

2. Data Modeling – Relationships, Hierarchies, Calculations (DAX), Data Transformations, KPI’s – conditional formatting

3. Data Presentation – Excel, Pivot charts,

Importing and transforming data in PowerBI and Excel (Power Query)
Creating new columns of calculated fields in PowerBI and Excel (PowerPivot)
Creating new measures in PowerBI and Excel (PowerPivot)
Creating reports and dashboards in PowerBI and Excel (PowerView)

We will cover a PowerPivot 2013 Excel Series – and then perform the same processing in PowerBI

Here is the videoplaylist, here are the datafiles – (scroll down to PowerPivot 2013 Excel Series)

More TBD