Skip to main content

Data lake exports

EVA is an event-driven platform. Everything from transactions and returns to stock movements and mutations is logged and registered as an event in real-time. Using the data from these events you can create your own custom dashboards and reports.

Pushing data to a data lake​

The most flexible and future-proof way of creating dashboard and reports based on your data, is using a data lake. A data lake is a single store of enterprise data including raw copies of source system data. This setup has multiple benefits over a traditional database or data warehouse:

Scalable Unlike a traditional data warehouse, data lakes offer scalability, typically at lower costs.

Versatile A data lake can store both structured and unstructured data from diverse sources. In other words, it can store XML, logs, multimedia, sensor data, chat, social data, binary, and people data.

Schema Flexibility For traditional schema, you need to have your data in a structured format. Traditional data warehouse products are schema based. But for analytics, this could prove to be a glitch as the data needs to be analyzed in its raw form.

Setting up a data lake, including vendor selection and contracting, is a client responsibility. Of course, New Black can assist in this process. We recommend using a Standard general-purpose v2 Azure storage account.

Setting up your data lake​

There are several kinds of data lake integrations possible. These are all described, with their own specific settings, here below. In order to enable the settings, we do need to enable a plugin behind the scenes. Please reach out to your Strategic Lead.

Azure​

To set up your data lake, make sure you have a regular V2 Azure storage account. EVA needs your account's information to export your data. The following settings are absolutely necessary:

SettingDescription
Azure:DataLake:AccountNameAzure Account name
Azure:DataLake:AccountKeyAzure Account Key
Azure:DataLake:FileSystemThe "container" name in the storage account
Azure:DataLake:FolderDesired folder structure, for example: RAW/EVA will create a RAW folder with an EVA subfolder
Azure:DataLake:ServiceUrlYour Azure Service URL

AWS​

The following settings apply if you make use of AWS data lake. These settings make use of the same cron jobs to trigger the exports (see Config below), regardless of the Azure name.

SettingDescription
AWS:DataLake:RegionThe region where the S3 bucket lives.
AWS:DataLake:S3BucketThe name of the S3 bucket to store the files in.
AWS:DataLake:AccessKey-
AWS:DataLake:SecretKey-
AWS:DataLake:Folder(optional) The folder to store the files in the bucket. If not set, we use the root folder.

Google BigQuery​

The following settings apply if you make use of Google BigQuery data lake. These settings make use of the same cron jobs to trigger the exports (see Config below), regardless of the Azure name.

SettingDescription
GCP:DataLake:BucketThe name of the bucket to store the files in.
GCP:DataLake:CredentialThe credential in JSON format.
GCP:DataLake:Folder(optional) The folder to store the files in the bucket. If not set, we use the root folder.

Data Pack​

EVA exports the following data to your data lake:

  1. Orders
  2. Financial periods
  3. Financial events
  4. *Products
  5. *Replenishment products
  6. *Stock
  7. *Stock mutations
  8. *Organization units
  9. Organization unit sets
  10. General ledgers
  11. *Prices
  12. Sales invoices
  13. Users
  14. Fulfilment results - this is a very heavy export. Either manually enable it with DataLake:ExportFulfillmentResults or rely on Admin Suite's Fulfilment chapter to view the results.
  15. Shipments
  16. *User-OU relations
  17. User tasks
  18. *User cards
  19. Event ledgers
  20. Discounts
  21. Discount campaigns
  22. Discount layers
  23. Wishlists

Each data pack preceded by a * is activated by means of a scheduled task (see Config), which means their frequency is configurable. All others will stream towards your data lake based on changes.

Reporting and data

For any sort of reporting or reconciliation we recommend that you use data lake exports, and not APIs. Furthermore, if there's data you wish to include that are not mentioned in the above list, please raise this through a sprint.

Event ledgers​

Aside from the above list of events, EVA also facilitates the export of event ledgers. Because you might not want your DataLake to be stuffed with event ledgers, you'll need to explicitly enable this by switching DataLake:ExportEventLedger to true.

Event ledger types
- AuthenticationAttempt
- Login
- Logout
- OrderCreated
- OrderLinesPlaced
- OrderAutoCompleted
- ManualReturnOrderCreated
- PaymentCreated
- PaymentFinalized
- CashDrawerOpened
- CashTransaction
- CashDrawerClosed
- CashDrawerClosedTimeout
- CashDrawerClosingDeviation
- CashDrawerOpeningDeviation
- UserTaskStarted
- UserTaskCompleted
- UserDetachedFromUserTask
- UserTaskIgnored
- DiscountUsed
- ManualDiscountGiven
- InvoiceCreated
- InvoicePrinted
- InvoiceDuplicatePrinted
- InvoiceMailed
- InvoiceDownloaded
- InvoiceArchived
- ReceiptPrinted
- ReceiptDuplicatePrinted
- ReceiptPrintFailed
- ReceiptReprinted
- ReceiptReprintFailed
- MessageTemplateUpdated
- SettingUpdated
- EVAInstanceStarted
- FinancialPeriodClosed
- FinancialReportingPeriodClosed
- FinancialPeriodAuditExported
- FiscalPeriodArchived
- CashExpense
- CashDeposit
- CashCorrection
- CashMovement
- UserAttachedToRole
- UserRemovedFromRole
- FunctionalityAddedToUser
- FunctionalityRemovedFromUser
- FunctionalityAddedToRole
- FunctionalityRemovedFromRole
- OrganizationUnitChanged
- OrganizationUnitCreated
- XReport
- ZReport
- FincialPeriodClosingImpedimentIgnored

Config​

Most data packs are exported by default; you can enable the other exports by setting up the following cron tasks:

  • EVA.DataLake.Tasks.ExportPriceLists
  • EVA.DataLake.Tasks.ExportProducts
  • EVA.DataLake.Tasks.ExportOrganizationUnits
  • EVA.DataLake.Tasks.ExportReplenishmentProducts
  • EVA.DataLake.Tasks.ExportStock
  • EVA.DataLake.Tasks.ExportStockMutations
  • EVA.DataLake.Tasks.ExportUserStoreRelations
  • EVA.DataLake.Tasks.ExportUsercards

These are all full exports and are not necessarily exported often. You may want to set these to run at midnight for example. An exception to this however is EVA.DataLake.Tasks.ExportStockMutations, which you may want to run every 10 or 15 minutes for example.

Examples​

Take into account that the following lists of examples are just that: examples. They give a realistic view of what a data export in a fully configured environment might look like, but they can be outdated and may be missing data which you currently are getting from your exports. For a comprehensive view of all exportable properties, see the Overview of all exportable properties below.

{
    "ID": "FinancialPeriodID",
    "FinancialPeriodNumber": null,
    "PreviousFinancialPeriodID": "PreviousFinancialPeriodID",
    "OpeningTime": "2021-06-12T07:19:47.963",
    "ClosingTime": null,
    "AutoClosed": false,
    "Status": "Open",
    "OrganizationUnitID": "OUID",
    "User": {
        "ID": "UserID",
        "Nickname": "UserNickname",
        "EmailAddress": "UserMailAddress",
        "FirstName": "UserFirstName",
        "LastName": "UserLastName",
        "Gender": null,
        "LanguageID": "fr",
        "CountryID": "FR",
        "Salutation": null
    },
    "CashDeposits": [],
    "CashJournals": [{
            "FinancialPeriodID": "FinancialPeriodID",
            "PreviousCashJournalID": "PreviousFinancialPeriodID",
            "PaymentTypeID": "PaymentTypeID",
            "PaymentType": {
                "PaymentMethodID": "PaymentMethodID",
                "Name": "Cash",
                "Code": "CASH",
                "LedgerClassID": null,
                "BackendRelationID": null,
                "Category": "Cash",
                "CashJournalMethod": "OpenAndClose"
            },
            "CurrencyID": "EUR",
            "Type": "Default",
            "OpeningTime": "2021-06-12T07:28:07.453",
            "ClosingTime": "2021-06-12T17:29:54.487",
            "OpeningAmount": 491.8800,
            "ClosingAmount": 490.8800,
            "OpeningDeviation": 0.0000,
            "ClosingDeviation": null,
            "StationID": "StationID",
            "DeviceID": "DeviceID",
            "OpeningDetailsObject": {
                "Coins": {
                    "1": 2,
                    "2": 7,
                    "0.01": 3,
                    "0.02": 0,
                    "0.05": 3,
                    "0.1": 7,
                    "0.2": 0,
                    "0.5": 0
                },
                "BankNotes": {
                    "5": 1,
                    "10": 17,
                    "20": 15,
                    "50": 0,
                    "100": 0,
                    "200": 0,
                    "500": 0
                },
                "CashDeposits": {}
            },
            "ClosingDetailsObject": {
                "Coins": {
                    "1": 1,
                    "2": 7,
                    "0.01": 3,
                    "0.02": 0,
                    "0.05": 3,
                    "0.1": 7,
                    "0.2": 0,
                    "0.5": 0
                },
                "BankNotes": {
                    "5": 1,
                    "10": 17,
                    "20": 15,
                    "50": 0,
                    "100": 0,
                    "200": 0,
                    "500": 0
                },
                "CashDeposits": {},
                "Data": null
            }
        }
    ],
    "Expenses": [],
    "CashCorrections": [],
    "CurrencyID": "EUR",
    "CostPriceCurrencyID": "EUR",
    "FinancialPeriodAuditID": "EUW",
    "GenerationTime": "2021-06-12T18:00:29.3443732Z"
}
{
    "GenerationTime": "2021-05-07T08:55:06.1021798Z",
    "ID": "StockMutationID",
    "Product": {
        "ID": "ProductID",
        "Name": "ProductName",
        "BackendID": "ProductBackendID"
    },
    "OrganizationUnit": {
        "IsDeleted": false,
        "Description": null,
        "Notes": null,
        "ParentID": "OrganizationUnitParentID",
        "RegisterCashLimit": 1000.0000,
        "SafeCashLimit": null,
        "BackendRelationID": "OrganizationUnitBackendRelationID",
        "BackendCompanyID": "OrganizationUnitBackendCompanyID",
        "BranchNumber": "OrganizationUnitBranchNumber",
        "GlobalLocationNumber": null,
        "Address": {
            "AddressedTo": null,
            "Street": "OrganizationUnitStreet",
            "HouseNumber": "OrganizationUnitHouseNumber",
            "Address1": null,
            "Address2": null,
            "ZipCode": "OrganizationUnitZipCode",
            "Subdistrict": null,
            "District": null,
            "City": "OrganizationUnitCity",
            "State": null,
            "Region": null,
            "CountryID": "DE"
        },
        "Latitude": OrganizationUnitLatitude,
        "Longitude": OrganizationUnitLongitude,
        "Type": ["Shop", "Pickup", "EVA"],
        "Status": "Open",
        "Subnet": "OrganizationUnitSubnet",
        "BankAccount": null,
        "VatNumber": null,
        "RegistrationNumber": null,
        "EmailAddress": "OrganizationUnitMailAddress",
        "PhoneNumber": "OrganizationUnitPhoneNumber",
        "UseForAccounting": true,
        "IpAddress": "OrganizationUnitIpAddress",
        "CountryID": "DE",
        "LanguageID": "de",
        "CurrencyID": "EUR",
        "CostPriceCurrencyID": "EUR",
        "TimeZone": "Europe/Berlin",
        "Region": null,
        "CostPriceCalculationMethod": "Manual",
        "ID": "OrganizationUnitID",
        "Name": "OrganizationUnitName",
        "BackendID": "OrganizationUnitBackendID"
    },
    "SourceStockLabel": "Sellable",
    "DestinationStockLabel": "Sellable",
    "Quantity": 2,
    "UnitCost": 21.2500,
    "Reason": "FullStockCount",
    "Remark": "OptionalRemark",
    "OrderLineID": "OrderLineID",
    "BackendSystemID": null,
    "BackendID": null,
    "Ledgers": [{
            "StockLabel": "Sellable",
            "Quantity": 2
        }
    ]
}

File paths​

DataPath
Financial events/financialevents/Year/Month/Day/file-batch.json
Financial periods/financialperiods/FinancialPeriodID/Year/Month/Day/file.json
General ledgers/generalledger/Year/Month/Day/file-batch.json
Orders/orders/OrderNumber/Year/Month/Day/file.json
Invoices/invoices/InvoiceID/Year/Month/Day/file.json
Organization units/organizationunits/Year/Month/Day/file.json
Organization unit sets/organizationunitsets/Year/Month/Day/file.json
Prices/prices/PriceListID/Year/Month/Day/file.json
Products/products/Year/Month/Day/file-batch.json
Stock/stock/OrganizationUnitID/Year/Month/Day/file.json
Stock mutations/stockmutations/StockMutationID/file.json
Event ledgers/eventledger/Year/Month/Day
Users/users/{Year}/Month/Day
Fulfilment results/fulfillmentresults/orderID/year/month/day/guid.json
User cards/usercards/Year/Month/Day/guid.json
Discounts/discounts/Year/Month/Day
Discount campaigns/discountcampaigns/Year/Month/Day
Discount layers/discountlayers/Year/Month/Day
Wishlists/wishlists/Year/Month/Day

Overview of all exportable properties​

We've shown some real-life examples of our data lake objects out of our own environment, but if you really want to dive into it, the following lists show every single possible property for each export. This way you can ascertain what properties you can expect and map to. 

{
  "GenerationTime": "2023-05-06T00:00:00",
  "ID": "string",
  "UserAgent": "string",
  "BackendID": "string",
  "BackendSystemID": "string",
  "Properties": 5,
  "Description": "string",
  "Type": "string",
  "GroupID": "80a3944f-5f4d-4a74-aad4-2b4f5a3b8b79",
  "CreationTime": "2023-05-06T00:00:00",
  "LastModificationTime": "2023-05-06T00:00:00",
  "Remark": "string",
  "CurrencyID": "string",
  "IsCompleted": true,
  "IsPaid": false,
  "IsShipped": false,
  "PaymentDate": "2023-05-06T00:00:00",
  "ShipmentDate": "2023-05-06T00:00:00",
  "CompletionDate": "2023-05-06T00:00:00",
  "InvoiceDate": "2023-05-06T00:00:00",
  "PlacementDate": "2023-05-06T00:00:00",
  "BillingAddress": {
    "FirstName": "string",
    "LastName": "string",
    "AddressedTo": "string",
    "HouseNumber": "string",
    "Address1": "string",
    "Address2": "string",
    "ZipCode": "string",
    "Subdistrict": "string",
    "District": "string",
    "City": "string",
    "State": "string",
    "Region": "string",
    "CountryID": "string",
    "Street": "string"
  },
  "ShippingAddress": {
    "FirstName": "string",
    "LastName": "string",
    "AddressedTo": "string",
    "HouseNumber": "string",
    "Address1": "string",
    "Address2": "string",
    "ZipCode": "string",
    "Subdistrict": "string",
    "District": "string",
    "City": "string",
    "State": "string",
    "Region": "string",
    "CountryID": "string",
    "Street": "string"
  },
  "FallbackShippingAddress": { // Only applicable in case of Click & Collect
    "FirstName": "string",
    "LastName": "string",
    "AddressedTo": "string",
    "HouseNumber": "string",
    "Address1": "string",
    "Address2": "string",
    "ZipCode": "string",
    "Subdistrict": "string",
    "District": "string",
    "City": "string",
    "State": "string",
    "Region": "string",
    "CountryID": "string",
    "Street": "string"
  },
  "Customer": {
    "ID": "string",
    "BackendID": "string",
    "BackendSystemID": "string",
    "Nickname": "string",
    "EmailAddress": "string",
    "FirstName": "string",
    "LastName": "string",
    "Gender": "string",
    "DateOfBirth": "2023-05-06T00:00:00",
    "PlaceOfBirth": "string",
    "PhoneNumber": "string",
    "LanguageID": "string",
    "CountryID": "string",
    "Salutation": "string",
    "CustomFields": [
      {
        "CustomFieldID": 5,
        "Name": "string",
        "BackendID": "string",
        "Value": {}
      }
    ]
  },
  "Lines": [
    {
      "ID": "string",
      "ParentID": "string",
      "ReturnedOrderID": "string",
      "ReturnedOrderLineID": "string",
      "Description": "string",
      "IsExported": true,
      "CreationTime": "2023-05-06T00:00:00",
      "LastModificationTime": "2023-05-06T00:00:00",
      "UnitPrice": 1.23,
      "UnitPriceBeforeCorrection": 1.23,
      "UnitPriceCorrectionReason": {
        "ID": "string",
        "Name": "string",
        "Description": "string"
      },
      "OriginalUnitPrice": 1.23,
      "OriginalUnitPriceInTax": 1.23,
      "UnitCost": 1.23,
      "TaxRate": 1.23,
      "CurrencyID": "string",
      "CostPriceCurrencyID": "string",
      "QuantityOrdered": 5,
      "QuantityCancelled": 5,
      "QuantityShipped": 5,
      "QuantityCommitted": 5,
      "QuantityExported": 5,
      "QuantityDelivered": 5,
      "QuantityReserved": 5,
      "TotalQuantityToShip": 5,
      "IsShipped": true,
      "IsInvoiced": false,
      "Type": "string",
      "Discounts": [
        {
          "DiscountID": "string",
          "DiscountBackendID": "string",
          "DiscountOrderLineID": "string",
          "Amount": 1.23,
          "CompliancyRule": "string"
        }
      ],
      "Product": {
        "ID": "string",
        "Name": "string",
        "BackendID": "string",
        "LedgerClassID": "string",
        "BackendSystemID": "string",
        "ManufacturerID": "string"
      },
      "SoldBy": {
        "ID": "string",
        "BackendID": "string",
        "BackendSystemID": "string",
        "Nickname": "string",
        "EmailAddress": "string",
        "FirstName": "string",
        "LastName": "string",
        "Gender": "string",
        "DateOfBirth": "2023-05-06T00:00:00",
        "PlaceOfBirth": "string",
        "PhoneNumber": "string",
        "LanguageID": "string",
        "CountryID": "string",
        "Salutation": "string",
        "CustomFields": [
          {
            "CustomFieldID": 5,
            "Name": "string",
            "BackendID": "string",
            "Value": {}
          }
        ]
      },
      "SerialNumber": "string",
      "IsCarryOut": true,
      "IsDelivery": false,
      "IsReservation": true,
      "IsOrdered": true,
      "RequestedDate": "2023-05-06T00:00:00",
      "AvailabilityStatus": "string",
      "ExpectedAvailabilityDate": "2023-05-06T00:00:00",
      "Remark": "string",
      "DiscountAmount": 1.23,
      "ShippingMethod": "string",
      "CustomFields": [
        {
          "CustomFieldID": 5,
          "Name": "string",
          "BackendID": "string",
          "Value": {}
        }
      ],
      "ProductVariationLines": [
        {
          "ID": "string",
          "Label": "string",
          "Value": "string"
        }
      ],
      "ProductRequirements": [
        {
          "ID": "string",
          "Name": "string",
          "Value": "any valid JSON value"
        }
      ],
      "Amounts": {
        "TotalAmountInTax": 1.23,
        "TotalTaxAmount": 1.23,
        "TotalAmount": 1.23,
        "NetTotalAmountInTax": 1.23,
        "NetTotalTaxAmount": 1.23,
        "NetTotalAmount": 1.23,
        "DiscountAmount": 1.23
      },
      "ReturnReason": {
        "ID": "string",
        "Name": "string",
        "BackendID": "string"
      },
      "TaxDetails": [
        {
          "Name": "string",
          "Type": "string",
          "Rate": 1.23,
          "Amount": 1.23
        }
      ]
    }
  ],
  "Payments": [
    {
      "ID": "string",
      "BackendID": "string",
      "BackendSystemID": "string",
      "IsConfirmed": true,
      "IsRefund": false,
      "Code": "string",
      "Description": "string",
      "Amount": 1.23,
      "PaidAmount": 1.23,
      "CurrencyID": "string",
      "PaymentDate": "2023-05-06T00:00:00",
      "PaymentMethod": "string",
      "BackendRelationID": "string",
      "DeviceID": "string",
      "CustomFields": [
        {
          "CustomFieldID": 5,
          "Name": "string",
          "BackendID": "string",
          "Value": {}
        }
      ]
    }
  ],
  "TotalAmountIncludingTaxes": 1.23,
  "OriginatingOrganizationUnit": {
    "ID": "string",
    "Name": "string",
    "BackendID": "string"
  },
  "SoldToOrganizationUnit": {
    "ID": "string",
    "Name": "string",
    "BackendID": "string"
  },
  "SoldFromOrganizationUnit": {
    "ID": "string",
    "Name": "string",
    "BackendID": "string"
  },
  "ShipToOrganizationUnit": {
    "ID": "string",
    "Name": "string",
    "BackendID": "string"
  },
  "ShipFromOrganizationUnit": {
    "ID": "string",
    "Name": "string",
    "BackendID": "string"
  },
  "PickupOrganizationUnit": {
    "ID": "string",
    "Name": "string",
    "BackendID": "string"
  },
  "CustomStatus": [
    "string"
  ],
  "OrderLedgers": [
    {
      "ID": 5,
      "Type": "string",
      "Description": "string",
      "OldValue": "string",
      "NewValue": "string",
      "CreationTime": "2023-05-06T00:00:00"
    }
  ],
  "CustomFields": [
    {
      "CustomFieldID": 5,
      "Name": "string",
      "BackendID": "string",
      "Value": {}
    }
  ],
  "GiftWrapping": {
    "WrapOrder": false,
    "WrapIndividually": true,
    "Message": "string",
    "GreetingCardProductID": "string",
    "Wraps": [
      {
        "Lines": [
          {
            "OrderLineID": "string",
            "Quantity": 5
          }
        ],
        "GreetingCardProductID": "string",
        "Message": "string"
      }
    ]
  },
  "Amounts": {
    "Open": {
      "InTax": 0.0,
      "PendingInTax": 0.0,
      "Capturable": 0.0,
      "CapturedInTax": 0.0,
      "Amount": 0.0,
      "Foreign": 0.0,
      "ForeignInTax": 0.0
    },
    "OpenAmountIsZero": true,
    "CurrencyID": "EUR",
    "Total": {
      "Amount": 0.0,
      "InTax": 0.0,
      "Tax": 0.0,
      "Foreign": 0.0,
      "ForeignInTax": 0.0
    },
    "UnroundedTotal": {
      "Amount": 0.0,
      "InTax": 0.0,
      "Tax": 0.0,
      "Foreign": 0.0,
      "ForeignInTax": 0.0
    },
    "Paid": {
      "Pending": 0.0,
      "Amount": 0.0,
      "Captured": 0.0,
      "Foreign": 0.0
    },
    "Taxes": [],
    "Types": {},
    "Discounts": {
      "TotalDiscountAmount": 0.0,
      "PerDiscount": null
    }
  }
}

Manual re-exporting​

By using the DataLakeBackfill service you can manually re-export specific Data lake documents.

Authorization

You need a specific (unscoped) functionality on your role called DataLakeBackfill in order to be able to use the datalake backfill service(s).

These types are currently supported:

  • FinancialPeriods = 1
  • Orders = 2
  • Invoices = 3
  • Users = 4
  • Discounts (this will trigger all connected data to the discount) = 5

Specify either a single ID, a list of IDs, or a date/time range From and To (inclusive).

As mandatory safety feature, specify the MaxNumberOfDocuments you'd like to run this for. When the amount of documents through above selection exceeds this number, this service will export NOTHING and return to you an error with the amount of documents it prevented from exporting, so you can adjust your selection or expand your max.

danger

Triggering large amounts of data may impact system performance!

Once done, the ID returned in the response can be used for subsequent services DataLakeBackfillStatus and DataLakeBackfillCancel.