Skip to main content

Zuora Revenue Secure Data Share for Snowflake

Zuora

Zuora Revenue Secure Data Share for Snowflake

Overview

Zuora revenue data model bundles all the revenue data that can extract the revenue-only data or the revenue and billing integrated data in a fast and optimal method. These data models help the user to build use case reports like creating views over the data model for features like analytical dashboards and data queries. We recommend that the user should have data model and SQL technical expertise to create views and write queries.

Revenue object data model

Secure Share - Revenue Data Model_New.png

Revenue Objects and Descriptions

RevenueContracts - contains the details of distinct Revenue Contracts based on the defined Revenue Contract Grouping Template.

RevenueCalendar - contains the Revenue Accounting Calendar details.

RevenuePeriods - contains the Open period details.

RevenueContractLinesDimensions - contains the Sales order transaction dimensional details.

RevenueContractLinesFacts - contains the Sales order transaction fact details.

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 custom-defined cost.

RevenueContractCostFacts - contains the Cost transaction fact, which includes both standard and custom-defined costs.

RevenueContractMJEntriesDimensions - contains the Manual journal transaction dimensional details.

RevenueContractMJEntriesFacts - contains the Manual journal transaction fact details.

RevenueContractVCDimensions - contains the Variable Consideration transaction dimensional details of both standard and custom-defined costs.

RevenueContractVCFacts  - contains the Variable Consideration transaction fact, which includes both standard and custom-defined VC.

RevenueContractAccountingType - contains the Accounting type details of every Accounting entries.

RevenueContractAccountingSegments - contains the Accounting segments details for deferring and releasing segments of every Accounting entry.

RevenueContractHolds - contains the details of Revenue Contract hold at all three levels such as line level, POB level, and Revenue Contract level.

RevenueContractApprovals - contains the details of Revenue Contract approvals and their history.

RevenueContractAccountingEntries - contains the Accounting Entries / Journal Entries of every transaction based on its performance obligation template.

RevenueContractActions - contains the details of every Revenue Contract POB's Release and Deferral actions made by both the system and manual actions.

RevneueContractAccountingEntrySummary - contains the Report level data like Additions, releases, begin balance, ending balance, and so on.

 

To learn about the Data Dictionary for Revenue Objects, see here.

Creating Views on-top of the Data Model

The following sample views show how to create the views required to build any use case-specific reports or analytical dashboards.

RollForward Report Sample query

The following is a sample query that shows how to get the RC roll forward report with limited fields. You can customize the query to add any number of attributes to the roll forward report.

SELECT  CustomerName
,sum(ReportingAddition) TotalReportingAddition
,sum(ReportingRelease) TotalReportingRelease
,sum(ReportingBeginBalance) TotalReportingBeginBalance
,sum(ReportingEndingBalance) TotalReportingEndingBalance
FROM REVENUECONTRACTACCOUNTINGENTRYSUMMARY schd,REVENUECONTRACTLINESDIMENSIONS line, REVENUECONTRACTACCOUNTINGTYPE acct
WHERE schd.RevenueContractLineID = line.RevenueContractLineID
  AND schd.accountingtypeid = acct.revenueaccounttypeid
  AND acct.BALANCE_SHEET_ACCT_FLAG = 'Y'
  AND schd.schd_type_flag in ('R','A')
  AND schd.revenueclientid = line.revenueclientid
  AND schd.revenueorganizationcode = line.revenueorganizationcode
  AND schd.revenuebookid = line.revenuebookid
GROUP BY CustomerName;

Line level Transactional Report Sample query (Booking / Billing / Accounting details

The following is a sample query that 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 roll forward report.

SELECT 
  rcld.revenuecontractlineid, 
  rcld.revenueorganizationname, 
  rcld.revenuebookname, 
  rcld.revenuecontractid, 
  rcld.salesordernumber, 
  rcld.salesorderlinenumber, 
  rcld.itemnumber, 
  rcld.customernumber, 
  rcld.productcategory, 
  rcld.productfamily, 
  rcld.businessunit, 
  SUM(rclf.extendedsellprice) extendedsellprice, 
  SUM(rclf.carveadjustment) carveadjustment, 
  SUM(rclf.allocatedextendedprice) allocatedextendedprice, 
  SUM(rclf.unreleasedrevenue) unreleasedrevenue,
  SUM(rclf.releasedrevenue) releasedrevenue,
  SUM(rcbf.billingextendedsellprice) billingextendedseliprice, 
  SUM(rcbf.billingdeferedamount) billingunreleasedrevenue, 
  SUM(rcbf.billingrecognizedamount) billingreleasedrevenue, 
  SUM(rcae.transactioncreditamount) transactionalnetrevenue, 
  SUM(rcae.transactionbilledcreditamount) transactionalbilledrevenue, 
  SUM(rcae.transactionunbilledcreditamount) transactionalunbilledrevenue, 
  SUM(rcae.transactioncarveadjustmentamount) transactionaladjustmentrevenue 
FROM RevenueContractLinesDimensions rcld, 
     RevenueContractLinesFacts rclf, 
     (SELECT /* Billings details */
      rcbf.revenuecontractlineid, 
      rcbf.revenueorganizationcode, 
      rcbf.revenueclientid, 
      rcbf.revenuebookid, 
      SUM(rcbf.billingextendedsellprice) billingextendedsellprice, 
      SUM(rcbf.deferedamount) billingdeferedamount, 
      SUM(rcbf.recognizedamount) billingrecognizedamount 
      FROM RevenueContractBillsFacts rcbf 
      GROUP BY 
      rcbf.revenuecontractlineid, 
      rcbf.revenueorganizationcode, 
      rcbf.revenueclientid, 
      rcbf.revenuebookid) rcbf, 
     (SELECT /* Revenue Accounting details */
      revenuecontractlineid, 
      revenueorganizationcode, 
      revenueclientid, 
      revenuebookid, 
      SUM(transactioncreditamount) transactioncreditamount,
      SUM(CASE WHEN accountingtypeid = 'R' AND unbilled_flag = 'N' THEN transactioncreditamount ELSE 0 END) transactionbilledcreditamount,
      SUM(CASE WHEN accountingtypeid = 'R' AND unbilled_flag = 'Y' THEN transactioncreditamount ELSE 0 END) transactionunbilledcreditamount,
      SUM(CASE WHEN accountingtypeid = 'X' THEN transactioncreditamount ELSE 0 END) transactioncarveadjustmentamount
      FROM RevenueContractAccountingEntries 
      WHERE accountingtypeid IN (SELECT revenueaccounttypeid 
                                 FROM RevenueContractAccountingType 
                                 WHERE wf_summ_type = 'Net Revenue') 
      GROUP BY 
      revenuecontractlineid, 
      revenueorganizationcode, 
      revenueclientid, 
      revenuebookid) rcae 
WHERE 
  rcld.revenuecontractlineid = rclf.revenuecontractlineid 
  AND rcld.revenueorganizationcode = rclf.revenueorganizationcode 
  AND rcld.revenueclientid = rclf.revenueclientid 
  AND rcld.revenuebookid = rclf.revenuebookid 
  AND rcld.revenuecontractlineid = rcbf.revenuecontractlineid (+) 
  AND rcld.revenueorganizationcode = rcbf.revenueorganizationcode (+) 
  AND rcld.revenueclientid = rcbf.revenueclientid (+) 
  AND rcld.revenuebookid = rcbf.revenuebookid (+) 
  AND rcae.revenuecontractlineid (+) = rcld.revenuecontractlineid 
  AND rcae.revenueorganizationcode (+) = rcld.revenueorganizationcode 
  AND rcae.revenueclientid (+) = rcld.revenueclientid 
  AND rcae.revenuebookid (+) = rcld.revenuebookid 
GROUP BY 
  rcld.revenuecontractlineid, 
  rcld.revenueorganizationname, 
  rcld.revenuebookname, 
  rcld.revenuecontractid, 
  rcld.salesordernumber, 
  rcld.salesorderlinenumber, 
  rcld.itemnumber, 
  rcld.customernumber, 
  rcld.productcategory, 
  rcld.productfamily, 
  rcld.businessunit;