Dataset Queryable Items allow you to specify the data you want to include in your query and the hierarchical relationships that tie the data together. The Items you can query in a Dataset come from 3 main sources; Entities, History and Queries themselves. The last one allowing you to re-query queries in a dataset.
Dataset Queryable Items Features
The items you can query in a Dataset are represented here in a hierarchical view that easily shows at a glance the structure of the data you are retrieving. This is manipulated using our standard TreeView for easily creating hierarchical structures. You can specify the relationships between different queryable items and how they join together. The Items you can query come from the following main sources:
Entities This allows you to query all entities in the system, and all their fields, including custom entities and those created through our API.
Audit History This allows you to query all of the audit history associated with any entity. The audit history itself contains the following main properties:
User Modified By
DateTime stamp
ChangeType
Update
Insert
Delete
Property Name
Old Value
New Value
Old Link Value
New Link Value
Queries This allows you to query any public SQL Wrapper in the system, allowing you to incorporate that set of data into your query whether it is created from a Dataset itself or a hand written SQL query.
Being able to query SQL Wrappers (which every Dataset creates one of anyway to retrieve their data [see here for more info]) allows you to easily build up a library of re-usable queries. These can be used by any user to get results but also to query themselves without having to understand any of your underlying data structure.
Datasets are responsible for defining a single set of data, as such you can't create multiple Queryable Items that aren't related to each other. They must all be related in some way together (hence the hierarchical view) otherwise they are really independent sets of data. If you want to merge various sets of data together you can create those sets individually using DataSets and merge them using our other tools such as Query Groups or VennSets.
Dataset Queryable Items Properties
Whenever you create a Dataset Queryable Item the system pops up a dialog box allowing you to choose the properties of the item you want to query. This has the following properties:
Name
Description
[Required] Queryable Item Type
The type of item you want to query. This can be an Entity, History, or Query.
Queryable Item
The item of the specified type that you want to query
Queryable Item Option
Some Queryable Items contain sub options. Permissions is an example of this. If you select a Permission item to query of the Entity type, this will display a set of options for which entity type permission you want to query
Replicate For All
If the queryable item type is a type that has sub Queryable Item Options, such as permissions, then this indicates you want to replicate your query for every option and union the results together.
[Required] Name
This is the user friendly name of the queryable item as it will appear in the treeview. This makes looking at the data structure very easy at a glance. It is also the name the user will see if they are able to open a row of data from this item type
Join Type
If this Queryable Item is a child of any other Queryable Item, then this specifies the type of join this queryable item will have to the overall dataset:
Exclusive Equivalent to Inner Join in SQL
Inclusive Equivalent to Outer Join in SQL
CrossJoin Equivalent to Cross Join in SQL
You can always edit these properties for any Queryable Item by selecting the
Properties command.
Queryable Item Joins
Whenever you create a new Queryable Item that is a child of another Queryable Item the system will automatically evaluate possible relationships between those two item types and create appropriate default joins to relate the two items together.
You can always click on the
Joins command to override and change these joins for any Queryable Item.
You can join any Queryable Item to any other Queryable Item through properties and parameters (if the item has them). Joins have the following properties:
Name
Description
[Required] Source Data Set Queryable Item
This is the item you want the selected Queryable Item to join to. In this description the selected Queryable Item is the Target of the join. The drop down allows selection of any Queryable Item in the tree structure, or the global parameters of the Dataset itself.
[Required] Source Queryable Item Field
A property within the selected Source Dataset Queryable Item that you want to use to join to the selected Queryable Item. If the Source is global parameters it will allow a selection from all your parameters. If the source is another queryable item it will provide a list of all fields that queryable item contains.
[Required] Target Queryable Item Field
This is the property on the selected queryable item that you want to use to join to the source queryable item. This will list all fields against this queryable item, and if it has parameters (is a query queryable item) those will also be listed allowing you to pass values into the parameters as part of the join. Doing so will force the join to be an outer apply join.
You can add as many joins as you require here, link to as many different queryable items as required, and edit any default joins the system created to link the two queryable items together by default.
Commands
A Dataset Queryable Item View has the standard set of commands and functions when being viewed from a tree view. See our standard tree view functionality.
Queryable Items also have the following custom commands:
Parameters Opens the popup dialog box allowing you to edit the properties of the Queryable Item such as the type and name. This is described in more detail above.
Joins Opens the popup dialog box allowing you to edit the joins of the Queryable Item. This is described in more detail above.
