0
Vote

AdventureWorks Data Warehouse: The Table Design in Visio file is not consistent with the real DB

description

Environment:

  1. SQL Server Katmai
  2. SQL Samples
  3. Visio
     

Repro Steps:

  1. Open ‘<SampleRoot>\ Tools\Samples\AdventureWorks Data Warehouse\ AdventureWorksDW.vsd’ via Visio
  2. Select Tables and Columns in DB ‘AdventureWorksDW’:
    select name,object_id from sys.tables
     
    select c.object_id,c.column_id,c.name,t.name as typename, c.max_length, c.is_nullable, c.is_identity,c.is_filestream
    from sys.columns c
    left join sys.types t on t.user_type_id=c.user_type_id
    where c.object_id in (select object_id from sys.tables)
  3. Compare the Tables and Columns at 1st and 2nd.
     

Expectation:

The Tables and Columns in Visio should be consistent with the ones in real DB
 

Actual Result:

The Tables and Columns in Visio are not consistent with the ones in real DB.
 
[Visio]Column not exists: DimCustomer-Title
[Visio]Column not exists: DimCustomer-EnglishEducation
[Visio]Column not exists: DimCustomer-SpanishEducation
[Visio]Column not exists: DimCustomer-FrenchEducation
[Visio]Column not exists: DimCustomer-EnglishOccupation
[Visio]Column not exists: DimCustomer-SpanishOccupation
[Visio]Column not exists: DimCustomer-FrenchOccupation
[Visio]Column not exists: DimEmployee-StartDate
[Visio]Column not exists: DimEmployee-EndDate
[Visio]Column not exists: DimEmployee-Status
[Visio]Column not exists: DimGeography-SalesTerritoryKey
[Visio]Column not exists: DimGeography-EnglishCountryRegionName
[Visio]Column not exists: DimGeography-SpanishCountryRegionName
[Visio]Column not exists: DimGeography-FrenchCountryRegionName
[Visio]Table not exists: DimProduct
[Visio]Column not exists: DimProductCategory-EnglishProductCategoryName
[Visio]Column not exists: DimProductCategory-SpanishProductCategoryName
[Visio]Column not exists: DimProductCategory-FrenchProductCategoryName
[Visio]Column not exists: DimProductSubcategory-EnglishProductSubcategoryName
[Visio]Column not exists: DimProductSubcategory-SpanishProductSubcategoryName
[Visio]Column not exists: DimProductSubcategory-FrenchProductSubcategoryName
[Visio]Column not exists: DimPromotion-EnglishPromotionType
[Visio]Column not exists: DimPromotion-SpanishPromotionType
[Visio]Column not exists: DimPromotion-FrenchPromotionType
[Visio]Column not exists: DimPromotion-EnglishPromotionCategory
[Visio]Column not exists: DimPromotion-SpanishPromotionCategory
[Visio]Column not exists: DimPromotion-FrenchPromotionCategory
[Visio]Column not exists: DimPromotion-EnglishPromotionName
[Visio]Column not exists: DimPromotion-SpanishPromotionName
[Visio]Column not exists: DimPromotion-FrenchPromotionName
[Visio]Column not exists: DimSalesReason-SalesReasonReasonType
[Visio]Column not exists: DimTime-EnglishMonthName
[Visio]Column not exists: DimTime-SpanishMonthName
[Visio]Column not exists: DimTime-FrenchMonthName
[Visio]Column not exists: DimTime-EnglishDayNameOfWeek
[Visio]Column not exists: DimTime-SpanishDayNameOfWeek
[Visio]Column not exists: DimTime-FrenchDayNameOfWeek
[Visio]Table not exists: FactInternetSales
[Visio]Table not exists: FactResellerSales
[Visio]Table not exists: ProspectiveBuyer
[Visio]Table not exists: DatabaseLog
[RealDB]Column not exists: DimProductCategory-ProductCategoryName
[RealDB]Column not exists: DimProductSubCategory-ProductSubCategoryName
[RealDB]Column not exists: DimPromotion-PromotionName
[RealDB]Column not exists: DimPromotion-PromotionType
[RealDB]Column not exists: DimPromotion-PromotionCategory
[RealDB]Column not exists: DimSalesTerritory-GeographyKey
[RealDB]Column not exists: DimEmployee-SCDStartDate
[RealDB]Column not exists: DimEmployee-SCDEndDate
[RealDB]Column not exists: DimEmployee-SCDStatus
[RealDB]Table not exists: FactSales
[RealDB]Column not exists: DimGeography-CountryRegionName
[RealDB]Column not exists: DimCustomer-Salutation
[RealDB]Column not exists: DimCustomer-Education
[RealDB]Column not exists: DimCustomer-Occupation
[RealDB]Column not exists: DimTime-DayNameOfWeek
[RealDB]Column not exists: DimTime-MonthName
[RealDB]Column not exists: DimOrganization-ParentOrganizationName
[RealDB]Column not exists: DimSalesReason-SalesReasonType

comments