Large OLAP hierarchies in Spotfire

When you are analyzing external cube data in Spotfire, you may encounter situations when a hierarchy filter takes a really long time to load, or even fails to load. This happens because the filter is populated in advance by reading the hierarchy outline from the cube. If the hierarchy is large (with many different levels and categories), the loading of all values at once may even cause problems for the cube data source itself.

About this task

If you notice that the creation of a filter takes an unreasonably long time, or, if you know that your cube contains a very large hierarchy, you can configure the hierarchy to be loaded on request in the filter, instead of loading the entire hierarchy at once.

Example: Configuring a cube hierarchy to be loaded on request

These steps describe how a cube hierarchy can be configured to be loaded on request, so that large OLAP hierarchies can be handled in Spotfire.

About this task

Note: This configuration must be done separately for each large hierarchy in the cube data connection.

In this example, an embedded data connection to a Microsoft SQL Server Analysis Services cube is used as an example, but you can use the same steps to configure hierarchies from any cube data source.

Procedure

Click Files and data .
In the Files and data flyout, click Connect to.
In the list of connectors, click Microsoft SQL Server Analysis Services.
In the Microsoft SQL Server Analysis Services flyout, click New connection.
In the Microsoft SQL Server Analysis Services Connection dialog, specify the Server you want to connect to.
Click Connect.
Select the Cube of interest.
Click OK.
In the Data Selection in Connection dialog, click on the object of interest in the left pane, then click Add >.
In the Data selection in connection list, locate the large hierarchy that you want to load on request and right-click on it.
From the pop-up menu, select Load Values on Request.
Repeat steps 10 and 11 for all hierarchies that you want to load on request.
When you are done with the data connection configuration, click OK.
The connection is added to the Data tables list in the Add Data Tables dialog.
The final step, the Add data to analysis flyout, is a summary view of the data tables that will be added to your Spotfire analysis. Make sure that the data table that is added from your connection is configured as External, for in-database analysis.

Tip: To change the load method of a data table to External, click the data table in the summary view, and then use the drop-down menus to select External.
Click OK.

Results

When creating a filter for the hierarchy, the values in the filter will be loaded on request.

Tip: You can edit a previously created embedded data connection by selecting Data > Data connection properties, clicking Settings and then clicking Edit. A data connection saved in the library is edited by selecting Data > Manage data connections, browsing to the connection in the library and clicking Edit.

Hierarchy filters loaded on request

When the connection has been configured to load values on request, the hierarchy filter will query the cube for the currently shown values only, and a new query is sent to the cube each time a new category is expanded.

About this task

When hierarchies are loaded on request, the filter cannot display the search field that is otherwise available. You may also see progress indications for each category you expand, as new values are fetched from the external source.

Hierarchy filters from external cube sources will include an (All) check box and the setting that indicates that a category is only partially selected will be turned on by default. These settings can be switched off in the Filtering Scheme Properties dialog.