Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to design a data warehouse using this example scrubbed data. I need to declare the "grain". I know its the lowest level of detail in the fact table, but I am having a hard time determining it. Any help would be appreciated. (Sorry about the table column alignments - i tried my best to get them aligned)

TABLE: Banks

Bank_ID  Bank_Name  ABA_Code        Bank_City   Bank_State   Recip_Zip
1       Bank of NY  102938475    Albany        NY       12204
2       Bank of NJ  283746591    Englewood  NJ      07631
3       NY Federal  564738290    Albany     NY      12204
4       NJ Federal  123987475    Englewood  NJ      07631
5       NY Credit   654321789    Rochester  NY      14602

Banks consist of one or more accounts.

TABLE: Accounts

Acct_ID Acct_No          Bank_Id    Title
1            102938458  1   Subscriber Payment Acct
2            918273645  1   Provider Payment Acct
3            228374659  2   Zero Dollar Acct
4            374658291  2   BCB Provider Acct
5            234567891  3   BCB Subscriber Acct

Accounts are where the payment checks o electronic payments for drawn from.

TABLE: Recipients

Recip_ID   Recip_Type   Recip_No    Recip_Title   Recip_City    Recip_State   Recip_Zip
1         P          100001 Art Gains     Albany    NY       12204
2         S          220202 Mick Tean     Englewood NJ       07631
3         S          103003 Jake Adam     Binghamton    NY       13905
4         P          404004 Jane Doe         Rochester  NY       14602
5         P          977005 Jac Angel     Belvidere NJ       07823

Recipients (of a payment) can be Providers (P) or Subscribers (S)

TABLE: Payments

Payment_ID  Acct_ID Check_No    Check_Amt   Check_Date  Recip_No
1   1   102938475   6919.70 06/17/2011  100001
2   1   102938476   23.50   09/09/2011  220202
3   2   459287654   3.12    01/21/2012  404004
4   2   459287655   17345.01    07/07/2012  977005
5   3   102938476   300.47  02/11/2013  103003

Payments are master records and are the highest level of transaction. They consist of one or more claims.

TABLE: Claims

Claim_ID    Payment_Id  Claim_Amt   Claim_Date  Claim_Source
1   1   3459.85 06/01/2011  AA
2   1   3459.85 06/15/2011  AA
3   2   23.50   01/02/2012  AA
4   3   100.00  01/28/2012  BB
5   3   200.47  02/01/2013  BB

Claims consist of one or more Line items (services rendered by a provider)

TABLE: Claim Line Items

Item_ID Claim_ID    Start_Date  End_Date    Proc_Code   Proc_Desc   Billed_Amt
1   1   05/01/2011  05/01/2011  30.01   CRM NO  1230.25
2   1   05/01/2011  05/01/2011  30.02   ARB DD  1950.00
3   1   05/01/2011  05/01/2011  30.12   DSJ 01  279.60
4   2   12/15/2011  12/15/2011  99.98   LLL SS  729.95
5   2   12/15/2011  12/15/2011  99.99   IT2 GG  2729.90

Line Items are (services provided) the lowest transactional level detailing the procedure and cost.
share|improve this question
    
You need to familiarize yourself with the concept of cube data. That where you can take grains of fact for a particular dimension and aggregate them over time. In this case the dollar amounts would be a good place to start. e.g. Billed_Amt by Proc_Code by Month for the last 12 months. –  randy melder Feb 11 '13 at 23:32
add comment

1 Answer

up vote 4 down vote accepted

To quote Ralph Kimball, "declaring the grain" is:

saying exactly what a fact table record represents

You need to choose which entity from you list above represents the grain. You could choose any of them, however claim_line_items looks the best candidate to me as this is at the lowest level (it is the only table with no children).

If you make claims your grain, then it won't be possible to report on what the individual line items are. This may be what the business wants to track (and they don't care about line items). However, it'll be very difficult to change the grain to line items at some point in the future if they change their mind. Whereas you can make the grain line items and provide aggregations which show summaries at the claim level.

For a more detailed discussion about this (again by Kimball) go here.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.