- Business Intelligence with MicroStrategy Cookbook
- Davide Moraschi
- 345字
- 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
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.
- 中國新股民必讀手冊（2015年最新版）
- Microsoft Dynamics GP 2016 Cookbook
- 審計學基礎
- Magento 2 Cookbook
- 讓財報說話：世界500強CFO帶你輕松讀財報（鮮讀版）
- Metabase Up and Running
- Securing Hadoop
- 財務審計實務指南
- 2016年度注冊會計師全國統一考試專用輔導教材（圖解版）：審計
- IBM SPSS Modeler Cookbook
- 企業并購審查中的相關市場界定：理論與案例
- 統計學視角下的金融高頻數據挖掘理論與方法研究
- 多項目管理方法及其應用研究（國家社科基金后期資助項目）
- 人口預測模型研究及應用
- 圖解經濟博弈論（圖解經濟學叢書）