Estou tentando projetar um data warehouse usando este exemplo de dados limpos. Eu preciso declarar o "grão". Sei que é o nível mais baixo de detalhes na tabela de fatos, mas estou tendo dificuldade em determiná-lo. Qualquer ajuda seria apreciada. (Desculpe pelos alinhamentos das colunas da tabela - tentei ao máximo alinhá-los)
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.
