Purpose
The purpose of this document is to highlight the current LISA data model. This documentation will include both the F&O data model and also what is available in the data warehouse.
F&O Data Model
The below ERD includes the main LISA tables that are most commonly used:
The main tables that a user will interact with are the Subscription plans, Subscription plan lines and subscription actions. Use of all other tables is circumstantial, meaning that a user will only do CRUD operations in these tables depending on which functionalities are being used.
Every table that is used by LISA, whether it be an extension of a standard table or a completely custom table, can be linked back to a subscription plan. This can be done either via a direct relationship or via another table that links to it.
Data entities to be used
For most of the tables that are used in LISA, there are specific entities that are to be used. The following is a list of entities relating to the main LISA tables that can be utilised:
- Subscription plans
Table name: BFTLicenseGroupTable
Entities to be used:
- BFTLicenseGroupEntityV2 – For standard FO data import/export.
- BFTLicenseGroupTableCDSEntity – To be used for DualWrite integrations with CRM.
- Subscription plan lines
Table name: BFTLicenseGroupLines
Entities to be used:
- BFTLicenseGroupLinesEntityV3 – For standard FO data import/export.
- BFTLicenseGroupLinesCDSEntity – To be used for DualWrite integrations with CRM.
- Subscription actions
Table name: BFTLicenseActionsTable
Entities to be used:
- BFTLicenseActionsTableEntity – For standard FO data import/export.
- BFTLicenseActionsTableCDSEntity – To be used for DualWrite integrations with CRM.
- Subscription parameters
Table name: BFTParameters
Entity to be used: BFTParametersEntity
- Billing posting profiles
Table name: BFTBillingPostingProfile
Entity to be used: BFTBillingPostingProfileEntity
- Customer Entitlements
Table name: BFTLicenseGroupLineEntitlement
Entity to be used: BFTLicenseGroupLineEntitlementEntity
Data available in the data warehouse
Refresh the Subscriptions measurement
As a part of the LISA product there are some cubes that are exposed in the data warehouse. To sync data to these tables, the ‘Subscriptions’ measurement must be used, which can be found at System administration -> Entity store:
Data available in the Data warehouse
The data that is available in the data warehouse were primarily designed to meet the requirements for the embedded Power BI reports that are included as a part of the product. The following is a list of what is available and the original purpose of each measure:
- BFTCustInvoiceTransExpanded
Used in the Global map report that is included as part of the Analytics report for LISA. Uses a variety of standard and custom tables to get invoice information. The following screenshot shows all the datasources that are used:
The Measure group includes measures such as:
- Sum of LineAmount
- Sum of Posted tax
- Sum of posted invoice quantity
Some of the dimensions included in this measure group are:
- Company
- Customer
- SalesCategory
- Currency
- DeliveryLocation
- BFTLicenseGroupNotification
This measure group is used in the Plan count analysis report that is a part of the Analytics report for LISA. It provides a measures that counts the number of plans published for the first time. The Dimensions for this measure group are
- Company
- Date
- Customer
- BFTAMRRReportCube
Used in the AMRR report that is a part of the LISA AMRR reports. AMRR is a combination of two standard business reports: ARR (Annualised recurring revenue) and MRR (Monthly recurring revenue). These reports break down revenue by year, quarter, and month. This also groups revenue by reason. To do so, the subscription actions, subscription plans, and subscription plan lines are used to calculate these reports.
The only measure in this measure group is a sum of LineAmount.
The available dimensions include:
- Company
- Subscription plan line reason
- Month
- Year
- Month name
- Quarter
- BFTAverageCustomerLifeCube
Used in the Average customer life report that is a part of the LISA AMRR reports. The primary use is to calculate the average customer life of a customer per company. The measures in the measure group are:
- Count of customers
- Sum of customer life
The only dimension available in this measure group is a dimension for the company field.
- BFTAverageCustomerLifeValueCube
Used in the Average customer life value report that is a part of the LISA AMRR reports. The primary use is to calculate the average customer life value (money spend throughout the customer’s life) and acquires this by making use of subscription plans, subscription plan lines and actions.
For customers to be included in this aggregate measure, they must have posted actions of type ‘Generate sales order invoice’ relating to published plans which are of type ‘Per unit subscriptions. Also, the only lines that will be taken into consideration in this measurement are lines which have a start date which is less than the current date.
This measure group has the customer life field as an attribute but, the only measure that is included is a sum of LineAmount.
The dimensions include:
- Company
- Date_From
- Date_To
- Customer
- Churn rate reports
There are three churn rate reports as part of the LISA AMRR reports, which are:
- BFTAMRR_ChurnRateCube
- BFTAMRR_ChurnRateQuarterlyCube
- BFTAMRR_CHurnRateYearlyCube
These reports make use of the subscription actions, subscription plans and subscription plan lines tables where a reason code of type cancel is used.
For each of the above-mentioned measure groups, there are three attributes:
- Total churned lines
- Churned revenue
- Total lines
The difference between the measure groups is the dimensions that are available. The dimensions available for the respective measure groups are:
- Company, Month, MonthName, Quarter, Year
- Company, Quarter, Year
- Company, Year
- BFTAMRR_NRRRCube
Used in the Net Revenue Retention Rate report that is a part of the LISA AMRR reports. This report calculates revenue for the current period and compares it to that of the previous period. Report is available by three groupings: monthly, quarterly, and yearly. This measurement used a view that is comprised of the subscription actions, subscription plans and subscription plan lines tables.
The measures included in this measure group are:
- CurrRevenue
- PrevRevenue
Dimensions include:
- Company
- Month
- Year
- Month name
- Quarter
- BFTCustomerLifeDetailsCube
This is used as part of the AMRR reports and is used in every report where there is anything relating to customers. This uses the Subscription plans and plan lines tables to acquire customer details based on subscription usage. The customers that are taken into consideration for this measure group are customers who have a published subscription plan. Also, the dates to calculate the average customer life will be based on the lines which have a start date less than the current date.
There is only one measure included in this measure group which is the count of customers.
The dimensions include:
- Company
- Date_Start
- Date_End
- BFTActiveCustomersView_V2
This is used in the part of the AMRR reports for LISA. The primary use is to calculate active customer details from the subscription plan and subscription plan lines tables. The definition of an active customer is a customer which has a posted sales order action linked to one of the subscription plans.
The only measure in this measure group is a distinct count of customers.
The dimensions include:
- Company
- Year
- Quarter
- Month
- Month name
- BFTAnalytics_CostVsSalesCube
Used as part of the Analytics reports for LISA. Used to calculate a sum for cost and sales for each month/quarter/year depending on the grouping of the report chart. This measure group uses the subscription actions, subscription plans and subscription plan lines to gather the info that is required.
The measures include in this measure group are:
- Cost
- Revenue
- Margin
The dimensions include:
- Company
- Month name
- Month
- Year