If you were me, will you append all projects into one table in early stage and then build relationship/ measures? Or will you build measure for each of them and finally generate/summarize table in later stage?
I don't want to build everything and then regret in later stage when it's too late.
Any comment/ link/ video will be very much appreicated.
Case background:
- Company building a whole new dataset and BI report.
- 3 Departments and many teams: 3 music schools, 2 art studio, and 4 fitness club (each having common and different activities).
- Key KPI: clients engaged, hours of services, training provided (different for each project), and a happiness improvement index (Common), fitness improvement (only for physical training), etc
- Data source: Each stuidio/ club has their own excel, similar but different format.
Strucutre:
- Star Schema, fact tables around (e.g. happiness index, type of training, date table, etc)
- Fact tables to convert activities into happiness (e.g. 1 music lesson = 1 point, 1 pottery = 1.5 points, treadmill, aerobic, etc)
Foreseeable questions from executive:
- Yearly/ Quarterly/ Monthly figures on clients/ hours/ training (Organization and project level)
- Total achievement by organization (Improve in happiness/ fitness in community)
- Five years trend analysis
- All they need are high-level numbers, not complicated DAX calculation.
Thanks in advance. Any feedback will be appreicated.
