July 21, 2009 at 9:03 am
I have a fact table
Time1,Order1,Product1,FirstCode1,FirstSecondaryCode1,SecondSecondaryCode1,FinalCode1
Time1,Order1,Product2,FirstCode1,FirstSecondaryCode2,SecondSecondaryCode1,FinalCode1
Time1,Order1,Product3,FirstCode2,FirstSecondaryCode1,SecondSecondaryCode2,FinalCode2
.................
Time5,Order5,Product11,FirstCode2,FirstSecondaryCode2,SecondSecondaryCode2,FinalCode2
Time5,Order5,Product12,FirstCode2,FirstSecondaryCode2,SecondSecondaryCode2,FinalCode2
......................
Time100,Order100,Product1,FirstCode15,FirstSecondaryCode12,SecondSecondaryCode1,FinalCode1
etc
All Orders are foreign keys pointing to the order dimension, products are foreign keys pointing to the products dimension and the same for time. There are also FirstCode, FirstSecondaryCode, SecondSecondaryCode and FinalCode foreign keys pointing to the corresponding primary keys in the FirstCode, FirstSecondaryCode, SecondSecondaryCode and FinalCode dimensions . These 4 product codes can be any value from a list for each of the codes
The user wants to be able to select all the orders that have :
1. FirstCode1 or First Code 2 or FirstSecondaryCode2 or SecondSecondaryCode15 or FinalCode20
2. FirstCode3 or FirstSecondaryCode11 or SecondSecondaryCode10 or FinalCode4
3. SecondSecondaryCode5 or FinalCode3
In the first example FirstCode1 or First Code2 they are part of the same dimension so the condition includes an "OR" anyways. But since FirstCode, FirstSecondaryCode, SecondSecondaryCode, FinalCode the joining will include the "AND" condition between different dimensions (First Code, FirstSecondaryCode, SecondSecondaryCode, FinalCode)
So the user is going to select the values of the codes from the codes dimensions but also the "OR" condition can apply over ALL or just SOME of the dimension codes (like in example 3 where will be limited to SecondSecondaryCode5 or FinalCode3 but all the members of the First Code and FirstSecondaryCode.
Is it any way that I can implement the OR condition in the cube ( without the user inputting MDX) ?
July 21, 2009 at 2:20 pm
The user wants to be able to select all the orders that have :
1. FirstCode1 or First Code 2 or FirstSecondaryCode2 or SecondSecondaryCode15 or FinalCode20
Without really knowing the data, it seems like this is comparing apples and oranges. I wonder how meaningful it really is to have a set with such disparate elements in it.
That said, I think it can be done, although how easily depends on the interface you are using to allow them to select these conditions.
You are going to need to use a Union() or "+" to join the sets together. That means that each of your sets is going to have to have the same dimensionality which means that each of your tuples will need to be in the form ([FirstCode].[FCValue], [FirstSecondaryCode].[FSCValue], [SecondSecondaryCode].[SSCValue], [FinalCode].[FCValue]).
Furthermore, each of the tuples needs to be of the same granularity to make the union meaningful. Otherwise, you might have [FirstCode].[FirstCode1],...,[FinalCode].[All] and [FirstCode].[All],...,[FinalCode].[FinalCode20] which would double count the value for [FirstCode].[FirstCode1],....,[FinalCode].[FinalCode20]
So, I think that you're going to have to generate a set for each selection and the set will need to include the children for all relevant dimensions not selected. For example, when you select [FirstCode1] you'll need to generate the set
[FirstCode].[FirstCode1]
* [FirstSecondaryCode].Children
* [SecondSecondaryCode].Children
* [FinalCode].Children
After generating a set for each selection, union all of those sets together.
It's not pretty, and I think that's directly related to the fact that the set really is not meaningful, as I stated at the beginning.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 22, 2009 at 4:48 am
If the user is doing this as a one off then it can be done (although there may be a lot of scrating of head).
If there is some sort of repeated business logic could this be applied on the back end. This would be asier maintenance wise and you would not get any overhead of doing the calculation on the fly in the cube.
Ells.
:w00t:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply