Time Frames?

Hi All

Just wanted to get your opinions on this.

We are currently building a data warehouse based on individual card transactions from scratch.

In order to do this I am having to get the data that we need from 4 different business databases assign all the relevant relationships between them all and clean the data up, either through code or by creating exception reporting for the business to data cleanse the source systems (there are a lot of user data entry errors).

I have been working on this for about 6 weeks going through each system deciding on the data that we need from each one and designing the relevant structure of the new database, I am now getting pressured by one of the business analyst to just put what we have got into tables so that we can do some reporting on it now!

This is the kinda of person who would create Weekly, monthly and other aggregated tables of the data if they had half the chance, oh and by the way this is the first time I have really designed a dimensional database.

My question is, from your experiences how long would a process like this take? I know that each project will be different but just an idea would be good

Thanks

So in 6 weeks, you've analyzed 4 different databases, created a target data structure and defined source to target mappings. Seems like a reasonable amount of time to me. You can always run reports off of your staging/ODS tables to placate the natives. The hardest part of the process is understanding the source data. Once you know the data, building the model and ETL go fairly quickly (assuming that you use tools).

oh I know in one respect I have made a lot of progress but the business does not see that and its hard getting people to understand that even though I have put a lot of work in they are no nearer to getting reporting out if it as yet due to issues that I am having to work through with the source data. They just expect us to write a query and bang the results into a table to report on and I am trying to move them away from that as this is how the have operated in the past! Guess I will get there at some point though!

It really depends on how complex and clean your source systems are to begin with. It seems like you have already done the data profiling and identified all the gaps and inconsistencies.

If this is your first stab at dimensional modeling, it would be the best time to use the Conformed Dimension approach. It is very important to define all the dimensions used in the Fact tables and identify all sources for the Dimension loads. If you have a single Master source for the Dimensions than you're in luck.

Providing an interim reporting solution is not uncommon. But be aware that once the interim reports are out, and deemed as gospel, then when the Data Warehouse finally comes online and the numbers are different, you will have a tough time convincing the Analyst that their numbers were wrong. This is really a political issue.

It's an ongoing struggle between the BI team and the business users for the short term and long term needs. It is absolutely imperative that you have a Executive level sponsor who sees the value in BI and understands the level of commitment (time and resource) for a successful BI implementation.

An average delivery time for a single business process is about 4~6 months for us. Again, depending on the complexity of your environment.