![Page 1: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/1.jpg)
Your favorite data modeling tool - your partner in crime for DWHA?
Frederik Naessens
Freelance BI Architect
![Page 2: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/2.jpg)
Why this presentation?
Data Vault training & certification
![Page 3: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/3.jpg)
Data vault automation conference – Utrecht – 6 October 2011
![Page 4: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/4.jpg)
Model driven development
¤ Use a dwh automation software package
¤ If not possible:
¤ Don’t just do nothing! ¤ Go solo
¤ Start small
¤ Create awareness within the company
![Page 5: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/5.jpg)
Custom generators have a bad reputation
¤ Doesn’t scale well ¤ More people
¤ How to share/version/merge your input?
¤ More data
¤ Lots of data redundancy
¤ Can you ever migrate to DWHA tool?
![Page 6: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/6.jpg)
My goal
¤ Show how to benefit from the (underused) potential of your data modeling tool: ¤ Extend your model (with missing 10%) ¤ Support your multi-user & incremental approach ¤ Standardize ¤ Share information with others ¤ Automate
¤ This by: ¤ Only using standard functionality of the tool
API – custom plugins ¤ Based on common available information
![Page 7: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/7.jpg)
My preparation
¤ Installed CA ERwin Data Modeler Standard Edition on april 3rd
¤ Read this very good book during easter holidays
![Page 8: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/8.jpg)
Scope
¤ “Left to right” Vs. “Right to Left” approach
![Page 9: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/9.jpg)
Scope
¤ “Left to right” Vs. “Right to Left” approach
![Page 10: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/10.jpg)
Scope today
¤ Logical to physical Data Vault model conversion
¤ Logical model ¤ 3NF extended model
¤ More “DV-unaware” people friendly
¤ Less tables!!
¤ Physical tables are automatically built based on naming conventions
![Page 11: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/11.jpg)
Model extensions
¤ Define user defined properties in model on following levels ¤ Model level
¤ Version number ¤ Table level
¤ Driving Data Vault Type ¤ Column level
¤ Business Key ¤ Attribute Cluster
¤ Relationship level ¤ Is Reference Link ¤ Unit Of Work
Hub Link Satellite
![Page 12: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/12.jpg)
Demo screenshots
¤ Step 1 – v.01 ¤ Business 3NF Model ¤ Generated Data Vault Model
¤ Step 2 – Modifications ¤ Assign business key to Order_Details table ¤ Split attributes of Orders in 2 distinct Attribute Clusters ¤ Assign different unit of work to Orders foreign keys
¤ Step 3 – v.02 ¤ Changed Business 3NF model ¤ Slightly different generated Data Vault Model
![Page 13: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/13.jpg)
Categories
CategoryID
CategoryNameDescriptionPicture
CustomerDemographics
CustomerTypeID
CustomerDesc
Shippers
ShipperID
CompanyNamePhone
CustomerCustomerDemo
CustomerIDCustomerTypeID
Products
ProductID
ProductNameSupplierID (FK)CategoryID (FK)QuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinued
Suppliers
SupplierID
CompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFaxHomePage
Customers
CustomerID
CompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFax
Territories
TerritoryID
TerritoryDescriptionRegionID
EmployeeTerritories
EmployeeIDTerritoryID
Region
RegionID
RegionDescription
Order_Details
OrderDetailsID
OrderID (FK)ProductID (FK)UnitPriceQuantityDiscount
Orders
OrderID
CustomerID (FK)EmployeeID (FK)OrderDateRequiredDateShippedDateShipVia (FK)FreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountry
Employees
EmployeeID
LastNameFirstNameTitleTitleOfCourtesyBirthDateHireDateAddressCityRegionPostalCodeCountryHomePhoneExtensionPhotoNotesReportsToPhotoPath
BM v.01
Blue: Business entity Red: Association between business entities Underlined: Business Key
![Page 14: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/14.jpg)
Hub_Customers
Hub_Customers_SQN
Hub_Customers_LDTSHub_Customers_RSRCCustomerID
Hub_Employees
Hub_Employees_SQN
Hub_Employees_LDTSHub_Employees_RSRCEmployeeIDReportsTo
Hub_Shippers
Hub_Shippers_SQN
Hub_Shippers_LDTSHub_Shippers_RSRCShipperID
Hub_Orders
Hub_Orders_SQN
Hub_Orders_LDTSHub_Orders_RSRCOrderID
Hub_Categories
Hub_Categories_SQN
Hub_Categories_LDTSHub_Categories_RSRCCategoryID
Hub_Suppliers
Hub_Suppliers_SQN
Hub_Suppliers_LDTSHub_Suppliers_RSRCSupplierID
Hub_Products
Hub_Products_SQN
Hub_Products_LDTSHub_Products_RSRCProductIDSupplierIDCategoryID
Hub_Region
Hub_Region_SQN
Hub_Region_LDTSHub_Region_RSRCRegionID
Hub_Territories
Hub_Territories_SQN
Hub_Territories_LDTSHub_Territories_RSRCTerritoryID
Hub_CustomerDemographics
Hub_CustomerDemographics_SQN
Hub_CustomerDemographics_LDTSHub_CustomerDemographics_RSRCCustomerTypeID
Lnk_Employees_Default
Lnk_Employees_Default_SQN
Lnk_Employees_Default_LDTSLnk_Employees_Default_RSRCHub_Employees_SQNHub_Employees_ReportsTo_SQN
Lnk_Orders_Default
Lnk_Orders_Default_SQN
Lnk_Orders_Default_LDTSLnk_Orders_Default_RSRCHub_Orders_SQNHub_Customers_SQNHub_Employees_SQNHub_Shippers_ShipVia_SQN
Lnk_Products_Default
Lnk_Products_Default_SQN
Lnk_Products_Default_LDTSLnk_Products_Default_RSRCHub_Products_SQNHub_Suppliers_SQNHub_Categories_SQN
Lnk_Territories_Default
Lnk_Territories_Default_SQN
Lnk_Territories_Default_LDTSLnk_Territories_Default_RSRCHub_Territories_SQNHub_Region_SQN
Lnk_Order_Details
Lnk_Order_Details_SQN
Lnk_Order_Details_LDTSLnk_Order_Details_RSRCHub_Orders_SQNHub_Products_SQN
Lnk_EmployeeTerritories
Lnk_EmployeeTerritories_SQN
Lnk_EmployeeTerritories_LDTSLnk_EmployeeTerritories_RSRCHub_Employees_SQNHub_Territories_SQN
Lnk_CustomerCustomerDemo
Lnk_CustomerCustomerDemo_SQN
Lnk_CustomerCustomerDemo_LDTSLnk_CustomerCustomerDemo_RSRCHub_Customers_SQNHub_CustomerDemographics_SQN
Sat_Customers_Default
Sat_Customers_Default_LDTSHub_Customers_SQN
Sat_Customers_Default_RSRCSat_Customers_Default_LEDTSCompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFax
Sat_Employees_Default
Sat_Employees_Default_LDTSHub_Employees_SQN
Sat_Employees_Default_RSRCSat_Employees_Default_LEDTSLastNameFirstNameTitleTitleOfCourtesyBirthDateHireDateAddressCityRegionPostalCodeCountryHomePhoneExtensionPhotoNotesPhotoPath
Sat_Shippers_Default
Sat_Shippers_Default_LDTSHub_Shippers_SQN
Sat_Shippers_Default_RSRCSat_Shippers_Default_LEDTSCompanyNamePhone
Sat_Orders_Default
Sat_Orders_Default_LDTSHub_Orders_SQN
Sat_Orders_Default_RSRCSat_Orders_Default_LEDTSOrderDateRequiredDateShippedDateFreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountry
Sat_Categories_Default
Sat_Categories_Default_LDTSHub_Categories_SQN
Sat_Categories_Default_RSRCSat_Categories_Default_LEDTSCategoryNameDescriptionPicture
Sat_Suppliers_Default
Sat_Suppliers_Default_LDTSHub_Suppliers_SQN
Sat_Suppliers_Default_RSRCSat_Suppliers_Default_LEDTSCompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFaxHomePage
Sat_Products_Default
Sat_Products_Default_LDTSHub_Products_SQN
Sat_Products_Default_RSRCSat_Products_Default_LEDTSProductNameQuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinued
Sat_Order_Details_Default
Sat_Order_Details_Default_LDTSLnk_Order_Details_SQN
Sat_Order_Details_Default_RSRCSat_Order_Details_Default_LEDTSOrderDetailsIDUnitPriceQuantityDiscount
Sat_Region_Default
Sat_Region_Default_LDTSHub_Region_SQN
Sat_Region_Default_RSRCSat_Region_Default_LEDTSRegionDescription
Sat_Territories_Default
Sat_Territories_Default_LDTSHub_Territories_SQN
Sat_Territories_Default_RSRCSat_Territories_Default_LEDTSTerritoryDescription
Sat_CustomerDemographics_Default
Sat_CustomerDemographics_Default_LDTSHub_CustomerDemographics_SQN
Sat_CustomerDemographics_Default_RSRCSat_CustomerDemographics_Default_LEDTSCustomerDesc
DV v.01
![Page 15: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/15.jpg)
Categories
CategoryID
CategoryNameDescriptionPicture
CustomerDemographics
CustomerTypeID
CustomerDesc
Shippers
ShipperID
CompanyNamePhone
CustomerCustomerDemo
CustomerIDCustomerTypeID
Products
ProductID
ProductNameSupplierID (FK)CategoryID (FK)QuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinued
Suppliers
SupplierID
CompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFaxHomePage
Customers
CustomerID
CompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFax
T erritories
TerritoryID
TerritoryDescriptionRegionID
EmployeeTerritories
EmployeeIDTerritoryID
Region
RegionID
RegionDescription
Order_Details
OrderDetailsID
OrderID (FK)ProductID (FK)UnitPriceQuantityDiscount
Orders
OrderID
CustomerID (FK)EmployeeID (FK)OrderDateRequiredDateShippedDateShipVia (FK)FreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountry
Employees
EmployeeID
LastNameFirstNameTitleTitleOfCourtesyBirthDateHireDateAddressCityRegionPostalCodeCountryHomePhoneExtensionPhotoNotesReportsToPhotoPath
BM v.02
![Page 16: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/16.jpg)
H ub_Customers
Hub_Customers_SQN
Hub_Customers_LDTSHub_Customers_RSRCCustomerID
Hub_Employees
Hub_Employees_SQN
Hub_Employees_LDTSHub_Employees_RSRCEmployeeIDReportsTo
Hub_Shippers
Hub_Shippers_SQN
Hub_Shippers_LDTSHub_Shippers_RSRCShipperID
H ub_Orders
Hub_Orders_SQN
Hub_Orders_LDTSHub_Orders_RSRCOrderID
H ub_Categories
Hub_Categories_SQN
Hub_Categories_LDTSHub_Categories_RSRCCategoryID
Hub_Suppliers
Hub_Suppliers_SQN
Hub_Suppliers_LDTSHub_Suppliers_RSRCSupplierID
Hub_Products
Hub_Products_SQN
Hub_Products_LDTSHub_Products_RSRCProductIDSupplierIDCategoryID
H ub_Order_Details
Hub_Order_Details_SQN
Hub_Order_Details_LDTSHub_Order_Details_RSRCOrderDetailsID
Hub_Region
Hub_Region_SQN
Hub_Region_LDTSHub_Region_RSRCRegionID
H u b_Territories
Hub_Territories_SQN
Hub_Territories_LDTSHub_Territories_RSRCTerritoryID
H u b _CustomerDemographics
Hub_CustomerDemographics_SQN
Hub_CustomerDemographics_LDTSHub_CustomerDemographics_RSRCCustomerTypeID
L nk_Employees_Default
Lnk_Employees_Default_SQN
Lnk_Employees_Default_LDTSLnk_Employees_Default_RSRCHub_Employees_SQNHub_Employees_ReportsTo_SQN
L n k_Orders_Default
Lnk_Orders_Default_SQN
Lnk_Orders_Default_LDTSLnk_Orders_Default_RSRCHub_Orders_SQNHub_Customers_SQNHub_Employees_SQN
L n k_Orders_ShipInfo
Lnk_Orders_ShipInfo_SQN
Lnk_Orders_ShipInfo_LDTSLnk_Orders_ShipInfo_RSRCHub_Orders_SQNHub_Shippers_ShipVia_SQN
L n k_Products_Default
Lnk_Products_Default_SQN
Lnk_Products_Default_LDTSLnk_Products_Default_RSRCHub_Products_SQNHub_Suppliers_SQNHub_Categories_SQN
L n k _Order_Details_Default
Lnk_Order_Details_Default_SQN
Lnk_Order_Details_Default_LDTSLnk_Order_Details_Default_RSRCHub_Order_Details_SQNHub_Orders_SQNHub_Products_SQN
L n k _Territories_Default
Lnk_Territories_Default_SQN
Lnk_Territories_Default_LDTSLnk_Territories_Default_RSRCHub_Territories_SQNHub_Region_SQN
L n k _EmployeeTerritories
Lnk_EmployeeTerritories_SQN
Lnk_EmployeeTerritories_LDTSLnk_EmployeeTerritories_RSRCHub_Employees_SQNHub_Territories_SQN
L n k _CustomerCustomerDemo
Lnk_CustomerCustomerDemo_SQN
Lnk_CustomerCustomerDemo_LDTSLnk_CustomerCustomerDemo_RSRCHub_Customers_SQNHub_CustomerDemographics_SQN
S a t_Customers_Default
Sat_Customers_Default_LDTSHub_Customers_SQN
Sat_Customers_Default_RSRCSat_Customers_Default_LEDTSCompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFax
S at_Employees_Default
Sat_Employees_Default_LDTSHub_Employees_SQN
Sat_Employees_Default_RSRCSat_Employees_Default_LEDTSLastNameFirstNameTitleTitleOfCourtesyBirthDateHireDateAddressCityRegionPostalCodeCountryHomePhoneExtensionPhotoNotesPhotoPath
S at_Shippers_Default
Sat_Shippers_Default_LDTSHub_Shippers_SQN
Sat_Shippers_Default_RSRCSat_Shippers_Default_LEDTSCompanyNamePhone
S at_Orders_Default
Sat_Orders_Default_LDTSHub_Orders_SQN
Sat_Orders_Default_RSRCSat_Orders_Default_LEDTSOrderDateRequiredDateShippedDateFreight
S a t_Orders_ShipInfo
Sat_Orders_ShipInfo_LDTSHub_Orders_SQN
Sat_Orders_ShipInfo_RSRCSat_Orders_ShipInfo_LEDTSShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountry
S a t_Categories_Default
Sat_Categories_Default_LDTSHub_Categories_SQN
Sat_Categories_Default_RSRCSat_Categories_Default_LEDTSCategoryNameDescriptionPicture
S at_Suppliers_Default
Sat_Suppliers_Default_LDTSHub_Suppliers_SQN
Sat_Suppliers_Default_RSRCSat_Suppliers_Default_LEDTSCompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFaxHomePage
S at_Products_Default
Sat_Products_Default_LDTSHub_Products_SQN
Sat_Products_Default_RSRCSat_Products_Default_LEDTSProductNameQuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderReorderLevelDiscontinued
S a t_Order_Details_Default
Sat_Order_Details_Default_LDTSHub_Order_Details_SQN
Sat_Order_Details_Default_RSRCSat_Order_Details_Default_LEDTSUnitPriceQuantityDiscount
S at_Region_Default
Sat_Region_Default_LDTSHub_Region_SQN
Sat_Region_Default_RSRCSat_Region_Default_LEDTSRegionDescription
S a t _Territories_Default
Sat_Territories_Default_LDTSHub_Territories_SQN
Sat_Territories_Default_RSRCSat_Territories_Default_LEDTSTerritoryDescription
S a t _CustomerDemographics_Default
Sat_CustomerDemographics_Default_LDTSHub_CustomerDemographics_SQN
Sat_CustomerDemographics_Default_RSRCSat_CustomerDemographics_Default_LEDTSCustomerDesc
DV v.02 2 satellites for order
Order_details hub
2 link tables for order foreign keys
![Page 17: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/17.jpg)
Extract & Share information
¤ Forward Engineering ¤ Based on FE templates
¤ Based on FE option sets
¤ Xml exports
¤ Crystal reports ¤ Standard reports
¤ Customized reports
¤ ODBC Query tool
![Page 18: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/18.jpg)
FE result
![Page 19: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/19.jpg)
Query tool
![Page 20: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/20.jpg)
Multi user
¤ Merge & compare ¤ Part of Complete compare wizard
¤ User defined properties are also taken into account!
¤ Versioning ¤ Part of workgroup edition
¤ Not tested
![Page 21: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/21.jpg)
Bulk editing & Automation
¤ Bulk editor
¤ Phased approach: ¤ Modify model
¤ Forward engineer
¤ Convert with external utility
¤ Reverse engineer to DV model
¤ Not experimented with add-ins yet
![Page 22: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/22.jpg)
My goal (repeated)
¤ Show how to benefit from the (underused) potential of your modeling tool: ¤ Extend your model (with missing 10%) ¤ Supports your multi-user & incremental approach ¤ Standardize ¤ Share information with others ¤ Automate
¤ This by: ¤ Only using standard functionality of the tool
API – custom plugins ¤ Based on common available information
![Page 23: Your favorite data modeling tool, your partner in crime for Data Warehouse Automation?](https://reader033.vdocuments.us/reader033/viewer/2022051516/559b90e11a28abe35d8b4794/html5/thumbnails/23.jpg)
Thanks!!
+32 473 82 60 80
A special thanks to:
Donna Burbank, Hans Hultgren Volvo IT colleagues: Stijn Roelens & Kristof Vanduren Frédéric Barbier, Erik-Jan Koning