Business Vault – is an extension of the base level Data Vault (Raw Vault), designed for preparing data for analytics and building business logic. If Raw Vault focuses on collecting and storing data in its original form, Business Vault adds processed and aggregated data necessary for analytical systems and reports.

Key Principles of Business Vault

  1. Using business rules:
    Business Vault includes data modified or aggregated according to business rules (e.g., calculating KPIs).
  2. Preserving change history:
    Like Raw Vault, Business Vault retains a complete history of changes.
  3. Enriching with external data:
    Data can be enriched with information that did not enter Raw Vault (e.g., APIs, third-party services).
  4. Supporting traceability:
    All calculations must be transparent and documented so analysts can track the source and logic of the data.

Types of Objects in Business Vault

1. Calculated Satellites:

Satellites that store calculation results based on business rules.

Example:
Calculate the total monthly purchases of a customer.

  • Raw Vault:
    Contains customer transactions:
      HubCustomerKey TransactionAmount TransactionDate
      1 100.00 2025-01-01
      1 50.00 2025-01-15
  • Business Vault (Calculated Satellite):
      HubCustomerKey TotalMonthlySpend Month LoadDate
      1 150.00 2025-01 2025-01-31

2. Point-in-Time (PIT) tables:

PIT tables simplify data access by joining hubs, links, and satellites into a single dataset for a specific point in time.

Example:
A PIT table can combine information about passengers, flights, and booking details:

HubPassengerKey HubFlightKey BookingDetailsKey LoadDate
1 101 1001 2025-01-02

3. Bridge tables:

Used to combine data from multiple sources or calculate aggregates.

Example:
Creating a bridge table linking customers and flights through the number of bookings.

  • Raw Vault:
      HubPassengerKey HubFlightKey BookingID
      1 101 B001
      1 102 B002
  • Business Vault (Bridge Table):
      HubPassengerKey TotalBookings
      1 2

Example of Building a Business Vault

Task: Build a Business Vault for analyzing passenger activity.

  1. Raw data (Raw Vault):

    • Hub_Passengers: Stores unique passenger keys.
    • Hub_Flights: Stores unique flights.
    • Link_PassengerFlights: Links passengers and flights.

    Example:

    HubPassengerKey PassengerID LoadDate
    1 CUST001 2025-01-01
    HubFlightKey FlightID LoadDate
    101 F123 2025-01-02
    LinkKey HubPassengerKey HubFlightKey LoadDate
    1001 1 101 2025-01-02

  2. Enrichment in Business Vault:

    • Add a table calculating the number of flights per passenger.

    Calculated Satellite (Sat_PassengerActivity):

    HubPassengerKey TotalFlights LoadDate
    1 5 2025-01-31
    • Add a PIT table for convenient analysis at a point in time.

    PIT_PassengerDetails:

    HubPassengerKey HubFlightKey LoadDate
    1 101 2025-01-02

Recommendations for Building a Business Vault

  1. Automate calculations:
    Use tools like dbt or SQL scripts to automate complex calculations.
  2. Document business rules:
    Record all formulas and transformations so analysts can easily understand them.
  3. Maintain traceability:
    Link Business Vault data with corresponding Raw Vault objects to track the source and transformation of data.
  4. Optimize performance:
    Use indexes and aggregates to speed up analytical queries.

Conclusion

Business Vault is a bridge between raw data models and business analytics. It simplifies the creation of reports and dashboards while ensuring the preservation of change history and transparency of calculations. Building a high-quality Business Vault requires a clear strategy, process automation, and close collaboration between technical and business teams.