Package liability fact table
This table lists the fact table columns used for package liability data model.
Bi_FactPackageUserRedemptions
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
|---|---|---|---|---|---|
ApprovalNumber | VARCHAR(256) | Displays the Approval number associated to a payment. | |||
Centerwid | bigint | A unique identifier for the center, generated in Centre dimension(Bi_DimCentre). | Bi_DimCenter centerwid | ||
ClassCategoryCode | varchar(64) | Tells you the class category code | |||
ClassCategoryName | varchar(512) | Tells you the class category name | |||
classwid | bigint | A unique identifier for class level details.(Bi_DimTrainingClass) | Bi_DimTrainingClass classwid | ||
ClassParentCategoryCode | varchar(64) | Tells you the class parent category code | |||
ClassParentCategoryName | varchar(512) | Tells you the class parent category name | |||
ClassTagname | varchar(510) | Tells you the class tag name | |||
ClosedBywid | bigint | A unique identifier of the employee who closed the invoice(bi_dimuser). | Bi_Dimuser userwid | ||
ClosedDateInCenter | DATE | The date on which the invoice was closed. | |||
ClosedDateTimeInCenter | TIMESTAMP | The date and time at which the invoice was closed. | |||
CreatedBywid | bigint | A unique identifier of the employee who created the invoice(bi_dimuser). | Bi_Dimuser userwid | ||
CreatedDateInCenter | DATE | The date on which the invoice item was created. | |||
CreatedDateTimeInCenter | TIMESTAMP | The date and time at which the invoice item was created. | |||
CreditCardLastFourDigits | NVARCHAR(32) | Displays the last four digits of the credit card used for a payment. | |||
EtlCreatedBy | bigint | ETL system admin user who inserted or updated the corresponding record in this table. | |||
EtlCreatedDate | TIMESTAMP | Latest timestamp at which the corresponding record was either inserted or updated in this table. | |||
FactSegmentId | INT | A unique identifier for each record generated in the fact table. | Yes | ||
FinalSalePrice | FLOAT | Displays the final sale price of a package. The price is inclusive of discounts and taxes. | |||
Invoice_No | VARCHAR(100) | Displays the invoice number associated to sale, refund, or redemption transaction. | |||
InvoiceId | VARCHAR(36) | Displays the invoice id associated to sale, refund, or redemption transaction. | |||
InvoiceItemId | VARCHAR(36) | Displays invoice item id associated to sale, refund, or redemption transaction. | |||
InvoiceStatus | smallint | This indicates whether the invoice is open or closed. | |||
ItemDiscount | FLOAT | Displays the discount of item based on package level discount. Discount of package is prorated based on "package item price" field. This it done to get the contribution of item at package level. | |||
ItemFinalSalePrice | FLOAT | Displays final sale price of package items. Final sale price of package is prorated based on "package item price" field. This is done to get the contribution of an item at the package level. | |||
Itemwid | bigint | A unique identifier for the Item(Bi_DimItems). | Bi_DimItems itemwid | ||
ItemListPrice | FLOAT | For Custom Packages - Displays the final sale price of an item in the package. This can be a service or product. Does not include invoice level discount. For Series Packages - Displays the sale price of an item at the center level. This can be a service or product. Does not include invoice level discount. Note: In the case of category or tag, this field displays the average price of the corresponding category or tag. | |||
ItemPaymentAmount | FLOAT | Displays amount paid for package at item level based on Final Sale price of an item. | |||
ItemRedemptionCenterwid | bigint | The Centre unique identifier where the item was redeemed.(Bi_DimCenter) | Bi_DimCenter centerwid | ||
ItemSaleById | VARCHAR(36) | This is the unique identifier of the employee who sold the item in the package to the guest. | Bi_Dimuser userwid | ||
ItemSaleCenterwid | bigint | A unique identifier of the centre where the item sale happened(Bi_DimCentre). | Bi_DimCenter centerwid | ||
ItemSalePrice | FLOAT | Displays sale price of package items. Sale price of package is prorated based on "package item price" field. This is done to get the contribution of an item at the package level. | |||
ItemTypewid | bigint | A unique identifier for the Item Type(Bi_DimItemType). | Bi_DimItemType itemtypewid | ||
MaximumRedemptionDate | DATE | Displays the last time when any item of this package was redeemed. | |||
MinimumRedemptionDate | DATE | Displays the first time when any item of the package was redeemed. | |||
OrganizationId | VARCHAR(36) | A unique identifier for the organition for which this data belongs | Bi_DimCenter OrganizationId | ||
PackageItemPrice | FLOAT | Displays the final sale price of an item in package (can be service or product). In case of Category or Tag association, the package item price is the average value of Item Final Sale Price of all the Products/Services under that Category or Tag. | |||
PackagePaymentAmount | FLOAT | Displays the total payment made for a package. | |||
PackageUserwid | bigint | A unique identifier generated for package sold.(Bi_DimPackageUser) | Bi_DimPackageUser PackageUserwid | ||
PackageVersionwid | bigint | A unique identifier for package version(Bi_DimPackage). | Bi_DimPackager PackageVersionwid | ||
PaymentCollectionCenterwid | bigint | The Centre unique identifier where the payment was collected.(Bi_DimCenter) | Bi_DimCenter Centerwid | ||
PaymentDateInCenter | DATE | Displays centre date on which payment is triggered for the purchase of a package. | |||
PaymentDateTimeInCenter | TIMESTAMP | Displays the centre date and time at which payment is triggered for the purchase of a package. | |||
PaymentModeNumber | VARCHAR(36) | Indicates the mode of payment. For gift card or prepaid card payments, this populates the 'giftcarduserid'. For membership payments, this populates 'usermembershipid'. For package payments, this populates 'packageuserid'. | Bi_DimAgent Agentwid | ||
PaymentOptionType | VARCHAR(100) | Displays payment method used. Example: For a card used for payment, it will tell if card used is Master or VISA. | |||
PaymentType | varchar(100) | The mode of payment(Bi_DimStatus). | Bi_DimStatus StatusTypeCode | ||
PODID | integer | Internal to the ETL, says from which POD data is being synced the table | |||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
ProductCategoryCode | VARCHAR(64) | The code corresponding to the product category. | |||
ProductCategoryName | VARCHAR(512) | Displays the category to which the product belongs. Example: Lipstick | |||
Productwid | bigint | A unique identifier for a product, generated in Product dimension(Bi_DimProduct). | Bi_DimProduct productwid | ||
ProductParentCategoryCode | VARCHAR(64) | The code corresponding to the parent product category. | |||
ProductParentCategoryName | VARCHAR(512) | Displays the parent category to which the product belongs. Example:Cosmetics | |||
ProductTagName | VARCHAR(512) | Displays the product tags for products that are part of the package. | |||
Quantity | INT | Displays the total quantity of items (Product/Service) provided with a package. | |||
QuantityRedeemed | INT | Displays the total quantity of items (Product/Service) redeemed from a package. | |||
RedeemedInvoiceItemId | VARCHAR(36) | Displays Invoice item id associated with redemption transaction. | |||
RedemptionAmount | FLOAT | Displays the total amount redeemed for a package item of total quantitiy redeemed. | |||
RedemptionDate | TIMESTAMP | Displays the date on which package item was redeemed. | |||
RefundInvoiceID | VARCHAR(36) | Displays Invoice id associated with refund transaction. | |||
RefundInvoiceItemID | VARCHAR(36) | Displays Invoice item id associated with refund transaction. | |||
resourcewid | bigint | A unique idenitifer for the room information that is used as part the service, generated in resource dimension(Bi_DimResource) | Bi_DimResource Resourcewid | ||
SaleBywid | bigint | A unique identifier of the employee who made the sale(bi_dimuser). | Bi_Dimuser userwid | ||
SaleDateInCenter | DATE | For Service item type, this is the date and time at which the service was performed. For other item types, this is the date and time at which the item was added to the invoice. | |||
SaleDateTimeInCenter | TIMESTAMP | For Service item type, this is the date and time at which the service was performed. For other item types, this is the date and time at which the item was added to the invoice. | |||
SalePrice | FLOAT | Displays the sale price of a package. | |||
ServiceCategoryCode | VARCHAR(64) | The code corresponding to the service category. | |||
ServiceCategoryName | VARCHAR(512) | Displays the category to which the service belongs. Example: Modified Brazilian | |||
Servicewi | bigint | A unique identifier for a Service, generated in Service dimension(Bi_DimService). | Bi_DimService Servicewid | ||
ServiceParentCategoryCode | VARCHAR(64) | The code corresponding to the parent service category. | |||
ServiceParentCategoryName | VARCHAR(512) | Displays the parent category to which the service belongs. | |||
ServiceTagname | VARCHAR(510) | Displays the associated service tags. | |||
Therapistwid | bigint | Unique identifier for each employee who is scheduled to service the guest. This is generated in analytics and it does not exist in core.(Bi_DimUser) | Bi_Dimuser userwid | ||
TotalRefundValue | FLOAT | The total amount refunded through the refund transaction in invoice. | |||
TransactionId | VARCHAR(36) | Displays unique GUID for a transaction. | |||
unitItemDiscount | VARCHAR(64) | Displays discount of item (Service/ Product). Note: This field is populated only for Custom packages. | |||
UnitItemType | INT | Displays if it is a Product, Product Category, or Product Tag. Or, whether it is a Service, Service Category, or a Service Tag. This will help you to understand the product/service dimension attributes of a package. | |||
userwid | bigint | A unique identifier for the guest, generated in User dimension(Bi_DimUser). | Bi_Dimuser userwid | ||
Void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
groupid | Internal to Zenoti. Used for data processing. | BIGINT | |||
benefitrefundqty | INTEGER | Indicates the quantity (#) of package refunded. | |||