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, this column contains the unique identifier of the vehicle (used for the trip) in the Expensya database. |
AS | VehicleType | When it concerns mileage, 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 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. |
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: True: if the distance traveled hasn't been modified by the user. False: otherwise. |
CB | VehicleName | In the case of a mileage, 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, this column contains the fiscal horsepower of the vehicle used for the trip. |
CJ | IKRate | In the case of a mileage, this column contains the mileage rate used. |
CK | StartCity | In the case of a mileage, this column contains the departure address for the trip. |
CL | EndCity | In the case of a mileage, this column contains the arrival address for the trip. |
CM | InvoiceDescription | The description of the expense. |
CN | CreationYearKm | In the case of a mileage, 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, 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 |
