your favorite data modeling tool, your partner in crime for data warehouse automation?
Post on 07-Jul-2015
492 Views
Preview:
TRANSCRIPT
Your favorite data modeling tool - your partner in crime for DWHA?
Frederik Naessens
Freelance BI Architect
Why this presentation?
Data Vault training & certification
Data vault automation conference – Utrecht – 6 October 2011
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
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?
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
My preparation
¤ Installed CA ERwin Data Modeler Standard Edition on april 3rd
¤ Read this very good book during easter holidays
Scope
¤ “Left to right” Vs. “Right to Left” approach
Scope
¤ “Left to right” Vs. “Right to Left” approach
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
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
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
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
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
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
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
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
FE result
Query tool
Multi user
¤ Merge & compare ¤ Part of Complete compare wizard
¤ User defined properties are also taken into account!
¤ Versioning ¤ Part of workgroup edition
¤ Not tested
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
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
Thanks!!
frederik@k25.be
+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
top related