MS SQL Server OLAP - Granularity issues!

Author

Granularity issues!

grahamrichter@gmail.com

2005-09-19, 11:23 am

I have a simple cube, with a single dimension and a fact table. It
measures Number of sales, and the only dimension is Location with
levels State, City and Branch.

On my report I need to report on market penetration, i.e. Sales per
Population. Now the sales are stored in the Fact table, at the Branch
level. I have the population on a City level, not on a Branch level. In
other words my Population's granularity is one level higher than my
Sales measure, yet I need to divide the one by the other at a City or
State level to get the ratio.

The question is: Where do I store the Population of a city? Is it in
the Fact table? Or is it a member property of the City dimension? How
can I divide Sales by Population in a MDX query?

Much appreciated!

Darren Gosbell

2005-09-20, 9:23 am

In AS2k what you would need to do is to create one cube that contains
the facts at the city granularity and another that has the sales facts
and then join them via a virtual cube. I am guessing that these 2 cubes
would have the same dimensions. In the cube with the population figures
you would disable the levels below "city".

You will then be able to create a calculated measure in the virtual cube
for the sales per population.

For more information on different data granularity have a look at the
following article from Microsoft
http://go.microsoft.com/fwlink/?linkid=23157

I was written for OLAP Services (SQL7), but still applies to AS2k. But
it will all change in AS'05 with the advent of the UDM and measure
groups.

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1127140353.767793.11840@g44g2000cwa.googlegroups.com>,
grahamrichter@gmail.com says...
> I have a simple cube, with a single dimension and a fact table. It
> measures Number of sales, and the only dimension is Location with
> levels State, City and Branch.
>
> On my report I need to report on market penetration, i.e. Sales per
> Population. Now the sales are stored in the Fact table, at the Branch
> level. I have the population on a City level, not on a Branch level. In
> other words my Population's granularity is one level higher than my
> Sales measure, yet I need to divide the one by the other at a City or
> State level to get the ratio.
>
> The question is: Where do I store the Population of a city? Is it in
> the Fact table? Or is it a member property of the City dimension? How
> can I divide Sales by Population in a MDX query?
>
> Much appreciated!
>
>

SQL McOLAP

2005-09-20, 1:24 pm

If you only need to see this ratio at the city level, there is a way you
could do it with the population stored in your city level table (if it's
snowflaked) or in your city level column (repateated for each city if in
star) while still using just one cube.

You can create a calculated member that evaluates which level you're on in
your axis, and if in city, it grabs the population. Then another that will
divide by the city member property storing your population.

Something like:

--create a measure calc member for the population
With member [measures].[population] as
'IIF([location].currentmember.level.ordinal = [location].[city].level.ordinal,
[location].currentmember.properties("Population"),0)'

--create a measure calc member that then does the division
member [measures].[sales ratio] as
'IIF([measures].[population] <> 0,
[measures].[sales]/[measures].& #91;population],0)',
solve_order = 10

select { [measures].[sales ratio] } on columns,
{ [location].[city].members } on rows
from [cubename]

Those calculated members could be combined, I figured seperating them like
that would make this easier to understand.

However, if you need to do other evaluations at other levels, or need
population to aggregate up a hierarchy, follow Darren's excellent suggestion
with another cube joined up into a virtual. The article he provides a link
to is very helpful.

Good luck.

- Phil

"grahamrichter@gmail.com" wrote:

> I have a simple cube, with a single dimension and a fact table. It
> measures Number of sales, and the only dimension is Location with
> levels State, City and Branch.
>
> On my report I need to report on market penetration, i.e. Sales per
> Population. Now the sales are stored in the Fact table, at the Branch
> level. I have the population on a City level, not on a Branch level. In
> other words my Population's granularity is one level higher than my
> Sales measure, yet I need to divide the one by the other at a City or
> State level to get the ratio.
>
> The question is: Where do I store the Population of a city? Is it in
> the Fact table? Or is it a member property of the City dimension? How
> can I divide Sales by Population in a MDX query?
>
> Much appreciated!
>
>

Sponsored Links