Any help with this would be appreciated, it's proving to be a sticky challenge as the complexity of my report increases. I have a database for a student information system, it has tables for Student Details, Enrollments, Attendance, Programs, Discipline, and Demographics, among others. I need to be able to connect all of them together in a way that everything can filter everything. I might need to know which students in the Hispanic demographics group, who enrolled any time during March 2024, at Third Street School, were absent on Wednesdays in May of 2025 for Illness, and who have at least one discipline record for lateness, but are not enrolled in a special education program. Or I might need to count how many times each student has been given a detention for using a mobile device in class, in sixth grade, on fridays, with a GPA less than 2.5. I need to generate both a chart counting the number of these students over time, as well as a list of them individually in a table along with basic demographic data and aggregated data from the above and other tables.
Demographics is a static fact table, it has no time dimension and presents things like their Name, Address, Birthday, etc, things that generally do not change and if they do still only matter in the current iteration.
Student Details is a yearly changing table of things like grade leve, gpa, language fluency, etc.
Enrollment and Programs are ranged events. A student could be enrolled for a whole year, from August 15 to June 5th, or from December 2nd to December 17th. Same thing for programs. And a student could change enrollments or programs mid-year, so they can have multiple of each per school year.
Discipline and Attendance are singular events, a student is absent on a day and that's recorded. If they are absent a whole week then they get 5 separate records. Same for discipline, each time they get into trouble for something it has a specific date it was recorded on.
In the past I've built views to make the data more connectable (every record has an school-id-month-year identifier), but it also means for report size and performance reasons I have been limited to a monthly granularity for some data that would be better handled at the daily level. And even that can be quite slow at times. I've been exploring other approaches to handling events in progress, one using measures with filters, and (without success so far) using snapshot tables that have a row for every single day of the event. The main issue with snapshot tables that convert in progress events to daily ones is that the number of rows grows very quickly, 25,000 records per day, 320 days per year, 25 years of data. 200 million+ rows per table, and there are quite a few date-range tables in our system. Meanwhile, when using measures with date filters it looks like they would just keep growing as I added more tables that need to be filtered for events in progress, and I can't seem to find a way to use this with a basic table visual to for example get a list of student ID's that meet all the filtering requirements in the time range specified, and there's probably some other caveats I'm forgetting here.
This is a very basic version of my new model, with sample data populated in. It fairly accurately represnts a microcosm of what I'm dealing with: https://www.transfernow.net/dl/20251105h78YnPIM
Any help in how to best handle this kind of interconnectivity would be amazing, I've been reviewing videos and tutorials and they all deal with models less complex than mine, so things kind of fall apart once I start trying to scale up.
