Agile BI - Data Model

View previous topic View next topic Go down

Agile BI - Data Model

Post  DataWarehouse on Wed Aug 03, 2011 5:13 am

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?

DataWarehouse

Posts : 3
Join date : 2011-08-03

View user profile

Back to top Go down

Re: Agile BI - Data Model

Post  John Simon on Wed Aug 03, 2011 6:35 am

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Agile BI - Data Model

Post  DataWarehouse on Thu Aug 04, 2011 2:00 am

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?

DataWarehouse

Posts : 3
Join date : 2011-08-03

View user profile

Back to top Go down

Re: Agile BI - Data Model

Post  John Simon on Thu Aug 04, 2011 2:26 am

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

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Agile BI - Data Model

Post  Dave Jermy on Thu Aug 04, 2011 5:10 am

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

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Agile BI - Data Model

Post  ngalemmo on Thu Aug 04, 2011 11:13 am

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).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Agile BI - Data Model

Post  ngalemmo on Thu Aug 04, 2011 11:24 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Agile BI - Data Model

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum