Aggregated Measure

[kellog1]

Gurus,
I have relational database where there is only one measure that is at an aggregated level whereas Dimensions are at lower level of granularity. Let me give you an example...


ProjectNumber CurrentAmount
5501900000 2000000.00

ProjectNumber Code
5501900000 310
5501900000 124
5501900000 200

Can somebody shed some light on designing a fact table for the above scenario.

[BoxesAndLines]

Build a bridge table for the dimension.

[kellog1]

Can you explain it using an example?

Thanks.

[BoxesAndLines]

http://www.kimballgroup.com/html/10dt/DT124AlternativesMulti-valuedDimensions.pdf

[hang]

Best practice, always try to get atomic grains in the fact, that is CurrentAmount at Code level. If that's impossible, you need to snowflake the project dimension into two dimensions, dimProject and dimCode (or whatever you would call this component level dimension). It's probably good idea to have a count (Code) in your fact table, so that you can see how many codes before further drilling down to see what codes are involved in each project.

[kellog1]

Best practice, always try to get atomic grains in the fact, that is CurrentAmount at Code level. If that's impossible, you need to snowflake the project dimension into two dimensions, dimProject and dimCode (or whatever you would call this component level dimension). It's probably good idea to have a count (Code) in your fact table, so that you can see how many codes before further drilling down to see what codes are involved in each project.

The problem is CurrentAmount is not split at Code Level. Its aggregated at Project Level. But as you suggested I have "Snowflaked" my dimension into DimPorject and DimCode. But still when I do the aggregations the numbers are duplicated for each code. For example in the above scenario the actual Aggregate for ProjectNumber should be 200,000 but in Cube its showing as 600,000.

[hang]

The actual measure for ProjectNumber is not an aggregate, but rather a base measure. You only see aggregates at attribute levels, if exist, higher than ProjectNumber. If configured properly, your cube should not aggregate CurrentAmount on Code, as the lower granularity does not exist in the fact.

What you are supposed to see on your cube, when you expand the project node, is a list of codes that make up the project and N/A's in all the corresponding metric cells. I guess you might have to create calculated measure to produce the desired visual result.

[Jeff Smith]

It's probably 2 fact tables. One at the Project Level and the other at the Code level. The Code level fact table is a factless fact table.

If needed, on the Code Level fact table, you can include the Project Cost at each Code along with a Count of total codes. If a Project cost $20,000 and involved 4 codes, the table would have the project listed 4 times, along with the $20,000 and 4 (total codes). You could create a column that allocated the Cost to each Code - $20,000 * (1/4) and a column that allocated the Project (1/4) to the codes.

This would enable you to report the number of projects, total cost, and number of codes. You could report the number of Projects that used a code.

But, you could never report the Dollars at the Code Level.

[Sponsored content]