Skip to main content

Keeping the Cubes Current

This page generally discusses how to keep the cubes current, as needed within your Business Intelligence implementation process. Additional pages describe cube synchronization and the Cube Manager in detail.

Overview

The generic phrase updating a cube refers to the process of causing a cube to reflect the current contents of the source table and related tables. The system provides three techniques:

  • Rebuild the cube, using the Build option in the Architect, for example. This process can be time-consuming, and queries cannot be executed while a cube is being rebuilt.

    • You can also use Selective Build to rebuild certain elements of the cube if you expect that only certain columns in the source table have been updated.

  • Synchronize the cube. The cube synchronization feature (also known as the DSTIME feature) enables InterSystems IRIS Business Intelligence to keep track of changes to the data. You periodically synchronize the cube to include those changes.

    It is possible to execute queries during synchronization.

    Depending on the cube implementation and depending on which data changes, it may not be possible to use this feature; see When Cube Synchronization Is Possible.

  • Update the cube manually. This process uses the %ProcessFact() and %DeleteFact() methods. Unlike with the other options, in this case, it is necessary for your code to know which records of the fact table to update or delete.

    It is possible to execute queries during the manual updates.

You can use any suitable combination of these techniques. The following table compares them:

  Rebuilding Synchronizing Updating Manually Selective Build
Comparative duration of process long short short long
Able to execute queries during this process no yes yes yes (cube elements being rebuilt not available for queries)
Technique is available in all scenarios yes no yes yes*
Technique requires you to know which records were changed no no yes no
Technique invalidates parts of the result cache yes yes no yes
User interfaces that provide this option Cube Manager and Architect Cube Manager none Architect

*Selective Build attempts to synchronize the cube at the end of the main build procedure. You can still perform a Selective Build when synchronization is not possible, but Selective Build does not update data in fact table columns other than the columns included in the build. In such cases, a full build is necessary to ensure that all data in the cube are current.

For information on the Cube Manager, see Using the Cube Manager.

Cube Updates and Related Cubes

For any kind of update, whenever you have cube-to-cube relationships, it is necessary to update the cubes in a specific order. In particular, update the independent cube first. Then update any cubes that depend on it. To do this, you can use the Cube Manager, which traverses the relationships and determines the correct update order.

Or you can write and use a utility method or routine that builds your cubes in the appropriate order.

Cube Updates and the Result Cache

For any cube that uses more than 512,000 records (by default), the system maintains and uses a result cache. For any combination of update techniques and tools, you should also carefully consider the frequency of cube updates, because any update could invalidate parts of the result cache.

For large data sets, the system maintains and uses a result cache for each cube as follows: Each time a user executes a query (via the Analyzer for example), the system caches the results for that query. The next time any user runs that query, the system checks to see if the cache is still valid. If so, the system then uses the cached values. Otherwise, the system re-executes the query, uses the new values, and caches the new values. The net effect is that performance improves over time as more users run more queries.

When you update a cube by synchronizing or rebuilding it, the system clears the parts of the result cache which are no longer valid. The details depend upon options in the cube definition (see Cache Buckets and Fact Order). Therefore, it is not generally desirable to update constantly.

Note:

Manually updating a cube does not automatically invalidate the results cache. This is because InterSystems IRIS determines when cached results are outdated based on entries in the ^OBJ.DSTIME global, which the %ProcessFact()Opens in a new tab and %DeleteFact()Opens in a new tab methods do not update. (^OBJ.DSTIME acts as a buffer in the automatic processes for updating cubes, as described in the next section.) To ensure that queries on the cube do not return cached results which are outdated, you must invoke the %SetCubeDSTime() method after manual updates (for example, by calling it in the %OnAfterProcessFact() method of the cube class). Alternatively, you can invoke %SynchronizeCube() to invalidate the cache after a manual updates if you take precautions to ensure that fact insertions are not duplicated (see Updating Cubes Manually).

Updating Cubes Manually

As described in When Cube Synchronization Is Not Possible, it is sometimes necessary to update a cube manually. In these situations, your application must do the following:

  1. Determine the IDs of the affected records in the base class.

  2. Update the cube for those records by calling the %ProcessFact() and %DeleteFact() methods of %DeepSee.UtilsOpens in a new tab.

    As input, these methods require the ID of the affected row or rows.

Note:

%ProcessFact enables the developer to completely control single-ID inserts or updates into a DeepSee cube. In proivding that capability it bypasses the concurrency protection that are provided within %BuildCube and %SynchronizeCube to prevent multiple processes from attempting the same work.

When including %ProcessFact in custom code, it is strongly recommended that this code prevents multiple calls on the same cube, ID pair. Without this protection there is known potential to perform duplicate inserts into the fact table if %ProcessFact is simultaneously called on the same ID in multiple processes.

The following list provides information on these methods:

%ProcessFact()
classmethod %ProcessFact(pCubeName As %String, 
                         pSourceId As %String = "", 
                         pVerbose As %Boolean = 0) as %Status 

Where pCubeName is the logical name of a cube, and pSourceID is the ID of a record in the base class used by that cube. For the given cube, this method updates the corresponding row of the fact table, the associated indexes, and any level tables if affected.

If pVerbose is true, the method writes status information to the console.

%DeleteFact()
classmethod %DeleteFact(pCubeName As %String, 
                        pSourceId As %String = "", 
                        pVerbose As %Boolean = 0) as %Status

Where pCubeName is the logical name of a cube, and pSourceID is the ID of a record in the base class used by that cube. For the given cube, this method deletes the corresponding row of the fact table and updates the indexes correspondingly.

If pVerbose is true, the method writes status information to the console.

Disabling Cubes

In certain scenarios, you may wish to temporarily disable a cube. This can serve to prevent users from encountering errors when attempting to use a cube while its definition is being edited, or when correcting a known error. Unlike deleting a cube, disabling a cube preserves the code apart from whatever is manually edited. As a disabled cube becomes invisible to the Cube Manager, InterSystems strongly advises against disabling cubes which already have established relationships.

In order to disable a cube, perform the following procedure:

  1. Log in to the Management Portal as a user with administrative privileges.

  2. Ensure you are in the desired Analytics-enabled namespace.

  3. Navigate to Home > Analytics and click GO.

  4. Click Open and select the appropriate cube from the pop-up window.

  5. In the Details pane to the right of the interface, you will see a checkbox labeled Disabled. Click this to disable the cube.

Once you have implemented the changes you wish to implement, you may reenable the cube by unchecking the Disabled box described above. You will be required to rebuild the cube when reenabling.

Injecting Facts into the Fact Table

In rare cases, you might need the fact table to include records that do not correspond to any source records. In such cases, use the %InjectFact() method of the cube class.

This method has the following signature:

classmethod %InjectFact(ByRef pFactId As %String, 
                        ByRef pValues As %String, 
                        pDimensionsOnly As %Boolean = 0)
                        as %Status

Where:

  • pFactId is the ID of the fact. Set this to "" for an insert. On return, this argument contains the ID used for the fact.

  • pValues is a multidimensional array of fact values. In this array, the subscript is the sourceProperty name (case-sensitive).

  • pDimensionsOnly controls whether the method affects both the fact table and dimension tables or just the dimension tables. If this argument is true, the method affects only the dimension tables. You use this argument if you prebuild the dimension tables as described in the next section.

    Caution:

    Do not use this method to update dimension tables for levels that are based on source expressions. To add records to those tables, instead use an SQL UPDATE statement.

    You can use %InjectFact() to update dimension tables for levels that are based on source properties.

Pre-building Dimension Tables

By default, the system populates the dimension tables at the same time that it builds the fact table. It is possible to prebuild one or more dimension tables so that they are populated before the fact table, if this is necessary for some reason.

To pre-build one or more dimension tables, do the following:

  • Implement the %OnBuildCube() callback in the cube definition class. This method has the following signature:

    classmethod %OnBuildCube() as %Status
    

    The %BuildCube() method calls this method just after it removes the old cube contents and before it starts processing the new contents.

  • In this implementation, invoke the %InjectFact() method of the cube class and specify the pDimensionsOnly argument as true.

    For details on this method, see the previous section.

For example, the following partial implementation predefines the Cities dimension in the HoleFoods sample:

ClassMethod %OnBuildCube() As %Status
{
    // pre-build City dimension
    Set tVar("Outlet.Country.Region.Name") = "N. America"
    Set tVar("Outlet.Country.Name") = "USA"

    Set tVar("Outlet") = 1000
    Set tVar("Outlet.City") = "Cambridge"
    Do ..%InjectFact("",.tVar,1)

    Set tVar("Outlet") = 1001
    Set tVar("Outlet.City") = "Somerville"
    Do ..%InjectFact("",.tVar,1)

    Set tVar("Outlet") = 1002
    Set tVar("Outlet.City") = "Chelsea"
    Do ..%InjectFact("",.tVar,1)

    Quit $$$OK
}

Notes:

  • It is necessary to provide a unique ID as well as a name for a member.

  • For completeness, this code should also provide the city population, longitude, and latitude, because the corresponding dimension table contains these values.

  • It is also necessary to provide values for any higher level members.

Updating a Dimension Table Manually

In some cases, there is no change to your base class, but there is a change to a lookup table that is used as a level. In these cases, you can update the cube in any of the ways described earlier in this page. If the only change is to a single dimension table, however, it is quicker to update the level table directly. You can do so via the %UpdateDimensionProperty() method of %DeepSee.UtilsOpens in a new tab.

This method has the following signature:

classmethod %UpdateDimensionProperty(pCubeName As %String, 
                                     pSpec As %String, 
                                     pValue As %String, 
                                     pKey As %String) 
                                     as %Status

Where:

  • pCubeName is the name of the cube.

  • pSpec is the MDX member expression that refers to the level member to update. You must use the dimension, hierarchy, and level identifiers in this expression. For example: "[docd].[h1].[doctor].&[61]"

    As a variation, pSpec can be a reference to a member property. For example: "[homed].[h1].[city].&[Magnolia].Properties(""Principal Export"")"

    The system uses this argument and the pCubeName argument to determine the table and row to update.

  • pValue is the new name for this member, if any.

    Or, if you specified a member property, pValue is used as the new value of the property.

  • pKey is the new key for this member, if any.

    Specify this argument only if you specify a member for pSpec.

You can make three kinds of changes with this method:

  • Specify a new key for a member. For example:

    Set tSC = ##class(%DeepSee.Utils).%UpdateDimensionProperty("patients","[docd].[h1].[doctor].&[186]",,"100000")
    

    By default, the key is also used as the name, so this action might also change the name.

  • Specify a new name for a member. For example:

    Set tSC = ##class(%DeepSee.Utils).%UpdateDimensionProperty("patients","[docd].[doctor].&[186]","Psmith, Alvin")
    

    By default, the name is the key, so this action might change the key.

  • Specify a new value for some other property (both Name and Key are properties). For example:

    Set memberprop="homed.h1.city.Pine.Properties(""Principal Export"")"
     
    Set tSC = ##class(%DeepSee.Utils).%UpdateDimensionProperty("patients",memberprop,"Sandwiches")
    

See Also

FeedbackOpens in a new tab