Databases Reference
In-Depth Information
The second type of performance that must be monitored is that of the
BI application and any other data access. Data access performance is highly
visible to the user community. When queries and reports do not run in a
timely manner, IT is usually informed immediately. While IT may already
have some tools in place to monitor query performance, explore what tools
may be available from the BI tool vendor, possibly for an additional cost.
Another thing that must be monitored is utilization of resources. As the use
of resources nears capacity, this can result in performance problems or the
inability to meet the system and/or user needs. There are many different places
where capacity must be monitored, including the ETL processing resources,
the database itself (data storage and query processing), application servers,
web servers, and sometimes simply the number of licenses for each different
product. Someone must be responsible for keeping an eye on each of these
different areas.
Business intelligence tools can be used to help you monitor your entire data
warehouse environment. This requires that you capture statistics about the data
warehouse itself. The ETL system can capture the number of rows processed,
the size of the data processed, the number (and type) of warnings and errors
encountered, and the time required to complete each step. Capture and store
database query statistics, including the number of users who log in and the
number of queries that are run. See what your BI tool can capture to help you
understand which reports are being used and which are not.
Using the statistics just described, you can create trend reports, and compare
actual results to targets. For example, you can compare total ETL processing
time to the load window that you have. If the time used is steadily creeping
toward the maximum time available, then you can investigate the situation to
determine what is causing the increased time. Is more data flowing through
the system? Are you waiting for other systems to complete before you can
begin processing? You can perform an analysis to both figure out where to
look and determine what needs to be done to ensure completion in the allotted
timeframe.
In addition to comparing actual performance to targets or goals, you can also
track each of these measurements over time. Is there an increase or decrease
in the number of queries run against the data warehouse? If so, you can
investigate to figure out why. Is there an increase in the number of rows being
loaded each week? If so, you may need to adjust the schedule regarding the
purchase of additional disk space.
Leverage the capabilities of BI to help manage the business of building and
maintaining the data warehouse. As you have seen, there is more work to be
done than merely keeping the technology up to date with sufficient capacity
to maintain acceptable service levels. There is additional work to maintain the
data warehouse itself.
