Workflow data model bundles all the revenue data, which can be extracted as the revenue-only data or the billing and revenue integrated data in a fast and optimal method. These data models can help you to build use case reports. For example, you can create views over the data model for features such as analytical dashboards and data queries. It is recommended for the user to have data model and SQL technical expertise before creating views and writing queries.
Revenue object data model
The following diagram is a high-level view of how key data objects are related to one another within Zuora Revenue.
Revenue objects and descriptions
The following objects are present in the data model. Click the name of each object for more details.
REVENUECALENDAR: Contains the details of the accounting calendar that are configured in Zuora Revenue.
REVENUECONTRACT: Contains the details of distinct revenue contracts (RC) based on the defined RC grouping template.
REVENUECONTRACTACCOUNTINGENTRIES: Contains the accounting entries / journal entries of every transaction based on its performance obligation template.
REVENUECONTRACTACCOUNTINGSEGMENTS: Contains the Accounting segments details for deferring and releasing segments of every accounting entry.
REVENUECONTRACTACCOUNTINGTYPE: Contains the accounting type details of every Accounting entries.
REVENUECONTRACTACTIONS: Contains the details of every revenue contract POB's release and deferral actions thats are taken by both the system and manual operation.
REVENUECONTRACTAPPROVALS: Contains the details of revenue contract approvals and their history.
REVENUECONTRACTBILLSDIMENSIONS: Contains the billing transaction dimensional details, which include transaction types such as INV, CM, CM-R, CM-C, CM-RO, and RORD.
REVENUECONTRACTBILLSFACTS: Contains the billing transaction fact, which includes transaction types such as INV, CM, CM-R, CM-C, CM-RO, and RORD.
REVENUECONTRACTCOSTDIMENSIONS: Contains the cost transaction dimensional details of both standard standard and user-defined cost.
REVENUECONTRACTCOSTFACTS: Contains the cost transaction fact, which includes both standard and user-defined costs.
REVENUECONTRACTHOLDS: Contains the details of revenue contract hold at all the line level, POB level, and Revenue Contract level.
REVENUECONTRACTLINESDIMENSIONS: Contains the sales order transaction dimensional details.
REVENUECONTRACTLINESFACTS: Contains the sales order transaction fact details.
REVENUECONTRACTMJENTRIESDIMENSIONS: Contains the manual journal transaction dimensional details.
REVENUECONTRACTMJENTRIESFACTS: Contains the Manual journal transaction fact details.
REVENUECONTRACTVCDIMENSIONS: Contains the variable consideration (VC) transaction dimensional details of both standard and user-defined VC.
REVENUECONTRACTVCFACTS: Contains the variable consideration transaction fact, which includes both standard and user-defined VC.
REVENUEPERIODS: Contains the open period details.
Creating views based on the data model
The following sample queries show how to create the views that are required for building any use case-specific reports or analytical dashboards.
Line level transactional report sample query
The following sample query shows how to get the line-level transactional report with limited fields including the booking, billing, and accounting details. You can customize the query to add any number of attributes to the line level transaction report.
Waterfall sample query
The following sample query shows how to get the waterfall report.
SELECT C.ACCOUNTINGPERIODID AS ASOFPERIODID, S.REVENUESCHEDULEID , S.REVENUECONTRACTLINEID, S.ROOTREVENUECONTRACTLINEID, S.SCHEDULEDPERIODID, S.POSTINGPERIODID, S.REVENUEORGANIZATIONCODE, S.REVENUEBOOKID, S.REVENUECLIENTID, S.ACCOUNTINGSEGMENT, S.ACCOUNTINGTYPEID, S.NETTING_ENTRY_FLAG, S.SCHD_TYPE_FLAG, (S.TRANSACTIONDEBITAMOUNT + S.TRANSACTIONCREDITAMOUNT) AS TACTIVITY, (S.FUNCTIONALDEBITAMOUNT + S.FUNCTIONALCREDITAMOUNT) AS FACTIVITY, (S.REPORTINGDEBITAMOUNT + S.REPORTINGCREDITAMOUNT) AS RACTIVITY, S.CREATEDACCOUNTINGPERIOD, S.CREATEDDATE, S.CREATEDBY, S.UPDATEDDATE, S.UPDATEBY, S.INCREMENTALREFESHDATE FROM REVENUECONTRACTACCOUNTINGENTRIES S, REVENUECALENDAR C, REVENUECONTRACTACCOUNTINGTYPE A WHERE S.CREATEDACCOUNTINGPERIOD <= C.ACCOUNTINGPERIODID AND S.SCHEDULEDPERIODID >= C.ACCOUNTINGPERIODID AND S.ACCOUNTINGTYPEID = A.REVENUEACCOUNTTYPEID AND A.WATERFALL_FLAG = 'Y' and C.ACCOUNTINGPERIODNAME = '<PERIOD NAME>';