locked
Define SSAS Tabular measures in batch? RRS feed

  • Question

  • We have an Excel PowerPivot model that we are trying to move to SSAS Tabular. The model is complex and has over 300 measures. To help group these in Excel, we pasted in dummy tables and defined the measures in logical groupings. Now when we are migrating to SSAS, we have to delete the pasted tables --that have ALL of the measures, in order for the import to work successfully. Any suggestions on how to migrate the model? We have a nice report of all of the measures from DAX Studio, but I am not looking forward to manually re-entering 300 measures through the user interface. Any ideas on how to do this easier? Is there any way to define measures in batch? Or is there a way to change the pasted table to a file so the import will work? Will the import work in SSAS 2016?
    Thursday, November 19, 2015 3:08 PM

Answers

  • In SSAS 2016, the model will be defined by a JSON document. You will be able to script the measures. You would have to modify the list of measures you have to fit whatever format is required in SSAS - I do not know yet what this format looks like, but it's beginning to be rolled out in CTP3 for SQL Server.

    You will still not be able to import pasted tables.

    If you can wait for SQL Server 2016, then that's your solution. If you can't wait, then I suggest getting an intern.

    In the future in Power Pivot models, if you're just trying to get blank tables, use a stupid SQL query instead of pasting blank tables:

    SELECT
        BlankColumn = NULL



    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Wednesday, December 2, 2015 8:39 AM
    • Marked as answer by Charlie Liao Thursday, December 3, 2015 8:38 AM
    Thursday, November 19, 2015 3:32 PM
  • The new json bim file format looks pretty nice, but I don't think we even have a release date for SQL 2016 so that is months away.

    If you use a dummy query like Greg suggests above that can replace you pasted table. You could create a number of dummy columns to group your measures.

    Then there is a project on codeplex called DaxEditor - which should let you edit your DAX calculations as one long script http://daxeditor.codeplex.com/

    Unfortunately it won't let you logically group your measures in the measure grid, but that information is stored in the .layout file for your project, so there may be a way to manually update that, but it has some double encoded xml so you'd need to build a script of some sort and this is not something I've tried. Otherwise you'll need to drag and drop your measures around in the grid which is not a great experience as each drop will generate an update to your workspace model which introduces an annoying delay and there is no way to switch that off.


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Charlie Liao Wednesday, December 2, 2015 8:39 AM
    • Marked as answer by Charlie Liao Thursday, December 3, 2015 8:38 AM
    Thursday, November 19, 2015 8:02 PM

All replies

  • In SSAS 2016, the model will be defined by a JSON document. You will be able to script the measures. You would have to modify the list of measures you have to fit whatever format is required in SSAS - I do not know yet what this format looks like, but it's beginning to be rolled out in CTP3 for SQL Server.

    You will still not be able to import pasted tables.

    If you can wait for SQL Server 2016, then that's your solution. If you can't wait, then I suggest getting an intern.

    In the future in Power Pivot models, if you're just trying to get blank tables, use a stupid SQL query instead of pasting blank tables:

    SELECT
        BlankColumn = NULL



    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Wednesday, December 2, 2015 8:39 AM
    • Marked as answer by Charlie Liao Thursday, December 3, 2015 8:38 AM
    Thursday, November 19, 2015 3:32 PM
  • The new json bim file format looks pretty nice, but I don't think we even have a release date for SQL 2016 so that is months away.

    If you use a dummy query like Greg suggests above that can replace you pasted table. You could create a number of dummy columns to group your measures.

    Then there is a project on codeplex called DaxEditor - which should let you edit your DAX calculations as one long script http://daxeditor.codeplex.com/

    Unfortunately it won't let you logically group your measures in the measure grid, but that information is stored in the .layout file for your project, so there may be a way to manually update that, but it has some double encoded xml so you'd need to build a script of some sort and this is not something I've tried. Otherwise you'll need to drag and drop your measures around in the grid which is not a great experience as each drop will generate an update to your workspace model which introduces an annoying delay and there is no way to switch that off.


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Charlie Liao Wednesday, December 2, 2015 8:39 AM
    • Marked as answer by Charlie Liao Thursday, December 3, 2015 8:38 AM
    Thursday, November 19, 2015 8:02 PM