| ## Table: `dim_agreement` | |
| ### Schema: `public` | |
| ### All Columns and their Descriptions: | |
| - `agreement_id` (text): Unique identifier for the agreement. This is likely the primary key. | |
| - `customer_id` (text): Identifier for the customer associated with the agreement. This is likely a foreign key referencing a customer table. | |
| - `agreement_type` (text): The type of agreement (e.g., Service Level Agreement, Sales Agreement). | |
| - `agreement_name` (text): The name or title of the agreement. | |
| - `agreement_status` (text): The current status of the agreement (e.g., Active, Inactive, Expired). | |
| - `description` (text): A textual description of the agreement. | |
| - `document_number` (text): The document number associated with the agreement. | |
| - `statement_of_intent` (text): Indicates whether a statement of intent is associated with the agreement. | |
| - `version` (text): The version number of the agreement. | |
| - `initial_date` (timestamp without time zone): The date the agreement was initially created. | |
| - `agreement_period_start_date` (timestamp without time zone): The start date of the agreement period. | |
| - `agreement_period_end_date` (timestamp without time zone): The end date of the agreement period. | |
| - `completion_date_start_date` (timestamp without time zone): The start date of the completion date. | |
| - `completion_date_end_date` (text): The end date of the completion date. | |
| - `auto_renewal` (text): Indicates whether the agreement has auto-renewal enabled. | |
| - `commitment_unit` (text): The unit for the commitment amount (e.g., USD, hours). | |
| - `rating_type` (text): The type of rating associated with the agreement. | |
| - `commitment_amount` (text): The committed amount associated with the agreement. | |
| - `remaining_payment_cycle` (text): The remaining payment cycle. | |
| - `termination_date` (text): The date the agreement was terminated. | |
| ### Relationships with Other Tables (Foreign Keys): | |
| - `customer_id` (FK) references a `customer` table (likely `dim_customer` or similar) on the `customer_id` column. This links the agreement to a specific customer. | |
| ### Cardinality of Relationships: | |
| - Relationship with `dim_customer` (or similar) is one-to-many. One customer can have multiple agreements. | |
| ### Common Use Cases/Example Queries: | |
| - Describe all active agreements for a specific customer. | |
| - Analyze agreements by type or status. | |
| - Track agreement start and end dates. | |
| - Example SQL Snippet: `SELECT * FROM dim_agreement WHERE customer_id = 'customer_id_value' AND agreement_status = 'Active';` | |
| ### Data Constraints and Business Rules: | |
| - `agreement_id` is likely unique and a primary key. | |
| - `agreement_status` might have a controlled vocabulary (e.g., Active, Inactive). | |
| - Date fields should be validated for data integrity. | |
| - Business rules may dictate agreement approval processes or renewal cycles. | |
| ### Data Update Frequency/Volatility: | |
| - Data is likely updated when agreements are created, modified, or terminated. | |
| - Update frequency would depend on the business processes. | |
| ### Potential Pitfalls/Things to Avoid When Querying: | |
| - Ensure correct date ranges when filtering by agreement period or completion dates. | |
| - Be aware of potential data quality issues in text fields. | |
| ### Important Notes/Considerations for Querying: | |
| - Join with the `dim_customer` table to retrieve customer information. | |
| - Consider indexing frequently queried columns (e.g., `customer_id`, `agreement_status`, `agreement_type`). | |
| --- | |
| ## Table: `dim_customer` | |
| ### Schema: `public` | |
| ### All Columns and their Descriptions: | |
| - `customer_id` (text): Likely the primary identifier for a customer. This is probably the primary key, although not explicitly stated. | |
| - `customer_name` (text): The name of the customer. | |
| - `customer_start_date` (timestamp without time zone): The date the customer record became active. | |
| - `customer_end_date` (timestamp without time zone): The date the customer record became inactive. | |
| - `customer_status` (text): The status of the customer (e.g., Active, Inactive). | |
| - `is_customer_active` (integer): Indicates whether the customer is currently active (1 or 0). | |
| - `customer_status_reason` (text): Reason for the customer's status. | |
| - `market_segment` (text): The market segment the customer belongs to. | |
| - `customer_segment` (text): The customer segment the customer belongs to. | |
| - `is_individual_customer` (integer): Indicates whether the customer is an individual (1 or 0). | |
| - `organization_name` (text): The name of the organization, if the customer is an organization. | |
| - `trading_name` (text): The trading name of the customer. | |
| - `organization_type_id` (text): The ID of the organization type. | |
| - `is_head_office` (boolean): Indicates if the customer is a head office. | |
| - `is_legal_entity` (boolean): Indicates if the customer is a legal entity. | |
| - `organization_start_date` (timestamp without time zone): The date the organization record became active. | |
| - `organization_end_date` (timestamp without time zone): The date the organization record became inactive. | |
| - `birth_date` (timestamp without time zone): The birth date of the customer, if an individual. | |
| - `death_date` (timestamp without time zone): The death date of the customer, if an individual. | |
| - `country_of_birth` (text): The country of birth of the customer, if an individual. | |
| - `party_status` (text): The status of the party. | |
| - `title` (text): The title of the customer, if an individual. | |
| - `generation` (text): The generation of the customer, if an individual. | |
| - `preferred_given_name` (text): The preferred given name of the customer, if an individual. | |
| - `given_name` (text): The given name of the customer, if an individual. | |
| - `middle_name` (text): The middle name of the customer, if an individual. | |
| - `family_name` (text): The family name of the customer, if an individual. | |
| - `family_name_prefix` (text): The family name prefix of the customer, if an individual. | |
| - `formatted_name` (text): The formatted name of the customer. | |
| - `full_name` (text): The full name of the customer. | |
| - `legal_name` (text): The legal name of the customer. | |
| - `gender` (text): The gender of the customer, if an individual. | |
| - `location` (text): The location of the customer. | |
| - `marital_status` (text): The marital status of the customer, if an individual. | |
| - `nationality` (text): The nationality of the customer, if an individual. | |
| - `place_of_birth` (text): The place of birth of the customer, if an individual. | |
| - `juridical_info` (text): Juridical information of the customer. | |
| - `latitude` (text): The latitude of the customer's location. | |
| - `longitude` (text): The longitude of the customer's location. | |
| - `number_of_employees` (text): The number of employees for the customer, if an organization. | |
| - `tax_exempt` (text): Indicates if the customer is tax-exempt. | |
| - `dunning_level` (text): The dunning level for the customer. | |
| - `contact_verified` (text): Indicates if the contact information is verified. | |
| - `customer_number` (text): The customer number. | |
| - `communication_method` (text): The communication method for the customer. | |
| - `sales_agent_id` (text): The ID of the sales agent. Likely a foreign key to a `sales_agent` table. | |
| - `account_manager_id` (text): The ID of the account manager. Likely a foreign key to an `account_manager` table. | |
| - `sales_partner_id` (text): The ID of the sales partner. Likely a foreign key to a `sales_partner` table. | |
| - `party_identifier` (text): The party identifier. | |
| - `customer_revision` (bigint): The customer revision number. | |
| - `party_revision` (bigint): The party revision number. | |
| ### Relationships with Other Tables (Foreign Keys): | |
| - Based on the column names, it is *highly likely* that the table has foreign keys to tables such as: `sales_agent`, `account_manager`, and `sales_partner`. Without the ability to see the foreign key constraints, this is an educated guess. | |
| ### Cardinality of Relationships: | |
| - Relationship with `sales_agent`, `account_manager`, and `sales_partner` is likely one-to-many. One sales agent/account manager/sales partner can be associated with multiple customers. | |
| ### Common Use Cases/Example Queries: | |
| - Describe common use cases: | |
| * Reporting on customer demographics. | |
| * Analyzing customer sales by segment. | |
| * Tracking customer status and activity. | |
| * Identifying customers by sales agent or account manager. | |
| - Example SQL Snippet: | |
| ```sql | |
| SELECT customer_name, market_segment, customer_status | |
| FROM dim_customer | |
| WHERE sales_agent_id = 'XYZ123'; | |
| ``` | |
| ### Data Constraints and Business Rules: | |
| - `customer_id` is likely the primary key and should be unique and not null. | |
| - `customer_start_date` should be before or equal to `customer_end_date`. | |
| - `is_customer_active` should reflect the status of the customer based on `customer_start_date`, `customer_end_date`, and `customer_status`. | |
| - Data quality checks should be in place to ensure the accuracy of demographic data (e.g., valid gender, valid countries). | |
| ### Data Update Frequency/Volatility: | |
| - The data is likely updated frequently as customer information changes. | |
| - The volatility of the data will vary depending on the specific attributes (e.g., contact information will be more volatile than birthdate). | |
| ### Potential Pitfalls/Things to Avoid When Querying: | |
| - Joining to other tables without properly understanding the relationships, especially the role of `sales_agent_id`, `account_manager_id`, and `sales_partner_id`. | |
| - Incorrectly filtering on date ranges. Consider using `customer_start_date` and `customer_end_date` to filter for active customers. | |
| - Not considering the impact of customer status when analyzing customer data. | |
| ### Important Notes/Considerations for Querying: | |
| - Always check `is_customer_active` or `customer_status` when querying to ensure you are getting the correct set of customers. | |
| - Be mindful of the date ranges when analyzing customer data over time. | |
| - Use the appropriate join conditions when joining with other tables. | |
| - Consider the impact of null values in fields such as `birth_date`, `death_date`, etc. | |
| --- | |
| ## Table: `dim_product` | |
| ### Schema: `public` | |
| ### All Columns and their Descriptions: | |
| * **product\_id (text):** Likely the primary identifier for the product. It's a text field. It is likely the primary key, though I don't have FK information. | |
| * **customer\_id (text):** The identifier for the customer associated with the product. Foreign key to the `dim_customer` table (inferred, as I lack foreign key info). | |
| * **agreement\_id (text):** An identifier for the agreement related to the product. Foreign key to the `dim_agreement` table (inferred). | |
| * **agreement\_name (text):** The name of the agreement related to the product. | |
| * **product\_offering\_id (text):** An identifier for the product offering. Potentially a foreign key to a `dim_product_offering` table (inferred). | |
| * **product\_offering\_name (text):** The name of the product offering. | |
| * **product\_name (text):** The name of the product. | |
| * **place\_id (text):** An identifier related to the product's location or place. Foreign key to `dim_place` (inferred). | |
| * **product\_class (text):** Categorization of the product (e.g., service, hardware). | |
| * **product\_status (text):** The current status of the product (e.g., active, inactive). | |
| * **is\_product\_active (integer):** Indicates if the product is active (1) or not (0). | |
| * **is\_product\_suspended (integer):** Indicates if the product is suspended (1) or not (0). | |
| * **is\_product\_in\_active (integer):** Indicates if the product is inactive (1) or not (0). | |
| * **is\_bundle (boolean):** Indicates if the product is part of a bundle (true) or not (false). | |
| * **order\_date (timestamp without time zone):** The date the product was ordered. | |
| * **start\_date (timestamp without time zone):** The date the product service started. | |
| * **price\_type (text):** The type of pricing associated with the product (e.g., recurring, one-time). | |
| * **price\_type\_value (text):** The value associated with the price type. | |
| * **recurring\_charge\_period (text):** The period for recurring charges (e.g., monthly, annually). | |
| * **tax\_rate (double precision):** The tax rate applied to the product. | |
| * **duty\_free\_amount (double precision):** The duty-free amount for the product. | |
| * **tax\_included\_amount (double precision):** The amount of tax included in the product price. | |
| * **add\_product\_order\_item\_id (text):** Identifier for adding a product to an order item. | |
| * **delete\_product\_order\_item\_id (text):** Identifier for deleting a product from an order item. | |
| * **sales\_agent\_id (text):** The identifier of the sales agent. Foreign key to `dim_sales_agent` (inferred). | |
| * **sales\_partner\_id (text):** The identifier of the sales partner. Foreign key to `dim_sales_partner` (inferred). | |
| * **commitment\_duration\_units (text):** The units for the commitment duration (e.g., months, years). | |
| * **commitment\_duration (double precision):** The duration of the commitment. | |
| * **commitment\_term\_name (text):** The name of the commitment term. | |
| * **commitment\_term\_type (text):** The type of commitment term. | |
| * **usage\_duration\_units (text):** The units for the usage duration. | |
| * **usage\_duration (double precision):** The duration of the product usage. | |
| * **usage\_term\_name (text):** The name of the usage term. | |
| * **usage\_term\_type (text):** The type of usage term. | |
| * **guarantee\_amount (text):** The amount of guarantee associated with the product. | |
| * **device\_type (text):** The type of device associated with the product. | |
| * **gl\_code (text):** General Ledger code associated with the product. | |
| * **infrastructure (text):** The infrastructure associated with the product. | |
| * **ip\_address (text):** The IP address associated with the product. | |
| * **mac\_address (text):** The MAC address associated with the product. | |
| * **oss\_code (text):** OSS code associated with the product. | |
| * **smart\_card\_serialnumber (text):** The serial number of the smart card associated with the product. | |
| * **sla (text):** Service Level Agreement associated with the product. | |
| * **spec\_type (text):** Specification type of the product. | |
| * **specsub\_type (text):** Specification subtype of the product. | |
| * **resource\_model (text):** The resource model associated with the product. | |
| * **rating\_type (text):** The type of rating associated with the product. | |
| * **postpaid\_type (text):** The postpaid type associated with the product. | |
| * **brand\_name (text):** The brand name of the product. | |
| * **tv\_infrastructure (text):** The TV infrastructure associated with the product. | |
| * **revision (bigint):** Revision number of the product. | |
| * **href (text):** Hypertext reference (URL) associated with the product. | |
| ### Relationships with Other Tables (Foreign Keys): | |
| * **customer\_id:** Foreign key referencing `dim_customer`. | |
| * **agreement\_id:** Foreign key referencing `dim_agreement`. | |
| * **product\_offering\_id:** Foreign key referencing `dim_product_offering`. | |
| * **place\_id:** Foreign key referencing `dim_place`. | |
| * **sales\_agent\_id:** Foreign key referencing `dim_sales_agent`. | |
| * **sales\_partner\_id:** Foreign key referencing `dim_sales_partner`. | |
| ### Cardinality of Relationships: | |
| * Relationship with `dim_customer` is likely one-to-many (one customer can have many products). | |
| * Relationship with `dim_agreement` is likely one-to-many (one agreement can have many products). | |
| * Relationship with `dim_product_offering` is likely one-to-many (one product offering can be associated with many products). | |
| * Relationship with `dim_place` is likely one-to-many (one place can have many products). | |
| * Relationship with `dim_sales_agent` is likely one-to-many (one sales agent can be associated with many products). | |
| * Relationship with `dim_sales_partner` is likely one-to-many (one sales partner can be associated with many products). | |
| ### Common Use Cases/Example Queries: | |
| * **Tracking Product Details:** Retrieving detailed information about a specific product. | |
| * Example SQL Snippet: `SELECT * FROM dim_product WHERE product_id = 'your_product_id';` | |
| * **Customer Product Overview:** Listing all products associated with a specific customer. | |
| * Example SQL Snippet: `SELECT * FROM dim_product WHERE customer_id = 'your_customer_id';` | |
| * **Aggregating product counts:** Calculating the number of products per product offering. | |
| * Example SQL Snippet: `SELECT product_offering_name, count(*) FROM dim_product GROUP BY product_offering_name;` | |
| ### Data Constraints and Business Rules: | |
| * `product_id` is likely unique and not null. | |
| * `is_product_active`, `is_product_suspended`, and `is_product_in_active` should be mutually exclusive (only one can be true at a time). | |
| * Date fields (order\_date, start\_date) should be consistent with business timelines. | |
| ### Data Update Frequency/Volatility: | |
| * Data is likely updated frequently, with changes reflecting product status, customer associations, and order information. | |
| ### Potential Pitfalls/Things to Avoid When Querying: | |
| * Ensure proper joins when querying across related tables (e.g., `dim_customer`, `dim_agreement`). | |
| * Be aware of product lifecycle (status) when filtering data. Consider `is_product_active` and other status flags. | |
| * Performance can suffer if large datasets are queried without appropriate indexes. | |
| ### Important Notes/Considerations for Querying: | |
| * Always join to other dimension tables (e.g., `dim_customer`, `dim_agreement`) to get a complete view of the product's context. | |
| * Use appropriate date ranges when analyzing product activity over time. | |
| * Consider the business rules related to product states when building queries. | |
| --- | |
| ## Table: `dim_product_order_item` | |
| ### Schema: `public` | |
| ### All Columns and their Descriptions: | |
| - `product_order_item_id` (text): Unique identifier for a product order item. Likely the primary key. | |
| - `product_order_id` (text): Likely a foreign key referencing a table containing product order information (e.g., `dim_product_order`). | |
| - `customer_id` (text): Likely a foreign key referencing the `dim_customer` table. | |
| - `agreement_id` (text): Likely a foreign key referencing the `dim_agreement` table. | |
| - `agreement_name` (text): Name of the agreement. | |
| - `order_item_agreement_id` (text): Identifier for the order item agreement. | |
| - `order_item_agreement_name` (text): Name of the order item agreement. | |
| - `billing_account_id` (text): Identifier for the billing account. | |
| - `sales_channel` (text): The sales channel through which the order was placed. | |
| - `order_date` (timestamp without time zone): The date the order was placed. | |
| - `order_status` (type): The status of the order (list status type: ACKNOWLEDGED,ASSESSING,CANCELLATION,CANCELLED,COMPLETED,INPROGRESS). | |
| - `order_item_status` (text): The status of the order item. | |
| - `order_description` (text): Description of the order. | |
| - `order_revision` (bigint): The revision number of the order. | |
| - `order_class` (text): The class of the order. | |
| - `order_href` (text): Hyperlink related to the order. | |
| - `order_item_type` (text): The type of the order item (e.g., 'Product', 'Service'). | |
| - `quantity` (bigint): The quantity of the product ordered. | |
| - `order_item_price_type` (text): The price type of the order item (e.g., 'Recurring', 'One-time'). | |
| - `order_item_recurring_charge_period` (text): The recurring charge period if applicable. | |
| - `order_item_tax_rate` (double precision): The tax rate applied to the order item. | |
| - `order_item_duty_free_amount` (double precision): The duty-free amount of the order item. | |
| - `order_item_tax_included_amount` (double precision): The tax-included amount of the order item. | |
| - `order_item_action` (text): The action performed on the order item (e.g., 'Add', 'Update', 'Delete'). | |
| ### Relationships with Other Tables (Foreign Keys): | |
| - `customer_id` (FK) references `dim_customer`: Links the order item to a specific customer. | |
| - `agreement_id` (FK) references `dim_agreement`: Links the order item to a specific agreement. | |
| - `product_order_id`(FK) references `dim_product`: links the order item to a specific product order. | |
| ### Cardinality of Relationships: | |
| - Relationship with `dim_customer` is one-to-many. One customer can have multiple order items. | |
| - Relationship with `dim_agreement` is one-to-many. One agreement can have multiple order items. | |
| - Relationship with `dim_product_order` is one-to-many. One product order can have multiple order items. | |
| ### Common Use Cases/Example Queries: | |
| - Track sales and revenue by customer, product, and agreement. | |
| - Analyze order item status and trends. | |
| - Calculate total revenue and taxes. | |
| - Example SQL Snippet: `SELECT sum(quantity * order_item_tax_included_amount) FROM dim_product_order_item WHERE customer_id = 'customer123';` | |
| ### Data Constraints and Business Rules: | |
| - `product_order_item_id` should be unique. | |
| - `order_date` should be a valid date. | |
| - `quantity` should be a non-negative number. | |
| - `order_item_tax_rate`, `order_item_duty_free_amount`, and `order_item_tax_included_amount` should be valid numeric values. | |
| ### Data Update Frequency/Volatility: | |
| - Data is likely updated frequently, reflecting new orders, order updates, and cancellations. | |
| ### Potential Pitfalls/Things to Avoid When Querying: | |
| - Ensure correct join conditions when joining with other tables. | |
| - Be mindful of the `order_item_status` to filter for relevant order items. | |
| - Consider time-based filtering using `order_date` for trend analysis. | |
| ### Important Notes/Considerations for Querying: | |
| - This table is central to understanding product order details. | |
| - Join with `dim_customer` and `dim_agreement` for customer and agreement details. |