Problems with dimensions and measures

hi i a newbie in the world of cubes and i have a problems to show a report of quantity of products sales for year

this show like this

1997 62

1998 62

1999 62

2000 62

and only sale producs sales in 1999 the right thing is

1997 0

1998 0

1999 62

2000 0

If you are using Analysis Services 2005 this seems like a problem with the relation between your product dimension and your fact table/measure group. Check the dimension usage tab in the cube designer in Business Intelligence Development Studio.

When dimensions and fact tables are not related correctly Analysis Services will show the All member value for the unrelated dimension.

HTH

Thomas Ivarsson

|||thanks for you help..

problems with average calculations that do not give correct value

I am currently building a cube and I have two measures that I need to calculate the average for (i.e. columns measureA and measureB).

measureA is a Sum of a value and measureB is the count of the number of occurences of the value.

I define a calculated Member that employs the calculation of measureA/measureB. This gives me an average that I want to roll up.

At the lowest level the calulation is correct, however as the rollups happen the calcultion becomes invalid. At the (all) level the calculation is quite far out.

The calculated member simply gives me the (All)measureA/(All)measureB.

Can anyone point me in the direction of how I can get a calculation of the individual measureA/measureB rolled up correctly giving me an overall average?

COUNT takes into account empty cells, so when you rollup, you are considering more cells than you should.

Use AVG, or filter out empty cells when using COUNT

See

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390791&SiteID=1

Hope this helps,

Santi

|||

Thanks Santi That did help me.

What I was trying to do was more complex than simply averaging a single column data and after studying the problem I found that it was far too complex and that using Avg was much better.

Many thanks

Rob