Need help on the following:

One dimenson "state" in cube1 has 50 states, Cube1 contains the measure "cost".

another dimension "Northeast" in cube1 has the states in Northeast region like CT, NY etc. Cube2 contains the measure "sales". It is clear that "Northeast" dimension is the subset of dimension "state". Cube2 may not have the idential set of dimensions as Cube1.

Now, I want to create a cube that has dimenson "Northeast", measure "cost","sale","profit", so I can see the "cost","sale","profit" of each state in Northeast, how to do that ?

The natural way for me is to create a virtual cube with dimenson "Northeast", measure "sale", then add measure "cost" using "LookupCube()" function to get the cost, however, since dimension "Northeast" in new virtual cube/cube2 is not the same as dimension "state" in cube1, how could the Northeast's member value (using CurrentMember function) be carried to the LookupCube function to use the fact that dimenson "Northeast" is the subset of "state" ?

Thanks in advance!

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Sorry, dimension "Northeast" should be in cube2, not cube1. The key question is: how to join the two cubes with one dimension in one cube is a subset of another dimension in the second cube. Normally, when the virtual cube joins two cubes if the dimension is identical in both cubes. Here the difference is that the dimension is not identical and is just a subset of another.

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

I think your problem stems from what your trying to do. Why not just include
a mid level between state and states that will allow the aggregation of
Northeast?

If it is a security issue and you dont want particular groups seeing
particular regions just use the role manager.

"Jun Wu" wrote:

Thanks you for your response, HWUK!

I think the solution to my problem can be found in other threads like http://www.dbforums.com/t691144.html, http://www.dbforums.com/t551405.html etc. Using linkMember rather than lookupCube function in the virtual cube.

I am still wondering that if the large dimension with all the possible values is used in both cube1 and cube2, joining two cubes in virtual cube is no problem, but how much is the impact for building cube2 because cube2 has only subset of dimension as Northeast, if there are many dimensions in cube 2 like this ( subset of dimensions in cube1), cube 2 is going to have lots lots of empty cells, is it not efficient ? Not sure how Analytical Service handle this situation. If there is not a lot of overhead ( buidling time, storage space, accessing time etc), it may be a not bad solution.



************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...