## 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.