October 7, 2009 at 8:29 pm
I am scratching my head how to approach this one or even whether it is possible with SSAS mdx.
The requirement is to identify patterns in the fact table of occurrances. We have captured true and false in our fact table for each given event for each date and we want to have a means to bring back any sets (basically date and event) where the pattern met is 0,0,0,-1.
I thought about trying to convert the set to a string to use in the mdx where clause, but doesnt seem like this is possible. Does anyone know if SSAS has this kind of ablility or how i can develop this type of search criteria.
Many thanks if you can assist.
Andrew
October 7, 2009 at 9:03 pm
Hey Andrew,
Where exactly have you got the true/false in your cube? I'm assuming it's either a single dim (with a surrogate key or perhaps a funky string of true/false comma separated values) or perhaps as a series of dims, where each has only two categories (true and false). I'm thinking the latter would not be hard to write MDX for.
If you haven't actually gotten it into a cube yet, the whole pattern you're describing reminds me of a bit masking article on here not that long ago, i think it's here[/url]. Could be way left of field but also could be fun to try out 🙂
Steve.
October 8, 2009 at 4:37 pm
Steve
Many thanks for your reply.
Currently the data is in the format in the fact table as follows:
Date Event
01/01/09 0
02/01/09 0
03/01/09 0
04/01/09 -1
05/01/09 -1
So the seach pattern needs to ignore the dates and find any event sequence where events = 0,0,0,1.
The more i research MDX, it doesnt seem like there will be a suitable way to do it and starting to think that my solution will be to change the ETL , create a non-additive measure and capture the last 5 events as a csv string. This will only work on one level and will not allow slicing to be performed on this measure, but will maybe give me what i want.
Any thoughts?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply