Post reply

SSAS2005 - How to create measures Folders with a script ?

  • June 4, 2008 at 2:08 am

    #186333

    Hi all,

    I use an application that generate SSAS 2005 cubes.

    My problem is that I have more than 100 measures and to simplify the building of reports, I would like to create Folders in which I 'll classify the measures.

    I know that I can add these folders manually in visual studio but when I deploy an existing cube the folders are erased.

    So my question is : there is a way to generate these folders using a script ?

    Thanks in advance.

    Stephane

  • June 5, 2008 at 6:58 am

    #824764

    When a script means a xmla-script, you should use the DisplayFolder element.

    Script the AdventureWorks-cube for an example.

    Peter Rijs
    BI Consultant, The Netherlands

  • July 22, 2008 at 5:46 am

    #846306

    I have succeed in creating an xmla file and I have tried to select lines in the code to create a folder

    (cf file script xmla.jpg)

    But this doesn't work, I have an error message which tells me that a measuregroup is missing in my query.

    I don't want to specify all elements of the cube in my query. I just want to add my measures in different folders.

    Does anyone can help me?

    Thanks

    Stéphane

  • July 23, 2008 at 2:59 am

    #846879

    Stephane,

    The code you give shows that you are trying to use a calculation. If I understand you question correctly, you should instead alter the DisplayFolder for the measures you want to change.

    See the example below for the correct placement in the xml-hierarchy (Note: I deleted several parts for readability).

    You should be able to alter measures for a single measuregroup at a time, or for the whole cube at once. However, you need to add/alter the displayfolder node in every measure node.

    {Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"}

    {Object}

    …

    {/Object}

    {ObjectDefinition}

    {MeasureGroup …}

    …

    {Measures}

    {Measure}

    {ID}Sales Amount 2{/ID}

    {Name}Internet Sales Amount{/Name}

    {DataType}Currency{/DataType}

    {Source}

    {DataType}Currency{/DataType}

    {Source xsi:type="ColumnBinding"}

    {TableID}dbo_FactInternetSales{/TableID}

    {ColumnID}SalesAmount{/ColumnID}

    {/Source}

    {/Source}

    {DisplayFolder}TestDisplayFolder{/DisplayFolder}

    {FormatString}Currency{/FormatString}

    {MeasureExpression}[Internet Sales Amount] / [Average Rate]{/MeasureExpression}

    {Translations}

    …

    {/Translations}

    {/Measure}

    …

    {/Measures}

    …

    {/MeasureGroup}

    {/ObjectDefinition}

    {/Alter}

    (To be shown on this forum I had to replace some characters by other, but I think it is readable this way)

    Peter Rijs
    BI Consultant, The Netherlands

  • July 23, 2008 at 5:22 am

    #846913

    Peter, thanks for your answers.

    I have more than one hundred calculated measures in my cube.

    When I deploy a cube all folders are deleted and the deployement frequency is every 4 hours.

    I need to automatize the process to create folders, because I can't imagine me inserting one hundred lines in the xmla code every 4 hours.

    Do I have to export after each deployement an xmla file and modify it to add a line for each folder or can I just use a part of the xmla query to create only the folders?

    Notice that the name of calculated measures will not change.

    Stephane

  • July 23, 2008 at 5:57 am

    #846940

    Stephane,

    Can you elaborate on deploying every 4 hours?

    I don't quite understand why and from where you (automatically) deploy every 4 hours, especially when you don't want to use BIDS to set the proper DisplayFolders.

    For a deployment you need to have the xmla somewhere, so you should be able to alter the DisplayFolder in that source.

    Only way I can think of now is that you have some 3rd party app deploying the cube every 4 hours, and you can't get to the source to alter the DisplayFolders.

    In that case I suggest you write some little proggie (in whatever you're familiar with), to pick up the xmla file after deployment and add the DisplayFolder attrributes where needed. Thinking of it, it might even be SSIS 🙂

    Peter Rijs
    BI Consultant, The Netherlands

  • July 23, 2008 at 7:19 am

    #847016

    You are right.

    I have a 3rd party app deploying the cube every 4 hours, and I can't modify the source.

    I am going to search how to automatize the modification of the xmla.

    Thanks for your help.

    Stéphane

  • July 23, 2008 at 8:17 am

    #847080

    Instead of modifying the xmla: You should also be able to pull this off using AMO (Analysis Management Objects) from C#, VB.Net, or a SSIS scripting task, check out http://msdn.microsoft.com/en-us/library/ms124924.aspx for more detail on AMO.

    Please let us know how you tackled this finally, it might be of benefit to others (and besides that, I'm just curious :w00t: )

    Peter Rijs
    BI Consultant, The Netherlands

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply