Model Object Types
      Back to home
      1. biGENIUS-X Help Center
      2. Generators
      3. Model Object Types

      Create a Multi-Active Satellite

      A Model Object of type Multi-Active Satellite can be created in the Silver Layer.

      What is a Multi-Active Satellite

      In a Data Vault model, a Multi-Active Satellite is a particular Satellite type (See Create a Satellite). 

      It is a structure built to house multiple active child records, where these
      child records have no stand-alone business key of their own.

      Use case example

      A prime example would be a person with multiple active phone numbers at the same time: his home phone number has not changed in the last 15 years, and his work phone number did change each time this person changed employer.
      Conditions that force a Multi-Active Satellite to be built include bad quality of source data, lack of metadata on the source feed, and lack of stand-alone business key for the child records.

      Source data

      Let's consider the following source tables:

      • Person.PhoneNumberType
      • Person.PersonPhone
      • Person.Person

      Mr. DUFFY Lee Terry has 1 Work phone number by default in the database:

      SELECT per.BusinessEntityID, per.LastName, per.MiddleName, per.FirstName, pho.PhoneNumber, typ.[Name]
      FROM [Person].[Person] per
      JOIN [Person].[PersonPhone] pho on pho.BusinessEntityID = per.BusinessEntityID
      JOIN [Person].[PhoneNumberType] typ on typ.PhoneNumberTypeID = pho.PhoneNumberTypeID
      WHERE per.BusinessEntityID = 2

      Let's add another Work Phone:

      INSERT INTO [Person].[PersonPhone] VALUES (2, '815-555-0138',3,'2021-05-25');

      You should now have these source data:

      Data Vault model

      Let's create the following object:

      • 1 Hub Person_Hub From the Stage Person
        • Set the Property DeduplicationMethod of the Dataflow to Distinct
      • 1 Multi-Active Satellite PersonPhone_Satellite from the Stage PersonPhone
        • Add the terms PhoneNumber and PhoneNumberType (uncheck BK)
        • Map the default column BG_Sequence to the following term rule: 
          • ROW_NUMBER() OVER(partition by s1.BusinessEntityID order by s1.PhoneNumber)

      Load of data

      Generate, deploy, and load the project.

      Then check the data for Mr DUFFY Lee Terry:

      -- Check the data of the Satellite
      SELECT * FROM [RDV].[RDV_MAS_PersonPhone_Satellite]
      WHERE [Hub_HK] = (
      SELECT [Hub_HK]
      FROM [RDV].[RDV_HUB_Person_Hub]
      WHERE [BusinessEntityID] = 2 --Mr DUFFY Lee Terry
      )

      You should have this result:

      2 Work phone numbers active at the same time

      Creation steps

      There are two possibilities for creating a Multi-Active Satellite Model Object:

        In each creation possibility, you should, after Multi-Active Satellite creation: 

        • Define the sequence: the value in Sequence should be distinct for each row. Map the Default Term BG_Sequence to:
          • a Source Term if it fulfills the requirement (is specific for each row)
          • a calculation with a ROW NUMBER function if no Source term can be used. Example:
        ROW_NUMBER() OVER(PARTITION BY BusinessEntityID ORDER BY PhoneNumber)

        Properties

        A Multi-Active Satellite Model Object can be configured through the following Properties: 

        Default Terms

        A Multi-Active Satellite Model Object will include the following Default Terms:  

        Business Rules

        The following Business Rules are checked during the Model Object creation: