- 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年最新版）
- 審計全流程技術操作實務指南
- 新中國審計制度變遷
- Azure Strategy and Implementation Guide
- 中國特色社會主義國家審計制度研究
- Big Data Visualization
- Microsoft Dynamics CRM 2011 Scripting Cookbook
- Team Foundation Server 2013 Customization
- 中國政府統計問題研究
- vSphere Design Best Practices
- 政策建模技術：CGE模型的理論與實現
- Oracle Primavera Contract Management，Business Intelligence Publisher Edition v14
- 計量經濟學理論與應用：基于Eviews的應用分析
- Oracle E-Business Suite Financials R12：A Functionality Guide
- Microsoft Dynamics CRM 2016 Customization（Second Edition）