☰
Navigation :

Database Model

Introduction

The OCS reporting database data model presented here is the PostgreSQL database data model for the OCS, storing both snapshots of the real-time OCS wallet data, and bucket credit/debit activity.

Note that the OCS reporting database does not:

By default the reporting data is stored in a PostgreSQL database called n2reporting, in the schema n2ocs. Each N-Squared system component uses its own schema (e.g. the OCS uses n2ocs, while the CRM uses n2crm), however the schema name may be altered during system installation.

Installation of this data model is covered by the installation instructions.

OCS Data Model

The OCS Wallet Snapshot

Database table: n2ocs.n2ocs_account_snapshot

This table captures the output of a regular snapshot of all OCS wallets. Each snapshot of the OCS real-time database is uniquely identified by the nifi_insert_timestamp column value.

An OCS wallet may be listed more than once in this table with the same nifi_insert_timestamp due to having multiple buckets. An OCS wallet without any buckets will have a single row, with bucket details set to NULL.

Column Name Type Description
nifi_insert_timestamp timestamp with timezone The nominal timestamp at which point the OCS snapshot was taken. Each extract has the same timestamp, even if the exact time the OCS wallet was extracted is not quite at this timestamp. This is not directly related to insert time.
id text The OCS wallet.id field value. In telecommunication systems, this would often be the MSISDN.
type text The product type of the wallet. This corresponds to the wallet.annotation[@type='group'].properties.name field
wallet_state text The current lifecycle’s state value. This corresponds to the wallet.current_lifecycle.state field, and will reference a state in an wallet state machine. This will be NULL if the wallet has no lifecycle.
state_entered_at timestamp with timezone The date and time that the current lifecycle state began. This will be NULL if the wallet has no lifecycle.
transition_out_time timestamp with timezone The date and time that the current lifecycle will exit, if the current state is one with a predetermined date/time at which it will exit. This often happens with ACTIVE to DORMANT transitions. This will be NULL if the wallet has no lifecycle.
bucket_id text The ID of the bucket whose value and time are listed in this record. This will be NULL if this record is for a wallet with no buckets. The ID of the bucket corresponds to the associated wallet.bucket._id field value.
value bigint The value at the time the bucket is read, and as it is stored in the OCS (so microcents, bytes, etc) of the bucket with the given ID. This will be NULL if this record is for a wallet with no buckets.
initial_value bigint The value at the time the bucket was created, as it is stored in the OCS (so microcents, bytes, etc) of the bucket with the given ID. This will be NULL if this record is for a wallet with no buckets.
unit text The bucket unit. One of the OCS units - microcents, bytes, seconds, counter or flag.
expiry timestamp with timezone The expiry timestamp of the bucket with the given ID. This will be NULL if this record is for a wallet with no buckets, or the bucket has no expiry.

OCS Latest Wallet Snapshot

Database view: n2ocs.latest_n2ocs_account_snapshot

This view is a view across the database table n2ocs.n2ocs_account_snapshot, presenting only the latest wallet snapshot data. This view is defined functionally as:

CREATE OR REPLACE VIEW n2ocs.latest_n2ocs_account_snapshot AS
SELECT
    *
FROM
    n2ocs.n2ocs_account_snapshot
WHERE
    nifi_insert_timestamp = (SELECT MAX(nifi_insert_timestamp) FROM n2ocs.n2ocs_account_snapshot);

OCS Debit/Credit Activity

Database table: n2ocs.n2ocs_debit_credit_activity

This table captures debits and credits applied by the OCS through OCS engine activity. The OCS engine is generally responsible for rating sessions (data, voice), events (e.g. SMS), and granting credit (e.g. voucher redemption) and all such activities are captured by OCS EDRs.

When configured to process OCS EDRs, the data pipeline will load debit and credit activities into the following activity table. Each debit/credit event (combination of session_id and event_id) may have more than one row in the database table due to the event debiting, or crediting, multiple buckets.

Column Name Type Description
node_name text The OCS node which processed this event.
event_timestamp timestamp with time zone The date and time that the OCS node processed this event.
session_id text The JSLEE unique ID of the session associated with the event being processed. Where the OCS is processing a Diameter event, this will be the Diameter session ID.
event_id text The event ID within the session of this event. The event ID will match the event ID from the Diameter session, if this event is part of a Diameter session.
account_id text The OCS wallet.id field value. In telecommunication systems, this would be the MSISDN.
called_party text Where the event relates to a charge with a called party (or B-party), this field will give that party. This may be NULL.
calling_party text The calling party, normally the same as the account_id, however it may not be if normalisation is applied. This may be NULL if the event was not triggered with a calling party.
bucket text The bucket ID that was debited or credited as part of the event handled by the OCS. This may be NULL if no debit or credit was successfully applied.
unit text The unit of the bucket that was debited or credited. This may be NULL if no debit or credit was successfully applied.
adjustment_amount bigint The amount that was debited or credited. Where a value is credited, this will be negative. Where a value is debited, this will be positive. This may be NULL if no debit or credit was successfully applied.