SSAS: Data selector for cubes

Technical Value

Recently we had a request from one of our customers to provide a reduced version of the cube. The idea behind was to let a certain department to work with the dedicated cube which holds only the small portion of data. This brings not only performance for queries, but also reduces the processing time and makes it easier to move or backup the cube.

We have reformulated this request as following requirements:

  • the reduced version must have exactly same metadata and same source database, but…
  • …must have a reduced set of members in some dimensions
  • …must load facts only for the remaining dimension members

The first requirement also implies only one version of objects in the Data Source View.

We found the following elegant solution: SQL queries return a full or a reduced datasets for dimensions and facts based on the value of APP_NAME() which we provide in the connection property “Application Name” for data source:


Here is the example of the query (DimOrganization in AdventureWorks):

  1. SELECT o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership,
  2.             o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
  3. FROM   dbo.DimOrganization AS o INNER JOIN
  4.        dbo.DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
  5. WHERE APP_NAME()!='REDUCED_CUBE' OR c.CurrencyAlternateKey='USD'

The query returns records only with “USD” for the reduced cube.

This solution can be used not only to reduce production cubes, but also for development to speed up the processing.

Hope it helps!

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden