Release Note: DataJet v5.9.22

Key Features

The key features of this release are: 

• Filtered Joins - it is now possible to use a filter to control which records are used in a join
• Combined Profile Report - allows individual profiles to be joined together to form a single output
• New Dashboard Layout - smaller grid, allowing for greater layout flexibility
• Enhanced Export to support UPSERTING of data - performance improvements and additional verification
• Project Descriptions - Project description available in Manage Project and Open Project dialogs to support management of many projects
• Table Rename - prototype with limited scope

Overview

Documentation

• Join Layers
• Update IF documentation

API

• ModifiedMethod: ExecuteStoredScript
• ModifiedMethod: ExportIntoTable
• ModifiedMethod: AddJoin
• ModifiedMethod: AddJoinLayer
• NewMethod: RenameTable

Engineering, Reporting and Analytics

• FIX: Multi-Function profile returns wrong total for a function measure if asint applied as post-function
• Separation of “null” row, and “rows with null data” in profile and multi-function profile
• Additional Dashboard Layout - 18x8
• Implement "favourInteger" in Query Matrix
• LOWER(A) and UPPER(A) functions now accept continuous string

Scripting & Automation

• FIX : Copy Paste Issue in Script Editor
• FIX : Pulling a stored script from Script Editor | Scripts | Stored Scripts doesn't include the realm
• FIX : PUSH script appends Last\_Saved\_Date \(e.g., \[09/09/2023 13:53:23\] to script name 
• FIX: ExecuteStoredScript not returning scriptID in GetScriptResult if no scriptID provided
• Count([Table],*]) added to IF conditions

Database Build, Export and Management 

• FIX : ExportIntoTableEx causing "discrete field file size error...could not load file"
• FIX : DetachAllTables error : Method Not Executed  
• FIX : DeleteTable is not cleaning up underlying data files 
• Ability to Filter Joins
• Improved error reporting for InjectPackage  and InjectTables
• Ability to add a project description, displayed in project list dialogs

General Desktop UI

• FIX : Treeview shows no counts for tables with > 2B rows 
• FIX : Context panel needs to be refreshed when a new project is opened
• Carry 'saved' name to Save dialog for dataset in hotbar

Breaking Changes

There are no breaking changes in this release.

BUG Fixes

Known Issues/Constraints

Downloading and running the latest version of the desktop client from https://download.datajet.app/desktop may give a windows security message.

Changes to API Methods

Feature Details

PROTOTYPE: RenameTable

A limited ability to rename tables is now available.   This is a shallow rename, and does not alter any constructors.   Joins to the table will be deleted and will need to be manually recreated.

Combined Profile Report

A new profile report type is available, allowing multiple simple profiles with similar row labels to be quickly joined together to create a combined profile.    Profiles from different tables can be joined together in a single result grid.

Separation of “null” row, and “rows with null data” in profile and multi-function profile

New options have been added to the profile and multi-function profile "Options" dialog:

• Hide Totals
• Hide Null Category

Enhanced Export to support UPSERTING of data

Upserting data is where the following happens to an existing table as part of an update process:

• Some records may be kept
• Some records may be deleted
• Some records may be modified
• Some records may be added

See (How to - Upsert Data for further details on upserting)

ExportIntoTable has been modified to automatically use the ExportIntoTableEx approach when it is appropriate (i.e., when there are many fewer rows being deleted/modified than kept).   This means that ExportIntoTable can be used for both stages of the Upsert process. (Previously ExportIntoTableEx was sometimes recommended for the first Export).   The new process to use for upserting data is as follows:

Check base table BASE exists 

Load UPSERT table 

(create keys and join) 

//Calculate the records to keep on the BASE table
create KEEPDATASET as field on BASE

//Export Records to keep from BASE into NEWTABLE
ExportIntoTable KEEPDATASET of BASE into NEWTABLE

//Append new and modified records in the UPSERT table into NEWTABLE
ExportIntoTable (APPEND) UPSERT into NEWTABLE

//Rename NEWTABLE to BASE
DeleteTable BASE
ExportIntoTable NEWTABLE into BASE

//Clean up temporary tables
DeleteTable NEWTABLE
DeleteTable UPSERT

Additional Dashboard Layout - 18x8

Dashboard layout options are now offered as grid sizes, and the maximum grid size has been increased to 18x8:

NOTE: A maximum of 24 reports can be added to a dashboard.   The increased number of empty slots in the 18x8 dashboard is to provide greater control over report layout.

"Favor Integer" available in Query Matrix

Favor Integer can now be applied to a query matrix.   This will display cells that have no non-zero digits after the decimal point as Integer.  (Note - if the cell has non-zero decimal digits, it will still be displayed as decimal)

Note - to force a decimal column to be displayed as integer, use Post-Processing, to replace the source column with a ColumnMath column of type Integer.

Count([Table,*]) added to IF conditions

The number of rows in a table can be evaluated in a conditional IF statement using Count([TableName],*) notation:

{
  "method": "IF",
  "condition": "Count([BikeTripData],*)>90000000",
  "project": "BikeData2"
}

Ability to Filter Joins

When joining two tables, it is now possible to select which records in the foreign table will be included in the join by applying a filter:

Improved error reporting for InjectPackage  and InjectTables

Increased error checking has been added to InjectPackage and InjectTables to help these proceses.

InjectTables has been modified to formally block injecting a table into it's source project.

See Deep Dive - Sharing Data by Injection for more details on injecting data.

Ability to add a project description, that is displayed in project list dialogs

Project descriptions can now be added as part of the CreateProject method:

{
  "method": "CreateProject",
  "description": "Sample Bike Project for testing",
  "project": "BikeData2_smp",
  "name": "BikeData2_smp"
}

These description will be displayed in the Manage Project and Open Project dialogs:

It is also possible to specify the project description using the SetProjectProperties method:

{
  "method": "SetProjectProperties",
  "description": "Version 5.9 demo database",  
  "project": "Demonstration"
}

Carry 'saved' name to Save dialog for dataset in hotbar

If a dataset has been renamed in the hotbar, right-clicking and choosing save will carry the new name into the save dialog: