/************************************************************ * Konsido Table Definitions * * version 1.0 * * * * This script creates the necessary tables for the Konsido * * dimensional data model output format. It does not include * * primary keys, foreign keys, or indexes. * * * * The tables are created in the 'dbo' schema and are * * prefixed with 'dim_' for dimension tables and 'fact_' for * * fact tables. * * You can modify (search and replace) the [dbo] schema as * * needed for your database environment. * ************************************************************/ CREATE TABLE [dbo].[dim_category] ( [dw_id_category] [int] NOT NULL, [code] [int] NOT NULL, [name] [nvarchar] (300) NOT NULL, [description] [nvarchar] (2408) NULL, [code_lvl0] [int] NOT NULL, [code_lvl1] [int] NULL, [code_lvl2] [int] NULL, [name_lvl0] [nvarchar] (300) NOT NULL, [name_lvl1] [nvarchar] (300) NULL, [name_lvl2] [nvarchar] (300) NULL, [examples] [varchar] (1000) NULL, [code_lvl0_txt] [varchar] (32) NULL, [code_lvl1_txt] [varchar] (32) NULL, [code_lvl2_txt] [varchar] (32) NULL, [name_lvl0_txt] [varchar] (300) NULL, [name_lvl1_txt] [varchar] (300) NULL, [name_lvl2_txt] [varchar] (300) NULL ); CREATE TABLE [dbo].[dim_category_change] ( [dw_id_category_change] [int] NOT NULL, [category_scheme_name] [varchar] (100) NOT NULL, [code] [int] NOT NULL, [name] [nvarchar] (300) NOT NULL, [scheme_version] [int] NOT NULL, [change_type] [varchar] (100) NULL, [description] [varchar] (2048) NULL ); CREATE TABLE [dbo].[dim_chart_of_accounts] ( [dw_id_chart_of_accounts] [int] NOT NULL, [gruppering_number] [varchar] (16) NOT NULL, [ejerforhold] [varchar] (256) NULL, [art_lvl0] [varchar] (256) NULL, [art_lvl1] [varchar] (256) NULL, [dranst] [varchar] (256) NULL, [hovedkonto] [varchar] (256) NULL, [hovedfunktion] [varchar] (256) NULL, [funktion] [varchar] (256) NULL, [gruppering] [varchar] (256) NULL, [konsido_purpose_category] [varchar] (300) NULL, [purpose_number] [varchar] (32) NULL, [purpose_name] [varchar] (255) NULL, [account_name] [varchar] (255) NULL ); CREATE TABLE [dbo].[dim_client] ( [dw_id_client] [int] NOT NULL, [client_iid] [int] NOT NULL, [client_id] [varchar] (200) NOT NULL, [client_name] [varchar] (255) NULL, [client_target_population] INT NULL ); CREATE TABLE [dbo].[dim_compliance] ( [dw_id_compliance] [int] NOT NULL, [supplier_compliance] [bit] NULL, [supplier_compliance_text] [varchar] (64) NULL, [category_compliance] [bit] NULL, [category_compliance_text] [varchar] (64) NULL, [supplier_category_compliance] [bit] NULL, [supplier_category_compliance_text] [varchar] (64) NULL, [catalog_compliance] [bit] NULL, [catalog_compliance_text] [varchar] (64) NULL, [price_compliance] [bit] NULL, [price_compliance_text] [varchar] (64) NULL, [e_commerce_compliance] [bit] NULL, [e_commerce_compliance_text] [varchar] (64) NULL ); CREATE TABLE [dbo].[dim_compliant_category] ( [dw_id_compliant_category] [int] NOT NULL, [start_date] [date] NOT NULL, [end_date] [date] NOT NULL, [contract_ids] [varchar] (8000) NULL ); CREATE TABLE [dbo].[dim_compliant_category_supplier] ( [dw_id_compliant_category_supplier] [int] NOT NULL, [supplier_tax_id] [varchar] (32) NOT NULL, [category_code] [int] NOT NULL, [start_date] [date] NOT NULL, [end_date] [date] NOT NULL, [contract_ids] [varchar] (8000) NULL ); CREATE TABLE [dbo].[dim_compliant_item] ( [dw_id_compliant_item] [int] NOT NULL, [supplier_tax_id] [varchar] (30) NOT NULL, [contract_id] [varchar] (128) NOT NULL, [contract_item_prices] [varchar] (128) NULL, [customer_gln_number] [varchar] (30) NULL, [start_date] [date] NOT NULL, [end_date] [date] NOT NULL ); CREATE TABLE [dbo].[dim_compliant_supplier] ( [dw_id_compliant_supplier] [int] NOT NULL, [dw_id_supplier] [int] NOT NULL, [compliant_start_date] [datetime2] (7) NOT NULL ); CREATE TABLE [dbo].[dim_cpo_potentials] ( [dw_id_cpo_potentials] [int] NOT NULL, [cpoa_1_kategorivarians] [int] NULL, [cpoa_2_vidensniveau_udledninger_og_miljoimpact] [int] NULL, [cpoa_3_fakturakvalitet_og_detaljegrad_i_fakturaerne] [int] NULL, [cpoa_4_kompleksitet_i_forhold_til_pavirkning_af_kategorien] [int] NULL, [cpoa_5_emballagens_vaegt_sammenlignet_med_varens_vaegt] [decimal] (19, 8) NULL, [cpoa_5_1_emballageaffald_til_egen_handtering_i_kg_per_dkk_10_00] [decimal] (19, 8) NULL, [cpoa_5_2_hovedtyper_af_emballageaffald_til_egen_handtering_ekskl_pantvarer_og_eur_paller] [nvarchar] (2048) NULL, [cpoa_5_3_produktaffald_ekskl_emballage_i_kg_per_dkk_10_00] [decimal] (19, 8) NULL, [cpoa_5_4_tidspunkt_for_produktbortskaffelse_i_maneder_efter_kob] [decimal] (19, 8) NULL, [cpoa_5_5_produkt_eller_restprodukt_beskrevet_som_affaldskategorier] [nvarchar] (2048) NULL, [cpoa_6_1_co_2e_udledt_i_kg_per_dkk_10_00] [decimal] (19, 8) NULL, [cpoa_6_2_primaert_co_2_scope] [int] NULL, [cpoa_6_3_kildehenvisning_co_2_beregning] [varchar] (512) NULL, [cpoa_7_andel_af_co_2_udledning_der_teoretisk_set_kunne_bortfalde] [decimal] (19, 8) NULL, [cpob_1_potentiale_leverandor] [int] NULL, [cpob_3_potentiale_produkt] [int] NULL, [cpob_5_potentiale_logistik] [int] NULL, [cpob_7_potentiale_aktivitet] [int] NULL, [cpob_9_potentiale_levetidsforlaengelse] [int] NULL, [cpob_11_potentiale_ekstern_genanvendelse] [int] NULL, [cpoc_1_pavirkning_grundvand] [int] NULL, [cpoc_2_pavirkning_vandmiljo] [int] NULL, [cpoc_3_pavirkning_luftkvalitet] [int] NULL, [cpoc_4_pavirkning_uv_indstraling] [int] NULL, [cpoc_5_pavirkning_avlsdyr] [int] NULL, [cpoc_6_pavirkning_vilde_dyr] [int] NULL, [cpoc_7_pavirkning_biodiversitet] [int] NULL, [cpod_1_pavirkning_folkesundhed] [int] NULL, [cpod_2_pavirkning_borgerservice] [int] NULL, [cpod_3_pavirkning_medarbejdertrivsel] [int] NULL, [cpoe_1_problemstillinger_arbejdsmiljo] [int] NULL, [cpoe_2_problemstillinger_londumpning] [int] NULL, [cpoe_3_problemstillinger_menneskerettigheder] [int] NULL, [cpoe_4_csr_problemstilling_4] [int] NULL, [cpoe_5_csr_problemstilling_5] [int] NULL, [cpof_1_relationer_til_fn_verdensmal] [nvarchar] (2048) NULL, [cpo_samlet_relative_potentiale] [decimal] (19, 8) NULL, [cpo_gns_realiseringsstatus] [decimal] (19, 8) NULL, [cpo_ikke_realiseret_potentiale] [decimal] (19, 8) NULL, [cpo_samlet_analyseusikkerhed] [decimal] (19, 8) NULL, [cpo_gns_miljoimpact] [decimal] (19, 8) NULL, [cpo_gns_sundhedsimpact] [decimal] (19, 8) NULL, [cpo_gns_csr_impact] [decimal] (19, 8) NULL, [cpoa_6_2b_scope_3_category] [varchar] (128) NULL, [cpoa_6_3_kildehenvisning_co_2_id] [varchar] (256) NULL, [dw_id_category] [int] NULL ); CREATE TABLE [dbo].[dim_customer] ( [dw_id_customer] [int] NOT NULL, [tax_id] [varchar] (32) NULL, [gln_number] [varchar] (16) NOT NULL, [p_number] [varchar] (16) NOT NULL, [name_on_invoice] [varchar] (200) NOT NULL, [address_on_invoice] [varchar] (200) NOT NULL, [name_and_tax_id] [varchar] (512) NULL, [name] [nvarchar] (512) NULL, [tax_id_name] [nvarchar] (512) NULL, [p_number_name] [nvarchar] (512) NULL, [gln_ean_name] [nvarchar] (512) NULL, [tax_register_cvr_name] [nvarchar] (512) NULL, [tax_register_p_number_name] [nvarchar] (512) NULL, [nemhandel_register_name] [nvarchar] (512) NULL, [extra_info] [varchar] (200) NOT NULL ); CREATE TABLE [dbo].[dim_date] ( [dw_id_date] [int] NOT NULL, [date] [date] NULL, [day_of_month] [varchar] (2) NULL, [day_name] [varchar] (9) NULL, [day_name_eng] [varchar] (9) NULL, [day_of_week] [varchar] (1) NULL, [day_of_week_in_month] [varchar] (2) NULL, [day_of_week_in_year] [varchar] (2) NULL, [day_of_quarter] [varchar] (3) NULL, [day_of_year] [varchar] (3) NULL, [week_of_month] [varchar] (1) NULL, [week_of_quarter] [varchar] (2) NULL, [week_of_year] [varchar] (2) NULL, [month] [varchar] (2) NULL, [month_name] [varchar] (9) NULL, [month_name_eng] [varchar] (9) NULL, [month_of_quarter] [varchar] (2) NULL, [quarter] [varchar] (1) NULL, [quarter_name] [varchar] (32) NULL, [quarter_name_eng] [varchar] (32) NULL, [year] [varchar] (4) NULL, [first_day_of_month] [date] NULL, [last_day_of_month] [date] NULL, [first_day_of_quarter] [date] NULL, [last_day_of_quarter] [date] NULL, [first_day_of_year] [date] NULL, [last_day_of_year] [date] NULL, [is_holiday] [varchar] (16) NULL, [is_weekday] [varchar] (16) NULL, [holiday_name] [varchar] (64) NULL, [createdat] [datetime2] (7) NOT NULL, [updatedat] [datetime2] (7) NOT NULL ); CREATE TABLE [dbo].[dim_invoice] ( [dw_id_invoice] [int] NOT NULL, [file_name] [varchar] (200) NOT NULL, [document_type_dan] [varchar] (20) NULL, [invoice_id] [varchar] (100) NULL, [number_of_lines] [int] NULL, [payment_due_days] [int] NULL, [customer_contact] [varchar] (200) NULL, [order_reference_id] [varchar] (100) NULL, [sales_order_id] [varchar] (100) NULL, [supplier_contact] [varchar] (200) NULL, [e_commerce] [varchar] (16) NULL, [invoice_size_range] [varchar] (50) NULL, [supplier_contract_reference_id] [varchar] (256) NULL, [total_payable_amount] [decimal] (19, 4) NULL, [total_tax_amount] [decimal] (19, 4) NULL, [total_tax_amount_currency_code] [varchar] (10) NULL, [total_payable_amount_currency_code] [varchar] (10) NULL ); CREATE TABLE [dbo].[dim_invoice_item] ( [dw_id_invoice_item] [int] NOT NULL, [item_unit_code] [varchar] (16) NOT NULL, [item_id_short] [varchar] (200) NULL, [item_description_short] [varchar] (200) NULL, [item_unit_code_text_dan] [varchar] (100) NULL, [item_unit_code_text_eng] [varchar] (100) NULL, [item_name_short] [varchar] (200) NULL, [co2_emission_kg_per_unit] [decimal] (18, 6) NULL, [item_price_base_quantity] [decimal] (18, 2) NULL, [item_price_base_quantity_unit] [varchar] (32) NULL, [orderable_unit_factor] [decimal] (18, 4) NULL, [unspsc_code] [varchar] (16) NULL, [item_standard_id] [varchar] (256) NULL, [item_standard_id_type] [varchar] (32) NULL, [item_id_type] [varchar] (64) NULL, [unspsc_text] [varchar] (256) NULL ); CREATE TABLE [dbo].[dim_invoiceline] ( [dw_id_invoiceline] [int] NOT NULL, [line_number] [int] NULL, [item_price_currency_code] [varchar] (10) NULL, [total_price_currency_code] [varchar] (10) NULL, [delivery_address] [varchar] (200) NULL, [order_reference_id] [varchar] (256) NULL, [tax_amount_currency_code] [varchar] (10) NULL, [free_of_charge] [varchar] (32) NULL ); CREATE TABLE [dbo].[dim_invoiceline_text] ( [dw_id_invoiceline_text] [int] NOT NULL, [invoice_note_short] [varchar] (200) NULL, [invoiceline_note_short] [varchar] (200) NULL ); CREATE TABLE [dbo].[dim_organisation] ( [dw_id_organisation] [int] NOT NULL, [id] [varchar](40) NOT NULL, [name] [varchar] (200) NULL, [name_lvl1] [varchar] (200) NULL, [name_lvl2] [varchar] (200) NULL, [name_lvl3] [varchar] (200) NULL, [name_lvl4] [varchar] (200) NULL, [name_lvl5] [varchar] (200) NULL, [name_lvl6] [varchar] (200) NULL, [name_lvl7] [varchar] (200) NULL, [name_lvl8] [varchar] (200) NULL, [name_lvl9] [varchar] (200) NULL, [name_lvl10] [varchar] (200) NULL, [name_lvl11] [varchar] (200) NULL, [name_lvl12] [varchar] (200) NULL ); CREATE TABLE [dbo].[dim_posting] ( [dw_id_posting] [int] NOT NULL, [project_number] [int] NULL, [project_name] [varchar] (200) NULL, [account_number] [varchar] (30) NULL, [registrant] [varchar] (200) NULL, [voucher_id] [varchar](128) NULL ); CREATE TABLE [dbo].[dim_posting_text] ( [dw_id_posting_text] [int] NOT NULL, [text_short] [varchar] (200) NULL ); CREATE TABLE [dbo].[dim_supplier] ( [dw_id_supplier] [int] NOT NULL, [tax_id] [varchar] (32) NULL, [gln_number] [varchar] (16) NOT NULL, [local_supplier] [varchar](64) NULL, [p_number] [varchar] (16) NOT NULL, [name_on_invoice] [varchar] (200) NOT NULL, [address_on_invoice] [varchar] (200) NOT NULL, [postcode_on_invoice] [varchar] (32) NOT NULL, [name] [nvarchar] (512) NULL, [tax_id_name] [nvarchar] (512) NULL, [tax_id_address_postnumber] [nvarchar] (16) NULL, [tax_id_konsido_context_code] [int] NULL, [tax_id_konsido_context_title] [varchar] (300) NULL, [tax_id_konsido_context_category] [varchar] (300) NULL, [tax_id_industry_code] [varchar] (16) NULL, [name_and_tax_id] [varchar] (512) NULL, [tax_register_cvr_name] [varchar] (512) NULL, [tax_register_p_number_name] [varchar] (512) NULL, [nemhandel_register_name] [varchar] (512) NULL, [p_number_name] [nvarchar] (512) NULL, [p_number_industry_code] [varchar] (16) NULL, [result_industry_code_text] [varchar] (256) NULL, [extra_info] [varchar] (200) NOT NULL, [tax_id_number_of_employees] [int] NULL, [tax_id_number_of_fulltime_equivalent] [int] NULL, [tax_id_company_size_sme] [varchar] (64) NULL ); CREATE TABLE [dbo].[bridge_item_annotation] ( [dw_id_item_annotation] [int] NOT NULL ,[dw_id_item_annotation_group] [int] NOT NULL ); CREATE TABLE [dbo].[dim_item_annotation] ( [dw_id_item_annotation] [int] NOT NULL ,[item_annotation_name] [varchar](255) NOT NULL ,[item_annotation_type] [varchar](255) NULL ); CREATE TABLE [dbo].[dim_item_annotation_group] ( [dw_id_item_annotation_group] [int] NOT NULL ,[item_annotation_group] [varchar](2048) NOT NULL ); CREATE TABLE [dbo].[fact_spend] ( [dw_id_invoiceline] [int] NOT NULL ,[dw_id_invoice] [int] NOT NULL ,[dw_id_supplier] [int] NOT NULL ,[dw_id_customer] [int] NOT NULL ,[dw_id_invoiceline_text] [int] NOT NULL ,[dw_id_invoice_item] [int] NOT NULL ,[dw_id_posting] [int] NOT NULL ,[dw_id_posting_text] [int] NOT NULL ,[dw_id_chart_of_accounts] [int] NOT NULL ,[dw_id_organisation] [int] NOT NULL ,[dw_id_compliant_item] [int] NOT NULL ,[dw_id_compliant_category] [int] NOT NULL ,[dw_id_compliant_category_supplier] [int] NOT NULL ,[dw_id_date_invoice_order_reference_issue] [int] NOT NULL ,[dw_id_date_invoice_issue] [int] NOT NULL ,[dw_id_date_posting] [int] NOT NULL ,[dw_id_date_effective] [int] NOT NULL ,[dw_id_category] [int] NOT NULL ,[dw_id_compliant_supplier] [int] NOT NULL ,[dw_id_client] [int] NOT NULL ,[dw_id_compliance] [int] NOT NULL ,[invoice_issue_date] [date] NULL ,[invoice_order_reference_issue_date] [date] NULL ,[posting_date] [date] NULL ,[posting_fiscal_year] [int] NULL ,[invoiceline_quantity] [decimal](19, 9) NULL ,[invoiceline_total_price] [decimal](19, 4) NULL ,[invoiceline_total_price_dkk] [decimal](19, 4) NULL ,[invoiceline_item_price] [decimal](19, 4) NULL ,[invoiceline_item_price_dkk] [decimal](19, 4) NULL ,[compliant_item_price] [decimal](19, 4) NULL ,[supplier_compliant] [int] NULL ,[catalog_compliant] [int] NULL ,[cpo_calc_co2e_total_kg] [decimal](19, 4) NULL ,[cpo_calc_packaging_waste_kg] [decimal](19, 4) NULL ,[cpo_calc_product_waste_kg] [decimal](19, 4) NULL ,[cpo_calc_total_co2e_potential] [decimal](19, 4) NULL ,[cpo_calc_master_score] [decimal](19, 4) NULL ,[cpo_calc_uncertainty] [decimal](19, 4) NULL ,[cpo_calc_co2e_per_dkk] [decimal](19, 4) NULL ,[cpo_calc_co2e_total_kg_by_unit] [decimal](19, 4) NULL ,[price_index_category_result_rel_2020] [decimal](19, 4) NULL ,[invoiceline_item_price_idx_adj_rel_2020] [decimal](19, 4) NULL ,[invoiceline_total_price_idx_adj_rel_2020] [decimal](19, 4) NULL ,[cpo_calc_co2e_total_kg_by_product] [decimal](19, 4) NULL ,[cpo_calc_co2e_total_kg_result] [decimal](19, 4) NULL ,[line_tax_amount] [decimal](19, 4) NULL ,[actual_delivery_date] [date] NULL ,[invoiceline_quality_score] [decimal](8, 4) NULL ,[dw_id_organisation_alt] [int] NOT NULL ,[dw_id_item_annotation_group] [int] NULL );