DW Precision & Expectations

[alpinestart]

I'm faced with a situation that is really a fundametal difference is thinking. Our CIO feels a data warehouse is for directional reporting, trending, high level view into the business. Many users expect it to be a detail view at the transactional level and expect it to tie to the penny to source G/L systems.
Of course the CIO "accepts" a certain level of variability in the data (<@1% is fine with him).

So my question is - is it realistic to build a warehouse to match source system to the penny? Should we strive for this?
I can see arguments for both sides and would like to hear others people's opinions and experiences.
Of note: this is NOT a financial, gl data warehouse per say. it mainly a sales dw.

thanks

 

[MDXer]

The answer is it should do both. THe DW needs to support the needs of your users. The main difference that I see is the CIO would be working from various aggregate tables while the the other users would be dealing with the atomic data grain. It takes more effort in design to deliver both but in the end would be easier.


In past projects I have delivered DWs that reported nothing but trends and market share. My current project does trend and share but also foots to the penny what the OLTP systems report. This isn't always easy but is needed.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[teccum]

It is possible to build a DW to support transactional level analysis and aggregatable analysis. But it requires a lot of effort to maintain over a period of time.

If you forsee your data warehouse to expand considerably over a period of time then I would suggest to maintain a seperate environment like an ODS and configure the details level users to this data store and configure the high level picture or analyst users to the dimensionally organized data warehouse.

By this way it is easier to maintain and mainly satisfy the users performance requirement which would really require over a long period of time.

Yes, their is considerable of investment on this approach too but it is worth taking for a longer benefit.

2 cents
teccum

 

[alpinestart]

Year-over-year comparisons show the DW is within 1% of the source gl. Again the requirements for this where around high level decision making, trending, etc.

Does anyone know of a source for an industry standard of DW to source data variance and level of acceptability?

 

[johnherman]

The degree to which the DW varies from the source system could be user or department specific. Our accountant types want things to the penny. Other groups such as marketing are satisfied with accuracy to the thousands.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[cumbers5306]

the best thing would be , to gather the information who the final users would be and gather their requirements, and get the sample reports built during the initial discussion sessions. Once you get their expectations, then the level of detail, that is captured and the granularity and the precision of data can be defined. there is some cost associated to the precision, make them understand that, and they will be in line with your view . everyone is happy