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:

Create a Model Object with the wizard:
- Recommended possibility
- Use the Wizard Create a Multi-Active Satellite from a Stage or a Raw Vault Composite that contains the following steps:
  - Create Model Object
  - Create Dataflow
  - Create Relations: number = {1}
  - Map Foreign Key Terms
  - Map selected Terms: exclude the Business Key(s) = {true}, exclude the Foreign Key(s) = {true}
  - Add to View
Create manually a Model Object:
- Not recommended possibility
- Create a Model Object of type Multi-Active Satellite in the Silver Layer
- Create a Dataflow Set
- Add a Model Object to the Dataflow Set: add the Stage or Raw Vault Composite Model Object
- Auto-map the Source Terms: select the auto-map mode and auto-map all the Source Terms except the ones defined as Business Keys and the Default Source Terms in the Source Model Object
- Add a Relationship to the corresponding Hub
- Map the Foreign Key Terms: select the target mode and manually map the Foreign Key Terms to the corresponding Source Terms (with or without a complex expression)

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:

Implementation Type
Deduplication Method or Optimization Method (Only for Spark Generator)
Deduplication Partition or Optimization Method Columns (Only for Spark Generator)
File format (Only for Spark Generator)

Default Terms

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