DeepSee Implementation Guide
How the DeepSee Query Engine Works
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Search:    

This appendix explains how DeepSee executes queries. You may find this information useful when you are viewing query plans or diagnosing problems. This appendix discusses the following topics:

Important:
This appendix provides some information on globals used internally by DeepSee. This information is provided for demonstration purposes; direct use of these globals is not supported. The organization of these globals is subject to change without notice.
Introduction
This section introduces the basic concepts. The next section provides a more detailed description.
Use of Bitmap Indices
When you compile a cube class, DeepSee creates the fact table class that the engine uses. This class defines all bitmap indices as needed by the engine; these are stored in the global ^DeepSee.Index. When you build or synchronize a cube, DeepSee updates these indices as appropriate. When it is necessary to find records in the fact table, the query engine combines and uses these bitmap indices as appropriate.
As an example, one bitmap index provides access to all the records that contribute to the Snack member of the Product Category level. Another bitmap index provides access to all the records that contribute to the Madrid member of the City level. Yet another provides access to all the records that contribute to the 2012 member of the YearSold level. To find all the records that contribute to Snack, Madrid, and 2012, DeepSee combines those bitmap indices and then uses the resulting index to retrieve the records.
Caching
For any cube that uses more than 512,000 records (by default), DeepSee maintains and uses a result cache. In this case, whenever DeepSee executes queries, it updates the result cache, which it later uses wherever possible. The result cache includes the following globals:
These globals are not populated until users execute queries. The cache grows in size as more queries are executed, resulting in faster performance because DeepSee can use the cache rather than re-executing queries.
Note that the cache does not include values for any properties defined with isReference="true". These values are always obtained at runtime.
Buckets
For any cube that uses more than 512,000 records (by default), DeepSee organizes the cache into buckets. Each bucket corresponds to a large number of contiguous records in the fact table, as shown in the following figure:
The final bucket (or partial bucket) is the active bucket and is not represented in the cell cache.
By default, the fact table contains records in the same order as the source table. You can specify Initial build order for the cube to control the order in which DeepSee examines the source table records when it performs a full build of the cube; see Other Cube Options in Defining DeepSee Models.
Whenever the fact table is updated, DeepSee discards parts of the cache as appropriate. More specifically, DeepSee invalidates any buckets that use records from the affected part or parts of the fact table. Other buckets are left alone. When it executes a query, DeepSee uses cached data only for the valid buckets. For records that do not have valid cached results, DeepSee uses the bitmap indices and recomputes the needed intermediate values. As the last phase of query execution, DeepSee consolidates the results. Thus DeepSee can provide results that come from a combination of cached data and new or changed data. Also, because some of the engine work can be split by bucket, DeepSee can (and does) perform some processing in parallel.
Default Bucket Size
By default, a bucket is 512,000 records. The bucket size is controlled by the bucketSize option, which expresses the bucket size as an integer number of groups of records, where a group is 64,000 contiguous records. The default bucketSize is 8, so that the default bucket is 8 x 64,000 records or 512,000 records. For information on bucketSize, see <cube> in Defining DeepSee Models.
Engine Steps
To process a query, DeepSee performs the following steps:
  1. Preparation, which occurs in process (that is, this step is not launched as a background process). In this phase:
    1. DeepSee parses the query and converts it to an object representation, the parse tree.
      In the parse tree, each axis of the query is represented separately. One axis represents the overall filtering of the query.
    2. DeepSee converts the parse tree to a normalized version of the query text.
      In this normalized version, for example, all %FILTER clauses have been combined into a single, equivalent WHERE clause.
    3. DeepSee generates a hash that is based on the normalized query text. DeepSee uses this hash value as the query key. The query key enables DeepSee to look up results for this query in the globals discussed in this appendix.
    4. If DeepSee finds that it is possible to reuse previous results for this query (from ^DeepSee.Cache.Results), DeepSee does so and skips the following steps.
  2. Execute axes, which also occurs in process. In this phase:
    1. DeepSee executes any subqueries.
    2. DeepSee examines the slicer axis (the WHERE clause), merges in any relevant filtering (such as from a subject area filter), and updates ^DeepSee.Cache.Axis with information about this axis.
    3. DeepSee examines each of the remaining axes and updates ^DeepSee.Cache.Axis.
  3. Execute cells, which occurs in the background (in multiple parallel processes). In this phase, DeepSee obtains intermediate values for each cell of the results, separately for each bucket, as follows:
    1. First DeepSee checks to see if ^DeepSee.Cache.Cell contains a value for the cell for the given bucket.
      If so, DeepSee uses that value.
    2. Otherwise, DeepSee uses the applicable nodes of ^DeepSee.Index to obtain the bitmap indices that it needs. DeepSee combines these bitmap indices and then uses the result to find the applicable records in the source table.
      If the cache uses buckets, DeepSee adds nodes to ^DeepSee.Cache.Cell for use by later queries.
  4. Consolidation, which occurs in process. In this phase:
    1. For each slicer axis, DeepSee examines each result cell for that axis.
      For each result cell, DeepSee finds all the nodes in ^DeepSee.Cache.Cell that contain values for this cell.
      It then combines those values.
    2. For each result cell, DeepSee then combines the results across the slicer axes and obtains a single value.
      For information, see the next section.
    DeepSee evaluates the CURRENTMEMBER function during the consolidation phase. In contrast, it evaluates other functions earlier in the processing.
Axis Folding
In the consolidation phase, if there are multiple slicer axes, DeepSee combines results across these axes, for each result cell. This step is known as axis folding.
Important:
Axis folding means that if a given source record has a non-null result for each slicer axis, that record is counted multiple times.
To determine whether axis folding is required, DeepSee considers all the filters applied to the query, from all sources: the subject area, the pivot table, and the dashboard. The net combination of these filters determines whether axis folding is needed, as follows. The following table lists the main possibilities:
Form of Filter Axis Folding Needed?
Single member. Example: [PRODUCT].[P1].[PRODUCT CATEGORY].&[Candy] No
Single measure. Example: [MEASURES].[Units Sold] No
A tuple (combination of members or of members and a measure). Example: ([Outlet].[H1].[City].&[7],[PRODUCT].[P1].[PRODUCT CATEGORY].&[Candy]) No
A cross join (another form of combination). Example: NONEMPTYCROSSJOIN([Outlet].[H1].[City].&[7],[PRODUCT].[P1].[PRODUCT CATEGORY].&[Candy]) No
The %OR function, wrapped around a set expression that lists multiple members. Example: %OR({[Product].[P1].[Product Category].&[Candy],[Product].[P1].[Product Category].&[Snack]}) No
A set expression that lists multiple members but does not use %OR. Example: {[Product].[P1].[Product Category].&[Candy],[Channel].[H1].[Channel Name].&[2]} Yes
To create these expressions (as filters) in the Analyzer, you generally drag and drop items to the Filters box. To create the set expressions in the last two rows, you must use the Advanced Filter editor. Note that DeepSee automatically uses the %OR function when possible; the Advanced Filter editor does not display it as an option.
Query Plans
If you execute a query in the Query Tool, you can see the query plan. Similarly, if you execute a query programmatically (as described earlier in this book), you can call the %ShowPlan() method of your result set. For example:
SAMPLES>do rs1.%ShowPlan()
-------------- Query Plan ---------------------
**SELECT {[MEASURES].[AVG TEST SCORE],[MEASURES].[%COUNT]} ON 0,[AGED].[AGE 
BUCKET].MEMBERS ON 1,[GEND].[GENDER].MEMBERS ON 2 FROM [PATIENTS]****
DIMENSION QUERY (%GetMembers): SELECT %ID,DxAgeBucket MKEY, DxAgeBucket 
FROM DeepSee_Model_PatientsCube.DxAgeBucket ORDER BY DxAgeBucket**
**DIMENSION QUERY (%GetMembers): SELECT %ID,DxGender MKEY, DxGender 
FROM DeepSee_Model_PatientsCube.DxGender ORDER BY DxGender**
**EXECUTE: 1x1 task(s) **
**CONSOLIDATE**
-------------- End of Plan -----------------
Note that line breaks and spaces have been added here to format the documentation properly for its PDF version.
Query Statistics
If you execute a query programmatically (as described earlier in this book), you can call the %PrintStatistics() method of your result set. For example:
SAMPLES>do rs1.%PrintStatistics()
Query Statistics:
 Results Cache:                        0
 Query Tasks:                          1
 Computations:                        15
 Cache Hits:                           0
 Cells:                               10
 Slices:                               0
 Expressions:                          0
 
 Prepare:                          0.874 ms
 Execute Axes:                   145.762 ms
  Columns:                         0.385 ms
  Rows:                          144.768 ms
   Members:                      134.157 ms
 Execute Cells:                    6.600 ms
 Consolidate:                      1.625 ms
 Total Time:                     154.861 ms
 
ResultSet Statistics:
 Cells:                                0
 Parse:                            3.652 ms
 Display:                          0.000 ms
 Total Time:                       3.652 ms
The values shown here are as follows: