Data Model - Core (Bevica Power BI)

Core dataset tables, relationships, and key reporting conventions for Bevica Power BI.

This article documents the data model. In most cases fields in the reporting table directly correspond to the field in Business Central (BC) so each field is not documented here, unless there are specific notes about them.

Note that some fields may be hidden to users in Power BI

Tables

Table Name	Comments
Items
Customers
Vendors
Item Ledger Entries	For Sales, Purchases, Costs
Item Sales History	History entries only
Item Budget Entries	Sales Budgets - if uploaded
Dimension Set Entries	Filters entries by the posted Dimensions (Global/Shortcut Dimensions from GL Setup)
Dates	Auto generated based on date setup
Time Intelligence	For calculating comparisons - see below.
Open Sales Orders	Including prepayment information
Open Purchase Orders	Including prepayment information

Item Ledger Entries

This is the core of the 'fact' table and is a full replication of the Item Ledger table.

It is useful to have a good understanding of the costing concepts in Business Central and Bevica.

Relationships

• Customer
• Vendor
• Item
• Dates
• Dimension Sets

Dates

• Financial Years are defined in initial setup.
• Weeks start on a Sunday

Measures

Actual, Expected and Non-Inventoriable Amounts

Refer to this article in the context of Power BI: Inventory Costings (Bevica BI)

Refer to standard documentation on Costing in Business Central: Design Details Expected Cost Posting

Examples can also be found in this article: Bevica Inventory Postings

A broad summary is:

• Actual Amounts are invoiced costs
• Expected Amounts are shipped or received costs which have not been invoiced
• Non-inventoriable Amounts are item charges and Bevica Additional Charges. These are always actual costs (i.e., there is not an expected/actual version of non-inventoriable costs)

Orders which have been Shipped or Received will be in the calculations as 'Expected' amounts. Depending on how you report you may only want to look at Actual Amounts.

Invoiced Only

You can use the Completely Invoiced field from the Item Ledger Entries to filter to only fully invoiced orders, whether sales or purchase.

Measures

Refer to the relevant section:

• Sales Measures
• Inventory Measures
• Purchase Measures
• Data Model - Open Orders (Bevica Power BI) [deprecated] (for measures)
• Sales and Purchase Ledger

Sales Measures if filtered by Vendor will use the Item > Vendor No. - i.e., the default vendor and not the vendor goods were purchased through.

Notes on Inventory Valuation and dates

The Item Ledger value directly corresponds to the underlying Value Entries and GL entries which are posted. The 'Amounts' on the Item Ledger are the sum of the underlying Value Entries.

However, the Posting Date on the Item Ledger Entry, and therefore in Power BI, is the date the stock movement occurred. Value Entries can have separate Posting Dates to the Item Ledger to which they are assigned. Typically this will happen based on the dates which any adjustments are run.

When comparing a Power BI inventory valuation calculation to a Bevica Inventory Valuation report there may be differences based on the ILE Posting Date (from Power BI) being different to the Value Entries Posting Date (which is used in the Inventory Valuation) for some of the cost elements. As such, you may see differences due to this. For 'correct' inventory valuation such as audit or period end you should use the Bevica Inventory Valuation.

Ensuring that General Ledger Setup Posting Dates and Inventory Periods are controlled should minimise any variations over wide date ranges.

Time Intelligence

Refer to this article for using time intelligence filters: Dates and Time Intelligence (Bevica Power BI)