CODEWITHSUNDEEP
ssasmdxolapolap-cube
nuit9
nuit9

Reputation: 1833

MDX equivalent to SQL's GROUP BY for excluding duplicate rows

My cube has a fact table that contains a row for every modification made to any entity. What I need is a measure that will return a count of the entities based on the selected dimensions. So if the user selects a month from the date dimension then the measure should return the number of entities that were modified that month (rather than the number of modifications).

In SQL this would be something like:

SELECT EntityID, COUNT(*)
FROM FactTable
WHERE Date BETWEEN X AND Y
GROUP BY EntityID

How can you do this in MDX? Surely this would be an extremely common scenario with cubes.

Upvotes: 1

Views: 3449

Answers (1)

Hrvoje Piasevoli
Hrvoje Piasevoli

Reputation: 286

Your t-sql query is equivalent of mdx query:

select 
    [Measures].[<Fact rows count measure>] on columns, 
    <Entity dimension members> on rows 
from [<Cube Name>] 
where (<month member>)

In the above query [Fact rows count measure] would be a measure with aggregation formula Count - count of rows However, if you need to return the distinct count of entity members when you slice by another dimension, you basically have several options:

  1. create a distinct count measure on the entityID key
  2. create a calculated measure with expression: count(exists(existing [Entity].[Entity].[Entity].MEMBERS,,'Measure Group Name'))

HTH, Hrvoje Piasevoli

Upvotes: 3

Related Questions