[SQL] Creating Dims and Facts Assistance [closed]

i got this source table:

CREATE TABLE staging_cet_events (
id INT PRIMARY KEY, -- Event code
timestamp DATETIME, -- Event submission time
user_id INT, -- User code
verb VARCHAR(255), -- Action performed
object_id INT, -- Object code
school_id INT, -- School code
lo_id INT, -- Questionnaire code
session_id INT, -- Session code
product VARCHAR(255), -- Product
context_referrer VARCHAR(255), -- Referrer page
user_role VARCHAR(50), -- User role
modified_date_id DATE -- Daily date
);


And I got this measures to build:

1. Number of teachers who logged in during the past week – absolute number, percentage change from the previous week.
2. Number of students who logged in during the past week – absolute number, percentage change from the previous week.
3. Distribution of sources of logins to Matach websites (from where users arrive outside of Matach websites) in the context_referrer field.
4. Hourly distribution of logins for the past month.
5. Definition of an active teacher and active student based on the number of logins in a month and calculation (more than 4 logins in the last 30 days): a. Percentage of active teachers in the past week – absolute number, percentage change from the previous week. b. Percentage of active students in the past week – absolute number, percentage change from the previous week.
6. Distribution of time spent: a. Distribution of time spent at the SESSION level. b. Distribution of time spent at the PRODUCT + User level.

How my Dims and Facts should look like in order to be able to provide this KPI's in a Tableau report?

Thanks.

With some help from Chat gpt i was able to create some Dims AND Facts but it got all mixed up and not very understandable.

Continue reading...