As a guy that has done a lot of data warehouse work, I can say with confidence that the hardest part is building a road map of where you want to go with your data warehouse. If you get too detailed you fall into analysis paralysis, if you aren’t detailed enough you end up with a mess that doesn’t really answer all the questions you hoped to ask of your data warehouse.
I choose to follow the methodology described in the book Agile Data Warehouse Design (you can read more about it here). The Agile Data Warehouse (ADW) methodology is very collaborative. We design the data warehouse together (at a high level) in a series of sessions that involve both the business and the tech teams. I’ve found those sessions go better when I have a few ideas to bring to the table before we start. To generate those ideas I sit down with a legal pad and a fresh pen for a little brainstorming. I find that it’s too easy to get distracted if I try to work through this brainstorming on my laptop, pen and paper is best for me.
Time to brainstorm
I sit down and I try to fill as many pages as I can by asking (and answering) the question WHO does WHAT? Let’s imagine I’m building a data warehouse for a school … my brainstorming might look like this:
- student (WHO) takes (DOES) test (WHAT) or maybe teacher (WHO) records (DOES) test grade (WHAT)
- student (WHO) participates (DOES) in Extra Curricular Activity (WHAT)
- student (WHO) eats (DOES) a meal (what)
- teacher assigns disciplinary action
- mechanic maintains school bus
I would think about every function (business process) of a school. I then work through the things students and teachers do on a daily basis (assignments, tests, meals, after school activities, continuing education (for teachers). I would try to think through all the other things that happen at a school (librarian orders books, librarian processes book requests, nurse administers health screenings, attendance officer reviews absences) .
Better now than later
I go through this process because it gives me an idea of prompts I might use when I am leading the group sessions with the tech and business teams. It would be embarrassing if somehow we built out a data warehouse for a school that didn’t consider extra curricular activities or disciplinary actions, but if the people at the school that are most focused on those areas aren’t represented in the planning we do up front no one might think ask about these areas until much farther down the road. The more complete your picture of the data warehouse is (at a high level), the more successful you will be in implementing it.
I’ll expand on these ideas a bit in future blog posts. Until then, here’s a little homework for you. In the comments, share a couple examples of Who Does What from your industry. I’d love for people coming to this post in the future to see a long thread of examples from different industries to inspire them.