Recently somebody asked me: why you are saying you are building a data mart? Whatever you are building, is being built in the application database, even thought it happens in the “staging area”, designated for the “reporting” stuff.
And then I started to think about what we really mean when we are saying “we are building a data mart”. Yes, you can say, that building a data mart means building all those fact and dimension tables, and creating indexes, and storing aggregates… but the most important is to map the OLTP data structures to the reporting structures, to define, which data from the application tables we need and how we want to process it in order to make reporting fast, easy and efficient. Until we define this mapping, there is no data mart.
Does this mapping belong to the application? I do not think so. Why – because the application can be 100% functional without all these staging schemas. The only purpose and the only reasons for them to exist – to provide the “service” for the data mart. The application technically “does not know” about the reporting structure, besides, the reporting structures actually may be different for different reporting needs.
How this mapping is done, is a data mart “responsibility. Although I may end up doing it anyway, and “I” means “an application database” 🙂