search

fivetran / dbt_netsuite

Data models for Fivetran's Netsuite connector, built using dbt.
https://fivetran.github.io/dbt_netsuite/
Apache License 2.0
36 stars 34 forks source link

[Bug] Consolidated results only available for root subsidiary #93

Closed jmongerlyra closed 8 months ago

jmongerlyra commented 10 months ago

Is there an existing issue for this?

Describe the issue

NetSuite defines consolidation relationships and translation rates in the consolidated_exchange_rates table. Currently, the consolidation parent dimension is missing for all but the root parent because of the line below in int_netsuite2__acctxperiod_exchange_rate_map.sql. 

where consolidated_exchange_rates.to_subsidiary_id in (select subsidiary_id from subsidiaries where parent_id is null)  -- constraint - only the primary subsidiary has no parent

Natively in NetSuite consolidated financial reporting can be generated at each layer. For example, using the hierarchy below, consolidated results for Sub D should include D, E, F translated into USD.

Subsidiary Hierarchy:

Sub A (USD)
    Sub B (USD)
        Sub C (USD)
            Sub D (USD)
                Sub E (GBP)
                    Sub F (CHF)
                        ...

The pull request below contains a patch for this issue. https://github.com/fivetran/dbt_netsuite/pull/90

Relevant error log or model output

n/a

Expected behavior

All models should be disaggregated by to_subsidiary for consolidation reporting at each level.

dbt Project configurations

config-version: 2 name: 'netsuite' version: '0.10.0' require-dbt-version: [">=1.3.0", "<2.0.0"]

models: netsuite: +materialized: table +schema: netsuite netsuite: intermediate: +materialized: ephemeral netsuite2: intermediate: +materialized: ephemeral

vars: netsuite:

Netsuite staging models

netsuite_accounting_books: "{{ ref('stg_netsuite__accounting_books') }}"
netsuite_accounting_periods: "{{ ref('stg_netsuite__accounting_periods') }}"
netsuite_accounts: "{{ ref('stg_netsuite__accounts') }}"
netsuite_classes: "{{ ref('stg_netsuite__classes') }}"
netsuite_consolidated_exchange_rates: "{{ ref('stg_netsuite__consolidated_exchange_rates') }}"
netsuite_currencies: "{{ ref('stg_netsuite__currencies') }}"
netsuite_customers: "{{ ref('stg_netsuite__customers') }}"
netsuite_departments: "{{ ref('stg_netsuite__departments') }}"
netsuite_expense_accounts: "{{ ref('stg_netsuite__expense_accounts') }}"
netsuite_income_accounts: "{{ ref('stg_netsuite__income_accounts') }}"
netsuite_items: "{{ ref('stg_netsuite__items') }}"
netsuite_locations: "{{ ref('stg_netsuite__locations') }}"
netsuite_subsidiaries: "{{ ref('stg_netsuite__subsidiaries') }}"
netsuite_transaction_lines: "{{ ref('stg_netsuite__transaction_lines') }}"
netsuite_transactions: "{{ ref('stg_netsuite__transactions') }}"
netsuite_vendor_types: "{{ ref('stg_netsuite__vendor_types') }}"
netsuite_vendors: "{{ ref('stg_netsuite__vendors') }}"
netsuite2_account_types: "{{ ref('stg_netsuite2__account_types') }}"
netsuite2_accounting_book_subsidiaries: "{{ ref('stg_netsuite2__accounting_book_subsidiaries') }}"
netsuite2_accounting_books: "{{ ref('stg_netsuite2__accounting_books') }}"
netsuite2_accounting_period_fiscal_calendars: "{{ ref('stg_netsuite2__accounting_period_fiscal_cal') }}"
netsuite2_accounting_periods: "{{ ref('stg_netsuite2__accounting_periods') }}"
netsuite2_accounts: "{{ ref('stg_netsuite2__accounts') }}"
netsuite2_classes: "{{ ref('stg_netsuite2__classes') }}"
netsuite2_consolidated_exchange_rates: "{{ ref('stg_netsuite2__consolidated_exchange_rates') }}"
netsuite2_currencies: "{{ ref('stg_netsuite2__currencies') }}"
netsuite2_customers: "{{ ref('stg_netsuite2__customers') }}"
netsuite2_departments: "{{ ref('stg_netsuite2__departments') }}"
netsuite2_entities: "{{ ref('stg_netsuite2__entities') }}"
netsuite2_entity_address: "{{ ref('stg_netsuite2__entity_address') }}"
netsuite2_items: "{{ ref('stg_netsuite2__items') }}"
netsuite2_jobs: "{{ ref('stg_netsuite2__jobs') }}"
netsuite2_location_main_address: "{{ ref('stg_netsuite2__location_main_address') }}"
netsuite2_locations: "{{ ref('stg_netsuite2__locations') }}"
netsuite2_subsidiaries: "{{ ref('stg_netsuite2__subsidiaries') }}"
netsuite2_transaction_accounting_lines: "{{ ref('stg_netsuite2__transaction_accounting_lines') }}"
netsuite2_transaction_lines: "{{ ref('stg_netsuite2__transaction_lines') }}"
netsuite2_transactions: "{{ ref('stg_netsuite2__transactions') }}"
netsuite2_vendor_categories: "{{ ref('stg_netsuite2__vendor_categories') }}"
netsuite2_vendors: "{{ ref('stg_netsuite2__vendors') }}"
accounts_pass_through_columns:
  - name: "special_account_type_id"
    alias: "special_account_type_id"
classes_pass_through_columns: []
departments_pass_through_columns: []
transactions_pass_through_columns: []
transaction_lines_pass_through_columns: []
balance_sheet_transaction_detail_columns: [subsidiary_id, subsidiary_name]
income_statement_transaction_detail_columns: []

Package versions

packages:

What database are you using dbt with?

snowflake

dbt Version

1.6.1

Additional Context

No response

Are you willing to open a PR to help address this issue?