Working with data from Microsoft SQL Server Analysis Services in Spotfire

If you have access to data in a Microsoft SQL Server Analysis Services system, you can use the Microsoft SQL Server Analysis Services Connector in Spotfire to connect to your OLAP cube. On this page, you can find information about how to work with Analysis Services data in Spotfire.

Dimensions and Attributes

Dimensions organize data with relation to an area of interest, for example, time, product, customer, region, etc. An attribute is a kind of categorization.

For each dimension, you can be interested in a number of different dimension attributes. For example, the 'Product' dimension may be investigated with regard to the attributes 'Product.Color' or 'Product.Model'. These attributes can also be organized into multi-level hierarchies.

Measure Groups

Measure groups are used to associate dimensions with measures.

A cube includes measures in measure groups, business logic, plus a collection of dimensions that give context for evaluating the numerical data that a measure provides.

Both measures and measure groups are an essential component of a cube. A cube cannot exist without at least one of each.

Spotfire allows you to select a single measure group in the Data Selection in Connection dialog, which can help you limiting the data from the cube:

Note that if you select a dimension belonging to another measure group you will automatically also include that measure group in the selection and the measure group will also be visible in the Data in analysis flyout. (However, you will not get the measures from that measure group, only selected measures.)

Most of the times, you will probably only be interested in analyzing data from a single measure group, but there may be exceptions. It is important to know what you are doing if you are working with cube data in Spotfire. For example, if data is selected from two different, unrelated measure groups you might end up with empty visualizations.

Hierarchies

Hierarchies are used to organize measures that are contained in a cube. In Spotfire, attribute hierarchies and single-level user hierarchies are represented as columns, whereas multi-level hierarchies are represented as hierarchies.

In Microsoft SQL Server Analysis Services, attributes are, by default, organized into two-level hierarchies consisting of a leaf level and an optional 'All' level. The All level contains a single member – the All member – which represents the aggregated value of the attribute's members across the related measures. Note that if the hierarchy is non-aggregatable, the All level is not created.

Henceforth, this structure is called an attribute hierarchy, to distinguish it from a user hierarchy. A user hierarchy is a user-defined hierarchy of attributes that is used in Microsoft SQL Server Analysis Services to organize the members of a dimension into a hierarchical structure that provide navigation (drill-down, drill-up) paths, by which users can browse the data. The positions of members within the hierarchy are controlled by the order of the attributes in the hierarchy's definition. Each attribute in the hierarchy definition constitutes a level in the hierarchy.

Attribute hierarchies are represented as a single column in Spotfire, containing the leaf level members and an implicit All member (if defined). The All member is not visible as a column value in Spotfire.

User hierarchies are represented as hierarchies in Spotfire unless the user hierarchy is a single level (1 level) hierarchy. Then the hierarchy is represented as a column in Spotfire.

Microsoft SQL Server Analysis Services cubes in Spotfire

Normally, you would use measures on axes where actual values are to be displayed, such as, on the value axis of a bar chart, or as cell values in a cross table. Dimension columns, that is, attribute hierarchies and user hierarchies, can be used to split the viewed data into smaller subsets on categorical axes.

When you look at cube data in the Data in analysis flyout, you have the possibility to select one measure group and one related dimension at a time, which may help you selecting suitable options on the axes. The whole cube is treated as a single data table in Spotfire when you analyze it in-database.

There are also other implications of working with cube data. Since the measures are defined by the context of a dimension, it is not possible to create filters for measures.

When importing cube data, all cube aggregations are lost. Hierarchies are imported down to the leaf level and measure values for that level are retrieved. See Importing data from cube data sources more information.

Named Sets

Named sets return a data set based on a defined logic. They are primarily used to create data sets that are often requested from the cube.

There are two types of named sets – static named sets and dynamic named sets.

Note: Spotfire currently only supports named sets that return a certain single level in a user hierarchy or an attribute hierarchy (i.e., a 1-level hierarchy). That is, Spotfire can only handle results that can be seen as a single column.

Static named sets are evaluated once during creation – they are never re-evaluated. Dynamic named sets, on the other hand, are evaluated each time a query references it and is evaluated in a current context. For example, a dynamic set may show the Top 50 customers. This dynamic set could either be displayed over different countries in the world, or within a single country, when using standard cube reporting tools.

When you are working with dynamic named sets in Spotfire, the content of a dynamic named set column depends on the current context, that is, it depends on the current limitations and filtering. Therefore, the axis expression must be evaluated after the current filtering is applied, when dynamic sets are used in visualizations.

This is different from how axes traditionally are evaluated in Spotfire, but the 'evaluate after'-setting is applied automatically when you are placing a dynamic named set column on an axis. See Axis settings for more information.

If you limit the selected data to male customers only, and use a set showing Top 50 customers, you will get the top 50 male customers. If limitations make the number of available male customers lower than 50, then this is the number you will end up with. All of these calculations are handled by the cube.

Note: If you are going to import data from a Microsoft SQL Server Analysis Services cube, and named sets are included in the selection, the sets will be used to limit the data retrieved on import and to calculate the measures. For example, if the selection of data to import contains data from Germany and France, and a Top 50 customers set is used in the import, the Top 50 customers from Germany and France are imported. Filtering out Germany from the imported data may result in less than 50 values, because the set will now show only those customers that were imported from France. The same selection of data when working in-database will add the set as a dimension and the filtering will let you see the Top 50 values from France.

Known limitations on import

During import, sets are applied once. This means that selection of multiple sets with no intersecting data will result in a data table without any data.
PKI:s are not supported.