HyperConnect Usage Fact Table
Fact table of daily HyperConnect billable usage, one row per organization, center, product, and day. Several products can have a row for the same center on the same day. A day's usage can be recalculated and delivered again with the latest value. Rows appear only for organizations that use HyperConnect. SmartBot rows here are usage quantities only; SmartBot conversation outcomes are in bi_facthcvoicecallmetrics. HyperConnect sends this data once a day, covering the previous day. When you load the S3 files into your own warehouse, update rows by hc_row_key (org_center_id_product and the usage date); export_run_id identifies the delivery and is not part of the key. Do not delete rows that are missing from a later delivery.
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| facthchyperconnectusageswid | BIGINT IDENTITY | NOT NULL | Surrogate warehouse key for the usage record. |
| org_name | VARCHAR(256) | NULL | Organization display name when the record was exported. |
| centerwid | BIGINT | NULL | Warehouse key referencing the center dimension. |
| center_name | VARCHAR(256) | NULL | Center display name when the record was exported. |
| org_center_id_product | VARCHAR(256) | NOT NULL | Combined organization ID, center ID, and product key that identifies the usage series. |
| usage_date | DATE | NOT NULL | Calendar day the usage covers. |
| product | VARCHAR(64) | NULL | Billable product key. See Enumerator Values. |
| product_display_name | VARCHAR(256) | NULL | Display name of the product from the rate plan. |
| usage | FLOAT | NULL | Quantity used on that day, in the unit given by uom. |
| credit_value | FLOAT | NULL | Per-unit credit value from the plan when the usage was recorded. |
| uom | VARCHAR(64) | NULL | Unit of measure (for example, Mins, SMS, Message). |
| subscription_number | VARCHAR(128) | NULL | Billing subscription number, when known. |
| subscription_name | VARCHAR(256) | NULL | Subscription display name; can be empty. |
| last_updated_timestamp | TIMESTAMP | NULL | When this usage record was last written. |
| usage_metadata | VARCHAR(65535) | NULL | Product-specific usage breakdown as JSON text; often {}. |
| component_api_response | VARCHAR(65535) | NULL | Component counts used to calculate usage, as JSON text; often {}. The counts are also delivered in the component_* columns. |
| component_incoming_count | BIGINT | NULL | Incoming message count, from the usage components. |
| component_total_segments | BIGINT | NULL | Total message segments, from the usage components. |
| component_twoway_count | BIGINT | NULL | Two-way message count, from the usage components. |
| component_total_sent_count | BIGINT | NULL | Total messages sent, from the usage components. |
| component_date | DATE | NULL | Date reported in the usage components. |
| component_inbound_segments | BIGINT | NULL | Inbound message segments, from the usage components. |
| component_mms_count | BIGINT | NULL | MMS message count, from the usage components. |
| component_marketing_count | BIGINT | NULL | Marketing message count, from the usage components. |
| component_outbound_segments | BIGINT | NULL | Outbound message segments, from the usage components. |
| component_notification_count | BIGINT | NULL | Notification message count, from the usage components. |
| hc_row_key | VARCHAR(512) | NOT NULL | Unique key of the row: org_center_id_product and the usage date combined. |
| void | BOOLEAN | NULL | Soft-delete flag. Always FALSE for this table. |
| export_run_id | VARCHAR(64) | NULL | Identifier of the HyperConnect export run that delivered the row: its UTC date and time as YYYYMMDDHHMMSS. |
Note: All fact tables also include standard ETL metadata columns (
podname,organizationid,groupid,etlCreatedDate) which are omitted from this reference for brevity.
Primary Key
facthchyperconnectusageswid
Foreign Keys
| Column | References |
|---|---|
| centerwid | Bi_DimCenter.centerwid |
Enumerator Values
| Column | Values |
|---|---|
| product | Billable product keys, for example voice, air (AI Receptionist), connect, connect-whatsapp, smartbot, voice-international |
| void | Always FALSE (usage rows are not deleted at the source) |