Search code examples
ssas

SSAS Development Using Top n In Queries


I'm fairly new to SSAS development and when the existing team was giving me a run through of the existing SSAS project they mentioned that every query has a SELECT TOP *n* in it that they then manually go into the XML file and comment out when they are ready to migrate to production (and make sure you pick an n that no one else is using).

This was done because it takes too long to import the data into Visual Studio without the TOP n in the queries.

Is this really best practice, or is there a better way to set up the development environment so that you don't have to comment out code before a deployment?


Solution

  • I assume you are talking about Analysis Services Tabular which does load the data at design time into memory in your "workspace database" which is usually a local Analysis Services Tabular instance.

    You may consider creating a SQL view layer and building the Analysis Services model on top of the views. This recommendation is mentioned here with reasons: http://www.jamesserra.com/archive/2013/03/benefits-of-using-views-in-a-bi-solution/

    But SELECT TOP X may not be enough. For example, if SELECT TOP 100 * FROM FactSales only returns fact rows for stores in the southwest but SELECT TOP 100 * FROM DimStore only returns stores in the northeast then it will be challenging to develop your model and calculations because everything will be rolling up to the blank store. So consider putting some more intelligent filter logic into the views.