Using Edit Table Properties on a partitioned tabular model

In PowerPivot, the Edit Table Properties dialog box is used for a few things. It is used to add columns to a table, remove columns from a table, and adjust the set of rows included in the table. This works great for PowerPivot. All the data in the PowerPivot model is stored in a single partition, so there is no ambiguity as to what goes where. As soon as you change the table definition in the Edit Table Properties dialog, the partition definition for the table is modified, the table is reprocessed, the new data is shown, and all is well.

In the tabular designer things are a bit more complicated. Tables can have many partitions. Therefore, the set of rows included in the table must be defined in the Partition Manager for each individual partition, not in Edit Table Properties for the entire table. However, there is still a need to have a single definition of the set of columns in the table (the data source view). This can’t be handled at the partition level, as it is impossible to mash together all the partition definition queries for the table and come up with a coherent column set. What to do?

In the end, we decided to keep the Edit Table Properties dialog. For a table with a single partition, the Edit Table Properties dialog works the same as in PowerPivot. If you make a change to Edit Table Properties, the partition definition is automatically updated, the new data is fetched, and all is well. In a table with two or more partitions, the behaviour is different in three ways, as I describe below.

Editing the row set: Editing the row set in Edit Table Properties has no effect on a partitioned table. Even though you see the filter in the Edit Table Properties dialog, it is disregarded at processing time. The individual partition definition queries are used to fetch the data instead.

Adding a column: To add a column to a table, you must add the column using the Edit Table Properties dialog. However, for a partitioned table, just using Edit Table Properties is not sufficient. When you try to process this model after adding the column, you would get the error “The operation failed because the source does not contain the requested column. You can fix this problem by updating the column mappings.” To solve this problem, you must open the Partition Manager and update each individual partition definition to add the column you want. Once you have fixed up both Edit Table Properties and the partition definitions, you can then re-process your model and get the data.

Removing a column: You can remove a column using the Edit Table Properties dialog. This gets rid of the column in the column set, so you no longer see it in table and you can no longer query it. However, for a partitioned model, it is a good idea to modify the partition definition query and get rid of the column there as well to avoid unnecessary data coming down the wire during processing.

This information about having to use both the Edit Table Properties and Partition Manager dialog box to adjust the column set appears in the MSDN documentation, as a note in the Add Columns to a Table topic. Also there’s a little warning in the Edit Table Properties dialog for partitioned models, which tells you about both the column and the row behaviours.

If you miss the docs, you might think that Edit Table Properties is buggy. After all, the row set you see in Edit Table Properties is not necessarily the row set you get when you process. Because of the long winded explanation I gave above, the Edit Table Properties behaviour one of those things that we on the product team call “by design” (a resolution that must be the bane of many bug filers on Connect). Hopefully after this post you can now understand what is going on, and you can now add or remove rows or columns from partitioned models with impunity.

All that said, I would be interested in hearing from you if you consider the current design to be a bug, instead of a design feature. Alberto Ferrari filed a Connect item on Edit Table Properties that you can use. If you think the design should change, feel free to upvote that item and add some comments about what you would consider the correct behaviour to be. Thanks