CODEWITHSUNDEEP
sql-servermdxbusiness-intelligenceolap
user2035610
user2035610

Reputation:

Distinct count over multiple dimensions

I am completely new to MDX.

I have to implement a set of indicators. Example:

Discharges, for patients ages 18 years and older with either: - any-listed ICD-9-CM procedure codes for esophageal resection; or - any-listed ICD-9-CM procedure codes for gastrectomy and any-listed ICD-9­ CM diagnosis codes for esophageal cancer. Link to complete spec here.

Thus I think I need to create a measurement (lets call is countX) that counts the number of facts (discharges) that belong to some computed set OR that belong to some other computed set. If a fact belongs in both sets, then it should only be counted once.

A set definition may contain crossjoins and filters over multiple dimensions.

The idea is then to be able to slice countX along any dimensions (ideally including those used to compute the sets).

I already learned that the UNION operator only works when joining sets over the same dimension. So, is my approach feasible to do using MDX? Maybe I can formulate the problem in a different way to somehow make use of calculated members or so?

Or would it be better to create specific fact or dimension tables populated with the correct information using SQL?

Thanks

Upvotes: 0

Views: 372

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

You can UNION two sets of different dimensionality by creating tuples that use both dimensions, but basically ignore one in one case, and the other in the other case.

I don't know your cube or your data, so I'm going to use very simple psuedocode. Say you want to get all widgets that are red and all widgets that are small.

Another way to think is: I want all widgets that are red, regardless of their size, and I want all widgets that are small, regardless of their color.

So psuedo-MDX for expressing this would be:

({[dimColor].[&Red]}, {[dimSize].[All]})
+
({[dimColor].[All]}, {[dimSize].[&Small]})

Get the specific member(s) you want from Dimension A, CrossJoined with ALL members of Dimension B. And then you can UNION that with ALL members of Dimension A, CrossJoined with the specific member(s) you want from Dimension B, because you have met UNION's requirement that the two sets have the same dimensionality.

Upvotes: 1

Related Questions