Employee commission fact table
Bi_FactEmployeeCommission_s3
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
AppointmentStatus | SMALLINT | A unique identifier that signifies the status of the Appointment, generated in status dimension(Bi_DimStatus) | Bi_DimStatus_s3 StatusTypeCode | ||
BonusCommission | DECIMAL(19,4) | Any additional commission bonus earned by the employee such as amount earned for being a good performer. | |||
CancellationCommission | DECIMAL(19,4) | The commission earned by the employee as a result of cancellation revenue. | |||
CancellationRevenue | DECIMAL(19,4) | Guests may need to pay a cancellation fee when they cancel their appointments. This is called cancellation revenue. This field populates the cancellation fee applicable to the corresponding item. | |||
centerid | VARCHAR(36) | A unique identifier for the center, generated in Centre dimension(Bi_DimCentre). | Bi_DimCenter_s3 centerID | ||
ClassBonus | DECIMAL(19,4) | The bonus earned by the employee when the required number of people attend a class. | |||
ClassCommission | DECIMAL(19,4) | The commission earned by an employee as a result of selling a class. | |||
Commission | DECIMAL(19,4) | The commission amount earned by the employee for the corresponding item. For refunded items, this can be a negative value. This is calculated against 'item sale revenue' and already has the commission factor calculated in. | |||
CommissionFactor | FLOAT | The commission factor or adjustment applicable. This can be configured for each service, product, package, etc. For example, if this is 200%- the employee earns double the configured commission. If this is 50%- the employee earns half the configured commission. | |||
ConsiderForPayroll | BOOLEAN | Indicates whether the commissions earned in the record should be considered for payroll. | |||
Deduction | DECIMAL(19,4) | While commissions are awarded to employees for items they sell or services they perform, businesses may also want to apply deductions for costs they incur. Deductions can include costs such as advertising, rent, and other internal costs. This field populates the deduction applied to the corresponding item. | |||
EF | DECIMAL(19,4) | Do not use this column. Deprecated | |||
EmployeeId | VARCHAR(36) | A unique identifier for an employee(Bi_Dimuser). | Bi_DimUser_s3 userid | ||
etlCreatedBy | DECIMAL(19,0) | 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. | |||
factEmployeeCommissionId | DECIMAL(19,0) | A unique identifier(integer value) for each record generated in the fact table. | Yes | ||
FinalSalePrice | DECIMAL(19,4) | The price at which the item was sold. The price is inclusive of discounts and taxes. | |||
FreeServiceCommission | DECIMAL(19,4) | The commission earned by the employee as a result of free service revenue. This is calculated against 'free service revenue' and already has the commission factor calculated in. | |||
FreeServiceRevenue | DECIMAL(19,4) | The revenue associated with the employee that accounts for free service revenue. | |||
InvoiceCenterID | VARCHAR(36) | The unique identifier of the centre where the invoice was generated.(Bi_Dimcenter) | Bi_DimCenter_s3 centerID | ||
InvoiceCloseDate | DATE | The date on which the invoice was closed.This value is not populated if the invoice is not closed. | |||
InvoiceId | VARCHAR(36) | A unique identifier for the invoice. It is unique within the organization. | |||
InvoiceItemId | VARCHAR(36) | Unique identifier for each invoice item within the invoice. | |||
InvoicelevelDeduction | DECIMAL(19,4) | Commission deductions can be set up on each invoice from which the employee earns commission. This field displays the amount that is to be deducted from the employee's commission for the corresponding invoice. If there are multiple items in the invoice, this is prorated against each item. | |||
InvoiceNo | VARCHAR(320) | Unique identifier for the invoice (unique within the center). This is the invoice associated with the corresponding appointment. | |||
IsAddOn | BOOLEAN | A flag indicating whether the service is an add-on. - True indicates that the service is an add-on - False indicates that the service is not an add-on | |||
IsTherapistRequested | BOOLEAN | Indicates whether the guest requested for a particular employee to perform the service. Possible values: - True: Employee was requested - False: Employee was not requested | |||
ItemID | NVARCHAR(MAX) | A unique identifier for the Item(Bi_DimItems). | Bi_DimItems_S3 itemid | ||
ItemSaleRevenue | DECIMAL(19,4) | The revenue associated with the employee for performing services and selling products, memberships, packages, and giftcards. This revenue already takes into account 'deduction'. It represents the revenue on which the employee commission is calculated. | |||
ItemType | BIGINT | A unique identifier for the Item Type(Bi_DimItemType). | Bi_DimItemType_S3 itemtypeid | ||
NoShowCommission | DECIMAL(19,4) | The commission earned by the employee as a result of no show revenue. | |||
NoShowRevenue | DECIMAL(19,4) | Guests may need to pay a no-show fee when they do not show up for their appointments. This is called no-show revenue. This field populates the no-show fee applicable to the corresponding item. | |||
organizationid | VARCHAR(36) | A unique identifier for the organition for which this data belongs | Bi_DimCenter_s3 OrganizationId | ||
PaymentDateInCenter | DATE | The date on which the payment was made. | |||
PayPeriodName | VARCHAR(2048) | The name of the payroll generation period. This column is replaced with "PayPeriodName_New". For old customers, pay period name is still populated in this column | |||
PayPeriodName_New | VARCHAR(2048) | The name of the payroll generation period. For all new customers, payperiod name is populated in this table | |||
PayrollEndDate | DATE | The end date of the payroll period. | |||
PayrollStartDate | DATE | The start date of the payroll period. | |||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
RedoAppointmentAmount | DECIMAL(19,4) | The amount earned by the employee for redo of an appointment | |||
RefundFromItemType | SMALLINT | A unique identifier for the referenced Item Type.(Bi_DimItemType) | Bi_DimItemType_S3 itemtypeid | ||
RequestTherapistBonus | DECIMAL(19,4) | The bonus earned by the employee as a result of the corresponding guest requesting for that particular employee to perform the service. | |||
SalePrice | DECIMAL(19,4) | List price of the item. | |||
ServiceDate | DATE | The date on which the service was performed. | |||
ServiceDeduction | DECIMAL(19,4) | Commission deductions can be set up on each service from which the employee earns commission. This field displays the amount that is to be deducted from the employee's commission for the corresponding service. | |||
ServiceID | NVARCHAR(MAX) | A unique identifier for a Service, generated in Service dimension(Bi_DimService). | Bi_DimService_s3 ServiceID | ||
SplitCommissionPercentage | FLOAT | Split commission percentage to which the employee is entitled. | |||
SSG | DECIMAL(19,4) | Support staff Gratuity earned by the employee | |||
Tip | DECIMAL(19,4) | The tip amount earned by the employee against the corresponding invoice. This is populated in a separate row against the invoice id, since this cannot be split out for each item in the invoice. Note that tips are populated only for employees who earned some commission in the corresponding pay period. | |||
UserId | VARCHAR(36) | A unique identifier for the guest, generated in User dimension(Bi_DimUser). | Bi_DimUser_s3 userid | ||
void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
groupid | Internal to Zenoti. Used for data processing. | BIGINT | |||