Link Search Menu Expand Document
Virtues

Split PL Schemas by IRM

urn:js:virtue:aspire:standard:12.1

TL;DR

Split PL schemas by IRM.

Definition

The Snowflake PL schemas in ADW are split into:

  1. ADW_CUSTOMER_PL
  2. ADW_PRODUCT_PL
  3. ADW_PROPERTY_PL
  4. ADW_LOGISTICS_PL
  5. ADW_SALES_PL
  6. ADW_INTERACTIONS_PL
  7. ADW_REFERENCE_PL
  8. ADW_FINANCE_PL
  9. ADW_COLLEAGUE_PL
  10. ADW_SUPPLY_CHAIN_PL

UPDATE 5TH Jan 2022. ERWIN has been replaced by Wherescape 3D

Background

A proposal was made and agreed by the Decision Forum to split the existing Snowflake PL schemas. As Wherescape 3D is the master for the DDL for these schemas this change will impact this tool as well.

The initial proposal was to split the schemas into the L1 subject areas, however after discussions between Simon and the Information Architect, it was decided that breaking down the schemas into L1 subject areas was too low level and could be liable to change, so the breakdown suggested was by the four Product Families:-

  • PRODUCT
  • CUSTOMER
  • FINANCE
  • LOCATION

The Decision Forum on 18/03/19 agreed to split the Snowflake PL schemas into ADW into:

ADW_CUSTOMER_PL

ADW_PRODUCT_PL

ADW_PROPERTY_PL

ADW_LOGISTICS_PL

ADW_SALES_PL

ADW_INTERACTIONS_PL

ADW_REFERENCE_PL

ADW_FINANCE_PL

ADW_COLLEAGUE_PL

ADW_SUPPLY_CHAIN_PL

Note, it is assumed the ‘incumbent’ Presentation Layers, e.g. I2C or PROD4 are unchanged.

Note, the ADW_PII_PL schema will remain unchanged because this contains the PII LOOKUP object.

Note, ADW_STATIC is currently unused although the squads have put temporary objects in here, to be advised if this is to be split too?

It was decided to create additional schemas in the ADW_PL area only. The Raw Data Vault (ADW_RDV) and Business Data Vault (ADW_BDV) schemas will not be changed at the current time

Outstanding Decisions

Revisit decision to retain single ADW_RDV and ADW_BDV schemas.

The IRM subject areas have changed since the Wherescape 3D delivery subject areas were created. Previously the IRM subject areas were called the Business Subject Areas, and the Logical and Solution Subject Areas were called the Delivery Subject Areas. Since the Product Families have been created based in part on the L1 Subject Areas there is greater visibility on these, so alignment should be considered within Wherescape 3D to be consistent.

There is an outstanding proposal to migrate the Delivery Subject Areas to reflect the PL Schemas as follows:

  • Party - rename/re-organize to - Customer
  • Product - keep as - Product
  • Location - rename/re-organize to - Property
  • Account - merge with - Customer
  • Logistics - keep as - Logistics
  • Online Activity - merge with - Interactions
  • Sales - keep as - Sales
  • Events - rename - Interactions
  • Promotion - merge with - Product
  • Reference - keep as - Reference
  • Corporate Finance - rename - Finance
  • Human Capital Management - rename/re-organize to - Colleague
  • Template - keep as - Template
  • NEW - Supply Chain
  • Space & Range (proposed) - merge with - Property

This is an Wherescape 3D subject area only, it is not implemented in Snowflake.

On every Wherescape 3D entity and table/view there is an element of UDP (user defined property) meta-data called Subject Area, set to 01 – PARTY, 02 – PRODUCT, 03-LOCATION etc. This existing mechanism can be used to identify ownership of each Wherescape 3D object. This meta-data is used when generating reports and is imported into Alation.

The perceived benefits and risks of splitting the schemas are as follows.

Benefits

  • Clearer identification  of ADW objects and their ownership (Snowflake / Wherescape 3D)
  • Easier navigation (Snowflake)
  • Reduce risk of duplication (Snowflake)
  • Segregates release allowing greater granularity of versioning & access control (Snowflake / Wherescape 3D)
  • Allows for more parallel modelling (Wherescape 3D)
  • The design still in it’s infancy, the effort and risks will increase as the design matures.

Risks

  • As the design matures the recommended split is recognized as no longer fit for purpose and is further realigned.
  • Duplicate RDV objects will be required in Wherescape 3D where required to add clarity to a model. In this example EAN_HUB (LOCATION) is included in a SALES model. (Wherescape 3D)

                       

  • Work will need to be undertaken in Wherescape 3D including:-
    • Creating new Owners
    • Creating new Schema ERD’s
    • Aligning current subject areas with the IRM L1 subject areas
    • Moving objects within Wherescape 3D because of the above change
    • Standards and L5 MMG will need updating.
    • Making these changes will require downtime and
      • will need a senior modeller to do the work.
      • no squad changes will be possible to the models during this work.
      • will require support from all families to be clear on ownership of objects (including reference ones like Date and Time and Meta-data). 

Recommendation

  • Retain the single model for the all Wherescape 3D L5 layers, increasing the number of diagrams to reflect the schema split.
  • No split the Snowflake RDV / BDV layer schemas to reflect the split of the Snowflake L5 schemas. To be reviewed at regular intervals.

    Assumption

    Any changes will not require any changes to the Wherescape 3D L4 model?

Actions

  1. The definition of the new Delivery Subject Areas need to be determined, this should be done between the Product Owners, Business Analysts and Technical Designers. (Work on this has been initiated)
  2. The work to assess the impact on the Ab Initio Framework of creating the new Snowflake schemas will need to be assigned to an Ab Initio architect/engineer
  3. The impact on any SnowSQL code of this change needs to be assessed (for example, hard coded schemas in code)
  4. Alignment of each Wherescape 3D PL object to the new Delivery Subject Areas - Senior Technical Designer.
  5. Co-ordination and delivery of modification to the Wherescape 3D model - Technical Designers.

Appendix

Migrated From Confluence

link Original Author : Osborne, Nick