Hi,
We have a two-cube solution built in AS 2008 that works like this: an Industry cube that contains Widget data for all clients, and a Client cube that contains Widget data & shares several (but not all) dimensions with the Industry. The Industry cube contains everybody, including the calling client, so when the Client is pulling an Industry measure, we want the results to include everybody else. Clients are represented in the Industry cube with a "clients" dimension that just has two members: the key & the client name. The industry cube's count measure is called [I Widget Count] & the same measure in the client cube is [Widget Count].
An MDX expression like this works great from the query editor:
WITH SET [Industry] AS Except( [Cube Clients].[Client].[All].Children
, [Cube Clients].[Client].[Client 05] )
MEMBER [Measures].[Industry Widget Count] AS sum( [Industry], [I Widget Count] )
SELECT ({ [Widget Count]
, [Industry Widget Count]
, [I Widget Count] }) on 0
FROM [Client 05 Cube]
Everything seems fine, the counts for client & industry are perfect. Adding shared or non-shared dimensions to axis 1 works also, & everything seems great.
As soon as I add the set to the cube itself, the sum functions stop giving correct answers. So, in the calculated measures screen I've added:
CREATE DYNAMIC SET CURRENTCUBE.[Industry]
AS Except( [Cube Clients].[Client].[All].Children
, [Cube Clients].[Client].[Client 05] ) ;
CREATE MEMBER CURRENTCUBE.[Measures].[Industry Widget Count]
AS sum( [Industry], [I Widget Count] ),
VISIBLE = 1 ;
When browsing this measure in the cube, the count is completely wrong. The correct number (with no dimension constraints) is around 10,000,000 & the browser shows 30. I've tried looking through a trace window to see if I can read the code that the cube is using to generate the dynamic sum, but it is not visible.
Anyone have a clue why this is happening?