One Fact table having records at different granularity level

View previous topic View next topic Go down

One Fact table having records at different granularity level

Post  jjagadish on Thu Jan 21, 2010 7:52 am

Hello,
My question is just validation for an approach that we followed for one of our client requirements.

Based on the requirement the FACT table should be constrcuted by the records from 2 Source table A and B. The structure of the table is as follows..
Table A
PK1 PK2 PK3 TOT_AM

Table B
PK1 PK2 PK3PK4 AMOUNT

Table A is the Parent table for Table B and maintains a One to Many relatioship.

Due to the reporting requirement we got from the client it was decided that records from both the source tables have to loaded in the samer FACT table using a Union ALL, i.e. the extract SQL for this FACT table will be as follows..
SELECT PK1, PK2, PK3, NULL AS PK4, TOT_AM, NULL AS AMOUNT FROM TABLE A
UNION ALL
SELECT PK1, PK2, PK3, PK4, NULL AS TOT_AM, AMOUNT FROM TABLE B JOIN TABLE A ON A.PK1 = B.PK1 AND A.PK2 = B.PK2 AND A.PK3 = B.PK3


The FACT table will have a Surrogate Key as a Primary key.

As you can see from the above sql, we are loading 2 sets of records with different granularity level into the same FACT table. Even though it will help in achieving the reporting requirment I doubt the validity of this approach. For me, all the records in a table should be of the same granularity.

Please let me know your thoughts.

jjagadish

Posts : 2
Join date : 2010-01-21
Location : India

View user profile

Back to top Go down

I totally agree

Post  DanColbert on Thu Jan 21, 2010 8:56 am

I totally agree with your misgivings. It will cause a world of pain for you if one fact table contains two grains of data. It's just a bad idea.

Is "[Table A].TOT_AM" field just a rollup of "[Table B].AMOUNT"? If it is, you don't need to store that value anyway - store the grain data and whenever you need the TOTAL_AM, you can get it with a simple GROUP BY query.

I really can't think of any reason why you'd store the TOTAL_AM separately if it's a simple sum of AMOUNT, but I may be missing something. It's so easily attainable. Let me know if I'm missing something obvious.

I hope this helps.

Dan
avatar
DanColbert

Posts : 11
Join date : 2009-02-03
Age : 48

View user profile

Back to top Go down

Anothe amount in table A that cannot be calculated

Post  jjagadish on Thu Jan 21, 2010 9:15 am

Thank You Dan, for your quick response..

Apart from the TOT_AM field there other amount fileds in TABLE A which cannot be calculated from TABLE B. One approach that I thought was to bring down the records in TABLE A to TABLE B level using a query like the one below..

SELECT A.PK1, A.PK2, A.PK3, B.PK4, AM, ANOTHER_AM FROM TABLE A JOIN TABLE B ON A.PK1 = B.PK1 AND A.PK2 = B.PK2 AND A.PK3 = B.PK3

But the problem here is that the field ANOTHER_AM will be repetitive in multiple rows and when creating a report it will sum up (As we follow a standard of SUM for all measures in BO universes). This will result in wrong results in the report.

The requirement for reporting is as below..

PK1PK2PK3TOT_AMANOTHER_AM
PK1_VALPK2_VALPK3_VAL1000500

PK1PK2PK3PK4AM
PK1_VALPK2_VALPK3_VALPK4_VAL700
PK1_VALPK2_VALPK3_VALPK4_VAL300

As shown above the second table shows the detail information of record in the first table and both these tables will have to be in the same page of the report.

This can be achieved with the approach I mentioned earlier. But reporting will not be simple as we have to introduce multiple data providers. To make the reporting simple this approach was taken to load the FACT table.

jjagadish

Posts : 2
Join date : 2010-01-21
Location : India

View user profile

Back to top Go down

Re: One Fact table having records at different granularity level

Post  BoxesAndLines on Thu Jan 21, 2010 10:10 am

Mixing grains is always a bad idea. People inevitably sum the higher level grain measures causing incorrect reporting. There are 3 options I know of to deal with this.
1. Allocate the higher grained amounts to the lower level grain
2. Create another fact table to store the higher level grain
3. Store the higher level grain on 1 row of the lower level grain (which usually ends up with the same result you are trying to avoid)
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: One Fact table having records at different granularity level

Post  John Simon on Thu Jan 21, 2010 6:47 pm

Why not just divide the Another_Am by the count of the child records and store that in the fact table?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: One Fact table having records at different granularity level

Post  ngalemmo on Thu Jan 21, 2010 7:22 pm

"Due to the reporting requirement we got from the client it was decided that records from both the source tables have to loaded in the same FACT table using a Union ALL.."

What is the issue? Are you saying table A and B will not exist in the data warehouse?

In the normal course of events, a data warehouse will contain multiple fact tables with different granularity. Also, in the normal course of events it is found that aggreagations of facts are necessary in some cases to improve performance. In the act of aggregation, facts at lower grains are reduced in granularity (i.e. they are summarized) in order to be combined with other facts at a common grain.

Keep the detail (tables A and B) and create an aggregate if you need to (table C)... but I do not understand what a user requirement could be where you MUST create a table that is a really bad idea...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: One Fact table having records at different granularity level

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