Agile BI - Data Model

In Agile approach in DW, while we are designing Data Models and DB tables, should it cater for entire requirement or for sprint wise requirement? If Sprint wise requirement, how to incorporate it within sprint time?

It should cater for the sprint, but bear in mind future requirements.
So I'm about to start an Iteration(sprint), where I lead the team doing facts, and another team does the dimensions. In Iteration 1, we only load 4 dimensions and have those available in the fact table. In subsequent iterations we will add additional dimensions to the fact table, and create new fact tables.

So for iteration 1 we have 1 fact and 4 dimensions and are good to go.
But for iteration 2, we add 2 more dimensions and join to same fact. In this case we need to backfill the fact again right? And this step repeats for all iterations. Is this the right way to proceed?

Yes.
You will need to completely reprocess the fact table each iteration.

It should be noted that you can't create a fact table until the dimensions that define the grain of the fact have been created. Only other dimensions can be added to the table later.

Dave Jermy wrote:It should be noted that you can't create a fact table until the dimensions that define the grain of the fact have been created. Only other dimensions can be added to the table later.

Not really. The fact load process should accomodate missing dimension rows (finding new natural keys) and create dimension rows as needed (inferred dimension). One should be able to develop and test a fact load process without prior completion of the dimension loads (althought having real dimensions certainly helps).

DataWarehouse wrote:So for iteration 1 we have 1 fact and 4 dimensions and are good to go.
But for iteration 2, we add 2 more dimensions and join to same fact. In this case we need to backfill the fact again right? And this step repeats for all iterations. Is this the right way to proceed?

No. That's silly. Agile is suppose to help development, not hamper it.

I am assuming you have a full design. Sure, if you have implemented a fact table and a few years down the road you find the need to add dimensions, then a full reload is an option. But reloading and rebuilding during initial development all because of a methodology would be a really good reason not to use the methodology.

A fact load process reads a staging structure that contains natural keys and measures. One step is to convert the natural keys to dimension foreign keys. The fact process should be coded to do this for all dimensions, wither they have been loaded or not. Even if a dimension has not yet been designed, there should be a stub table containing a PK column and a NK column to support the fact load process. When the fact load encounters a natrual key that is not in the dimension table, it should create a dimension row for that natural key (inferred dimension row) and use the new surrogate PK in the fact. The fact process when finished, would really be finished. It will load and properly reference dimensions regardless of the state of the dimension load processes. No reloads or rework is necessary.