CODEWITHSUNDEEP
sql-serverssasmdxolapbids
Logan Serman
Logan Serman

Reputation: 29870

MDX query to sum a measure for all columns/rows

I have a cube that has:

DimEntity
EntityID
EntityName
EntityLongName

FactReturns
TotalReturn

What I want to do is sum the TotalReturns between all combinations of entities, so the output is like this:

             NameA        NameB
LongNameA    sum(A, 1)    sum(A, 2)
LongNameB    sum(B, 1)    sum(B, 2)

(sum(A, 1) = sum(A, A), because EntityName "A" and EntityID "1" refer to the same entity)

What I have now is this:

WITH MEMBER [Measures].[SumReturns] AS
    SUM([Dim Entity].[EntityID], [Measures].[TotalReturn])
SELECT 
    [Dim Entity].[EntityName].Children ON COLUMNS,
    [Dim Entity].[EntityLongName].Children ON ROWS  
FROM
    [Returns]
WHERE
    [Measures].[SumReturns]

But this just gives me a table like this:

             NameA            NameB
LongNameA    A.TotalReturn    null
LongNameB    null             B.TotalReturn
etc...

I am just learning MDX and having a real hard time understanding how this works.

Upvotes: 1

Views: 12695

Answers (1)

ic3
ic3

Reputation: 7680

You're mixing a bit MDX concepts, sorry for misleading you. I'd advise you to take some time reading some MDX documentation. You've a quick introduction to MDX here, it's also interesting to read this page; do not miss the next page - advanced.

  • It's null by construction as the tuple does not exist.

There is an important difference between dimensions and hierarchies. Dimensions represent a coordinate in your space of facts (aka your cube facts). In order to retrieve some values from this space (aka cube) you'll need to define a sub-space (aka cell). A hierarchy is 'another' way grouping the coordinates (entities) of a dimension. If you define two entities of different hierarchies of the same dimension you perform an intersection between this two entities. This intersection can be empty, which is your case.

Let's take an example :

  • EntityName is the coordinate of your [Dim Entity], this example is a flat dimension with the entities as coordinates.
  • Let's imagine you would like to group the entities of this dimension differently : The one containing an 'a' and the ones without. This new hierarchy has two members or entities [with a] and [without a].

Now it you ask the tuple ([Dim Entity].[Flat].[Entity 1],[Dim Entity].[Contains A].[with a]), it's always null as this tuple does not exist as there is no coordinate in the cube for this combination. Why ? [Entity 1] does not contain an 'a'.

I'm not sure what you're calculating but you need to twist your calculated member. After the comment of santiii, we need a new dimension :

 WITH MEMBER [Measures].[SumReturns] AS
   -- we're reseting here COLUMNS information, sum is not needed
  ([Dim Entity].[EntityName].defaultmember,[Measures].[TotalReturn])
  +
  -- we're reseting here ROWS
  ([Dim Entity 2].defaultmember,[Measures].[TotalReturn])
 SELECT 
  [Dim Entity].[EntityName].Children ON COLUMNS,
  [Dim Entity 2].[EntityName].Children ON ROWS  
 FROM [Returns]

Upvotes: 3

Related Questions