Modifying a table structure
Like it or not, database tables change. Due to upgrades in source systems, mergers and acquisitions, or simply because of a shift in the project management, we suddenly find that an SKU or a customer ID is not there anymore. Let alone systems in continuous development where the database seems to have a life of its own and morphs every now and then into different shapes. We all know how difficult it is to go back and test every ETL script and every report to see if they still return reliable data.
Sometimes changes happen without notice, you come to the office on a Monday morning to see that the CFO has a different color…, then you realize that something went wrong.
There is no vaccine for this, other than carefully documenting everything and hope for the best. Let's see how to refresh our table structure information using the Warehouse Catalog window.
Getting ready
You need to have completed the previous recipe and exercises to continue.
How to do it...
First of all, we will modify the structure of the DimDate table to add a column with the month's description:
- In command prompt type:
sqlcmd -S (localdb)\v11.0 -d AdventureWorksDW2008R2 - Once the
1>prompt appears, type:ALTER TABLE DimDate ADD CalendarYearMonth VARCHAR(7) go UPDATE DimDate SET CalendarYearMonth = CONVERT(VARCHAR(7), FullDateAlternateKey, 111) go exit
- Now go to the Warehouse Catalog window and right-click on the table
DimDatein the right pane and select Show Sample Data. - See that there is a new column at the end named
CalendarYearMonthwith the format YYYY/MM. - Click on Close and right-click again on
DimDate, then select Update Structure. - Now click on Save and Close to exit this window, MicroStrategy will write the changes to the metadata.
- Update the schema.
How it works...
It's very important to keep the metadata consistent with the latest information about the structure of the database, especially when changing the datatypes or when columns are removed from data warehouse tables.