MDX query issue

Question

In analysis services, I have cube that is based on hospitalization data. For each hospitalization there are potentially 9 icd codes and these are each stored in their own field in the view on which the cube is based. These are stored in a child table in the relational database on which the SSAS database is based.

I would like to query the cube to return all rows that have a certain ICD code in any one or more of the 9 icd code fields. It seems as if it should be simple to have this sort of "OR" in the WHERE or the Filter clause, but I'm not finding the correct method.

Thanks in advance, Jeremy Schrader

Answer 1

As far as i understand, you are an SQL guy and new to MDX, so that's why you have difficulties for the query.

it would be better if you tell us what are the measures you want to select with ICD codes but i am going to try to show you an mdx query sample as simple as possible. Your query should like below;

select {Measure1,Measure2,...} on columns
ICDCodeDimension.Children on rows
//{ICDCodeDimension.ICDCode1,ICDCodeDimension.ICDCode5,...} on rows
from Cube

MDX is highly advanced query language and there are many more concept you should know/learn to use it effectively.

Hope this help.

Answer 2

I am guessing that you'd have a dimension called [ICD Codes] with a single level called [Codes] and 9 members called [Code A] and [Code B] or whatever. Perhaps even a member for [No code] too?

In that case your query would be able to tell you the total number of hospitalisation cases for each code, for a certain time period, across all hospitals:

SELECT {[ICD Codes].[Codes].members} ON ROWS,
{[Measures].[Number of Cases]} ON COLUMNS
FROM [CubeName]
WHERE ([Time].[2010].[Quarter 1])

Answer 3

Thanks for both of your feedback. After I researched further (particularly an article here: http://sqlblog.com/blogs/mosha/default.aspx that uses the SUBCUBE method to give some "OR" functionality but with very poor performance) I realized that the OR construct that I was looking for requires record-level information and so doesn't work after the aggregation that SSAS performs. Thus, I need to create a field on the fact table that has the result of the SQL "OR" statement that I need.

In this case I will just create a flag for any record that has a certain range of ICD codes in any of the 9 ICD code fields. Then, I'll create a measure that gives a count of these. Luckily, the requirements of my app are that only a limited number of diagnoses need to be looked at in this way(i.e. any hospitalization that is diabetes-related,tobacco-related,etc.). I'm still curious how one would approach this if you needed to allow the user to choose any ICD code. My understanding at this point is that you would then need to revert back to plain SQL.

Jeremy