The first thing to do in any project of this nature is to set scope. That's mandatory. To do this, I first look at the value chains in question. It's unusual to do do two in one project, but is possible if there is a lot of dimensionality in common (e.g. stock and sales in a retail environment share the same dimensions). Normally, there's one value chain, leading to the load of one Fact table. Three would be too much.
Then I apply a Digital Equipment architectural method, establishing certainty/uncertainty in the Business, Systems, Technical and Product layers of the total Architecture (all four layers are necessary). This allows me to control and manage risk, as well as clarifying the constraints and areas that need further work.
The picture on the left is from an intensive Architecture and Design session I ran for one of Phoenix' clients, where we scoped the project in the first two days of the assignment. A & D continued for two solid weeks.
In the Lifecycle diagram, you'll see separate boxes for Design and Build. In practice, the lines don't really exist. That's because with modern tools like Warehouse Builder, Analysis is Design is Model is Build.
However, they are still conceptually separate, and the dimensional modelling phase which comes next is possibly the most crucial. It's an iterative process, and as design gets firmed up, it gets entered into the modelling tool.
That leads neatly into Extract, Transform and Load design, and there's a picture on the left that shows a part of that process. You'll note pieces of printout. These are record layouts from the source system. You'll also note hand-drawn entities and other relationships. With the dimensional structure in place, and the source system data requirement established, we can import the source system definitions, and then ETL becomes a matter of connecting the dots.
The actual physical design of the ETL is done in the development tool (of which more later).
The other architectural aspects are not shown here, but they are done in parallel with this main line of development. We use detailed checklists to ensure no item is forgotten.
