Agile methodology in Data Warhouse Project? How to go ahead

We have a web application which will collect users transaction data (Purchase information - retail). Now we are planning to build the data mart to build reports on top of it.

The application is not yet developed and will be developed in near time.

The problem is we are not yet clear how to go ahead with data mart development part. Should we create 1 fact table and 2-3 dimension table for the time being to meet initial reporting need and going forward add the dimensions.

However, If we follow this approach we would need to build the fact table for each version/release.
Since we no clear view of dimensions and facts at this point of time should we build the ODS to meet the reporting requirement?

Just wanted to know what you guys think is best approach to meet such a need.

Make it up? I don't know how to build a dimensional data model from a source database that does not exist. With agile you don't get any documentation and you do get constant refactoring.

Yeah! Thats the main issue how to determine data to be analyzed when there is no data. However client is very keen on building DW model from the day one of application development.

Yes it is a tough situation however all is not lost.

If you follow the modelling steps you will find that you can create a reasonable dimesional model. The application supports a business process. That would be your start. It should give you your subject areas. It would be help full if they have process diagrams specially in something like a BPMN. It will also give you some idea the sorts of analytics that might be of interest.
the Application database schema and the data types along with the process diagram and business requirements documents and functional requirements documents should give reasonable idea about your dimensions and facts. You can even get some idea of the cardinality of some of the attributes from design of drop down lists and menues and make decision on the attributes of the junk dimension.

However I would trail the developement team by some weeks as often there are missing attributes that are discovered during the development.

It is very risky trying to build a dimensional model for source that is being developed at the same time. However if you have good design methodology and Solution design documentation you should be able to mitigate the risk to some degree other wise I would highly recomand to hold back unitl the design of the application database has settled to some degree of confidence.

I have followed this twice and had some measure of success in first attempt at a reasonable dimensional design and very successfull on the second attempt. The key was the buiness process design and diagram. I found the detailed BPMN diagrams of the business process that we put together and validate with business were crucial to our success for both the application and Data mart design. We had to change the reports a litttle later but not the dimensional model.

I hope this helps.

kuldeepchitrakar wrote:
We have a web application which will collect users transaction data (Purchase information - retail). Now we are planning to build the data mart to build reports on top of it.

The application is not yet developed and will be developed in near time.

The problem is we are not yet clear how to go ahead with data mart development part. Should we create 1 fact table and 2-3 dimension table for the time being to meet initial reporting need and going forward add the dimensions.

However, If we follow this approach we would need to build the fact table for each version/release.
Since we no clear view of dimensions and facts at this point of time should we build the ODS to meet the reporting requirement?

Just wanted to know what you guys think is best approach to meet such a need.

It is very interesting and most favourable situation for data mart development. Gartner research indicates that about 90 % of the BI projects don’t deliver tangible benefits. Measuring benefits is not an easy job and most of the time perception plays a great role in establishing them.

To get most benefits from BI initiative is to add value to business by aligning BI and Business strategies (in turn business processes BPMN), addressing business pains in reporting & analysis and improving data quality in transactional systems.

Since your transactional system (source data) is not yet ready / in development, there is an opportunity to perform above tasks which will render an excellent dimensional model.

Once the dimensional model is established, it is easy to adopt agile development to minimise implementation risk.

I have adopted this integrated approach in many of my projects. Currently I am delivering a release (one dashboard, 2 to 4 analytical reports) after every 15-20 weeks. Isn’t it amazing in BI world?

Good luck

What is boils down to is: you guys need to be involved with the business to figure out what the new system is supposed to do. At some point, interfaces are going to be designed. This can and should be done well before the application system is complete. You should be able to develop a credible design by then. You just need to tweek things every now and then to stay in sync with the application design.

I had a similar experience twice before as well. One was implementing SAP and the other was implementing Oracle Financials. Both had documented interfaces that outlined what data they contain. It is enough information to get a pretty good idea of the data you will need to deal with. In the case of the SAP implementation, they did not finalize their configuration until 6 months before production. On our side, 90% of the work had been completed at that time, based on standard configuration information. We made the necessary adjustments and we initiated UAT a month later. The data warehouse was ready well before the operational system was released.

The primary reason we were sucessful is we were in close contact with the business and had an understanding of how they were planning to configure the application system. We designed with this in mind and hit the target most of the time.