Deploy Tabular Models and Retain Partitions

Kasper de Jonge asked a question on Twitter that I would like to answer here to a) give it more exposure, and b) I pretty quickly ran out of 140 characters in my Twitter reply.

I initially tweeted: Problem when deploy tabular models to Dev server from SSDT & lose dynamic partitions? Deploy with BISM Normalizer & retain partitions

Kasper tweeted: Hey Christian, what do you mean by dynamic partitions?

And here is my response …

Hi Kasper,

We often partition the large tables in a tabular model to speed up processing times (as I’m sure you know).  For example, we might partition by month and then process only the most recent couple of months every night – which is a lot quicker than processing the whole table.  Normally this is done in AMO code that is called from a SSIS package – so it can be managed by the ETL to increment/drop partitions, etc.  The version of the model in source control does not contain the partition objects.

While developing in SSDT, we frequently deploy to a dev server (obviously not test/production).  This is necessary because all the dev reports are pointing at the server – not the workspace database on the developer machine.  If the dev ETL has built partitions, they are lost upon deployment from SSDT (using right-click deploy) – and they take a long time to rebuild/reprocess.

When creating a BISM Normalizer difference comparison, we have the option to “include partitions in table definitions”.  If we leave this unchecked, partitions will not be considered when comparing tables.  BISM Normalizer will treat such tables as equal and will not mess with them – thereby avoiding reprocessing.

Include Partitions Option

The SSAS Deployment Wizard is the only other deployment method for tabular models that supports “retain partitions” functionality.  However, you would not use it to deploy from SSDT to the dev server when making a change to a DAX calc or something.  BIDS Helper‘s Deploy MDX Script feature serves a similar purpose for multidimensional.

For regular Test/Production deployments, we can use the deployment wizard. Alternatively, we can use BISM Normalizer to (more easily) create an xmla script for the release to apply on Test/Production.

Another use case is bug fixes and partial deployments.  BISM Normalizer can create a script to apply on Test/Production – which updates only the calculation(s) that need to be fixed without a full release cycle – and does not mess with everything else that is already there.  The deployment wizard only supports “all-or-nothing” deployments, which are not appropriate for bug fixes and partial deployments.

Cheers!

Christian

Advertisements

16 Responses to Deploy Tabular Models and Retain Partitions

  1. javierguillen says:

    Great functionality. Super handy for deploying hotfixes without having to reprocess the entire model. Thanks Christian!

    Javier Guillen

  2. Edoardo Apollonj Ghetti says:

    Hello,
    I’m having some problems using the retain partitions functionality. Surely I’m missing something, here is what I’m doing: I create a BISM Normalizer difference comparison between BISM project(12 partitions in my table) and Tabular deployed (a unique partition). I want to deploy my changes (I’ve only renamed a column for test purpose) and I want to mantein current partitions situation. Option “include partitions in table definitions” is unchecked. Partitions are not considered as a difference in comparison results. I validate the selection, then click “Commit Changes”. The column is renamed correctly but the partition in the Tabular deployed is wiped and substituted with the 12 partitions in the project.
    What’s wrong?

    Thank’s a lot for your courtesy!

    • Christian says:

      Hi Edoardo,

      If the table structure has changed (you said you created a column for test purposes), then it will not consider the tables equal, and it has to take the full table definition from the source table (including partitions). It will only retain partitions if the table structure is the same (but with different partition objects).

      Let me know if you need more info. Thanks!
      Christian

      • Edoardo Apollonj Ghetti says:

        Ok, now I understand better. But effectively I haven’t created a new calculated column, only renamed one that is already existing. Is this considered a table structure change too?
        Thank’s for the immediate reply!

      • Christian says:

        You’re welcome!

        Yes – BISM Normalizer still considers that to be a table-structure change – even though in this case it is just a column rename. It will only retain partitions if the Action is “Skip”. If the Action is Update, it technically just replaces the whole table with the one from the source (including partition definitions), and then ensures any relationship objects from the old table are created on the new table (if necessary columns are present). This just keeps things simple instead of having to check if it would be possible to retain partitions or not depending on the type of table-structure change.

        Thanks again,
        Christian

      • edoardo apollonj ghetti says:

        Ok. Just one very last thing. In the article you say: ” BISM Normalizer can create a script to apply on Test/Production – which updates only the calculation(s) that need to be fixed without a full release cycle – and does not mess with everything else that is already there”.
        Is this not what I need to deploy changes to my table retaining the current partitions situation? And if not, is there a way to do that? What do you suggest?
        Thank’s a lot!

      • Christian says:

        No problem!

        Calculations can be deployed without any dependency on table definitions. Bug fix objects (often calculations) can be scripted without affecting other objects. Basically, for any object (table, calculation, KPI, etc.) as long as the Action is Skip, it will not make any changes to that object.

        I hope this helps.
        Christian

  3. Edoardo Apollonj Ghetti says:

    Basing on your informations I’ve performed some tests. Now the situation is clear. Thank you.
    One curios thing that I’ve noticed in my testing activities is that BISM Normalizer doesn’t recognise if I hide or unhide a column or a table in the comparison and doesn’t deploy this kind of changes. Can you confirm that? So if I’ve to hide/unhide something I’ve to deploy from SSDT using right-click deploy (with all the problems that we already know), correct?
    Tnk!

    • Christian says:

      Edoardo, you are correct that BISM Normalizer does not consider if a table/column is hidden or not. If you wish I can make it do this in the next release – although I will probably not get time to do this for a few weeks because I have other stuff I need to get done.
      Cheers,
      Christian

      • Edoardo Apollonj Ghetti says:

        It would be great! Tnk!

      • Edoardo Apollonj Ghetti says:

        Christian,
        I would like to know if you had the possibility to develop the functionality that allows BISM Normalizer to recognize if a table/column is hidden or not.
        It would be great for me in the next days!
        Tnk!

        Edoardo

      • Christian says:

        Edoardo, I have still not had the time to do it as I have been busy with other things. I am hoping to get this done by Christmas.
        Thanks,
        Christian

  4. Edoardo Apollonj Ghetti says:

    Ok Christian, tnk. I will wait until Christmas then.

    • Christian says:

      Edoardo, the new version 1.3.10 includes measure/column/table visibility for comparison. Let me know if it works for you.
      Merry Christmas!
      Christian

      • Edoardo Apollonj Ghetti says:

        Hello Christian and thank you very much! I hope you enjoied the Christmas and that 2014 is going well.
        I will test the new release in the afternoon and I will let you know if any problem occurs.

        Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: