I have 20 years of data, partitioned by month (240 partitions) in my
cube. Each partition has a slicer = the month (eg. 199505)

Because the first 10 years have a lot less data, I wanted to combine
these into a single partition, but I am afraid of what might happen to
query performance.

I read somewhere that if you do not use a "slicer" for the partition,
but use a source-table filter on the partition instead, such as
"dbo"."Fact_Table"."Month" <= 199512, the Analysis Services engine
will not know how to search for this data as efficiently, say if a
user slices by Month = 199201 in a client application.

Any ideas on the overall impact of using a Source Table Filter in lieu
of a slicer value for the parition? Another scenario is when users
sometimes select "All" months so it has to jump to each parition,
which I assume fewer partitions benefit in that scenario (or do they)?

Thanks

Kory

Slicers and source table filters are not related as far as query performance
is concerned. Source Table Filters just limit the data that is included in
the partition during partition processing. Slicers, on the other hand,
provide information to the server so that the server can better execute
queries.

For example, suppose you have a slicer for a partition for January 2002. If
you're query references January 2002, the server will know to just scan this
partition and can ignore the others, provided that the other partitions have
slicers on them as well. It would have to scan the partitions where there
is no slicer defined. If you add a slicer to the partition, the server will
change the source table filter property on the partition, but one is used
for partition processing (source table filter) and the other is used at
actual end user query time (slicer). It's a recommended practice to have
slicers on all your partitions. It's also recommended to have partitions
that align themselves with users queries and the general rule of thumb is
that more partitions is better.

Please refer to the Analysis Performance Guide for more information. I
believe it goes into the topic much more deeply.

http://www.microsoft.com/technet/pro...e/ANSvcsPG.asp



Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.




"Kory Skistad" <kskistad (AT) hotmail (DOT) com> wrote

"Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote

I have an Oracle data warehouse and an MSAS OLAP server.
I'm using MS' Oracle ODBC provider.

When I define slicers AND table filters, my cube partitions
can't see any Oracle fact data. Consequently, processing ends
in a couple of seconds and the partitions stay empty.

When I define table filters only, my cube partitions get
processed OK.

When I define slicers only, my cube partitions, again, stay empty.


Conclusion: slicers somehow "prevent" MSAS from processing
desired Oracle fact data.

Configuration:
MSAS Ent Evaluation
Oracle 8.1.7 EE
Windows 2000 Server