Overview
PSS is the application database using Microsoft SQL Server and supports the following KORE products:
- KORE Sponsorship & Partner Engagement
- KORE Ticketing & Fan Engagement
- KORE Suites & Premium
KORE already supports an integration that syncs 180 objects from the PSS database into DWA once a day. In the current integration, KORE has expanded the amount of data objects being pushed into DWA to include the 23 views in the KOREIQ schema of the PSS database used by the KORE Sponsorship & Partner Engagement product.
The 23 objects, listed below are aggregated datasets designed to power the standard reporting package for the KORE Sponsorship & Partner Engagement product.
KORE wants to be able to provide these reporting view to customers within the DWA product so our customers can directly access this dataset without having direct database access to the application database, KORE PSS and be able to build their own custom reporting and analysis beyond what is being offered in the standard KORE provided reports.
This also gives our Customers the ability to blend this data set with other data sets already in KORE DWA.
This integration is currently set at once a day frequency and is a full pull every time.
To setup this integration, reach out to your CSX rep and provide your 'pssorgid' - PSS organization ID for the KORE customer
ERD and data dictionary
(This information is provided on a best-effort basis without guarantees. For clarity, most column names are omitted from this ERD.)
Table Descriptions
| Table Name | Description |
|---|---|
| sponsorship_activities_v | Provides details related to the history of all activities. The Customer Name field displays either the contact or account, depending on the provided ID field. Other relevant fields in this data source include account, whom the activity belongs to, the activity type, and whether the activity is completed or still open, which is based on the “Completed On” date. This will include all activities from KORE modules, including ticketing and Suites & Premium. |
| sponsorship_approval_actions_v | Provides details related to the history of all approval action on deals. Relevant columns in this data source include the different approval steps, the date a deal moves to a step, and the users involved. |
| sponsorship_available_inventory_v | Provides details on the history of all inventory items. The data source uses several views in the PSS database to display availability, based off default rate card assignments. It can show total units, total revenue, sold units, sold revenue, available units, and available revenue. Furthermore, data from pipeline and contracted deals is layered in alongside availability. |
| sponsorship_bills_v | Provides details related to the history of all bills in the database that are tied to a deal sheet and an account. Some deal information is provided by default, such as deal sheet number, rating, stage, type, and status Parameters exist for toggling between various bill amounts and bill dates. |
| sponsorship_bonus_inventory_reporting_v | Provides details about deal lines that are considered “Out of Contract” and if they are tied to existing Contractual deal lines. This data source is at the inventory level of detail twice, once for the initial inventory item and then again if there is a bonus item tied to that item. |
| sponsorship_campaign_forecast_v | Provides details about revenue and their relevant deals. This data source focuses on revenue, both from active and inactive deals. Deals created in error, lost deals, and inactive deals are brought in here through the approval process of a deal sheet. |
| sponsorship_deal_sheet_activities_v | Similar logic to the activities data source, but only activities against deal sheets. Provides details related to the history of only those activities made directly on a deal. The customer Name field displays either the contact or account, depending on the provided ID field. Other relevant fields in this data source include account, whom the activity belongs to, the activity type, and whether the activity is completed or still open, which is based on the “Completed On” date. |
| sponsorship_exclusivities_v | Provides details related to the topic of contract exclusivities. This would typically be used to show contracted deals, exclusivity type, industry, and exclusivity remarks. |
| sponsorship_expense_audit_v | Provides information about inventory then brings in expense allocation at the deal line level of detail. Deal seasons are based on the start year of the individual deal lines that make up a deal. This data source only looks at seasons within the last three years. |
| sponsorship_expenses_v | Checks deals for expense allocation data at the deal line level of detail. Including deal information, external reporting categories are brought in based on the expense allocation. |
| sponsorship_external_reporting_categories_v | Provides details equal to the Inventory Reporting data source, but also expands that out based on external reporting types. Only one type should be selected to display the same total revenue figure. Then, external reporting categories could be displayed. Inventory items without an external reporting category within a type would show as blank. |
| sponsorship_inventory_reporting_v | Provides details for deal-line revenue reporting purposes. It functions to report off all active deal lines – along with the product, category, division – only for the seasons when inventory items exist. It would be used predominantly for current-season reporting. If a future season has been opted out, a $0 placeholder will be in its place rather than what the revenue would have been for that season. |
| sponsorship_lost_deals_v | Provides details about deals and their active status. Most standard data sources filter only to active deals, where this source will bring in every deal, the sales team & coordinators involved, and the active status, such as Active, Inactive, Bad Deal (deal created in error) or if the deal was lost in the sales process. |
| sponsorship_options_v | Provides details related to the topic of contract options. Revenue is not displayed in this data source, only those deals with options and related details. Only would be used to show contracted deals, option type name, option deadline date, and option remarks. |
| sponsorship_over_delivery_v | Provides revenue details related to bonused or out-of-contract deal lines out of the Service Console. Key fields in this data source are deal sheet, account, and the make good or bonus value at the deal line level of detail. |
| sponsorship_rev_rec_audit_v | Provides details for the division level-of-detail for revenue reporting purposes. For seasons with inventory, where the deal has inventory, the revenue fields will match the details from the KOREIQ Sponsorship - Inventory Reporting view. This should predominantly be used for future-season revenue reporting. |
| sponsorship_rev_rec_inventory_reporting_v | Works for all active deal seasons, but is mostly just used for future-season analysis. For seasons when inventory exists, then these revenue fields exactly equal the values with the deal-line logic from the KOREIQ Sponsorship - Inventory view. For future seasons, these fields pro-rate the value of the deal based on the percentage distribution of assets from the most recent season with inventory. So, this reports level of detail is inventory, and contains deal line level details. |
| sponsorship_revenue_forecast_v | Provides details for the division level-of-detail for revenue reporting purposes. It is empowered by the PSS flattened database view entitled Sponsorship.SponsorshipRevenueForecastByUser. For seasons with inventory, where the deal has inventory, the revenue fields will match the details from the KOREIQ Sponsorship - Inventory Reporting view. This should predominantly be used for future-season revenue reporting. |
| sponsorship_sales_goals_v | Provides information related to goals by sale type (new, renewal or existing) and division level of-detail revenue reporting, which can be shown by user. Revenue is tied back to goals on five factors: User, division, season, round, and sale type. |
| sponsorship_trade_items_v | Provides details about inventory items considered “trade.” These items are then organized by deal, account, inventory category, and their fair market value. |
| sponsorship_trade_reconciliation_v | Provides similar logic as trade items but in reverse order, starting with deal information and showing which of those have trade items. |
Example queries
The view Sponsorship_revenue_forecast_v provides an comprehensive view of accounts and deal sheets of a customer.
Sometimes clients may have additional custom fields that they may either add to the account. Because the Sponsorship_revenue_forecast_v is a standard view, it might not have the information on custom fields. This information on the added custom fields to accounts lives in SyncedAccounts_Extension table.
In a scenario where the customer wants to view the information on all their accounts along with any custom fields they added, they can use the following query.
Select *
From kore_iq_sponsorship.sponsorship_revenue_forecast_v rev
Left Join korepss.SyncedAccounts_Extension ae
on rev.sf_accountid = ae.sf_accountid
;