Reporting Datamodel

The following contains a description of the various tables in the reporting database. Each section of the model is organized into seperate areas/silos mostly to indicate their usage area. Though its up to you to be creative.

The datamodel documentation is divided into a section for each of the various areas, as well as sub-sections for each table.

Some of the tables have noteworthy columns that are called out separately, in their respective sections.

General Purpose

The main area is an catch-all where things that are not naturally in one area or another is created. This contains reporting management data, as well as cross cutting data such as Organizations and Subscribers.

Schema Name : dbo

Available Tables

• Organizations

• Products

• Subscribers

• SubscriberContacts

• Tag

• ReportingGroups

• ProductReportingGroups

Organizations

Information about organizations such as organization name, e-mail, phone and address. Organization data is commonly denormalized.

Products

Contains information about all available products.

Noteworthy columns

ProductCategory: This is a grouping field only for reporting and analytics purposes, can be used to split the reports into groups. You create a group, and then link products to the groups via the API.

Subscribers

The main list of all subscribers in the system as well as the details of the primary contact. This information is mainly used for invocing, reporting and deliveries.

Noteworthy columns

ContactId: This is a reference to the subscriber contact from which the contact information is retrieved.

ExternalId: External reference as defined by a third party. Typically CRM or a master data system of sorts.

SubscriberContacts

All contact information for all subscribers, such as the primary contact address, billing addresses and delivery addresses.

Noteworthy columns

IsPrimary: Indicates the contact information is the default that will be used for billing, deliveries etc if not overridden by a specific selection of another contact. Identification: May contain information about organization number, customer references and more. Data is stored as a JSON array, and T-SQL JSON Functions can be used to parse the data.

Tag

Tags are text fields that can be associated with different entities, used for grouping and reporting purposes. They carry no value and are only represented in the reporting datamodel.

The idea is to let third parties tag on information as appropriate for their specific use case.

At the current time its possible to provide tags for:

• Subscriptions

• Subscribers

• Subscriber Accounts

• Payments

• Payment Demands

At the current time the API endpoint for creating a new Order contains a tag value that will be assigned to the first subscription, the subscriber and the subscriber account. It is designed as a list of tags where all tags will be added.

The ReferenceId will then correspond to Subscriber, SubscriberAccount and Subscription, respectively.

An example is to tag all orders with a “Sales Unit” or “Store” Id, allowing to report and analyze sales by physical department or store.

The vast majority of the entities in the reporting model have a connection to SubscriberId and can then be grouped / filtered in relation to the Tag given.

ReportingGroups

Reporting groups, as the name suggests, a way to group things for reporting purposes. Specifically Products and permanent discounts. For instance you may want to group all products of a specific type as “Cosmetics” and another as “Foodstuffs”. You can do this in each and every report you generate, or you can setup the group in the reporting model, and just group and filter on the reporting group.

Items can be included in multiple groups.

Economy and Billing

The economy and billing area/silo, contains data related to the recurring billing of the subscriptions. Anything you need to know about your billing is collected here such as Invoices and their state, outstanding transtractions and fees.

Schema Name : economy

Available Tables

• AccountTransaction

• Invoices

• PaymentDemands

• PaymentDemandFees

• PaymentDemandDetails

• PaymentDemandAllowances

• PaymentDemandCharges

• SubscriberLedgers

• SubscriberAmounts

AccountTransaction

Outstanding account transactions. This contains the current list of transaction that has yet to be billed (put on an Invoice). These are generated as payments are processed, subscriptions are cancelled and related.

Invoices

Summary data about the various invoices produced, such as state and issue date. Does not include the entire invoice, just summary data.

PaymentDemands

Invoicing of subscribers produces a “Demand for Payment”, and PaymentDemand, along with its related entities, contains details about what is billed such as:

• Reference to the subscription period(s) billed and the amount.

• Any transactions caused by metered billing or manual corrections.

• Time of issuing.

• Reference to the resulting Invoice Document.

• Reference to the Order/first period and the amount.

• State of settlment.

Payment Demand is the basis for payment claims that the system generates, and it is on the basis of these that an invoice is formed.

Example reporting/analysis use cases include:

• Total outstanding dept amount.

• Billed Amount by month or even by product.

• Degree of payment by area, organization or payment method.

PaymentDemandFees

Fees related to payment demands and reminders for the given payment demand.

PaymentDemandDetails

Details refers to the Subscriptions, Orders and external one-time transactions associated with a payment demand.

Keep in mind that Subscriptions are always billed in advance, but the subscription that is referred here is the past one (the one deciding how the next bill is going to be basically).

Noteworthy columns

NextSubscriptionId: This column is populated once the subscription is renewed/extended. Thus at the time when the NextSubscriptionId is populated the demand now covers and existing

PaymentDemandAllowances

Any allowances consumed from the Billing Account (AccountTransaction) are presented here. Allowances reduce the total amount to be claimed.

PaymentDemandCharges

Any extra charges/debits from the Billing Account (AccountTransaction) are presented here. Charges increase the total amount to be claimed.

Typically these would be metered charges, or left overs from previously settled demands with a missing amount.

SubscriberLedgers

The entity contains all ledger entries/transactions regardless of organization or state.

Represents the current total state of a subscribers accounts.

KPI: SubscriberAmounts

This is the sum of subscriberledgers grouped by subscriberid, organizationid and currency at any given time.

Can be used to give a “snapshot” insight into how much is billed or owed across all organizations. It is designed as a pre-calculated KPI.

Orders

The orders area/silo, contains data related to incoming orders, and basically should be your goto area for sales performance data.

Schema Name : order

Available Tables

• OrderAmounts

• OrderCompletedAmounts

• Orders

• Products

KPI: OrderAmounts and OrderCompletedAmounts

One table includes all orders that have been started (and later cancelled), while the other only summarises orders that have been completed.

Example use cases involves a running dashboard of the value of your orders over time.

Orders (and Products)

Summary data about all orders started, cancelled and completed.

It includes information such as the price/value, when it was created and completed, as well as when it was abandonned/cancelled if that was the cause.

It refers to a Products table so its possible to group Orders and Products and analyze sales behaviour using this data.

Subscriptions

The subscription area/silo, is similar to the Orders silo, except it related to data about the recurring business, it should be your goto area for reucrring performance data.

Schema Name: subscription

Avtailble Tables

• Subscriptions

• CanceledSubscriptions

• Contracts

• Enterprise Plans

• SubscriptionPackages

• SubscriptionPacakgeProducts

Subscriptions

All subscriptions registered in the system, with comprehensive information about, start and end time, details such as price, tax ( VAT ), number of units, cancellation status and reason for cancellation as well as renewal status.

CanceledSubscriptions

All canceled subscriptions, including reason for cancellation.

Contracts

Information on subscriptions with fixed contracts, and how long those contracts are defined to last.

EnterprisePlans

General information about agreements for enterprises used for common billing and rules for selling to these enterprises.

SubscriptionPackages and SubscriptionPacakgeProducts

Contains instance level details about a given subscription, detailing the specific for that subscription. More commonly known as a Subscription Plan.

Noteworthy columns

BillingFrequencyId: The frequency at which the Subscription is billed/renewed

• 1001 - Month

• 1003 - Quarter

• 1012 - Full year.

SubscriptionPackageChainId: Defines the package chain for stepping/changing packages during renewal. I.e. first pay 99 kroner the first month, then 149 for the second, 199 for the third before finally changing to the full price of 249.

InitialTermType: This is used if the first period is to have a different length. 10 - «Until date », 20 - «Number of days», 100 - «Out the month», 200 - «Out the year» .

InitialTermValue: This will then have slightly different values ​​depending on the type. For the value «10», then there will be a date. For “20” it will be a number of days “. For “100” and “200”, it is not used.

AutomaticStop: This means that the subscription will be automatically stopped after the period.

Payments

Schema Name: payment

Avtailble Tables

• DailyPaidAmounts

• Payments

KPI: DailyPaidAmounts

KPI style table with total amount of what is paid for any given date, for each organization.

This only includes payments that have been approved and complete, payments yet to be identified are not included in the summary.

Payments

All payments for each individual subscriber, regardless of source. The most common source values are OCR, PayEx , Manual, Import and MI (migrated).

Entity Relationship Diagram

The following is an ER diagram of the reporting data model serving as a visual reference.

        erDiagram
    Subscriber {
        Guid Id
        String ExternalId
        Int64 SubscriberNumber
        String Name
        String Phone
        String Email
        String City
        String Zip
        String Country
        String AddressLines
        Guid ContactId
    }
    SubscriberContact {
        Guid Id
        Guid SubscriberId
        String Type
        String Address
        String Email
        String Phone
        Bool IsPrimary
        String Identification
    }
    Tag {
        Guid Id
        String Value
        Guid ReferenceId
        String ReferenceType
    }
    ReportingGroup {
        Guid Id
        String Name
    }
    ProductReportingGroup {
        Guid Id
        Guid ProductId
        Guid ReportingGroupId
    }
    Product {
        Guid Id
        String Name
        String Description
        Decimal Price
        String ProductCategory
    }
    Subscription {
        Guid Id
        Guid SubscriberId
        String ExternalId
        String SubscriptionNumber
        String Name
        DateTime StartDate
        DateTime EndDate
        String Status
        Guid PlanId
        Int BillingFrequencyId
        Int SubscriptionPackageChainId
        Int InitialTermType
        String InitialTermValue
        Bool AutomaticStop
    }
    CanceledSubscription {
        Guid Id
        Guid SubscriptionId
        String Reason
    }
    Contract {
        Guid Id
        Guid SubscriptionId
        DateTime StartDate
        DateTime EndDate
        Int TermLength
    }
    EnterprisePlan {
        Guid Id
        String Name
        String Rules
    }
    SubscriptionPackage {
        Guid Id
        Guid SubscriptionId
        String Name
        Int BillingFrequencyId
    }
    SubscriptionPackageProduct {
        Guid Id
        Guid SubscriptionPackageId
        Guid ProductId
    }
    Invoice {
        Guid Id
        Guid SubscriptionId
        DateTime IssueDate
        DateTime DueDate
        Decimal Amount
        String Status
    }
    Payment {
        Guid Id
        Guid InvoiceId
        DateTime PaymentDate
        Decimal Amount
        String Method
    }
    AccountTransaction {
        Guid Id
        Guid SubscriberId
        Decimal Amount
        String Status
    }
    PaymentDemand {
        Guid Id
        Guid SubscriberId
        Guid InvoiceId
        DateTime IssueDate
        Decimal Amount
        String Status
    }
    PaymentDemandFee {
        Guid Id
        Guid PaymentDemandId
        Decimal Amount
        String Type
    }
    PaymentDemandDetail {
        Guid Id
        Guid PaymentDemandId
        Guid SubscriptionId
        Guid OrderId
        Decimal Amount
        Guid NextSubscriptionId
    }
    PaymentDemandAllowance {
        Guid Id
        Guid PaymentDemandId
        Decimal Amount
    }
    PaymentDemandCharge {
        Guid Id
        Guid PaymentDemandId
        Decimal Amount
    }
    SubscriberLedger {
        Guid Id
        Guid SubscriberId
        Decimal Amount
        String Status
    }
    SubscriberAmount {
        Guid Id
        Guid SubscriberId
        Guid OrganizationId
        String Currency
        Decimal Amount
    }

    Order {
        Guid Id
        Guid SubscriberId
        DateTime CreatedDate
        DateTime CompletedDate
        Decimal Value
        String Status
    }

    OrderAmount {
        Guid Id
        Guid OrderId
        Decimal Amount
        DateTime Date
    }

    OrderCompletedAmount {
        Guid Id
        Guid OrderId
        Decimal Amount
        DateTime Date
    }

    DailyPaidAmount {
        Guid Id
        Guid OrganizationId
        Decimal Amount
        DateTime Date
    }

    PaidAMountByValueDate {
        Guid Id
        Guid OrganizationId
        Decimal Amount
        DateTime ValueDate
    }

    Organization {
        Guid Id
        String Name
        String Email
        String Phone
        String Address
    }

    SubscriberAccount {
        Guid Id
        Guid SubscriberId
        String AccountNumber
        String Status
    }
    TotalIdpUsers {
        Guid Id
        Guid OrganizationId
        Int TotalUsers
        DateTime Date
    }
    IdpUsersByDate {
        Guid Id
        Guid OrganizationId
        Int UserCount
        DateTime Date
    }
    InvoicedAmount {
        Guid Id
        Guid OrganizationId
        Decimal Amount
        DateTime InvoiceDate
    }
    SubscriptionRevenueChanges {
        Guid Id
        Guid SubscriptionId
        Decimal ChangeAmount
        DateTime ChangeDate
    }
    SubscriptionRevenues {
        Guid Id
        Guid SubscriptionId
        Decimal RevenueAmount
        DateTime RevenueDate
    }

    Subscriber ||--o{ SubscriberAccount : account
    Organization ||--o{ TotalIdpUsers : idpusers
    Organization ||--o{ IdpUsersByDate : idpusersbydate
    Organization ||--o{ InvoicedAmount : invoiced
    Subscription ||--o{ SubscriptionRevenueChanges : revenuechange
    Subscription ||--o{ SubscriptionRevenues : revenue

    Subscriber ||--o{ SubscriberContact : has
    Subscriber ||--o{ Subscription : has
%% Subscriber ||--o{ Tag : tagged
    %% The Tag entity uses ReferenceId and ReferenceType for polymorphic relationships.
    %% Tag can reference multiple entity types (e.g., Subscriber, Order, etc.) via ReferenceType.
    %% This is a polymorphic association, not a direct relationship.
    Subscriber ||--o{ AccountTransaction : has
    Subscriber ||--o{ SubscriberLedger : ledger
    Subscriber ||--o{ SubscriberAmount : amount
    Subscriber ||--o{ Order : has
    Subscription ||--o{ Invoice : has
    Subscription ||--o{ CanceledSubscription : canceled
    Subscription ||--o{ Contract : contract
    Subscription ||--o{ SubscriptionPackage : package
    SubscriptionPackage ||--o{ SubscriptionPackageProduct : includes
    SubscriptionPackageProduct ||--o{ Product : product
    Product ||--o{ ProductReportingGroup : grouped
    ProductReportingGroup ||--o{ ReportingGroup : group
    Invoice ||--o{ Payment : paid
    Invoice ||--o{ PaymentDemand : demand
    PaymentDemand ||--o{ PaymentDemandFee : fee
    PaymentDemand ||--o{ PaymentDemandDetail : detail
    PaymentDemand ||--o{ PaymentDemandAllowance : allowance
    PaymentDemand ||--o{ PaymentDemandCharge : charge
    Organization ||--o{ PaidAMountByValueDate : has