Getting calculated measures to aggregate correctly

View previous topic View next topic Go down

Getting calculated measures to aggregate correctly

Post  heccard on Thu May 05, 2011 1:17 am

I have two fact tables in part of a system: a population table that has yearly population counts per municipality for a country and a crimes fact table that has yearly crime counts by crime type and by municipality. I have a time dimension, a territorial dimension (municipalities, states, country) and a crime types dimension. We created a measure group to relate crimes to population and a calculated measure for crimes per 100,000 population. It all looks good at first. But what we noticed was that the grand total of crimes per 100,000 population was wrong. Our data set of crimes is from 2006 to 2009 but we have population records going back to 2000. The grand total is giving us the sum of the crimes from 2006-2009 divided by the sum of all our population records (2000-2010). So the rate is wrong in the aggregate.

How should we deal with this problem? Is there a way to calculate the calculated measure so that the population records are only aggregated over the same period that has records in the related crimes table? Example: Sum of Crimes 2006-2009 / Sum of Population 2006 - 2009.

heccard

Posts : 2
Join date : 2011-04-16

View user profile

Back to top Go down

Re: Getting calculated measures to aggregate correctly

Post  VHF on Thu May 05, 2011 4:20 pm

Sounds like it is trying to give you the "big picture" from 2000 to 2010, but is treating the years lacking crime data as zero.

If you restrict your query to 2006 to 2009 do you the correct results?

At what time granularity to you want your result? Just a single overall figure, or a value for each year, or ???

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Getting calculated measures to aggregate correctly

Post  heccard on Thu May 05, 2011 5:39 pm

Yes, exactly. If I restrict the query to the years for which I have data I get a result that is closer to what I want. At least it is a value within the minimum and maximum range of rates for each municipality and crime type. Digging deeper we noticed that the cube is taking missing values as zeros. The lowest grain is crimes per municipality, per crime type per year. Even if there is no data for a given municipality (say no report from municipality A in year 2009), the rate that gets calculated for the crime type or state as a whole is counting the missing values as zero. So we are getting rates at the different granularities that are misleading.

What we want is to get, at any level of granularity, the "crime rate per 100,000 population for municipalities that reported".We need to exclude from the population totals any cells that correspond to a municipality that is missing crime data for the given year. This way the rate is calculated correctly taking into account only "crimes per 100,000 population for reporting municipalities".

Any ideas?

heccard

Posts : 2
Join date : 2011-04-16

View user profile

Back to top Go down

Re: Getting calculated measures to aggregate correctly

Post  VHF on Thu May 05, 2011 5:47 pm

It should be doable as a calculated measure in MDX -- have it force a NULL output whenever there are no input values for crimes.

However, that is beyond my MDX skills!

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Getting calculated measures to aggregate correctly

Post  John Simon on Sun May 08, 2011 8:18 pm

There is a default setting within the solution to set NULLS to zero. I can't remember where it is, but if you change that you should be fine.

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: Getting calculated measures to aggregate correctly

Post  ricaluanna on Thu May 26, 2011 8:38 pm

I can't seem to find the setting.:(


Last edited by ricaluanna on Thu Jun 02, 2011 7:47 pm; edited 1 time in total

ricaluanna

Posts : 1
Join date : 2011-05-26

View user profile

Back to top Go down

Re: Getting calculated measures to aggregate correctly

Post  John Simon on Thu May 26, 2011 9:36 pm

Go to the MSDN forum for SSAS and ask Boyan Penev or Darren Gosbell.

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: Getting calculated measures to aggregate correctly

Post  spincalis3 on Thu Jun 16, 2011 2:06 am

Thanks for your guide.

_________________
how to get pregnant and tinnitus cure

spincalis3

Posts : 1
Join date : 2011-06-16

View user profile http://naturaltinnituscure.org

Back to top Go down

Re: Getting calculated measures to aggregate correctly

Post  nersinhu on Tue Jun 28, 2011 3:34 pm

Wow thanks for this guide. o/

nersinhu

Posts : 2
Join date : 2011-06-28
Age : 27
Location : Brazil

View user profile http://sonynx810.org/

Back to top Go down

Re: Getting calculated measures to aggregate correctly

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