Renaming Columns Without Breaking the Schema

by · Published · Updated

This is just a quick tip that came up in the Ask Bryan comments the other day that I thought was post-worthy.

MicroStrategy creates logical objects that relate to the database model for the schema to build off of.  This includes logical objects for Database Tables and Columns.  Unfortunately, this can mean that Database level changes can become cumbersome to apply.  In 8.x, if you wanted to rename a column in the database, it required that you add the column as new so that you could manually move all of the Schema Objects (Attributes or Facts) to the new definition before you could delete the old.  If you had a large schema, it could be a lot of work.  Fortunately, in 9.x, there is a quick trick to do this change very fast.

MicroStrategy Architect is a graphical tool for quickly creating and maintaining the schema layer on top of the logical Database layer.  While there are lots of useful features in here, perhaps the biggest life saver is the ability to rename the Database Column that a MicroStrategy Column Object is pointed to.

First, open up Architect by choosing it from the Schema menu in Desktop.

Next, locate the table in your project that contains the changing column (you can use the search in the top right).  Click on the table that contains the column you want to change, and you can modify it’s Column property.

You’ll want to make this change in concert with your DBA, because although it doesn’t matter if you make this change before or after the physical table is altered, keep in mind that your schema will be pointed to the wrong column (either before or after) at some point.
After changing the Column Name, simply Save and Close and Update Schema.  Now all objects that use that column will be pointed to the new location without having to edit them all!  If you need to migrate this change to Production, its probably easier to just apply those steps directly in Production, but if you’re in love with Object Manager, just copy one of the affected objects and it’ll move the updated Column object with it.

NOTE: Since this column object is shared by all tables that use it, changing it will affect all definitions in all tables.  If the change needs to only occur in a single table, you’ll still need to update the table structure like normal to bring in the new column and update the Attribute/Facts individually.

Renaming Database Tables
This same trick works for renaming database tables as well.  Simply click on the Table object in Architect and modify the Database Name property.
In 8.x, you would have had to unmap all objects and remap them to the new table in order to facilitate a table rename.  Using Architect, you can do it in a single step.

Tags:

You may also like...

Follow:

Categories

About Me


Bryan Brandow
viz hacker, blogger, data enthusiast