Re: Date Concepts
Date: 1996/11/14
Message-ID: <01bbd1c6$d1fd7880$24a99ecd_at_FOX0802.LIPMAN.COM>#1/1
Rick and Debbie Shaver <rdshaver_at_tscnet.com> wrote in article
<E0HG7u.Gns_at_eskimo.com>...
> 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).
>
> 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??
>
> 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
>
>
I am a programmer currently working on using Oracle Designer 2000 to design
an Oracle database to replace the legacy system I wrote for health
insurance administration. My colleague, Noah Hart, and I have had a hard
time asking questions, let alone getting answers, regarding these "date
concepts." Consultants, software vendors and our various users tend to
offer holistic answers ( data warehouse, keep the legacy ) our to miss the
scope of the problem ( "Oh, you want to capture history!" ). It appears
that we will need to solve this problem ourselves.
We have come to some conclusions regarding our business and the type of
database that may work:
1) Time is a primary entity relationship used for primary business
functions.
2) There are many dimensions of time for a single transaction.
Date posted Date incurred Date paid Date received
3) Reporting is a primary function that must include full detail. (A check is a report on special paper.)
4) We don't want to write any more BASIC code.
Whether certain reports can be better accomplished using data warehouse/OLAP technologies remains to be seen. The Oracle Spatial Option is rumored to handle spans of time ( foreign key date matches a range in the master key), but at what cost we don't know [ Has anyone used it for this?]. Designer 2000 apparently has no provisions for these relationships
At this point we are looking at manually refining the the database design to include custom triggers and constraints to maintain date-based relationships and many, many indexes. Our current ISAM/BASIC files have as many as 13 indexes.
Good luck,
Douglas Flegal
Applications Developer
Lipman Insurance Administrators, Inc.
Received on Thu Nov 14 1996 - 00:00:00 CET