I have built two cubes and put them into a virtual cube
cube 1 contains all the sales values by date and location
cube 2 contains all the square footage information by date and
location.
The information in these cubes is not on the same date grain.

I want to produce a calculated member "sales per sqft" that is correct
for any selected time period.

If I have only one square foot value I can do the calculation like this

say 100 sq ft on 04/01/02

Iif(
([Accounts].[Sales],[Measures].[Square Feet],[date].[all date])=0, 0,
([AccountNumbers].[Sales]
,[Measures].[Amount])/
([AccountNumbers].[Sales],[Measures].[Square Feet],[date].[All date]))

But if i add a second value 30 sq ft on 04/30/02, i have a problem.
Any sort of square foot aggregation will be incorrect in some
circumstance.

I've fooled around with topcount but I can't get that working.

I could do this by keeping the square foot information on the same
grain as the sales information but that's such a waste of space that i
don't want to do it.

How can i solve this problem?

For the square footage, maybe you can adapt the Last Non Empty Value
calculation discussed in this MSDN paper:

http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/semiadd2.asp
Analysis Services: Semiadditive Measures and Inventory Snapshots

Amir Netz
Microsoft Corporation

Updated May 18, 2004

Applies to:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 Analysis Services

Summary: Focusing on a classic inventory problem, this article describes
the implementation techniques of semiadditive measures in online
analytical processing.

...

Measures.[Last Non Empty Value]:
CoalesceEmpty((Measures.[Value], Time.CurrentMember ) ,
(Measures.[Last Non Empty Value],Time.CurrentMember.PrevMember))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak Puri wrote:

Thanks!

That set me on the right track.
I ended up creating a new calculated measure that used the coalesce
empty recursively.
Like this...
CoalesceEmpty((Measures.[Value], Time.CurrentMember ) ,
(Measures.[Last Non Empty Value],Time.CurrentMember.PrevMember))

Best wishes!

Brad

Deepak Puri wrote:
wrong...
[Latest Square Feet] =

CoalesceEmpty(

(Ancestor([accountnumbers].currentmember,[AccountNumbers].[Level
01]),
Measures.[Square Feet], [Date].CurrentMember) ,
(Ancestor([accountnumbers].currentmember,[AccountNumbers].[Level
01]),
Measures.[Latest Square Feet],[Date].CurrentMember.PrevMember)
)

Lets say a company has 3 changes to their square footage.
If I go up to a level where more than one is involved I need to
calculate the time weighted avearge of that square footage, NOT the
sum. I'm not sure if this is a semiadditive measure or just non
additive.

I tried adding the Avg function in various places but I see no
change...
Feels like a black box experiment. I hope I start to understand this
better soon...

Thanks for any help!


Brad