One Fact table having records at different granularity level

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

PK3

PK4

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.

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

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..

PK1	PK2	PK3	TOT_AM	ANOTHER_AM
PK1_VAL	PK2_VAL	PK3_VAL	1000	500

PK1	PK2	PK3	PK4	AM
PK1_VAL	PK2_VAL	PK3_VAL	PK4_VAL	700
PK1_VAL	PK2_VAL	PK3_VAL	PK4_VAL	300

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.

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)

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

"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...