Working with 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.

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, instead of loading the entire hierarchy at once.

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

To configure a cube hierarchy to be loaded on request:

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.

1. On the authoring bar, click Files and data .

2. Click Connect to > Microsoft SQL Server Analysis Services.

3. Click New connection.

4. Specify the Server you want to connect to, and specify any other information required for establishing a connection.

5. Click Connect.

6. Select the Cube of interest.

7. Click OK.

8. In the Data Selection in Connection dialog, click on the object of interest in the left pane, then click Add >.

9. In the Data selection in connection list, locate the large hierarchy that you want to load on request and right-click on it.

10. From the pop-up menu, select Load Values on Request.

    

11. Repeat steps 8 and 9 for all hierarchies that you want to load on request.

12. When you are done with the data connection configuration, click OK.

    Response: The connection is added to the summary view in the flyout.

13. Make sure that the selected load method is External.

14. Click OK.

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 this way, 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.

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.