by Tony Marasco

When creating objects in Precision.BI, optimizing the objects for performance is a common goal. Achieving optimal object performance in Precision.BI, however, may not be easily defined. For example, is a three minute run time optimal or is tuning required? The time alone is not a measure of the object’s optimization. As a second example for consideration, an object retrieving five years of history aggregated on ten slices and sorted on each slice may complete in three minutes on one system and one minute on another system.. An object on another system that lists five, unsorted records from six months of history may retrieve data in forty seconds. Which object is running at peak efficiency? What tuning options are available?

Object Architecture
Different objects have different means of accessing and producing results in Precision.BI. Worksheets and basic Analyses (no Linked Groups), Linked Groups, and Custom Reports have very different architectures and these differences need to be considered to produce optimal results. Please note that the following architectural discussion is based on Precision.BI 4.0.x through 4.5.x and future application enhancements may change the way data are collected and reported.

Basic Worksheets, Analyses, and Charts
The most basic objects in Precision.BI are the Worksheets, Analyses, and Charts. These three objects will retrieve data from SQL Server, process it, and display the results. Most work occurs on the SQL Server itself. When analyzing a slow running basic object, index optimization or creation is the most common solution to a slow running or non-optimal object. Large data volume (many rows returned and/or many columns of data) could attribute to performance degradation based on the client PC’s configuration and even the network infrastructure.

Linked Groups
Linked Groups perform multiple passes against the database. That is, the core object passes a request to SQL Server for data. The request is completed and data is returned to Precision.BI. For each Linked Group, additional requests for data are passed to SQL Server. Data are then associated at the client PC to match data between the core object and its associated Linked Groups. Given the amount of slices, Linked Groups, and data, the Linked Group analysis may produce the final results slowly. Data could be returned from SQL Server in seconds but the volume of data to be processed on the client PC could cause the system to “freeze” for long periods of time. In this case, indexing or SQL Server adjustments may not increase performance dramatically. Improving performance is found by upgrading the PC or scheduling the object to run on the SQL Server itself.

Custom Reports
Like Linked Groups, Custom Reports perform additional processing on the client PC. A subreport running on each detail row requires more client PC processing power than a subreport running at an aggregated level. Likewise, VBScript processes data outside of SQL Server and requires the PC’s hardware to produce the results. As with Linked Groups, greater performance gains are seen by upgrading the hardware of the client PC or scheduling the report to be run on the SQL Server.

Other Considerations
The use of nested searches and other SQL intensive operations provide enhanced querying power within PrecisionBI. With that power is an expanded cost of computer time to provide the data to such queries. In many cases, Nested Searches and Stacked Searches may be optimized by tuning or adding indexes.

Conclusion
When analyzing Precision.BI object performance there are multiple areas to be considered. Object type, volume of data in the query, as well as the power of the individual client PC all have an effect on the optimization of PrecisionBI.

If you have specific objects that are performing slower than expected, a case may be submitted to customer.care@precisionbi.com.

Additionally, we are forming training classes to assist users with optimizing those objects. Please see the "PBI Expands Training" section of this newsletter or contact rsutter@precisionbi.com for additional information on training classes.