none
Automated Batch SSAS Cube Creation RRS feed

  • Question

  • We are trialling a web OLAP cube browser that works directly from SSAS cubes. We created our first SSAS cube using BIDS. The Cube browser works fine on it.

    Now I want to bypass all that painful messing about in GUIs and script a process to create 1000 similar cubes all with the same definition, differing only in a filter (WHERE clause) on the Dimensions which can be passed in as a parameter.  Where do I start?

    Thursday, October 16, 2008 2:53 PM

All replies

  •  rvsmith wrote:
    We are trialling a web OLAP cube browser that works directly from SSAS cubes. We created our first SSAS cube using BIDS. The Cube browser works fine on it.

    Now I want to bypass all that painful messing about in GUIs and script a process to create 1000 similar cubes all with the same definition, differing only in a filter (WHERE clause) on the Dimensions which can be passed in as a parameter.  Where do I start?

    Technically you would need to create 1000 databases, as dimensions are database level objects and if you wanted to filter them in SSAS you would need to base the dimensions off a named query in the DSV and then change this query the DSV for each database.

     

    You could either do this by scripting a template database to XMLA and then injecting the altered query into the XMLA and running that 1000 times. Or you could use the .NET AMO library to do this sort of thing programatically. There is an AMOAdventureWorks sample that is part of the Microsoft samples on codeplex which shows how to build a database from scratch using .Net code.

     

    Another alternative might be to use a single database and either use dynamic security or create 1000 roles so that you have one dimension with all the members and the security provides filtering.

    Friday, October 17, 2008 2:11 AM
    Moderator