Fabric Connector for Exact Online (English)

⌘K
  2. Home
  4. Fabric Connector for Exac...
  6. 2. Fabric Lakehouse
  8. 2.4 Datamodel Exact Online

2.4 Datamodel Exact Online

Below is an overview of all standard tables available. Pay close attention to the difference between ‘full-refresh’ and ‘incremental’ tables to structure your notebooks as efficiently as possible.

An efficient way to keep your data up to date at the lowest Fabric cost:

  • Create a first notebook for full-refresh tables and refresh it once per day.
  • Create a second notebook for incremental tables, which you can refresh multiple times per day.
  • Create a third notebook for rarely changing data, which you refresh once per week or month.

All notebooks can be linked to the same Lakehouse.

We recommend importing only the tables that are actually used. It is easier to add a table later than to remove one, because you often do not know where a table is being used.

This approach keeps your data current with the shortest refresh times and the lowest Fabric costs.

 

General

Tablename Table type Refresh type
ExactOnline.Companies() Dimension Full
ExactOnline.Calendar() Dimension (date table) Full

Financial

Tablename Table type Refresh type
ExactOnline.Ledger() Dimension Incremental
ExactOnline.LedgerTransactions() Fact Incremental
ExactOnline.LedgerMapping() Dimension Full
ExactOnline.LedgerClassifications() Dimension Incremental
ExactOnline.BudgetAmounts() Fact Full
ExactOnline.Documents() Fact Incremental
ExactOnline.Journals() Dimension Full
ExactOnline.CostCenters() Dimension Full
ExactOnline.CostUnits() Dimension Full
ExactOnline.PaymentConditions() Dimension Full
ExactOnline.Vat() Dimension Full

Items

Tablename Table type Refresh type
ExactOnline.Items() Dimension Incremental
ExactOnline.ItemGroups() Dimension Full
ExactOnline.ItemAssortments() Dimension Full
ExactOnline.ItemSalesprices() Fact Incremental
ExactOnline.ItemSuppliers() Dimension Incremental
ExactOnline.ItemPurchaseprices() Fact Incremental

Relations

Tablename Table type Refresh type
ExactOnline.Debtors() Dimension Incremental
ExactOnline.DebtorsOutstanding() Fact Full
ExactOnline.Creditors() Dimension Incremental
ExactOnline.CreditorsOutstanding() Fact Full
ExactOnline.RelationGroups() Dimension Full

Sales

Tablename Table type Refresh type
ExactOnline.SalesOrders() Fact Incremental
ExactOnline.SalesInvoices() Fact Incremental
ExactOnline.Quotations() Fact Incremental
ExactOnline.Opportunities() Fact Full
ExactOnline.GoodsDeliveries() Fact Incremental
ExactOnline.Selectioncodes() Dimension Full

Purchase

Tablename Table type Refresh type
ExactOnline.PurchaseOrders() Fact Incremental
ExactOnline.PurchaseInvoices() Fact Full
ExactOnline.GoodsReceipts() Fact Full

Stock

Tablename Table type Refresh type
ExactOnline.Stock() Fact Incremental
ExactOnline.Warehouses() Dimension Full
ExactOnline.StorageLocations() Dimension Full

HRM

Tablename Table type Refresh type
ExactOnline.Employees() Dimension Incremental
ExactOnline.Employments() Fact Incremental
ExactOnline.EmploymentContracts() Fact Incremental
ExactOnline.ScheduleHours() Fact Incremental
ExactOnline.Salaries() Fact Incremental
ExactOnline.Departments() Dimension Full
ExactOnline.JobTitles() Dimension Incremental
ExactOnline.LeaveRegistrations() Fact Incremental
ExactOnline.LeaveHours() Fact Incremental
ExactOnline.AbsenceRegistrations() Fact Incremental
ExactOnline.AbsenceHours() Fact Incremental

Projects and hours

Tablename Table type Refresh type
ExactOnline.Projects() Dimension Incremental
ExactOnline.ProjectActivities() Dimension Incremental
ExactOnline.ProjectHours() Fact Incremental
ExactOnline.ProjectCosts() Fact Incremental
ExactOnline.ProjectPlanning() Fact Incremental
ExactOnline.HourTypes() Dimension Incremental
ExactOnline.CostTypes() Dimension Incremental
ExactOnline.InvoiceTerms() Fact Full

Subscriptions

Tablename Table type Refresh type
ExactOnline.Subscriptions() Fact Incremental

Other

Tablename Table type Refresh type
ExactOnline.Users() Dimension Full
ExactOnline.Periods() Dimension Full
ExactOnline.Currencies() Dimension Full
ExactOnline.ExchangeRates() Fact Full

 

Migration from Power BI Connector for Exact Online

The data model is almost identical to the data model of the Power BI Connector for Exact Online, which makes migration straightforward. However, there are a few minor differences caused by the fact that incremental endpoints in the Exact Online API sometimes differ slightly.

There are a few small differences in these tables:

  • LedgerAmounts: the LedgerAmounts table has been removed. From now on, use the LedgerTransactions table. All fields from LedgerAmounts are also present in LedgerTransactions, making the conversion straightforward.
  • OpeningBalance: the OpeningBalance table is no longer available. You can now calculate the opening balance based on cumulative balances from LedgerTransactions. If the full history is available, you can determine opening balances for all years. Previously, this was limited to the last two years due to restrictions in the Exact Online API.
  • LedgerTransactions: the LedgerTransactions table now also includes entries for year-end closing (Type 310 – Period closing). Only balance transactions are imported for the opening balance; P&L entries are excluded to prevent the result from being zero.
  • Items: The field SalesPrice is no longer available. Instead, you can use the table ItemSalesprices, which contains sales prices per item per customer.
  • SalesInvoices: The field SelectionCodeID is no longer available.
  • ProjectActivities: The fields Baseline and BaselineDate are no longer available.
  • Stock: The fields SafetyStock and MaximumStock are no longer available. The field AvailableStock has been added.