Database Reference
In-Depth Information
Figure 18-8.
Reducing the amount of selected data using a named query
SSAS Hardware Options
As data is pulled from the data source, it is placed within files and folders defined in the SSAS database's
properties. By default, the files and folders are placed on the same hard drive that SSAS is installed on, but this
can be reconfigured so that the files are placed on a separate drive. Once again, the idea is to spread the workload
across as many resources (in this case hard drives) as possible.
Placing the SSAS database folder on a separate drive will help performance, but if the drive you place the
SSAS database folder on is a RAID stripe, you gain even more performance. Processing performance is improved
because multiple hard drives are used to record the data. And reporting performance is improved, because the
stored data can be retrieved from multiple hard drives simultaneously.
There is often a balancing act between processing performance and reporting performance. For example,
some processing performance is lost when a stripe is mirrored, because the data has to be redundantly copied
to the original and mirrored drives. But, the reporting performance improves, because part of the report data is
pulled from the original and the mirror at the same time.
Besides, the mirror will protect your cube and dimensional data in the event of a hard drive crash. Reports
will continue to work, albeit a bit slower, even if one drive fails, making a mirror a good investment for many BI
solutions. And, you will not need to reprocess the data from the data warehouse after you replace the failed drive
(since most RAID software just copies the data from the mirror when a drive is replaced).
In summary:
•
Only select data that you need in the data source view; never select all columns and rows
from a table if you do not need them.
•
Place indexes on foreign key columns in the data warehouse, since these columns are
joined in SQL
SELECT
statements when processing dimensions and cubes.
•
Place the SSAS database folders on RAID stripes to increase read-write performance.
•
Use a RAID mirror to provide fault tolerance.
Reporting Performance
Reporting performance with SSRS represents a special challenge, because of the large number of components
that interact with each other. Figure
18-9
shows a diagram of the various components and their most common
performance issues.
