Aggregated Measure

View previous topic View next topic Go down

Aggregated Measure

Post  kellog1 on Thu Dec 23, 2010 4:37 pm

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.

kellog1

Posts : 4
Join date : 2009-12-19

View user profile

Back to top Go down

Re: Aggregated Measure

Post  BoxesAndLines on Thu Dec 23, 2010 8:35 pm

Build a bridge table for the dimension.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Aggregated Measure

Post  kellog1 on Fri Dec 24, 2010 2:00 am

Can you explain it using an example?

Thanks.

kellog1

Posts : 4
Join date : 2009-12-19

View user profile

Back to top Go down

Re: Aggregated Measure

Post  hang on Sat Dec 25, 2010 6:51 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Aggregated Measure

Post  kellog1 on Mon Dec 27, 2010 5:56 pm

hang wrote: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.

kellog1

Posts : 4
Join date : 2009-12-19

View user profile

Back to top Go down

Re: Aggregated Measure

Post  hang on Mon Dec 27, 2010 7:30 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Aggregated Measure

Post  Jeff Smith on Tue Dec 28, 2010 10:38 am

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.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Aggregated Measure

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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