Membership user redemptions value fact table (s3)
Bi_FactMembershipUserRedemptionsValue_s3
Fact table recording the financial and redemption details of guest membership benefits — including quantities redeemed, credit balances, revenue-recognition values (IRR/MRR/SCR), liability, and refund tracking at the benefit-item level.
Field | Field type | Description | Primary Key | Mapped Dimension table | Mapped Dimension field |
|---|---|---|---|---|---|
FactSegmentId | BIGINT | Unique segment identifier for the record | PK | ||
CenterId | VARCHAR(36) | Unique identifier of the center | Bi_DimCenter | ||
UserId | VARCHAR(36) | Unique identifier of the guest/user | Bi_DimUser | ||
SaleById | VARCHAR(36) | Employee who made the sale | Bi_DimUser | ||
ClosedBy | VARCHAR(36) | User who closed the invoice | |||
CreatedBy | VARCHAR(36) | User who created the record | |||
OrganizationID | VARCHAR(36) | Unique identifier of the organization | |||
ItemType | INT | Type code of the item | |||
Itemid | VARCHAR(36) | Identifier of the item | |||
MembershipValueRecordType | INT | Record type code indicating the nature of the transaction | |||
MembershipVersionId | VARCHAR(36) | Version identifier of the membership | |||
UserMembershipId | VARCHAR(36) | Unique identifier of the user membership | |||
MembershipStartDate | DATE | Start date of the membership | |||
MembershipEndDate | DATE | End date of the membership | |||
MembershipCancellationDate | DATE | Date the membership was cancelled | |||
GracePeriod | DATE | Grace period end date | |||
MembershipType | INT | Type code of the membership (recurring, non-recurring) | |||
ServiceId | VARCHAR(36) | Identifier of the service | |||
CategoryId | VARCHAR(36) | Identifier of the service category | |||
ProductId | VARCHAR(36) | Identifier of the product | |||
Quantity | FLOAT | Total quantity of benefits available | |||
QuantityRedeemed | FLOAT | Quantity of benefits redeemed | |||
SalePrice | FLOAT | Sale price of the membership | |||
Discount | FLOAT | Discount amount applied | |||
Tax | FLOAT | Tax amount applied | |||
FinalSalePrice | FLOAT | Final sale price after discount and tax | |||
BenefitSaleLiability | FLOAT | Liability amount from the benefit sale | |||
MembershipIRR | FLOAT | Membership initially recognized revenue | |||
MembershipMRR | FLOAT | Membership monthly recognized revenue | |||
MembershipSCR | FLOAT | Membership service credit recognized revenue | |||
MembershipIRRWithOrder | FLOAT | Membership initially recognized revenue with order | |||
MembershipMRRWithOrder | FLOAT | Membership monthly recognized revenue with order | |||
MembershipSCRWithOrder | FLOAT | Membership service credit recognized revenue with order | |||
BenefitListPrice | FLOAT | List price of the benefit item | |||
ItemLevelApportionFactor | FLOAT | Apportion factor used for item-level revenue allocation | |||
MemberServicePrice | FLOAT | Price of the membership service | |||
RedemptionAmount | FLOAT | Monetary amount of the redemption | |||
ConvertedCreditAmount | FLOAT | Credit amount converted during benefit usage | |||
RemainingCreditAmount | FLOAT | Remaining credit amount after conversion | |||
ServiceRedemptionValuePerCredit | FLOAT | Service redemption value per unit of credit | |||
TotalServiceRedemptionValue | FLOAT | Total service redemption value | |||
RedemptionValueWithoutRecognizedRevenuePerCredit | FLOAT | Redemption value excluding recognized revenue per credit | |||
TotalRedemptionValueWithoutRecognizedRevenue | FLOAT | Total redemption value excluding recognized revenue | |||
ConvertedServiceRedemptionValue | FLOAT | Converted service redemption value | |||
RemainingServiceRedemptionValue | FLOAT | Remaining service redemption value after usage | |||
Suspense | FLOAT | Suspense (deferred revenue) amount | |||
IsCreditAmount | BOOLEAN | Flag indicating if this is a credit amount record | |||
CreditBenefitPK | BIGINT | Primary key of the credit benefit record | |||
CreditAmount | FLOAT | Total credit amount | |||
ServiceCreditAmount | FLOAT | Service credit amount included in the membership | |||
ProductCreditAmount | FLOAT | Product credit amount included in the membership | |||
OtherCreditAmount | FLOAT | Other credit amount included in the membership | |||
AdjustedCreditValue | FLOAT | Adjusted total credit value | |||
AdjustedServiceCreditValue | FLOAT | Adjusted service credit value | |||
AdjustedProductCreditValue | FLOAT | Adjusted product credit value | |||
AdjustedOtherCreditValue | FLOAT | Adjusted other credit value | |||
BalanceCreditAmount | FLOAT | Remaining balance of total credit | |||
BalanceServiceCreditAmount | FLOAT | Remaining balance of service credit | |||
BalanceProductCreditAmount | FLOAT | Remaining balance of product credit | |||
BalanceOtherCreditAmount | FLOAT | Remaining balance of other credit | |||
RefundedCreditAmount | FLOAT | Total credit amount refunded | |||
RefundedServiceCreditAmount | FLOAT | Service credit amount refunded | |||
RefundedProductCreditAmount | FLOAT | Product credit amount refunded | |||
RefundedOtherCreditAmount | FLOAT | Other credit amount refunded | |||
SaleDateTimeInCenter | TIMESTAMP | Sale date and time in center local timezone | |||
SaleDateInCenter | DATE | Sale date in center local timezone | Bi_DimDate | ||
ClosedDateTimeInCenter | TIMESTAMP | Invoice closed date and time in center local timezone | |||
ClosedDateInCenter | DATE | Invoice closed date in center local timezone | Bi_DimDate | ||
CreatedDateTimeInCenter | TIMESTAMP | Record created date and time in center local timezone | |||
CreatedDateInCenter | DATE | Record created date in center local timezone | Bi_DimDate | ||
RedemptionDateTime | TIMESTAMP | Date and time of the benefit redemption | |||
RedeemedInvoiceItemId | VARCHAR(36) | GUID of the redeemed invoice item | |||
AccruedDate | DATE | Date when revenue was accrued | |||
BenefitExpiryDate | DATE | Date when the benefit expires | |||
InvoiceId | VARCHAR(36) | Unique identifier of the invoice | |||
InvoiceItemId | VARCHAR(36) | Unique identifier of the invoice item | |||
InvoiceStatus | INT | Status code of the invoice | |||
GroupInvoiceId | VARCHAR(36) | Group invoice identifier | |||
GroupInvoiceOrder | INT | Order sequence within the group invoice | |||
Invoice_No | NVARCHAR(320) | Human-readable invoice number | |||
Receipt_No | NVARCHAR(320) | Human-readable receipt number | |||
RefundInvoiceID | VARCHAR(36) | Refund invoice identifier | |||
RefundInvoiceItemID | VARCHAR(36) | Refund invoice item identifier | |||
TotalRefundValue | FLOAT | Total monetary value of the refund | |||
TotalRefundQuantity | FLOAT | Total quantity refunded | |||
ItemSaleCenterID | VARCHAR(36) | Center where the item was sold | Bi_DimCenter | ||
ItemRedemptionCenterID | VARCHAR(36) | Center where the item was redeemed | Bi_DimCenter | ||
UserBenefitPK | BIGINT | Primary key of the user benefit record | |||
BenefitConversionPK | BIGINT | Primary key for the benefit conversion record | |||
BenefitServiceName | NVARCHAR(2048) | Name of the benefit service | |||
BenefitCategoryName | NVARCHAR(2048) | Name of the benefit category | |||
BenefitServiceCode | NVARCHAR(2048) | Code of the benefit service | |||
BenefitCategoryCode | NVARCHAR(2048) | Code of the benefit category | |||
RedeemedUserId | VARCHAR(36) | User who redeemed the benefit | Bi_DimUser | ||
RedeemedUserType | INT | Type code of the redeemed user | |||
RedeemPostExpiry | BOOLEAN | Whether redemption is allowed post-expiry | |||
IsManualUpdate | BOOLEAN | Flag indicating if the record was manually updated | |||
EtlCreatedBy | BIGINT | ID of the ETL process/user that created this record | |||
EtlCreatedDate | TIMESTAMP | Timestamp when the ETL process created this record | |||
PodName | VARCHAR(20) | Name of the pod (database partition) | |||
Void | BOOLEAN | Indicates whether the record has been voided | |||
groupid | BIGINT | Identifier for the organizational group |