Skip to main content

Functional Differences Between AtScale and Microsoft Excel

The following sections describe differences in the ways models appear and behave in Microsoft Excel vs. AtScale.

Default measure​

SSAS has a concept of a default measure, which Excel uses for measureless queries. Even if one isn't explicitly selected, one is chosen. For the AtScale sample model, it appears to be one of the measures in the Internet Sales fact table such as ISA or IOQ. This means that measureless queries should be thought of as having that measure present, with the rules for unrelated dimension handling being applied. For example, when related dimensions such as Color are selected, only the members with Internet Sales data are displayed, meaning Grey is left out. However, selecting an unrelated dimension like Reseller Bank will display every member.

AtScale does not use a default measure. In all cases, all members in measureless queries without filters are displayed.

Filtering measureless queries​

In Excel, when rows and columns are used, the unrelated dimension handling rules apply with respect to the default measure. When two dimensions unrelated to the default measure are put on rows and columns (one each), they do not take into account their shared related fact table. For example, placing Reseller Bank on rows and Due.StandardMonth on columns will display every member for reach, even though they are both related to RSA.

In AtScale, when query.factless.ignoreIncidentalFilter=true (ignoreIncidentalFilter for short, see About Factless Queries), filters are not applied to measureless queries, unless they are related outside of the fact table, e.g. selecting Customer and filtering on Country will change which Customer members are displayed.

Dimensional filters​

In Excel, dimensional filters apply with respect to the default measure, but also with respect to related selected dimensions. Examples:

  • Filtering on Color=NA when only StandardMonth.Year is selected will show only 2007 and 2008.
  • Filtering on unrelated dimensions with respect to the default measure only applies when the filter is from the same dimension. If ResellerName is selected, and Country=Germany is applied, no change to ResellerName will occur. However, filtering on Reseller Order Month will change which ResellerName rows are displayed.

In AtScale, when ignoreIncidentalFilter=false, filters are always applied to measureless queries. Unrelated filters with respect to the selected dimension fact tables are not applied.