BI Cube Export

What is a BI Cube Export file?

The Expensya BI Cube export is a file in CSV format delimited by semicolons.

This file centralizes the company's expenses in Expensya.

Each line in the BI Cube export represents an expense in Expensya.

What data is represented in the BI cube?

We can find the following data in the Cube:

  • Expenses,
  • Advances,
  • Reservations,
  • Credits,
  • Per diems,
  • Dates and validation deadlines,
  • Names of all validators (entire validation flow),

==> Some data may only appear in the BI Cube export if certain configurations are already set up at the client's account level.

Example: If a client does not have the Travel module, the data linked to the reservation part will not be displayed in the Cube.

Notably, there is the possibility to include:

  • The file of tags per user,
  • The transactions file.

Additional information:

The data exposed in the BI Cube export is presented according to the latest update made at the expense level.

Example: When an expense moves from 'pending accounting' to 'archived,' in the BI Cube export, we will display an expense line with the archived status.

Expensya updates the Cube by collecting all client expenses from the company's creation date up to D-1.

Filtering of cube data based on dates, expense categories, etc., is not done on the Expensya side.

BI Cube file specification:

Here is a specification of the columns and their descriptions in the BI Cube export:

Column

Column Name

Description

A

ID

The unique identifier of the expense in Expensya's database.

B

Value

The value of the expense in the currency it was made in.

c

ValueInCurrency

The expense value in the company's currency. If the expense is in a foreign currency, the value corresponds to the converted amount.

D

ValueToReimburse

The reimbursable value of the expense converted into the company's currency. This represents the reimbursement ceiling or, if the expense is non-reimbursable, this value will be 0.

E

Currency

The currency of the expense.

F

DateInvoice

The date when the expense was incurred.

G

DateCreation

The date the expense was added to Expensya.

H

MerchantName

Name of the supplier (McDonald's, Ibis Hotel, Shell, etc.).

I

MerchantCountry

Country where the expense was incurred.

J

MissionID

The unique identifier of the report (mission) containing the expense.

K

MissionShortId

The unique identifier of the report (mission) within the company containing the expense (blank if it's a mission order).

L

MissionOrderShortId

The unique identifier of the mission order within the company containing the expense (blank if it's not a mission order).

M

MissionType

An integer describing the type of report containing the expense.

N

MissionState

This field describes the state of the report containing the expense (rejected, pending accounting, validated, archived, sent, entered).

O

CategoryName

The name of the category.

P

Category

The type of category. Example: Dining, Accommodation, etc.

Q

ToReimburse

This field indicates if the expense is reimbursable, 'True' or 'False'.

R

IsBillable

This field indicates if the expense is billable to the client, 'True' or 'False'.

S

TvaValue

Value of the expense's VAT.

T

TvaInCurrency

The converted VAT value in the company's currency.

U

VatClaim

Recoverable VAT value.

V

VatClaimPercent

Percentage of VAT recovery.

W

CompanyId

The company's identifier in the Expensya database.

X

CompanyCreationDate

The company's creation date in Expensya.

Y

CountryCode

The country of the employee's company who created the expense.

Z

InFRCompany

The company's location: France or Other countries.

AA

UserID

The unique identifier of the user (employee) who created the expense in.

AB

UserMail

The email of the employee who created the expense.

AC

UserName

The first name and last name of the employee who created the expense.

AD

PayId

The user's identifier.

AE

PayId2

The user's first analytical axis who created the expense.

AF

PayId3

The user's second analytical axis who created the expense.

AG

PayId4

The user's third analytical axis who created the expense.

AH

PayId5

The user's fourth analytical axis who created the expense.

AI

PayId6

The user's fifth analytical axis who created the expense.

AJ

UserCurrency

The currency of the employee who created the expense. This field is empty or equal to "NULL" when the user has the same currency as their company.

AK

UserType

The Expensya role of the employee who created the expense.

AL

UserCreationDate

The date the employee was added/created in Expensya.

AM

LocalCountry

Employee's country. If empty or "NULL," it means the user has the same country as their company.

AN

ProjectName

The name of the expense's project.

AO

ProjectReference

The reference of the expense's project.

AP

ProjectExternalId

The unique identifier of a project.

AQ

ProjectValidatorId

The unique identifier of a project validator.

AR

VehiculeID

In the case of mileage expenses, this column contains the unique identifier of the vehicle (used for the trip) in the Expensya database.

AS

VehicleType

When it concerns mileage expenses, this column contains the type of vehicle used.

AT

ChargeAccount

Expense category charge account. Example: 625100, 625200, etc.

AU

Units

In the case of an expense with guests (external and/or internal), this column contains the number of guests + 1 (the employee is included). Otherwise, if the expense category is accommodation and includes multiple nights, this column contains the number of nights. Otherwise, if there are guests and nights, it will be calculated as follows: Units = (number of guests + 1) * number of nights. Otherwise, it will always be defined as 1.

AV

Guests

Contains the list of unique identifiers of different guests related to an expense (usually an email list).

AW

GuestsFullNames

Contains the list of first names and last names of different guests related to an expense.

AX

Conformity

If the expense doesn't comply with the company's rules, this column contains "False." Otherwise, it contains "True."

AY

BrokenRuleBudget

If the expense doesn't comply with the company's rules, this column contains the rule's ceiling to apply.

AZ

RuleCurrency

This column contains the rule's currency.

BA

Depassement

If the expense doesn't comply with the company's rules, this column contains the difference between the expense's value and the ceiling of the rule applied to this expense.

BB

OneUnitValue

The expense value divided by the number of 'units' (number of nights for accommodations or number of guests in an expense).

BC

RuleDescription

If the expense doesn't comply with the company's rules, this column contains the description of the rule to apply to the expense.

BD

RuleType

If the expense doesn't comply with the company's rules, this column contains the type of rule to apply to the expense.

-Warning.

-Mandatory.

-ReimbursableByCeiling (Ceiling rule).

-ReimbursableByFlatRate (Flat rate rule.)

-Exception request.

BE

RuleStartPeriodDate

This column contains the effective start date of the rule if it's a period rule.

BF

RuleEndPeriodDate

This column contains the effective end date of the rule. Otherwise, it would be empty.

BG

RuleName

If the expense doesn't comply with the company's rules, this column contains the name of the rule to apply to the expense.

BH

ManagerName

The first name and last name of the employee's manager who created the expense.

BI

ReviewerName

The first name and last name of the reviewer of the employee who created the expense.

BJ

InvoiceStartDate

In the case of mileage expenses or travel expenses, this is the departure date.

BK

TravelPolicy

In the case of an interface with a travel actor like Egencia, this field describes the expense's compliance with the company's policies, with values 'Yes' or 'No.'

BL

TravelReasonDescription

For travel expenses and in the case of a non-compliant expense, this column contains the reason why the expense was created.

BM

TravelDetails

For travel expenses, this column contains all the information related to reservations.

BN

DateEnvoi

Date of sending the note for verification or validation.

BO

DateValidation

Date in which the note was validated or rejected.

BP

DateArchivage

Date of archiving the note.

BQ

MileageDistance

The distance traveled if it's a mileage expense.

BR

InvoiceSource

The origin of the expense (web, mobile, email, transaction).

BS

InFR

This field indicates if the expense was made in France or not.

BT

BI1

A field to store user-configurations if requested.

BU

BI2

A field to store user-configurations if requested.

BV

BI3

A field to store user-configurations if requested.

BW

Retard

The delay from the expense date to its creation in Expensya.

BX

VoyageLocal

The nature of the expense regarding the company's location:

-Abroad

- Local

BY

RejectionComment

The comment rejecting the expense.

BZ

PaymentInstrument

The payment method used.

CA

IKConformity

The expense's conformity if it's a mileage expense:

True: if the distance traveled hasn't been modified by the user.

False: otherwise.

CB

VehicleName

In the case of a mileage expense, this column contains the name of the vehicle used for the trip.

CC

RejectorFullName

The name of the user who rejected the expense.

CD

PrimitiveCategories

The primitive category in Expensya.

CE

InvoiceCustomFieldsValues

This column contains a dictionary mapping all the custom fields' values of the expense between the field's name and the corresponding value.

CF

Tags

The list of tags associated with the expense separated by commas.

CG

Label

The name of the expense.

CH

Validators

The list of names and first names of the expense's validators separated by commas.

CI

HorsePower

In the case of a mileage expense, this column contains the fiscal horsepower of the vehicle used for the trip.

CJ

IKRate

In the case of a mileage expense, this column contains the mileage rate used.

CK

StartCity

In the case of a mileage expense, this column contains the departure address for the trip.

CL

EndCity

In the case of a mileage expense, this column contains the arrival address for the trip.

CM

InvoiceDescription

The description of the expense.

CN

CreationYearKm

In the case of a mileage expense, this column contains, for the used vehicle, the number of kilometers of the current year declared outside Expensya.

CO

LastYearDistanceKm

In the case of a mileage expense, this column contains, for the used vehicle, the number of kilometers of the previous year declared outside Expensya.

CP

CategoryES

This field contains the category name in Spanish.

CQ

CategoryNameES

This field contains the category name in Spanish configured in Expensya.

CR

CategoryEN

This field contains the category name in English.

CS

CategoryNameEN

This field contains the category name in English configured in Expensya.

CT

CategoryId

The unique identifier of the category in Expensya.

CU

CategoryParentId

The unique identifier of the parent category in Expensya.

CV

EntityName

This field indicates the group assigned to the expense category.

CW

DelaiRemboursement

The delay between the validation date and the archiving date of the expense.

CX

DelaiValidation

The delay between the sending date and the first validation date of the expense.

CY

DelaiSaisi

The delay between the creation date and the invoice date of the expense.

CZ

DelaiEnvoiManager

The delay between the expense sending date and the manager's validation date.

DA

DateEnvoiManager

This field contains the expense sending date for manager validation.

DB

DateValidationManager

This is the date of the manager's validation of the expense.

DC

IsConsumedBooking

In the case of a travel expense, this field indicates if the reservation is consumed, 'True' or 'False.'

DD

ExceedingValueTTC

The expense's compliance with changes in the TTC (all taxes included) value:

True: if the amount modified by the employee exceeds the amount detected by the OCR.

False: otherwise.

DE

ValueInLocalCurrency

The expense value in the employee's currency.

DF

LocalCurrency

The employee's currency.

DG

HasPhoto

This field indicates if there is a receipt attached to the expense, 'True' or 'False.'

DH

RejectionDate

The date of expense rejection.

DI

ValidatorsMails

The list of emails of the expense's validators separated by commas.

DJ

ValidatorsPayIds

The list of payIds of the expense's validators separated by commas.

DK

ProjectExtraData

This column contains a dictionary mapping all additional data configured at the project level


How did we do?


Powered by HelpDocs (opens in a new tab)