AggLevel1AccountUsageStats

Analysis area AggLevel1AccountUsageStats within Prod OMNIA Partners EDW at 25-Mar-2025 12:10:54

Table Name

AggLevel1AccountUsageStats

Description

Customer usage statistics for Trailing 12 months, YTD, and lifetime. This aggregate table contains data about revenue, sales, rank, # of suppliers used, # of contracts used, # of categories used and differences of the above.

Purpose

The purpose of the this aggregate table is to display a wide variety of customer usage statistics at the top-of-the-hierarchy level for various snapshots in time.

Concept

Grain

The technical data grain for Fact.SalesAndRevenue is set at 1 row for each TransactionId, AccountKey, ContractKey, and SupplierKey combination, per ReceiptDateKey.

In business speak, each row represents the monthly sum of a member's purchase from a particular contract, for a given financial month (ReceiptDate).

Table Definition

Display NameColumn NameMeasureAttributeComments
Level1 Force IdLevel1ForceId  Force ID for the top-of-the-hierarchy
Display Account NameDisplayAccountName  is the same value as the AccountName column, but has been run through a ProperCase function
Account TypeAccountType  The type, or classification, of the account.
SectorSector  Sector is either public sector or private sector
AddressAddress  Line 1 street address for the account. Can be either a physical of mailing address.
Address2Address2  Line 2 street address for the account. Can be either a physical of mailing address.
CityCity  City where the account is located in.
CountyCounty  County where the account is located in.
StateState  State where the account is located in.
Zip CodeZipCode  The postal code of the account.
Start DateStartDate  Date that an account became a participating agency with National IPA.
Sales Territory KeySalesTerritoryKey  DataWarehouse Key to dimension Dim.SalesTerritory
Current 12 Months - Sum of SalesCurrent12Months_SumOfSales  Current 12 Months - Sum of Sales
Current 12 Months - Rank by SalesCurrent12Months_RankbySales  Current 12 Months - Rank by Sales
Current 12 Months - Sum Of RevenueCurrent12Months_SumOfRevenue  Current 12 Months - Sum Of Revenue
Current 12 Months - Rank by RevenueCurrent12Months_RankbyRevenue  Current 12 Months - Rank by Revenue
Current 12 Months - Number Of Suppliers UsedCurrent12Months_NumOfSuppliersUsed  Current 12 Months - Number Of Suppliers Used
Current 12 Months - Number Of Contracts UsedCurrent12Months_NumOfContractsUsed  Current 12 Months - Number Of Contracts Used
Current 12 Months - Number Of Contract Categories UsedCurrent12Months_NumOfContractCategoriesUsed  Current 12 Months - Number Of Contract Categories Used
Current 12 Months - Months Since Last TransactionCurrent12Months_MonthsSinceLastTransaction  Current 12 Months - Months Since Last Transaction
Current 12 Months - Most Recent ReceiptDateCurrent12Months_MostRecentReceiptDate  Current 12 Months - Most Recent ReceiptDate
Current 12 Months - Sales DifferenceCurrent12Months_SalesDiff  Current 12 Months - Sales Difference
Current 12 Months - Sales Difference PercentCurrent12Months_SalesDiffPerc  Current 12 Months - Sales Difference Percent
Current 12 Months - Revenue DifferenceCurrent12Months_RevenueDiff  Current 12 Months - Revenue Difference
Current 12 Months - Revenue Difference PercentCurrent12Months_RevenueDiffPerc  Current 12 Months - Revenue Difference Percent
Current 12 Months - Number Of Suppliers Used DifferenceCurrent12Months_NumOfSuppliersUsedDiff  Current 12 Months - Number Of Suppliers Used Difference
Current 12 Months - Number Of Contracts Used DifferenceCurrent12Months_NumOfContractsUsedDiff  Current 12 Months - Number Of Contracts Used Difference
Current 12 Months - Number Of Contract Categories Used DifferencCurrent12Months_NumOfContractCategoriesUsedDiff  Current 12 Months - Number Of Contract Categories Used Difference
Current 12 Months - Number Of Corporate United Contracts UsedCurrent12Months_NumOfContractsUsed_CorporateUnited  Current 12 Months - Number Of Corporate United Contracts Used
Current 12 Months - Number Of National IPA Contracts UsedCurrent12Months_NumOfContractsUsed_NationalIPA  Current 12 Months - Number Of National IPA Contracts Used
Current 12 Months - Number Of OMNIA Private Sector Contracts UseCurrent12Months_NumOfContractsUsed_OMNIAPrivateSector  Current 12 Months - Number Of OMNIA Private Sector Contracts Used
Current 12 Months - Number Of OMNIA Public Sector Contracts UsedCurrent12Months_NumOfContractsUsed_OMNIAPublicSector  Current 12 Months - Number Of OMNIA Public Sector Contracts Used
Current 12 Months - Number Of Prime Advantage Contracts UsedCurrent12Months_NumOfContractsUsed_PrimeAdvantage  Current 12 Months - Number Of Prime Advantage Contracts Used
Current 12 Months - Number Of TCPN Contracts UsedCurrent12Months_NumOfContractsUsed_TCPN  Current 12 Months - Number Of TCPN Contracts Used
Current 12 Months - Number Of US Communities Contracts UsedCurrent12Months_NumOfContractsUsed_USCommunities  Current 12 Months - Number Of US Communities Contracts Used
Current 12 Months - Number Of Vizient Contracts UsedCurrent12Months_NumOfContractsUsed_Vizient  Current 12 Months - Number Of Vizient Contracts Used
Previous 12 Months - Sum of SalesPrevious12Months_SumOfSales  Previous 12 Months - Sum of Sales
Previous 12 Months - Rank by SalesPrevious12Months_RankbySales  Previous 12 Months - Rank by Sales
Previous 12 Months - Sum Of RevenuePrevious12Months_SumOfRevenue  Previous 12 Months - Sum Of Revenue
Previous 12 Months - Rank by RevenuePrevious12Months_RankbyRevenue  Previous 12 Months - Rank by Revenue
Previous 12 Months - Number Of Suppliers UsedPrevious12Months_NumOfSuppliersUsed  Previous 12 Months - Number Of Suppliers Used
Previous 12 Months - Number Of Contracts UsedPrevious12Months_NumOfContractsUsed  Previous 12 Months - Number Of Contracts Used
Previous 12 Months - Number Of Contract Categories UsedPrevious12Months_NumOfContractCategoriesUsed  Previous 12 Months - Number Of Contract Categories Used
Previous 12 Months - Months Since Last TransactionPrevious12Months_MonthsSinceLastTransaction  Previous 12 Months - Months Since Last Transaction
Previous 12 Months - Most Recent ReceiptDatePrevious12Months_MostRecentReceiptDate  Previous 12 Months - Most Recent ReceiptDate
Previous 12 Months - Number Of Corporate United Contracts UsedPrevious12Months_NumOfContractsUsed_CorporateUnited  Previous 12 Months - Number Of Corporate United Contracts Used
Previous 12 Months - Number Of National IPA Contracts UsedPrevious12Months_NumOfContractsUsed_NationalIPA  Previous 12 Months - Number Of National IPA Contracts Used
Previous 12 Months - Number Of OMNIA Private Sector Contracts UsPrevious12Months_NumOfContractsUsed_OMNIAPrivateSector  Previous 12 Months - Number Of OMNIA Private Sector Contracts Used
Previous 12 Months - Number Of OMNIA Public Sector Contracts UsePrevious12Months_NumOfContractsUsed_OMNIAPublicSector  Previous 12 Months - Number Of OMNIA Public Sector Contracts Used
Previous 12 Months - Number Of Prime Advantage Contracts UsedPrevious12Months_NumOfContractsUsed_PrimeAdvantage  Previous 12 Months - Number Of Prime Advantage Contracts Used
Previous 12 Months - Number Of TCPN Contracts UsedPrevious12Months_NumOfContractsUsed_TCPN  Previous 12 Months - Number Of TCPN Contracts Used
Previous 12 Months - Number Of US Communities Contracts UsedPrevious12Months_NumOfContractsUsed_USCommunities  Previous 12 Months - Number Of US Communities Contracts Used
Previous 12 Months - Number Of Vizient Contracts UsedPrevious12Months_NumOfContractsUsed_Vizient  Previous 12 Months - Number Of Vizient Contracts Used
Current Year To Date - Sum of SalesCurrentYTD_SumOfSales  Current Year To Date - Sum of Sales
Current Year To Date - Rank by SalesCurrentYTD_RankbySales  Current Year To Date - Rank by Sales
Current Year To Date - Sum Of RevenueCurrentYTD_SumOfRevenue  Current Year To Date - Sum Of Revenue
Current Year To Date - Rank by RevenueCurrentYTD_RankbyRevenue  Current Year To Date - Rank by Revenue
Current Year To Date - Number Of Suppliers UsedCurrentYTD_NumOfSuppliersUsed  Current Year To Date - Number Of Suppliers Used
Current Year To Date - Number Of Contracts UsedCurrentYTD_NumOfContractsUsed  Current Year To Date - Number Of Contracts Used
Current Year To Date - Number Of Contract Categories UsedCurrentYTD_NumOfContractCategoriesUsed  Current Year To Date - Number Of Contract Categories Used
Current Year To Date - Months Since Last TransactionCurrentYTD_MonthsSinceLastTransaction  Current Year To Date - Months Since Last Transaction
Current Year To Date - Most Recent ReceiptDateCurrentYTD_MostRecentReceiptDate  Current Year To Date - Most Recent ReceiptDate
Current Year To Date - Sales DifferenceCurrentYTD_SalesDiff  Current Year To Date - Sales Difference
Current Year To Date - Sales Difference PercentCurrentYTD_SalesDiffPerc  Current Year To Date - Sales Difference Percent
Current Year To Date - Revenue DifferenceCurrentYTD_RevenueDiff  Current Year To Date - Revenue Difference
Current Year To Date - Revenue Difference PercentCurrentYTD_RevenueDiffPerc  Current Year To Date - Revenue Difference Percent
Current Year To Date - Number Of Suppliers Used DifferenceCurrentYTD_NumOfSuppliersUsedDiff  Current Year To Date - Number Of Suppliers Used Difference
Current Year To Date - Number Of Contracts Used DifferenceCurrentYTD_NumOfContractsUsedDiff  Current Year To Date - Number Of Contracts Used Difference
Current Year To Date - Number Of Contract Categories Used DifferCurrentYTD_NumOfContractCategoriesUsedDiff  Current Year To Date - Number Of Contract Categories Used Difference
Current Year To Date - Number Of Corporate United Contracts UsedCurrentYTD_NumOfContractsUsed_CorporateUnited  Current Year To Date - Number Of Corporate United Contracts Used
Current Year To Date - Number Of National IPA Contracts UsedCurrentYTD_NumOfContractsUsed_NationalIPA  Current Year To Date - Number Of National IPA Contracts Used
Current Year To Date - Number Of OMNIA Private Sector Contracts CurrentYTD_NumOfContractsUsed_OMNIAPrivateSector  Current Year To Date - Number Of OMNIA Private Sector Contracts
Current Year To Date - Number Of OMNIA Public Sector Contracts UCurrentYTD_NumOfContractsUsed_OMNIAPublicSector  Current Year To Date - Number Of OMNIA Public Sector Contracts Used
Current Year To Date - Number Of Prime Advantage Contracts UsedCurrentYTD_NumOfContractsUsed_PrimeAdvantage  Current Year To Date - Number Of Prime Advantage Contracts Used
Current Year To Date - Number Of TCPN Contracts UsedCurrentYTD_NumOfContractsUsed_TCPN  Current Year To Date - Number Of TCPN Contracts Used
Current Year To Date - Number Of US Communities Contracts UsedCurrentYTD_NumOfContractsUsed_USCommunities  Current Year To Date - Number Of US Communities Contracts Used
Current Year To Date - Number Of Vizient Contracts UsedCurrentYTD_NumOfContractsUsed_Vizient  Current Year To Date - Number Of Vizient Contracts Used
Previous Year To Date - Sum of SalesPreviousYTD_SumOfSales  Previous Year To Date - Sum of Sales
Previous Year To Date - Rank by SalesPreviousYTD_RankbySales  Previous Year To Date - Rank by Sales
Previous Year To Date - Sum Of RevenuePreviousYTD_SumOfRevenue  Previous Year To Date - Sum Of Revenue
Previous Year To Date - Rank by RevenuePreviousYTD_RankbyRevenue  Previous Year To Date - Rank by Revenue
Previous Year To Date - Number Of Suppliers UsedPreviousYTD_NumOfSuppliersUsed  Previous Year To Date - Number Of Suppliers Used
Previous Year To Date - Number Of Contracts UsedPreviousYTD_NumOfContractsUsed  Previous Year To Date - Number Of Contracts Used
Previous Year To Date - Number Of Contract Categories UsedPreviousYTD_NumOfContractCategoriesUsed  Previous Year To Date - Number Of Contract Categories Used
Previous Year To Date - Months Since Last TransactionPreviousYTD_MonthsSinceLastTransaction  Previous Year To Date - Months Since Last Transaction
Previous Year To Date - Most Recent ReceiptDatePreviousYTD_MostRecentReceiptDate  Previous Year To Date - Most Recent ReceiptDate
Previous Year To Date - Number Of Corporate United Contracts UsePreviousYTD_NumOfContractsUsed_CorporateUnited  Previous Year To Date - Number Of Corporate United Contracts Used
Previous Year To Date - Number Of National IPA Contracts UsedPreviousYTD_NumOfContractsUsed_NationalIPA  Previous Year To Date - Number Of National IPA Contracts Used
Previous Year To Date - Number Of OMNIA Private Sector ContractsPreviousYTD_NumOfContractsUsed_OMNIAPrivateSector  Previous Year To Date - Number Of OMNIA Private Sector Contracts
Previous Year To Date - Number Of OMNIA Public Sector Contracts PreviousYTD_NumOfContractsUsed_OMNIAPublicSector  Previous Year To Date - Number Of OMNIA Public Sector Contracts
Previous Year To Date - Number Of Prime Advantage Contracts UsedPreviousYTD_NumOfContractsUsed_PrimeAdvantage  Previous Year To Date - Number Of Prime Advantage Contracts Used
Previous Year To Date - Number Of TCPN Contracts UsedPreviousYTD_NumOfContractsUsed_TCPN  Previous Year To Date - Number Of TCPN Contracts Used
Previous Year To Date - Number Of US Communities Contracts UsedPreviousYTD_NumOfContractsUsed_USCommunities  Previous Year To Date - Number Of US Communities Contracts Used
Previous Year To Date - Number Of Vizient Contracts UsedPreviousYTD_NumOfContractsUsed_Vizient  Previous Year To Date - Number Of Vizient Contracts Used
Lifetime - Sum of SalesLifetime_SumOfSales  Lifetime - Sum of Sales
Lifetime - Rank by SalesLifetime_RankbySales  Lifetime - Rank by Sales
Lifetime - Sum Of RevenueLifetime_SumOfRevenue  Lifetime - Sum Of Revenue
Lifetime - Rank by RevenueLifetime_RankbyRevenue  Lifetime - Rank by Revenue
Lifetime - Number Of Suppliers UsedLifetime_NumOfSuppliersUsed  Lifetime - Number Of Suppliers Used
Lifetime - Number Of Contracts UsedLifetime_NumOfContractsUsed  Lifetime - Number Of Contracts Used
Lifetime - Number Of Contract Categories UsedLifetime_NumOfContractCategoriesUsed  Lifetime - Number Of Contract Categories Used
Lifetime - Months Since Last TransactionLifetime_MonthsSinceLastTransaction  Lifetime - Months Since Last Transaction
Lifetime - Most Recent ReceiptDateLifetime_MostRecentReceiptDate  Lifetime - Most Recent ReceiptDate
Lifetime - Number Of Corporate United Contracts UsedLifetime_NumOfContractsUsed_CorporateUnited  Lifetime - Number Of Corporate United Contracts Used
Lifetime - Number Of National IPA Contracts UsedLifetime_NumOfContractsUsed_NationalIPA  Lifetime - Number Of National IPA Contracts Used
Lifetime - Number Of OMNIA Private Sector ContractsLifetime_NumOfContractsUsed_OMNIAPrivateSector  Lifetime - Number Of OMNIA Private Sector Contracts
Lifetime - Number Of OMNIA Public Sector Contracts Lifetime_NumOfContractsUsed_OMNIAPublicSector  Lifetime - Number Of OMNIA Public Sector Contracts
Lifetime - Number Of Prime Advantage Contracts UsedLifetime_NumOfContractsUsed_PrimeAdvantage  Lifetime - Number Of Prime Advantage Contracts Used
Lifetime - Number Of TCPN Contracts UsedLifetime_NumOfContractsUsed_TCPN  Lifetime - Number Of TCPN Contracts Used
Lifetime - Number Of US Communities Contracts UsedLifetime_NumOfContractsUsed_USCommunities  Lifetime - Number Of US Communities Contracts Used
Lifetime - Number Of Vizient Contracts UsedLifetime_NumOfContractsUsed_Vizient  Lifetime - Number Of Vizient Contracts Used
Max Receipt DateMaxReceiptDate  Max Receipt Date
DSSCreateTimeDSSCreateTime  Date and time the row was created in the data warehouse.
DSSUpdateTimeDSSUpdateTime  Date and time the row was updated in the data warehouse.

Column Documentation

No documentation found

Schema

Dimensions

The Dimensions associated with the table AggLevel1AccountUsageStats are :
No Dimensions found for this object.

Examples

Usage

Use OrganizationId (or OrganizationKey) to filter sales by different OMNIA Partners sales organizations.

The column MaxReceiptDate will be the same for all rows. It represents what the most recently-closed financial period.

Return to Top