Open Framework, Information Management Strategy & Collaborative Governance | Data & Social Methodology - MIKE2.0 Methodology
Wiki Home
Collapse Expand Close

Members
Collapse Expand Close

To join, please contact us.

Improve MIKE 2.0
Collapse Expand Close
Need somewhere to start? How about the most wanted pages; or the pages we know need more work; or even the stub that somebody else has started, but hasn't been able to finish. Or create a ticket for any issues you have found.

Technical Transformation Design Deliverable Template

From MIKE2.0 Methodology

Share/Save/Bookmark
Jump to: navigation, search
Under construction.png
This article is currently Under Construction. It is undergoing major changes as it is in the early stages of development. Users should help contribute to this article to get it to the point where is ready for a Peer Review.
This deliverable template is used to describe a sample of the MIKE2.0 Methodology (typically at a task level). More templates are now being added to MIKE2.0 as this has been a frequently requested aspect of the methodology. Contributors are strongly encouraged to assist in this effort.
Deliverable templates are illustrative as opposed to fully representative. Please help add examples to this template that are representative of the proposed output.

Transformation design comprises a significant part of the ETL development process. This step includes design of metadata transformations and data transformations. In the Logical Design, definitions of the elements of transformation design may have been considered in isolation. In the Physical Design, a unified design with the following transformation elements is sought:

  • Validation and Exception Handling
  • Technical Transformations
  • Business Rule Transformations
  • Data Enhancement (Lookup, Merge, Join, etc.)
  • Integration with Data Re-Engineering Products

Transformation Design includes Metadata Transformation and Data Transformation. Metadata Transformation involves converting the metadata from source definitions to target definitions. This makes it easier to deliver the data to targets or to compare the data to targets and is important for tracing lineage of data from source to targets.

Transformations such as the following can be performed here:

  • Changing the column names to correspond to the target
  • Changing columns from NULL to NOT NULL or vice versa
  • Trimming or expanding column widths
  • Converting strings to dates or vice versa

Examples

Example 1 - for Sample Transformation rules that is a part of ETL Physical Design

Table Name Column Name Data Warehouse Source Name Transform Comment
Appearance_Type_Dimension Appearance_Type_Dimension_Key   Surrogate key generated by ETL process. AppearanceType Dimension records must be generated in the ETL since no table exists from which they may be loaded. The record types required are defined in the Appearance Reason attribute below.
  Appearance_Reason   Record 1: Set to "Listing"
Record 2: Set to "ABCD"
Record 3: Set to "Target Media"
Record 4: Set to "Nearby"
  Version_Number   If no version of the dimension exists for the same Appearance Reason, then set to 1. Else set to previous Version Number

+ 1.

  Latest_Version_Indicator   Set to "Y" on load. If previous version of the dimension exists for the same Appearance Reason, then Indicator in previous version to be set to "N"
  Effective_Start_Date   Set to current date.
  Effective_End_Date   Set to NULL on load. If previous version of the dimension exists for the same Appearance Reason, then End Date in previous version to be set to current date - 1.
  Validity_Start_Date   Set to current date.
  Validity_End_Date   Set to NULL on load. If previous version of the dimension exists for the same Appearance Reason, then End Date in previous version to be set to current date - 1.
  Current_Validity_Indicator   Set to "Y" on load. If previous version of the dimension exists for the same Appearance Reason, then Indicator in previous version to be set to "N"
Calendar_Dimension Calendar_Dimension_Date   Surrogate key generated by ETL process. Calendar Dimension records must be generated in the ETL since no table exists from which they may be loaded. Data to be generated for all dates in the range 1/1/1990 to 31/12/2019.
  YYYYMMDD    
  DDMMYYYY    
  Full_Date_Name    
  Calendar_Year_Number    
  Calendar_Quarter_Number    
  Calendar_Month_Number    
  Calendar_Month_Name    
  Calendar_Month_Abbreviation    
  Calendar_Week_Number    
  Calendar_Day_Of_Year_Number    
  Financial_Year_Number    
  Financial_Quarter_Number    
  Financial_Month_Number    
  Financial_Week_Number    
  Financial_Day_Of_Year_Number    
  Day_Type    
  Day_Of_Month_Number    
  Day_Of_Week_Number    
  Day_Name    
  Day_Abbreviation    
  End_Of_Month_Indicator    
  End_Of_Calendar_Year_Indicator    
  End_Of_Financial_Year_Indicato    
  End_Of_Calendar_Quarter_Indica    
  End_Of_Financial_Quarter_Indic    
  Calendar_Year_Num_Text    
  Calendar_Quarter_Num_Text    
  Calendar_Month_Num_Text    
  Calendar_Week_Num_Text    
  Financial_Year_Num_Text    
  Financial_Quarter_Num_Text    
  Financial_Month_Num_Text    
  Financial_Week_Num_Text    
  Prev_Month_Cal_Year_Number    
  Prev_Month_Cal_Quarter_Number    
  Prev_Month_Cal_Month_Number    
  Prev_Month_Cal_Month_Name    
  Prev_Month_Cal_Month_Abbr    
  Prev_Calendar_Year_Number    
  Prev_Calendar_Quarter_Number    
  Prev_Calendar_Week_Number    
  Prev_Financial_Year_Number    
  Prev_Financial_Quarter_Number    
  Prev_Financial_Month_Number    
  Prev_Financial_Week_Number    
Heading_Change_Fact Heading_Change_Fact_Key   Surrogate key generated by ETL process. A Heading Change Fact record is to be generated for each new Classification Relationship record where the Classification Type of the

’ parent ’ Classification is "Heading", "Sub-Hdg" or "Sub-Sub-Hdg" and the Relationship type is "Replaced By"

  From_Heading_Dimension_Key DM.Heading_Dimension.Heading_Dimension_Key Heading Dimension for the Heading identified in the

’ parent ’ role of the Classification Relationship.

  To_Heading_Dimension_Key DM.Heading_Dimension.Heading_Dimension_Key Heading Dimension for the Heading identified in the

’ child ’ role of the Classification Relationship.

  Calendar_Dimension_Date AWH.Classification_Relationship.Classification_Rel_Start_Date Since the key to the Calendar Dimension is the actual date rather than a surrogate, this column will be sourced from the warehouse layer field as noted, and will then automatically

’ link ’ to the Dimension.

  Update_Date   Set to current date.
Heading_Dimension Heading_Dimension_Key   Surrogate key generated by ETL process. Each sub- (and sub-sub-) Heading will have its higher hierarchy level fields fully populated. Heading types not at the lowest level of the hierarchy will have null values recorded in lower hierarchy level fields. This is to permit facts to be linked to the appropriate Heading level, which will usually not be the lowest.
  Heading_Level AWH.Heading.Heading_Level  
  Heading_Tier AWH.Heading.Heading_Tier  
  Heading_Code AWH.Classification.Classification_Code Where Heading.Heading_Level = "Heading" and Heading.Classification_Key = Classification.Classification_Key
OR
Where Heading.Heading_Level = "Sub-Hdg" and Heading.Hierarchy_Heading_Key = Parent-Heading.Classification_Key and Parent-Heading.Classification_Key = Classification.Classification_Key
OR
Where Heading.Heading_Level = "Sub-Sub-Hdg" and Heading.Hierarchy_Heading_Key = Parent-Heading.Classification_Key and Parent-Heading.Hierarchy_Heading_Key = Main-Parent-Heading.Classification_Key and Main-Parent-Heading.Classification_Key = Classification.Classification_Key
  Heading_Name AWH.Classification.Classification_Name Where Heading.Heading_Level = "Heading" and Heading.Classification_Key = Classification.Classification_Key
OR
Where Heading.Heading_Level = "Sub-Hdg" and Heading.Hierarchy_Heading_Key = Parent-Heading.Classification_Key and Parent-Heading.Classification_Key = Classification.Classification_Key
OR
Where Heading.Heading_Level = "Sub-Sub-Hdg" and Heading.Hierarchy_Heading_Key = Parent-Heading.Classification_Key and Parent-Heading.Hierarchy_Heading_Key = Main-Parent-Heading.Classification_Key and Main-Parent-Heading.Classification_Key = Classification.Classification_Key
  Sub_Heading_Code AWH.Classification.Classification_Code If Heading.Heading_Level = "Heading" then set to NULL
ELSE
Where Heading.Heading_Level = "Sub-Hdg" and Heading.Classification_Key = Classification.Classification_Key
ELSE
Where Heading.Heading_Level = "Sub-Sub-Hdg" and Heading.Hierarchy_Heading_Key = Parent-Heading.Classification_Key and Parent-Heading.Classification_Key = Classification.Classification_Key
  Sub_Heading_Name AWH.Classification.Classification_Name If Heading.Heading_Level = "Heading" then set to NULL
ELSE
Where Heading.Heading_Level = "Sub-Hdg" and Heading.Classification_Key = Classification.Classification_Key
ELSE
Where Heading.Heading_Level = "Sub-Sub-Hdg" and Heading.Hierarchy_Heading_Key = Parent-Heading.Classification_Key and Parent-Heading.Classification_Key = Classification.Classification_Key
  Sub_Sub_Heading_Code AWH.Classification.Classification_Code Where Heading.Heading_Level = "Sub-Sub-Hdg" and Heading.Classification_Key = Classification.Classification_Key
ELSE
If Heading.Heading_Level in ("Heading", "Sub-Hdg") then set to NULL
  Sub_Sub_Heading_Name AWH.Classification.Classification_Name Where Heading.Heading_Level = "Sub-Sub-Hdg" and Heading.Classification_Key = Classification.Classification_Key
ELSE
If Heading.Heading_Level in ("Heading", "Sub-Hdg") then set to NULL
  Version_Number   If no version of the dimension exists for the same level and code, then set to 1. Else set to previous Version Number

+ 1.

  Latest_Version_Indicator   Set to "Y" on load. If previous version of the dimension exists for the same level and code, then Indicator in previous version to be set to "N"
  Effective_Start_Date AWH.Classification.Classification_Start_Date  
  Effective_End_Date AWH.Classification.Classification_End_Date  
  Validity_Start_Date   Set to current date on load.
  Validity_End_Date   Set to NULL on load. If previous version of the dimension exists for the same level and code, then End Date in previous version to be set to current date - 1.
  Current_Validity_Indicator AWH.Classification.Current_Validity_Indicator  
Heading_Search_Fact Heading_Search_Fact_Key   Surrogate key generated by ETL process. A count is recorded for each combination of Search Location and Heading Search Event (i.e. an Online Event of sub-type Search Event where the Event Type is "Heading Search", or "Combined Search") and the TCL Page Number is zero (other pages are the result of a page-down).
  Search_Type_Dimension_Key DM.Search_Type_Dimension.Search_Type_Dimension_Key Where Heading_Search_Event.Online_Event_Key = Online_Event.Online_Event_Key and Online_Event.Event_Type = Search_Type_Dimension.Search_Type
  Heading_Dimension_Key DM.Heading_Dimension.Heading_Dimension_Key Where Heading_Search_Event.Classification_Key = Classification.Classification_Key and Classification.Classification_Type = "Heading" and Classification.Classification_Code = Heading_Dimension.Heading_Code and Heading_Dimension.Heading_Level = "Heading" and Heading_Dimension.Latest_Version_Indicator = "Y"
  Search_Location_Dimension_Key DM.Search_Location_Dimension.Search_Location_Dimension_Key Where Heading_Search_Event.Online_Event_Key = Search_Location.Online_Event_Key and Search_Location.Location_Key = Location.Location_Key and Location.Location_Type = Search_Location_Dimension.Location_Type and ( ( if Location.Location_Type = "PostCode" then Location.Location_Short_Name = Search_Location_Dimension.PostCode ) or ( if Location.Location_Type = "BookArea" then Location.Location_Short_Name = Search_Location_Dimension.Book_Area_Code ) or ( if Location.Location_Type = "State" then Location.Location_Short_Name = Search_Location_Dimension.State_Code ) or ( if Location.Location_Type = "Country" then Location.Location_Short_Name = Search_Location_Dimension.Country_Name ) )
  Calendar_Dimension_Date AWH.Online_Event.Online_Event_Date Where Heading_Search_Event.Online_Event_Key = Online_Event.Online_Event_Key

Since the key to the Calendar Dimension is the actual date rather than a surrogate, this column will be sourced from the warehouse layer field as noted, and will then automatically

’ link ’ to the Dimension.

  Search_Count   Count of Seach Location / Online Event combinations aggregated by date, Search Location, Heading and Search type.
  Update_Date   Set to current date.
TCL_Change_Fact TCL_Change_Fact_Key   Surrogate key generated by ETL process. An TCL Change Fact record is to be generated for each new Customer Solution Relationship record where the Relationship type is "Replaced By"
  From_TCL_Dimension_Key DM.TCL_Dimension.TCL_Dimension_Key TCL Dimension for the Customer Solution identified in the

’ parent ’ role of the Customer Solution Relationship.

  To_TCL_Dimension_Key DM.TCL_Dimension.TCL_Dimension_Key TCL Dimension for the Customer Solution identified in the

’ child ’ role of the Customer Solution Relationship.

  Calendar_Dimension_Date AWH.Customer_Solution_Relationship.Customer_Solution_Rel_Start_Date Since the key to the Calendar Dimension is the actual date rather than a surrogate, this column will be sourced from the warehouse layer field as noted, and will then automatically

’ link ’ to the Dimension.

  Update_Date   Set to current date.
TCL_Dimension TCL_Dimension_Key   Surrogate key generated by ETL process. The TCL Dimension is generated fromn the Customer Solution table.
  ZSOL_Customer_Dimension_Key DM.ZSOL_Customer_Dimension.ZSOL_Customer_Dimension_Key Where ZSOL_Customer_Dimension.Customer_Id = Customer Solution.Customer Id and ZSOL_Customer_Dimension.Latest_Version_Indicator = "Y"
  TCL_Id AWH.Customer_Solution.TCL_Id  
  Displayed_Name AWH.Customer_Solution.Displayed_Name  
  Contract_Start_Date AWH.Customer_Solution.Contract_Start_Date  
  Contract_End_Date AWH.Customer_Solution.Contract_End_Date  
  Perpetual_Billing_Indicator AWH.Customer_Solution.Perpetual_Billing_Indicator  
  Perpetual_Billing_Start_Date AWH.Customer_Solution.Perpetual_Billing_Start_Date  
  Anniversary_Date AWH.Customer_Solution.Anniversary_Date  
  Seniority_Date AWH.Customer_Solution.Seniority_Date  
  TCL_Status AWH.Customer_Solution.TCL_Status  
  Online_Sort_Priority AWH.Customer_Solution.Online_Sort_Priority  
  Version_Number AWH.Customer_Solution.Version_Number  
  Latest_Version_Indicator AWH.Customer_Solution.Latest_Version_Indicator  
  Effective_Start_Date AWH.Customer_Solution.Customer_Solution_Start_Date  
  Effective_End_Date AWH.Customer_Solution.Customer_Solution_End_Date  
  Validity_Start_Date AWH.Customer_Solution.Record_Start_Date  
  Validity_End_Date AWH.Customer_Solution.Record_End_Date  
  Current_Validity_Indicator   If current date

> = Validity Start Date and Validity End Date is NULL then set to "Y" else set to "N".

 

Link: Technical Data Extraction Design Deliverable Template

Wiki Contributors
Collapse Expand Close