Query Performance Tuning - Expert Cube Development with SSAS Multidimensional Models - page 213

You can set connection string properties in SQL Management Studio
when you open a new MDX Query window. Just click on the Options
button on the Connect to Analysis Services dialog, then go to the
Additional Connection Parameters tab. Note that in certain versions
of SQL Management Studio there is a problem with this functionality,
so that when you set a connection string property it will continue to
be set for all connections, even though the textbox on the Additional
Connection Parameters tab is blank, until SQL Management Studio is
closed down or until you set the same property differently.
Aggregations
An aggregation is simply a pre-summarized data set, similar to the result of a SQL
SELECT statement with a GROUP BY clause, that Analysis Services can use when
answering queries. The advantage of having aggregations built in your cube is that
it reduces the amount of aggregation that the Analysis Services Storage Engine has
to do at query time, and building the right aggregations is one of the most important
things you can do to improve query performance. Aggregation design is an ongoing
process that should start once your cube and dimension designs have stabilized
and which will continue throughout the lifetime of the cube as its structure and
the queries you run against it change; in this section we'll talk about the steps you
should go through to create an optimal aggregation design.
Creating an initial aggregation design
The first stage in creating an aggregation design should be to create a core set of
aggregations that will be generally useful for most queries run against your cube.
This should take place towards the end of the development cycle when you're
sure that your cube and dimension designs are unlikely to change much, because
any changes are likely to invalidate your aggregations and mean this step will
have to be repeated. It can't be stressed enough that good dimension design is the
key to getting the most out of aggregations. Removing unnecessary attributes,
setting AttributeHierarchyEnabled to False where possible, building optimal
attribute relationships and building user hierarchies will all make the aggregation
design process faster, easier and more effective. You should also take care to
update the EstimatedRows property of each measure group and partition, and the
EstimatedCount of each attribute before you start, and as these values are also used
by the aggregation design process. BIDS Helper adds a very useful new button to the
toolbar in the Partitions tab of the Cube Editor which will update all of these count
properties with one click. Remember that if you are developing against a small subset
of your data you will have to enter these count values manually, so that the values are
similar to what they would be if you were using a full-sized production database.