proCube includes an Express Drill Through option that provides lightening-fast access to drill-through data. Express Drill Through is a dramatic performance enhancement; data that used to take many minutes to access now takes only seconds.
Rather than reading all records in a relational database—a time consuming process—the Express Drill Through uses a fast drill-though algorithm that only reads data points for members with one or more non-zero values in the multi-relational database (if a member only has zeros associated with it in the database, the Express Drill Through Algorithm will ignore it). By handling data sparcity in this way, proCube can access records of interest quickly and return drill-through data in seconds.
To further improve the performance of Express Drill Through, several assumptions are made about database records. It’s a given that some information is lost when a cube is built from a relational data source. Specifically:
A member name may be a concatenation of multiple database fields with space as the delimiter.
All leading and trailing spaces are trimmed off.
A field can be truncated before it is imported as a member name.
All of these transformations make it difficult for proCube to figure out what the original field values are given a member name. To address these issues, database tables are normalized based on the following assumptions:
There are no leading spaces in strings, and preferably no trailing spaces either.
proCube must be able to parse a concatenated member name. For example, a number or date followed by a string can be parsed; but two strings with spaces in them cannot.
A field cannot be null if it is to become a part of a member name.
When the above requirements are met, this new fast drill-through algorithm that only reads records of interest can be used. To limit the number of records pulled in, Express Drill Through issues a join query with a WHERE clause to constrain the result-set. The WHERE clause must use the original field values to define the search. If a field value is not truncated, the WHERE clause is an equality comparison; otherwise, it is a pattern matching clause such as LIKE ‘xyz%’ as in T/SQL.
For this algorithm to be fast, it is imperative that the relational database is properly indexed.
First, primary keys and foreign keys have to be created to facilitate the join.
Second, in order for the fields to become member names, they must be indexed for fast search.
If both conditions are met, the new algorithm will significantly speed up the drill through (in most cases). If these conditions are not met, it may make things much slower since the tables may be scanned multiple times.
proCube's Express Drill Through is the new default setting in the Drill Through dialog. A non-Express Drill Through option still exists to allow you to include members with both non-zero and zero-only records in the database. However, because proCube must read through all records in the database, the performance will be slower.
If you are upgrading from an earlier version of proCube and are planning to user the new Express Drill Through feature on an existing OLP file, you must first rebuild the Xchange Cube using the Rebuild Metadata option.