Getting calculated measures to aggregate correctly

[heccard]

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.

[VHF]

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

[heccard]

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?

[VHF]

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!

[John Simon]

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.

[ricaluanna]

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

[John Simon]

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

[spincalis3]

Thanks for your guide.

_________________
how to get pregnant and tinnitus cure

[nersinhu]

Wow thanks for this guide. o/

[Sponsored content]