Hi all

I'm using Analysis Server with SQL Server 2000 and I have performance
problems with drill-through on date dimensions on my MOLAP cubes. Here
is the thing:

I have a cube with sales order information and I want to be able to
drill-through to see all order lines for a cell. The cube has two
dimensions: Item, and Date, the date dimension has levels for Years,
Quarters, Months and Dates.

When I drill-thrugh my cubes on a cell which contain data for a
specific Month or day, it takes forever. If the date range is "All" or
a Year or a Quarter it all works fine but for smaller date ranges
(month or less) the drill-through takes 20 times longer. Any ideas?

Thanks
/Johan

Drilling-through is a regular SQL SELECT statement that
your AS client is sending trough the AS server to the SQL
server where the original table resides. So, if you have
performance problems you should treat them as any other
regular SQL Server-Client query performance issue (like:
you should have indexes on the columns involved in
dimension's structures, connection issues.). In your case
it looks like the lack of indexes is the problem: you
have better performance with large sets because SQL does
not have to look for too many individual rows, it just
performs a table scan; but when you filter at a more
granular level the right indexes in place will really
make a difference.

Thanks for your reply Adrian

You were absolutely right. The dimension table had no index at all.
When I added a clustered index, the drill-through took 1 second
instead of 30. Bingo!

/Johan

"Adrian Mos" <anonymous (AT) discussions (DOT) microsoft.com> wrote