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.

hi..

I would like to know if its possible to check for timeframeset(in10minute) or timeframeset(in30minute). As much as i know its only possible for 1,5,15 minutes. Can anybody guide me through this.

Thanks

Payroll check calculator

The time demands are unreasonable, but there are things you can do.

One is to communicate better. Let your boss and the business know what you are working on, what the issues are and next steps, etc... Push some of the issues back to the analyst and get him/her involved in working out solutions.

Don't be concerned with data quality or source consolidation. It is easy enough to build dimensions that can handle multiple data sources, retaining unique rows for each source, that can be integrated later. Raise data quality as a risk but don't spend much time worrying about it. If you implement the load processes right, it should be easy to deal with later (at least from a DW point of view). The thing to realize is data quality is rarely seen as an issue, particularly in immature organizations, until users begin using the data. Trying to deal with it up front is a no-win... it is simply viewed as a delay by the business. In addition, the business usually will not put any resouces into resolving it, making it an 'IT' problem. So, you are far better off not doing anything, let them see how the data really looks, then garner support for resolving the issue... with commitments from the business to provide resources (i.e. data stewards, etc...).

ngalemmo wrote:Raise data quality as a risk but don't spend much time worrying about it. If you implement the load processes right, it should be easy to deal with later (at least from a DW point of view).

I have followed what Nagalemmo just said in a few past projects, although feeling not 100% confident. Now I have a guideline and stick to it whenever possible.

Why don't you need worry about data quality problem, I mean in the source system, at this stage? Because the current system has it as well and the new system need to match the result even if it's got errors. However you need to focus on something that is highly valuable, but can't be achieved by the old system.

mazirra wrote:I would like to know if its possible to check for timeframeset(in10minute) or timeframeset(in30minute). As much as i know its only possible for 1,5,15 minutes.

I guess you are talking about time unit in the time dimension in addition to the date. You could have a time dimension with grain of 1 minute and some relevant attributes for 5 and 15 minutes at aggregate levels, similar to week, month and year in the date dimension.

You always need to be concerned about data quality, but you also need to be realistic. Depending on the maturity of the organization, trying to push quality up front is a real challenge. It requires a significant investment of people, money and time to do right (something the organization in question...or at least the analyst... has demonstrated there is no chance of such a committment). Unless you can show clear value in doing so, you are better off leaving that fight for a later time. The primary goal for the data warehouse is to accurately reflect the source systems. Once the business begins using it, and they begin to see the issues that data quality present, you can make a case to begin a quality program... provided you can show what the DW is presenting is accurate.

I always try and load everything in the data warehouse - in other words - initially one can ignore the quality of the source data - AS LONG as you bring everything into the data warehouse - simple example:

Let us say you have a fact table called FactTransactions and one dimension referenced by this fact table called DimBranch.

Now let us assume that there exists 100,000 records in the source transaction system - your Fact table should also hold at least those 100,000 records - EVEN if the measures are NULL / branch is NULL etc. - you then default the measure to something acceptable by the business like -1; 0 etc. AND you create an unknown branch ad link all the transactions to the unknown branch.

At least then when you do control checks the # of records should match and the measure totals should at a minimal balance - otherwise you are losing source records during your ETL process - which i have found reduces the user's confidence in the data warehouse dramatically.

MikeGB wrote: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

The time frames to be determined based on the implementation methodology (e.g. water fall or Agile), scope (one report or one data set?), business rules indicating the complexity in transformation and last but least is the data quality. In my view locking into a timeframe without full understanding of these dimensions may be risky.