Latest changes to the DAP data model & Redshift Serverless

Our data model is a live animal - continuously growing, changing, adapting and evolving. With each alteration, new problems can be solved in your organisation, more powerful predictions can be made to improve your operations, and more business value can be extracted from the platform. Many of those changes are inspired by input from you, our customers, and your analytical needs. We thank you for continuous input and involvement - your engagement is an essential driver for our ongoing development.

As the data model is still quite young, it will take a while for it to find its final form. It can be challenging to keep track of all the changes that are happening, as they are quite frequent at the moment. Therefore we will attempt to make a habit of providing frequent updates to our community via this channel, hoping that continuous news about new expansions will inspire you to go searching for new exciting insight.

As you know, not all tables are available/relevant to all customers (you won't see data you don't own), but hopefully there will be a little something for all of you in every update.
For complete and the most up to date version of the datamodel documentation, please visit:
https://dbdocs.io/dap-docs/DAP?schema=public&view=relationships
We will do our best to update this documentation as a part of every development task that involves data model changes. We are currently a little behind, admittedly, but are working through the backlog and we should be catching up soon.

With all that said - here's our most recent (and soon-to-come) updates:

Increased frequency for parcel tables:
We already read the parcel streams, and we already store all history. Meaning - if this is data you are interested in for analysis, you could read the data in DAP, rather than maintaining storage of the streams yourself.
So far, we have ingested data from those streams once per day, but very soon we will increase the frequency to hourly, which will give you close to live updates.

This should eliminate most needs for constant updates from RG reports pr_DI_RG_DeviationSummary ("Avviksliste for distrselskap og datointervall med kunder") and pr_RG_TapteOgReturnertePakker ("Kvitteringsliste siste status").

Recent changes to the Retail data model (løssalgsdata):

The retail data model has been revised. Some of the additions were motivated by a popular RG report, RG_LS_Fakturajournal (Fakturajournal i fakturakjøring), as a few of its columns were missing in our data model. The usecases covered by that RG report should now be a lot more easily solved via DAP.

• f_RetailerQuantity was expanded

  • New columns: returned_quantity and reported_quantity

  • This table aggregates (sums the quantity according to a given distr_date) data from “LsHistAntall” where:

    AntallTypeId = 1 is “ordered” (bestilt)
    AntallTypeId = 2 is “regulated” (regulert)
    AntallTypeId = 3 is “reported” (reklamert)
    AntallTypeId = 4 is “returned” (returnert)

• New table invoice
  Holds invoice specific data, such as invoice_nr (fakturanummer), invoice_date (Fakturadato), invoice_due_date (Forfallsdato), invoice_method (FaktForsendelsesmaate), amount (Beloep) etc.

• New table invoiceline
  Holds aggregated data of invoiced quantity a retailer has received for a product (En aggregering av antall for en forhandler på et produkt for en faktura.) e.g., sold_quantity, ordered_quantity, credited_quantity, returned_quantity, including Vat_rate (MvaSats), rate_percentage (SatsProsent), amount (Beloep)

New table, f_salarydraft:
The salary draft table informs among other things about the amount of work the courier should be paid for, and the amount of payment. Data consists of manual, as well as non-manual entries to the salary draft. Note that when the entries are not manual, salary_draft_status_id and salary_draft_status_name are missing. Additionally, most of the missing values in columns notes, invoice_location_id, cost_location_id, salary_draft_id and original_salary_draft_id occur when the entries are not manual. Data exposed only for distribution companies.

For more in-depth documentation, see

https://dbdocs.io/dap-docs/DAP?table=f_salarydraft&schema=public&view=table_structure

Sequence numbers coming to Module and Address tables:
Within the next few days we will be adding route_module_sorting (RuteModul.Sortering, The order sequence of modules in a route) to the Module table and delivery_point_sorting (DistrPunkt.Sortering, The order sequence of delivery points in a module) to the Address table, for historical deliveries. This addition should enable more powerful analyses of your routes' efficiency.

Additionally, column_name "delivery_point" in Address table will be changed to "delivery_point_id" (LevPunktId) for consistency (this column is named delivery_point_id in f_planneddelivery)

A heads up regarding Redshift Serverless:

We are working on setting up Redshift Serverless - currently the changes are live on dev and staging environments and going through testing. This change should improve the experience for our customers, and also unlock a few new features on the platform.
We will send out a separate email to our current customers shortly with more details about how the change will affect you, and how you can contribute with a few tests on staging over the next few days before we release the changes to production asap.

Other changes and bug fixes:

• Future date range for the planned delivery table was reduced from 730 to 31 days, which should be sufficient range for capturing all planned parcels.

• Fixed an issue with parcel event data model, where location_context_id was null for the majority of the data.

• Changed route_number to route_nr in all relevant tables for consistency

• total_with_subscription (AntMedAbonnement) & total_without_subscription (AntUtenAbonnement) columns were added to f_Adenclosure

• New table Productitem (Varetype). This table holds data about what items a delivery consists of - available to product companies.

• New table Productgroup can be used to filter products according to product groups (ProduktGruppeId is now product_group_id in DAP)

We love feedback!
Do any of these additions sound exciting to you?

Let us know how you use the data, and what value you are getting from it!

Best regards
Eirik Lyngvi, Head of Analytics