Extremely Slow Query Times

Is there something that I can do to improve the query times when using

Excel to query my AS2005 cube? It's EXTREMELY slow, even if I'm

the only one querying the cube.

The AS server should not be a bottle neck (Windows 2003 x64-bit, dual

core AMD Opteron, 7 + gigs of memory, etc.). I fee like our cube

is very small with little data s well.

In addition to the slow query times when using Excel, using the Browser

in Visual Studio locally on the server results in queries taking longer

to execute than I would expect.

What are some things that I can do to improve performance?

This is just a shot in the dark, but have you specified all the member property relationships between the attributes on your dimensions correctly? If you've created your dimensions using the wizard then the vast majority (possibly all) of your attributes will be directly linked to the key attribute only, and this can lead to less than optimal performance.

Here's an example of what I mean: say you have a Geography dimension with Continent, Country, State and City attributes and Address as the key attribute. You know there's a many-to-one relationship between Address and City, City and State, State and Country and Country and Continent, but by default the wizard will only create relationships on Address and City, Address and State, Address and Country and Address and Continent. What you need to do is go to the dimension editor in VS, then in the Attributes pane on the left hand side drag and drop attributes onto other attributes to create these relationships (AS knows about transitive relationships too, so you can delete ones like Address and Continent). Once you've done this then redesign your aggregations (usage-based optimisation might be a good idea too in the medium term) and reprocess, and you should see an improvement in performance.

Chris

|||

Chris,

I know what you're referring to, but I don't really understand how to implement it correctly.

Take

the following as my example. Let's say I have Product SKU and SKU

Description as available attributes in my Products dimension. While

editing the Products dimension, I notice that I can drag SKU

Description to create a relationship under Product SKU. I can also do

the opposite and drag Product SKU to create a relationship under SKU

Description. However, I can't do

both. Therefore, what is the difference between the 2 relationships.

Ultimately, a Product SKU can have only one SKU Description and

vice-versa.

Thanks!|||

In this case, yes, you have a 1:1 relationship between Product SKU and SKU Description, but I believe it's still beneficial to put the relationship in (probably by making Description a property of Product SKU, if the latter is the key attribute of your dimension). Relationships are useful for AS when it tries to design aggregations, use aggregations during querying, and for working out which attributes 'exist' with each other, all of which will improve query performance. Defining 1:1 relationships aren't going to have such a big impact though; do you have any 1:M relationships such as Product Category to Product SKU, Year to Month etc? Defining them in the dimension is likely to have a much more obvious effect.