Finance

Most of our integrations with business systems provide data exposed in a format we call Finance. It is one of our ER models where we have a subject-specific standard model regardless of the original source of the data. This allows it to be effectively used for standard reports and other comparisons between different systems.

tip

Enrich with Raw Mart

Some systems and tables have a corresponding table with raw data (as the data appeared directly from the source system) in the source system’s Raw Mart. To match a row in the Finance table with the corresponding original row in the Raw Mart, the attribute raw_id in Finance is used and should be compared with the attribute expn_id in the Raw Mart.

Definition

General Attribute for all Finance tables

Attribute	Data typ	Description
le_id	bigint	Exopens ID for the company
raw_id	nvarchar(60)	Exopens ID used to link in corresponding "Raw Mart"-tabell

info

raw_id can be used to link in system-specific data provided in a possible Raw Mart for the system. This ID should then be compared with the attribute expn_id in Raw Mart.

Legal Entity

Table name: Finance.DimLegalEntity

Attribute	Data typ	Description
expn_integration_id	bigint	Used to link a legal entity with correct expn_integration_id in a possible Raw Mart
le_name	nvarchar(100)
currency_base	nvarchar(3)
locked_period_date	int
fiscal_year_offset	int

Account

Table name: Finance.DimAccount

Attribute	Data typ	Description
account_id	bigint
account_number	nvarchar(60)
account_name	nvarchar(255)
account_label	nvarchar(315)

Cost Center

Table name: Finance.DimCostCenter

Attribute	Data typ	Description
cc_id	bigint
cc_number	nvarchar(50)
cc_name	nvarchar(100)
cc_label	nvarchar(150)

Customer

Table name: Finance.DimCustomer

Attribute	Data typ	Description
customer_id	bigint
customer_number	nvarchar(60)
customer_name	nvarchar(255)
customer_postal_city	nvarchar(60)
customer_postal_country	nvarchar(60)

Date

Table name: Finance.DimDate

Attribute	Data typ	Description
date_id	int
date	date
period_id	int
start_of_month_id	int
end_of_month_id	int
day_of_week_n	int
day_of_week_name_short	nvarchar(4)
day_of_week_name	nvarchar(7)
week_n	int
week_nn	nvarchar(2)
week_Vn	nvarchar(3)
quarter_n	int
quarter_Qn	nvarchar(2)
day_of_month_n	int
day_of_month_nn	nvarchar(3)
month_n	int
month_nn	nvarchar(2)
month_name_short	nvarchar(5)
month_name	nvarchar(9)
day_of_year_n	int
year_n	int

Product

Table name: Finance.DimProduct

Attribute	Data typ	Description
product_id	bigint
product_number	nvarchar(60)
product_name	nvarchar(255)

Project

Table name: Finance.DimProject

Attribute	Data typ	Description
project_id	bigint
project_number	nvarchar(100)
project_name	nvarchar(500)
project_status	nvarchar(20)
project_start_date	int
project_end_date	int

Supplier

Table name: Finance.DimSupplier

Attribute	Data typ	Description
supplier_id	bigint
supplier_number	nvarchar(60)
supplier_name	nvarchar(255)
supplier_postal_city	nvarchar(100)
supplier_postal_country	nvarchar(100)

Version

Table name: Finance.DimVersion

Attribute	Data typ	Description
version_id	nvarchar(50)
version_name	nvarchar(100)
version_descr	nvarchar(500)
version_type	nvarchar(20)
version_group	nvarchar(20)
version_current	int
version_start_date	int
version_end_date	int

Transaction

Table name: Finance.FactTransaction

Attribute	Data typ	Description
transaction_id	bigint
version_id	nvarchar(50)
account_id	bigint
cc_id	bigint
project_id	bigint
product_id	bigint
customer_id	bigint
supplier_id	bigint
voucher_series	nvarchar(36)
voucher_number	nvarchar(36)
voucher_year	int
transaction_date	int
amount	decimal(18, 2)
transaction_descr	nvarchar(4000)
document_path	nvarchar(126)

Invoice

Table name: Finance.FactInvoice

Attribute	Data typ	Description
invoice_id	bigint
invoice_number	nvarchar(50)
customer_id	bigint
supplier_id	bigint
currency	nvarchar(3)
invoice_reference	nvarchar(255)
invoice_type	char	C = Customer invoice, S = Supplier invoice
invoice_date	int
due_date	int
final_pay_date	int
balance_amount	decimal(18, 2)
total_amount	decimal(18, 2)
vat_amount	decimal(18, 2)
total_amount_in_currency	decimal(18, 2)
vat_amount_in_currency	decimal(18, 2)
document_path	nvarchar(255)

Invoice Row

Table name: Finance.FactInvoiceRow

Attribute	Data typ	Description
invoice_row_id	bigint
invoice_id	bigint	Huvudfakturan för raden
account_id	bigint
cc_id	bigint
project_id	bigint
product_id	bigint
customer_id	bigint
supplier_id	bigint
currency	nvarchar(3)
invoice_reference	nvarchar(255)
invoice_type	char	C = Customer invoice, S = Supplier invoice
invoice_date	int
due_date	int
final_pay_date	int
quantity	decimal(18, 2)
price	decimal(18, 2)
cost	decimal(18, 2)
discount_amount	decimal(18, 2)
total_amount	decimal(18, 2)
vat_amount	decimal(18, 2)
total_amount_in_currency	decimal(18, 2)
vat_amount_in_currency	decimal(18, 2)
descr	nvarchar(4000)
document_path	nvarchar(255)
type	nvarchar(30)

Order

Table name: Finance.FactOrder

Attribute	Data typ	Description
order_id	bigint
project_id	bigint
cc_id	bigint
customer_id	bigint
product_id	bigint
invoice_reference	nvarchar(50)
status	nvarchar(30)
description	nvarchar(1000)
shipping_date	int
order_date	int
ordered_quantity	decimal(18, 2)
delivered_quantity	decimal(18, 2)
discount_amount	decimal(18, 2)
price	decimal(18, 2)
cost	decimal(18, 2)
original_currency	nvarchar(3)
total_amount	decimal(18, 2)
vat_amount	decimal(18, 2)
Balance	decimal(18, 2)
Fraktas	nvarchar(1)
Mottagen	nvarchar(1)
Fakturerad	nvarchar(1)
Betalad	nvarchar(1)
Delbetalad	nvarchar(1)
RestOrder	nvarchar(1)
Makulerad	nvarchar(1)