BI: (Workaround) Changing partitioned tables in SSDT tabular designer

BI: (Workaround) Changing partitioned tables in SSDT tabular designer

I was working with a client the other day and we had what we thought was a simple situation:

  • A table in an SSAS tabular model project
  • The table had two partitions
  • We needed to remove 6 columns from the table

So, we’d already removed the 6 columns from the SQL Server view that the tables were being loaded from. We just needed to update the tabular model.

Now for an unpartitioned table, that’s easy. You open the table properties, click Design, wait for a moment, click below the query, then on the Query menu, click the option to Refresh. When you then click Import, you can then save the changes. We do that all the time.

However, the partitioned story was different.

  • If we tried to change the partitions, it would not let us save the changes because the partition differed from the table.
  • If we tried to change the table, it would not let us save the changes because the table differed from the partitions.

SIGH

There really doesn’t seem to be any great way to do it using the standard GUI interface.

Official Method

The “official” way is to:

  • Remove all but one partition
  • Make the changes
  • Put the partitions back

That doesn’t sound like much fun, particularly if you had a bunch of partitions.

Workaround

The workaround is to right-click the .bim file, then View Code, find the columns and carefully remove them from the JSON. When you save it, all is then happy.

Wish it wasn’t so, but I hope that helps someone.

2020-02-14