Database Reference
In-Depth Information
keys columns, but not exclusively. It is best to keep track of which columns are queried in the reports. Creating
a spreadsheet or table that tracks this is simple and effective, but getting all of the developers to record the
information is not nearly as simple. Still, it may be worth the effort, because knowing which objects are used in
your reports and which are not makes it easier to maintain SQL and SSAS databases. Speaking of which. . . .
Archiving Stored Data
One of the more effective ways of increasing performance is reducing the amount of data stored in either the
SQL or SSAS database. Failing to do so is one of the major performance bottlenecks in any BI solution. The basic
concept is simple; as time goes by, data that was once important is no longer and can be safely removed. For
example, consider a BI solution that focuses on sales data and stores data from the year 1990 to today. Sales
trends from the 1990s are no longer relevant to today's market, so while the best choice may not be to delete the
data, this question arises: “Do I really need this data in the data warehouse or cubes?” If the answer is no, then it
is a good idea to remove the data from the BI solution.
To understand how this impacts performance, think of a report that asks for sales that have occurred during
a sales event; to find this information, the data engine must search all the data for records with some sales event
flag. This means if you have 20 years of data and no index on the sales event flag, it has to search through twice
as many rows as 10 years of data. And even if an index is placed on the sales event column, the index would still
have to contain 20 years of values from that column instead of 10.
Caching Report Data
Reporting Server contains a number of caching options that allow you to retrieve report data directly from the
SSRS databases without having to repeatedly re-render the reports. These options provide a great degree of
performance and should be considered whenever report performance is slow.
In summary, to improve processing performance, follow these tips:
Only select data that you need in the Datasets; never select all columns and rows from a
table if you do not need them.
Place indexes on any column that represents a foreign key in a table, because these are
the columns that are queried when tables are joined in a SQL SELECT statement.
Place indexes on columns that are not foreign keys if they are often used to filter results.
Store only the data you need to in both the data warehouse and cube databases.
Place the SSRS databases on a RAID array and the various SSRS services on separate
computers (in other words, the web service on one and the databases on another).
Use SSRS caching options when appropriate.
Common Design Strategies
Although there are no cookie-cutter design patterns that will work for all BI solutions, there are a few common
design strategies that have been successfully implemented. These strategies involve spreading the workload of
each of the different components of the BI solution across multiple hard drives or multiple computers.
As we have mentioned, RAID is a common way to spread I/O workloads across multiple hard drives. In
a similar fashion, you can spread the many components of your BI Solution across multiple computers. One
strategy is to install SQL Server with SSIS on one computer, SQL Server with SSAS on another, and SQL Server
with SSRS on a third. This can be expanded by placing the SSRS Report Manager web application and SSRS web
service onto yet another machine. This complexity, however, is not always needed, and most solutions stop at
having the three basic servers (SQL Server, SSAS, and SSRS) on separate computers (Figure 18-10 ).
 
Search WWH ::




Custom Search