I'm making a project to track interactions. Follow my progress in these articles as I work towards a fully operational relation tracking site, SQL database, and REST API.

This first article introduces the tech stack and models the data based off of the Four-Step Dimensional Model Process in Kimball's Book: Data Warehouse Toolkit, which I will be referencing periodically later.

Four-Step Dimensional Model Process

1) Select the business process

Community leaders are often challenged to ensure everyone is included and feels like their needs are met. While for small groups a leader just has to remember who they talked to, if for some ungodly reason you have groups of +20 people and multiple people leading the community, you cannot easily ensure that everyone is being seen. To make things more complicated, some interactions are one on one, and some are within a group context. So I'm building a community tracker to make sure that leaders have access to data on whether each person in the community has interacted with the leaders.

Normally the process would involve talking with the operators of the business to understand their work and data needs, I am experienced in the business so I can skip that.

The Business Process: Track the process of interacting with community members in conversations and community events.

Business Needs

1. Provide a portal for data entry for individual interactions and event attendance between staff/community leaders and members.
2. Provide a backend database to answer the below business questions.
3. Provide a REST API so other developers can reference / extract data

Business Questions

Need answered

• Who is not regularly interacting with staff and community leaders?
• Who has had mainly negative interactions with the staff and community leaders?

Nice to have answered

• What types of interactions are there?
• What events are associated with a particular group leader, and which members are associated with those events?

2) Declare the Grain

• There are actually two transactions implicitly in the business process I outlined above. There will be one row for transactions at the interaction level, and one row for transactions at the event level.

Grain 1: One row per interaction between a student and staff
Grain 2: One row per event held by staff

3) Identify the dimensions

In order to map out dimension tables, we answer the who, what, where, when, why questions.
Who?

• The leader
• The student What?
• The event in which the interaction occurred (grain 1)
• The type of conversation / interaction (more on this later)
• Whether it was a recurring event (grain 2) Where?
• The location of the event / interaction When?
• The date of the event / interaction Why?
• N/A How?
• N/A

At this point, though, we have to recognize the issue with this type of data. Data like this isn't generated by a machine, but instead has to be inputted by a staff member. You can contrast this data to data like purchases in retail, salesforce data, etc.

Since this is the case, it's best to limit the amount of user input to the minimum viable product, at least at first. The business doesn't benefit from a product that their staff doesn't use.

4) Identify the facts

Interactions Fact Table

• Student Key
• Staff Key
• Conversation Type
• Event Key
• Location Key
• Date Key
• Negative or Positive Interaction

Events Fact Table

• Staff Key
• Attendance (Student Keys)
• Event Key
• Date Key
• Location Key
• Negative or Positive Interaction
• Conversation Type
• Recurring Event? (Y/N)

Now that these questions have been answered, we can create a sample diagram of how these fact and dimension tables relate:

Finally, I'll just quickly outline the tech stack for this project:

Tech Stack

• Python language
• Flask framework
• SQLite database

These components, already used in the earlier tutorial, should be all I need to make this project happen.

That's all for now, I'll be back with updates as the project progresses forwards!