Fact table granulartity to small or just built wrong

View previous topic View next topic Go down

Fact table granulartity to small or just built wrong

Post  dantolz on Thu Jun 18, 2015 8:35 am

I have a fact table thats starting to get out of hand and I'm wondering if its that granularity or the table is just built wrong. The fact table stores our award information and awards are stored in years. The award has a start date and and end date. So instead of putting the start date and end date in the fact table (referencing the dimension table for dates) they (the developers) have stored every single month in the fact table. My solution was to store the start_date and end_date only and use date calculations for reporting.

So an award will have the following structure in the fact table: All this data is duplicate except for the DATE_KEY.
CREATE TABLE FACT_1
(
AWARD_KEY NUMBER(38),
DATE_KEY INTEGER,
PROJECT_PERIOD_BEGIN_KEY INTEGER,
PROJECT_PERIOD_END_KEY INTEGER,
BUDGET_PERIOD_BEGIN_KEY INTEGER,
BUDGET_PERIOD_END_KEY INTEGER,
PRIMARY_INVESTIGATOR_KEY NUMBER(38),
CO_INVESTIGATOR_KEY NUMBER(38),
AWARD_TYPE_KEY NUMBER(38),
ACCOUNT_KEY INTEGER,
DIVISION_SUB_KEY NUMBER(38),
DIVISION_MASTER_KEY NUMBER(38),
DIRECT_BUDGET_AMT NUMBER,
INDIRECT_BUDGET_AMT NUMBER,
CHANGED_DATE DATE,
MAIN_SPONSOR_KEY NUMBER(38),
AWARD_BUDGET_KEY NUMBER(10),
PRIME_SPONSOR_KEY NUMBER(38),
IS_MASTER CHAR(1 BYTE),
CR_CUT_PERCENTAGE NUMBER(10,2),
IS_EAR CHAR(1 BYTE),
NO_COST_EXTENSION NUMBER(10),
PRE_AWARD_DATE DATE,
IS_CR_CUT CHAR(1 BYTE),
FA_RATE VARCHAR2(5 BYTE)
)

SET DEFINE OFF;
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781620, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781621, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781622, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into RS_WAREHOUSE.TRANS_FCT_LOOKUP4
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781623, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781624, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781625, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781628, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781629, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781630, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
Insert into FACT_1
(AWARD_KEY, DATE_KEY, PROJECT_PERIOD_BEGIN_KEY, PROJECT_PERIOD_END_KEY, BUDGET_PERIOD_BEGIN_KEY,
BUDGET_PERIOD_END_KEY, PRIMARY_INVESTIGATOR_KEY, CO_INVESTIGATOR_KEY, AWARD_TYPE_KEY, ACCOUNT_KEY,
DIVISION_SUB_KEY, DIVISION_MASTER_KEY, DIRECT_BUDGET_AMT, INDIRECT_BUDGET_AMT, CHANGED_DATE,
MAIN_SPONSOR_KEY, AWARD_BUDGET_KEY, IS_MASTER, IS_EAR, NO_COST_EXTENSION,
IS_CR_CUT, FA_RATE)
Values
(253392309, 594781631, 594781233, 594780806, 594780199,
594781635, 253587533, 253587533, 594765981, 594744391,
594809096, 594809096, 483.068493150685, 205.586301369863, TO_DATE('06/18/2015 00:32:26', 'MM/DD/YYYY HH24:MI:SS'),
253606914, 595123047, 'Y', 'N', 2,
'N', '48.5');
COMMIT;

dantolz

Posts : 2
Join date : 2015-06-18

View user profile

Back to top Go down

Re: Fact table granulartity to small or just built wrong

Post  ngalemmo on Thu Jun 18, 2015 11:39 am

Ok. What are your concerns?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Too many rows

Post  dantolz on Thu Jun 18, 2015 2:06 pm

We are over 30 million rows right now and growing. I know, its a warehouse right but the design seems wrong to me and I'm wondering if a fact table can "in fact" have so much duplicate data.

dantolz

Posts : 2
Join date : 2015-06-18

View user profile

Back to top Go down

Re: Fact table granulartity to small or just built wrong

Post  ngalemmo on Fri Jun 19, 2015 10:47 am

Either model will work. Wither the change will create more complexity depends on the types of queries that are performed.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact table granulartity to small or just built wrong

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum