Fact Table Review

• Fact tables hold numeric facts at the desired grain
• A measurable business process tends to create events that populate a fact table
• Will have multiple foreign keys linking to various dimensional tables
• Usually will have a compound primary key comprised of a collection of the dimension keys

Dimension Table Review

• Dimension tables hold descriptive information about a business event
• Often have many columns, but relatively fewer rows
• Has a single primary key (usually surrogate) that links to the fact table
• Primary source of query constraints, grouping, and labeling

Creating a Dimensional Model

1. Facts model important business processes, so you need to start by identifying the key business processes that you are interested in analyzing
2. Once you have the processes down, work out what level of granularity of the events you might need for each
3. Then you can work out what facts you have (or can compute) from the underlying data that describe that process at the desired granularity
4. Identify what dimensions you have access to that can provide additional context
5. Create your star schema database model

Example: Course Analytics

Dealing with Timestamps

• If your events have a timestamp associated with them, it can seem straightforward to just include that timestamp in the fact table
  • This is rarely done in isolation though!
• What role is the timestamp playing? Usually it would be for query slicing, and so instead should be broken up into dimensional tables.
  • If you just care about days, a date dimension would work fine
  • If you can about time of day, you could also have a time dimension
• You can still include the timestamp in the fact table, but you would usually only ever do so if you felt a need to exactly reconstruct a sequence of events

Activity: Patient Analytics

A Wrinkle

• Suppose some patients in the previous activity changed their insurance information
  • How do we account for this in the patient dimension table?
• Highlights the importance of dealing with slowly changing dimensions (SCDs)
• These are examples of dimension attributes that might change with time (but usually don’t change that often)
  • E.g. names, addresses, jobs, job titles
• We need consistent methods of accounting for these changes in our data warehouses
  • Generally broken down by types

Type 0: Keep Original

• The first type of SCD is perhaps the most straightforward: do nothing
• Here if a value changes, you ignore the change, and just continue to store the original
• Clearly this is not always wanted, and only makes sense for particular attributes
  • Commonly attributes that might be representing the original state of something
    • E.g. original_name, original_credit_score, orignal_signup_date

Type 1: Overwrite

• This type of SCD overwrites the original values whenever a change occurs
  • If someone’s name changes, you just update the original dimensional row with the new name
• This is great for holding the current state of things, but it erases the history
• May need to ensure that any downstream consumers of the data are updated to reflect the change

Type 2: Add a Row

• In this type of SCD, a new row is added whenever a value changes
  • This row would duplicate all other columns, but hold the updated value in the SCD column
• This would invalidate natural primary keys, as they would then be duplicated, so tables using this type must use surrogate primary keys
• Generally need to add 3 additional columns to the table as well:
  • row effective date (or timestamp) - when did this row become valid
  • row expiration date (or timestamp) - when did this row become invalid
  • current row indicator - a flag (boolean or “Y/N”) indicating if this row is active now

Type 2 Checklist

• Any time a value changes in a Type 2 SCD, you need to:
  • Add a new row and generate a new surrogate primary key for that row
  • Populate that row, including the row effective and current row columns (expiry will be NULL)
  • Update the previous row, adding a row expiry and changing the current row columns
  • Ensure that newer rows in your fact table link to the newly created dimensional row (use the correct foreign key)

Type 3: Add a Column

• This type of SCD adds a new column or attribute to the dimension table when a value changes
• Generally, the new value overwrites the original column, and the old value is moved to the new column
• Sometimes called an alternate reality, as it becomes easy to filter or group by either the current state of things or the older reality
• This quickly becomes unwieldy if the dimensions are changing very often, and so is perhaps the least used of these types

More Types

• There are more types as well:
  • Type 4: Adding mini-dimensions
  • Type 5: Mini-dimension with Type-1 outrigger
  • Type 6: Adding Type-1 attributes to Type-2 dimension
  • Type 7: Dual Type-1 and Type-2 dimensions
• But these are mostly combinations or variants of the above
• Being comfortable with Types 0 - 3 is generally sufficient

Example:

• Suppose we have a source users table that tracks current username, address, and birthplace
• Such a table would likely be translating into some form of dimension table in the warehouse
• How might the dimension table change for any of the available types should one of the columns change?

Dimensional Design Worksheet

Limitations of the Terminal

• While the terminal can be incredibly powerful, you may find it cumbersome to work with in some instances
  • Terminal text editors can take some tweaking to look nice (I should post some guides on this)
  • Viewing things like images in a terminal can be very cumbersome
• As such, in some cases, it can be useful to use a graphical tool that can leverage the SSH connection
• There are many tools that vary wildly across OS, but one useful cross-platform one that many will already have is VSCode

Visual Studio Code with SSH

• The easiest way to get running with SSH in VSCode is through the “Remote - SSH” extension, officially published by Microsoft
  • If it doesn’t come along automatically, the “Remote Explorer” extension is also recommended
• On the left-side menu, you’ll see an icon for “Remote Explorer” now
• Clicking on it, you’ll see a selection of all the profiles you have set up in your SSH config. Just click on the one you want to access!
• Once it opens, you probably want to click File, then Open Folder, and then select the home folder on the remote system!
• Now you can see everything!

More!

Why Dashboards

• Depending on your role in the company, you may just be responsible for creating and maintaining the data warehouse, or you might have to go beyond to deliver on the actual business questions
• Requires a distilling of the important information out of the warehouse and into a form that non-technical people can appreciate and understand
  • If you have made your warehouse well, this should be fairly easy
• Reports and dashboards are among the most common tools that you might use to convey this information

Grafana

Accessing Grafana

• Like MinIO, a single Grafana server is running that you all will connect to
  • Accessible at grafana.advde on port 3000
• You access Grafana through a web interface, so this is one more tunnel you will likely want to create!
• Each project will belong to its own organization, which will give you full control over creating your data connections and dashboards
  • A single organization admin account has been created for each project
  • Group login information is the same as for MinIO

Data Sources

• Grafana stores no data, it only accesses it from other various sources
• An admin account needs to set this up:
  • In the left menu, go to Connections
  • Search for Postgres (assuming you want to connect your warehouse) and select it
  • In the upper right click “Add new data source”
  • Populate the necessary portions with the data to connect to your data warehouse
    • Should be host, database name, user, and password
  • Under Additional Options, set the Postgres version to the latest possible
  • Hit “Save and Test” at the bottom. If you get the positive pop-up dialog, you are good to go!

Creating a Dashboard

• Click on the Dashboard section in the left menu
• Click the giant “Create Dashboard” button, and then “Add Visualization”
• Select your Postgres data source
• Create your visual!
• You can then go back to the dashboard to create more visuals
• You can arrange and size visuals on the dashboard
• Make sure you save the dashboard!

Making a Visual

Working with Times

• Grafana dashboards generally have a time window indicator provided near the upper right
• By default, changing this will just change the scales of your axes
• It can be useful to have changes to this value dynamically affect your SQl queries, which you can do by using the variables below

Variables

• You may want to add a little interactivity to your dashboard!
  • Maybe a dropdown, or a text field for filtering?
• You can define dashboard variables that you can then use in your SQL queries to make things dynamic
• While editing the dashboard (not a specific visual), go to Settings in the upper right
• Choose the variables tab and add a new variable
• Choose a type, give it a name, and fill in any other information
• In a visualization query editor, you can refer to that value with '$variable_name'
  • Note that it needs to be in a string if it gives text values!

To be Working on!

• I am giving you the rest of the evening to work with your groups to ensure you will make next Tuesday’s business decision deadline
• As a reminder of what you need by that point:
  • Airflow taking nightly snapshots into data lake
  • Designing your data warehouse for the initial business questions
  • Moving data from the lake into a data warehouse on some timetable
  • Creating dashboards that read from the warehouse and provide context and meaning to the business questions
• You should absolutely be working on documentation while you do this, but it will not be due next Tuesday (June 17)