不朽情缘在线试玩网站

書名： Business Intelligence with MicroStrategy Cookbook
作者名： Davide Moraschi
本章字?jǐn)?shù)： 345字
更新時(shí)間： 2021-07-23 15:32:01

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 DimDate in the right pane and select Show Sample Data.
See that there is a new column at the end named CalendarYearMonth with 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.

Note

You can watch a screencast of this operation at:

http://at5.us/Ch2V4

官术网_书友最值得收藏!

Business Intelligence with MicroStrategy Cookbook

Modifying a table structure

Getting ready

How to do it...

How it works...

Note