snippetMinor
Need to filter dimension where two attributes are the same
Viewed 0 times
sametheneedarewheredimensionattributestwofilter
Problem
I've obfuscated the scenario but the general idea still applies, so if the schema seems retarded, it probably is in this example...
Let's say I have a Student dimension, and in it are two attributes - Major and Minor (subjects).
I want to be able to create a Named Set in SSAS that gives me Students who have somehow been recorded as having the same subject for Major and Minor...
Dimension looks a little like this:
The underlying table looks something like this:
So, in effect, I want an MDX expression like this:
... but I can't quite figure out the syntax. Any ideas?
UPDATE:
If I use this syntax...
Then it slows down the ENTIRE cube for some reason. Every Measure (natural or calculated) takes several minutes as opposed to 1-2 seconds pre-cache.
Let's say I have a Student dimension, and in it are two attributes - Major and Minor (subjects).
I want to be able to create a Named Set in SSAS that gives me Students who have somehow been recorded as having the same subject for Major and Minor...
Dimension looks a little like this:
Student
- Major
- MinorThe underlying table looks something like this:
StudentID | MajorKey | MinorKey
1 50 51
2 45 101
3 58 56
4 45 45So, in effect, I want an MDX expression like this:
FILTER (
[Student].allmembers,
[Student].[Major] = [Student].[Minor]
)... but I can't quite figure out the syntax. Any ideas?
UPDATE:
If I use this syntax...
FILTER (
[Student].allmembers,
[Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)Then it slows down the ENTIRE cube for some reason. Every Measure (natural or calculated) takes several minutes as opposed to 1-2 seconds pre-cache.
Solution
I got it!
Basically the filter expression was correct, but I needed to Crossjoin the set what I wanted to filter against...
I know I answered this quick but I've spent the best part of 10 hours on this today :P
Basically the filter expression was correct, but I needed to Crossjoin the set what I wanted to filter against...
FILTER (
CROSSJOIN([Student].[Major].[Major], [Student].[Minor].[Minor]),
[Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)I know I answered this quick but I've spent the best part of 10 hours on this today :P
Code Snippets
FILTER (
CROSSJOIN([Student].[Major].[Major], [Student].[Minor].[Minor]),
[Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)Context
StackExchange Database Administrators Q#18737, answer score: 5
Revisions (0)
No revisions yet.