Need help calculating town value and value for all the other towns in the state.

[tlambert]

Hi, I'm new to FM and Transformer,

I have a dimension of State>County>Town and a fact of hospital discharge count.

When we report discharge counts for County or Town values, we always need to report the count for all the other counties/towns in the state. This is required so we can (further in the process) compare the county/state discharge rates to the rate for the rest of the state.

We can't compare to the whole state because it violates statistical assumptions.

I'm not sure the best way to go about doing this in a cube (we need to use cubes due to long run times in dimensional/relational data sources). I'm open to any solution which involves any/or all of Framework Manager/Transformer/Report Studio.

Additional info: I could force this by querying state level data and joining it 1:many onto the county/town level query results and then performing the calculation, or I could try to rig something up with joins in FM, but it seems like Transformer should be able to do this easily.

thanks for the help,
Tom

[cognostechie]

If I am understanding it correctly, what you are asking is what Transformer does automatically if you create a Dimension with State as the Highest Level followedby County and Town. It will roll up the data of all Towns into the County they belong to and all Counties into the State they belong to. So the problem is ?

[tlambert]

In a list, if I drill down to a town, I want two columns to be populated - One measure for the town and one for all the rest of the towns in the state (value of the current level [Town] subtracted from the value for coarsest level [State]).

If there are 100 discharges in the state, I want a table like this  (with an [optional] third column).

Town   Town_Count     Rest_of_state      [State]
A                  10                      90                100
B                   5                       95                100


Looking into this further, it seems there may be a solution in Report Studio which uses the tuple function to access the state value.

[tlambert]

Thank you all for looking at this.  The solution in Report Studio is simple with the tuple function where I am able to call something like "tuple([Discharge Count], [New Hampshire])" and it consistently returns the discharge count for the whole state. This is nice, but I'd rather push it to the cube because it will affect the downstream calculations I need to make (calculating rates and putting statistical confidence intervals around the estimates).

Any further help is appreciated,
Thank you,

Tom

[wyconian]

Hi

What kind of database are you using?  In oracle there are a load of 'analytical functions'.  These let you calculate a value (sucj as the discharge for all tows in the state etc) and return that value as an attribute of (for instance) all of the towns.

There are similar functions in SQL Server (2008) as well, I guess there will also be similar functions in the other major RDBMS