March 18, 2004 at 2:15 am
Hi,
I'm trying to see whether we can use a cube in our application and I have certain tasks, which are currently written in Stored Procedures, to try and replicate using MDX.
The (simplified) table structure is:
col1, col2 , col3, month, volume
Col1, col2 and col3 are selections that the user can make through a front end and I can create an SQL statement which uses these selections:
select distinct month, sum(volume)
from table
where col1 = 'col1_value'
and col2 = 'col2_value
and col3 = 'col3_value'
group by month
I have managed to produce a MDX expression which replicates this:
select {[Measures].[Volume] } on Columns,
{[Month].Members} on Rows
from cube_name
where ( [Col1].[col1_value], [col2].[col2_value], [col3].[col3_value] )
I have even managed to set up the same hierarchies (product and market hierachies) to produce the same numbers as I get from current database.
The problem I face is that the application allows user to define their own custom groups. I solve this problem in the SP using dymanic sql (the simple example above would also have been created using dynamic sql) and the SQL statement becomes
select distinct month, sum(volume)
from table
where col1 = 'col1_value'
and col2 = 'col2_value
and col3 in ('col3_value1', 'col3_value2', 'col3_value')
group by month
The question is how do I create a single MDX expression which produces the same result? Some of the groups may contain dozens of items and the users can select custom groups for all selection items giving hundreds or thousands of combinations.
I am sure that there are several different ways but all ideas welcome.
Jeremy
March 22, 2004 at 5:27 am
Take a look at Section 6, Chapter 23 'Business Case Solutions Using MDX' in the SQL Resource Kit (online at http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part1/c0161.mspx). Do a search on the following 'How Can I Implement a Logical AND or OR Condition in a WHERE Clause' in the document.
Basically your options are to create a calculated member (when trying to use multiple members from the same dimension) which will (prob) use the aggregate function and then use this as one of the tuples in your where clause. Alternate is to create a set using a filter statement and use this set on an axis (see code example 23.8).
Steve.
March 22, 2004 at 6:13 am
Thanks Steve - it helps a great deal.
Jeremy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply