DataSet HotBar - Functional Overview

Introduction

The DataSet HotBar is a dynamic space for working with DataSets and a key component of the DataJet solution.    It offers instant access to commonly used DataSet actions, as well as providing a temporary store for records and segments of interest.   DataSets that are in the HotBar will persist for the life-time of the active session.

This article introduces all functional elements of the DataSet HotBar.

Adding a DataSet to The HotBar

<<TODO - Video - Creating DataSets in the HotBar>>

DataSets can be created in the HotBar by dragging and dropping objects and selections from other workspace areas such as charts, dashboards and the database tree:

Creating DataSets from Fields

Dragging a field from the Database Tree and dropping it on the HotBar will bring up the Add DataSet Row dialog:

This allows a detailed query to be constructed.   The simplest type of query simply selects all records with a particular field value - to create a DataSet of field values, type the field value in the value box and select Save.  The DataSet will appear in the HotBar.

As well as selecting tables and fields, Operators and Logic can all be modified to create the precise query that is required.  

Logic

Operators

Operators	Description	Detail
=	equal to	selects all records where {field value} exactly matches the content of {value box}
<	less than	selects all records where {field value} is less than value in {value box}
>	greater than	selects all records where {field value} is greater than value in {value box}
>=	greater than or equal to	selects all records where {field value} is greater than or equal to value in {value box}
<=	less than or equal to	selects all records where {field value} is less than or equal to value in {value box}
!=	not equal to	selects all records where {field value} is not equal to value in {value box}
between	between	selects all records where {field value} is between the {lower value} and the {upper value}.  Includes records that match {lower value} and {upper value}
within	within	selects all records where {field value} is found in the labels of the result grid produced by the report in the {value box}.  {value box} is automatically populated with applicable reports. Supported Reports The report specified in {value Box} for the within operator must be a 1 dimensional profile.   The labels in the report will be compared to the values in the selected field.
in	in	selects all records where {field value} matches one of the list of values checked in {value box}.
like	like	selects all records where {field value} matches the value in {value box} case insensitive allows a wildcard search to be performed: *cat selects any record where {field value} ends incat cat*   selects any record where {field value} starts with cat  *cat* selects any record where {field value} contains cat
cslike	case sensitive like	selects all records where {field value} matches the value in {value box} case sensitive allows a wildcard search to be performed: *Cat selects any record where {field value} ends in Cat, but does not select records where {fieldvalue} ends in cat cat*   selects any record where {field value} starts with cat (but not CAT, Cat etc) *cat* selects any record where {field value} contains cat
first discrete	first occurence of	selects the first occurrence of each unique value for the specified field See Intrinsic Functions - Overview for more details.
last discrete	last occurence of	selects the last occurrence of each unique value for the specified field See Intrinsic Functions - Overview for more details.

DataSet Fields

DataSet fields have some special properties that allow them to work easily with the HotBar.    Unlike other fields, when a DataSet field is dragged from the Database Tree and dropped on the HotBar, the Add DataSet Row dialog is not displayed - instead a DataSet is created of all records that are IN the dropped DataSet.   This is the equivalent of selecting a {field value} of 1.  Hold down the CTRL key when dragging the field to select {field value} of 0.

Creating DataSets from Reports and Dashboards

DataSets can be created from Result Grids and Charts in Reports and Dashboards.   

To select from a single segment from a chart, click the desired segment - when the segment is highlighted it has been selected - and then drag into the HotBar. 

To select multiple segments, click the desired segments and check that they are highlighted.  Create the DataSet by dragging the drag icon from the report into the HotBar.  An "in" query will be created and the resulting DataSet added to the HotBar.

Clicking on a segment in a chart also highlights the relevant segment in the result grid.  Hold down the CTRL key to select individual rows in the result grid or use the SHIFT key to select a range.  Selected data can be dragged directly from the grid, or from the Drag Icon and then dropped in the HotBar.

Creating DataSets from Saved DataSets and Collections

DataSets can also be dragged into the HotBar from the DataSets Tab.  

Deleting DataSets

To delete a DataSet from the HotBar, select the DataSet to be deleted, and choose Right-Click Delete.  The DataSet will be removed from the HotBar and from the system.   To clear the HotBar of all DataSets, Right-Click on the HotBar and choose Delete All.

Saving DataSets

To store a permanent record of the rules used to create a HotBar DataSet, select the DataSet and choose Right-Click | Save.   The Save DataSet dialog will be displayed.  

By default the DataSet will be added as a new DataSet field on the owner table:

Unchecking the "Save As Field" box will add the new DataSet to the DataSet Tab of the Project Explorer instead.    Within the DataSet Tab, DataSets are automatically grouped by table: 

Once saved, a copy of the DataSet can be dragged from either the Database Tree or the DataSet Tab into the HotBar.

From the DataSet Tab, a copy of a saved DataSet can be added to the owner table as a DataSet field by selecting Right-Click | Save As Field.

Combining DataSets

<<TODO - Quick Snippet - Combining DataSets>>

One of the key features of DataJet is the ability to perform set operations on DataSets.   The HotBar provides a drag and drop interface for these operations.

DataSets can be combined using one of the following mechanisms:

• And
• Or
• Remove

Dropping a DataSet onto a DataSet that is in the DataSet HotBar will bring up the DataSet Drop Menu:

ANDing DataSets

ANDing two DataSets creates a DataSet that contains only the records that are in both DataSets.  In the table below, a 0 represents a records that is NOT in the DataSet, whereas a 1 represents a record that is in the DataSet.    

• Record 1 is not in either DataSet 1 or DataSet 2.
• Record 2 is in just DataSet 1
• Record 4 is in just DataSet 2
• Records 3 and 5 are  in DataSet 1 and Dataset 2

Selecting And will change the existing DataSet so that only records which are also in the dropped DataSet are included.  In the example above, Records 2,3 and 5 are in the existing DataSet (DataSet1).   DataSet2 is dropped on using the AND option.       Records 3 and 5 are in both DataSet1 AND DataSet2 and so the resulting DataSet contains 2 records.

There is no limit on the size of the DataSet that can be manipulated in this way.   DataSets containing hundreds of millions of records will re-combine almost instantly in the DataSet HotBar.

ORing DataSets

ORing two DataSets creates a DataSet that contains records that are in at least one of the DataSets:

Remove DataSet

Selecting Remove from the DataSet Drop menu will remove the records from the dropped DataSet from the original DataSet:

Undo

To undo the last operation on a DataSet, choose Right-Click | Undo. This will restore the DataSet to the point before the last DataSet operation.   Undo can be selected repeatedly, until the DataSet reaches its initial state.

Invalid DataSet

If a DataSet from an unrelated table is dropped onto a HotBar DataSet, the DataSet Count will be displayed as [...].  Selecting Right-Click | Open Copy will open a DataSet Scratch Tab in the Workspace and the invalid DataSet Error message will be displayed (Error calculating dataset.  No intermediate record set from relationship):

Use Engineering | Joins to make a relationship between 2 un-linked tables.   Matching records must be present on each table for a valid link to be formed.

HotBar Right-Click Menu

<<TODO - Video - DataSet Functions>>

Right-Click a DataSet in the DataSet HotBar to show the HotBar Right-Click Menu.     This provides access to all common DataSet operations.

Function

Selecting an item in the function menu will apply an intrinsic function query rule to the existing DataSet. The available intrinsic functions are:

• invert()
• random(X)
• random%(X.XX)
• sample(X)
• top(X)
• bottom(X)
• firstdiscrete(table.field)
• lastdiscrete(table.field)
• highdiscrete(table, table.field)
• topby(table.field, X)
• bottomby(table.field, X)

For more information on intrinsic functions, see Intrinsic Functions - Overview

Resolve At

<<TODO - Video - Queries and Resolution Levels>>

Determines the records that will be counted.  When a DataSet is created in the HotBar,  the DataSet count is shown.    This count relates to the "owner table" or "resolution table" of the DataSet, and is the table from which records are being counted.   Unless explicitly set otherwise, by default, the resolution level is the table from which the DataSet was created, and this is the table that the DataSet is "resolved at".  So in the example below, the first DataSet is "resolved at" the transaction level, and the second DataSet is "resolved at" the customer level:

The first line of the DataSet query shows the resolution level of the DataSet - i.e., which records are being counted.   The first example below is resolved transactions - ie., is counting transaction records.    The second two examples are both resolved at the customer level.

The DataSet HotBar can be used to change the resolution level of a query - i.e., to change what is being counted.  In the example below, the count of customers that are NOT linked to a transaction has been "resolved at" the transaction table.   This returns a count of 0, because the starting DataSet contained only records that had no link to the transaction table:

All possible changes to resolution level are displayed in the Resolve At Menu.    Different options are available depending on the current resolution level, or owner table of the DataSet. 

The Resolve At command puts the existing query into a subset, and ANDs it to all records from the selected table.

Duplicate

Creates another HotBar DataSet as an exact replica of the selected DataSet

Delete

Permanently deletes the DataSet from the HotBar and from the system.    Copies of DataSets that were stored in the DataSet Tab can be recreated as necessary by redragging into the HotBar - any changes that were made to DataSets in the HotBar will be lost unless the modified DataSet is resaved as either a field or a DataSet..

To delete all DataSets in the HotBar, right-click in the HotBar itself and choose Delete All.

Open Copy

Select Right-Click | Open Copy.  A new DataSet Scratch tab will be created from the selected DataSet allowing the Query Rules and the RecordView for the DataSet to be viewed and edited.

The DataSet Scratch tab will have an empty Column Template and a RecordView grid that shows only the record URN:   

Add Columns to the RecordView grid by dragging fields from the Database Tree and dropping them on the grid - only fields from the DataSet owner table can be added to the grid.   To add all fields for the owner table, drag the table object from the Database Tree and drop it on the RecordView grid.

Save

Brings up the Save DataSet dialog.  See Saving DataSets for more detail.