Re: Date Concepts

In article <E0HG7u.Gns_at_eskimo.com>, rdshaver_at_tscnet.com (Rick and Debbie Shaver) writes:

>Subject: Date Concepts
>From: rdshaver_at_tscnet.com (Rick and Debbie Shaver)
>Date: Thu, 7 Nov 1996 05:11:16 GMT
>
>I work for a small health insurance company in the Northwest. We are
>in the process of designing a data warehouse/operational data store.
>Our consultants recommended a data store at first because we kept
>saying we need detail information. We also need history information,
>which they say is a data warehouse.
>
>According to them, a traditional data warehouse is just a series of
>current snapshots of the data (time slices). That's well and good,
>except in the insurance business, a subscriber may call up on the 15th
>of the month and say cancel my insurance as of November 1st. So we
>cancel him effective 11/1. If we went with a traditional dw, the
>snapshot on 11/1 would show him active, the snapshot on the 15th would
>show him terminated on the 1st.
>
>In this scenario, if a user decided to do some reporting and chose the
>11/1 time slice to report on, they would be looking at incorrect data,
>because actually there was one less person effective on that day. But
>that information wasn't captured till the 15th.
>
>So how do you resolve this?? I'm sure we aren't the only ones that
>have crossed this bridge. Do you go back and correct the 11/1 time
>slice?? (I understand that's taboo).

You will need to ensure the "cyclicity" of your data warehouse processing matches the data "currency" requirement. That is, if your reporting/query requirement mandates that FIELD-X must be accurate within one day, then you'll need to source that field each day. QED. This is a problem that is more prevelant in ODSs because of the tactical-type reporting done against them.

Another usually-less-desirable solution is to create a DW (not ODS) architecture and drill through to the production database when that kind of currency is needed.

>
>I also have another question on this time slice thing. Say I have
>table that contains 12 months of summarized claims payments for each
>doctor. Each record has 12 buckets, one for each month of the year.
>(I understand this is the kind of thing you want in a DW). Each month
>you plop the new summarized amount into the appropriate bucket. So in
>the dw time slice for Jan you capture the Jan bucket. On Jan 15 (the
>second time slice) do you capture the Jan bucket again?? Then you
>have redundant data if you try and do any trending analysis through
>the time slices. What about on Feb 1st, do you capture the Jan and
>the Feb bucket??

If a running total is important then re-source all of the month throughout the month. If you don't want running totals, then only source after the month is closed, e.g. source all of Jan on Feb1. It's all driven by the users' information requirements.

>
>I guess I need to see some practical examples of this? If I can't
>figure out how this works, I sure can't explain it to the users!
>
>Any help or advice will be greatly appreciated!
>
>Debbie Shaver
>rdshaver_at_tscnet.com
>

Good luck!

Cheers,
Doug Laney, Regional Consulting Manager
Prism Solutions Received on Fri Nov 08 1996 - 00:00:00 CET