MS SQL Server OLAP - handling complex filters

Author

handling complex filters

mkambol@gmail.com

2006-03-29, 8:27 pm

We have an existing, SQL-based reporting application which allows
complex filtering using deeply nested and/or logic involving dimension
tables with potentially tens of thousands of members. There are large
numbers of these filters defined, and users can create new ones at any
time.

We're considering moving to SSAS2005, but we're not sure whether we can
make our filtering work. Translating the filters to MDX is not really
an option--performance is terrible. SSAS is not designed to perform
complex relational joins the way an RDBMS is.

So one of the options we've considered is creating either a named view
in DSV or a real view or table in the underlying DB for each one of
these filters. We could then create dimensions in SSAS using the
filter views as a source. Because these could be created by the user
at run-time we would need to frequently update the cube metadata to add
and modify dimensions. We're planning on creating ROLAP cubes, so
reprocessing should not be an issue.

One of our concerns is that SSAS will not scale well if we're dealing
with potentially hundreds of these filter dimensions. We're also
concerned that SSAS may not deal well with very frequent updates to
metadata.

Does anyone have any thoughts or suggestions?

Mosha Pasumansky [MS]

2006-03-30, 8:27 pm

Your approach doesn't seem to be the natural one for AS. Perhaps you could
give couple of example for your complex filters and then how you translate
them to MDX, perhaps it is possible to write MDX in such a way that it will
be natural for AS and performance will be good.

--
====================
==========_=========
===========
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL_og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
====================
==========_=========
===========
<mkambol@gmail.com> wrote in message
news:1143664412.911608.37540@i39g2000cwa.googlegroups.com...
> We have an existing, SQL-based reporting application which allows
> complex filtering using deeply nested and/or logic involving dimension
> tables with potentially tens of thousands of members. There are large
> numbers of these filters defined, and users can create new ones at any
> time.
>
> We're considering moving to SSAS2005, but we're not sure whether we can
> make our filtering work. Translating the filters to MDX is not really
> an option--performance is terrible. SSAS is not designed to perform
> complex relational joins the way an RDBMS is.
>
> So one of the options we've considered is creating either a named view
> in DSV or a real view or table in the underlying DB for each one of
> these filters. We could then create dimensions in SSAS using the
> filter views as a source. Because these could be created by the user
> at run-time we would need to frequently update the cube metadata to add
> and modify dimensions. We're planning on creating ROLAP cubes, so
> reprocessing should not be an issue.
>
> One of our concerns is that SSAS will not scale well if we're dealing
> with potentially hundreds of these filter dimensions. We're also
> concerned that SSAS may not deal well with very frequent updates to
> metadata.
>
> Does anyone have any thoughts or suggestions?
>

mkambol@gmail.com

2006-03-31, 1:29 pm

You're right, the approach is not a natural one for AS. Unfortunately
our business needs do not fit naturally into any OLAP system we've
found.

Here's an example of the sort of filtering we might be doing:

Filter by patients who met the following criteria for a particular
doctor visit:
( have a diagnosis code of (25000 - 2560) AND
( have a procedure code of (1000-1200)
OR have a procedure code of ( 1451)
)
and had a payment > 10000
)
Further constrain to the doctor visits within 3 months after such a
match occurred for each particular patient.

A client could use a filter roughly like that to find out whether a
patient diagnosed with Asthma was re-admitted within 3 months of a
non-Asthma condition.

Note that since each patient will have matched the first criteria at a
different point in time the time constraint will vary--one 3 month
range may be in 2000 and the next could be in 2001. Also, the number
of doctor visits in the fact table could be very large (100s of
thousands), so doing something like an MDX filter function would be
terribly inefficient.

In order to do this in SQL we've typically had to create intermediate
tables to join our fact table to. Our idea is to do something similar:
create an intermediate table in the RDBMS, update the Analysis
Services metadata to join in this table to the fact table, and create a
new dimension based on the table.

Because of the complexity of our needs we have a temptation to write
the whole solution ourselves, but that is unattractive because we would
like to be able to use front-end reporting tools like Cognos and
Crystal, and because there are lots of AS features we'd like to take
advantage of.

Sponsored Links