Datasets

Datasets provide the ability to query every single field in the system, combine them into any shape of data you require, and even build up a library of useful queries that can be re-queryed by the dataset tool again.

Dataset Queries

Datasets is the tool that allows business users to easily query any field in the entire system. Datasets have access to all the following data for querying:

• All system entities, custom entities and entites created through the API.
• Any associated permission tables
• The history of all entities and their permissions
• The ability to requery existing SQL Wrappers

When you've designed a dataset the system actually creates an underlying SQL Wrapper (which you have access to and can open/view) for you that holds the query the system will run against the database. See here for more information on SQL Wrappers.

All Datasets automatically have permissions enforced throughout them and can not be bypassed

Equally all datasets have canvas technology baked into them so they automatically tun correctly across any canvas you are using.

Fundamentals

Datasets automatically write a SQL Wrapper as SQL Wrappers are the core module for internal reporting throughout LemonEdge. SQL Wrapper results can automatically be incorporated into every part of the LemonEdge platform - whether they were created by the system automatically (for example through Datasets), or hand crafted by yourselves in SQL. Either way the can be fully integrated into the platform. For example whether a SQL Wrapper was created by a Dataset or not, it is still available in the set of items that a Dataset can query.

Benefits

By using the Dataset query tool rather than hand crafting your SQL you gain the following benefits:

• You don't have to understand the schema of the database or be knowledgeable about using SQL. You can be a business user who understands the data you want to query.
• You don't have to worry about handling permissions or canvases, the dataset writer takes care of that for you
• Whenever you receive a new version of LemonEdge any changes to the dataset engine will automatically re-write all your queries to immediately benefit from improved performance, and any schema changes that have been made.

Datasets, as with SQL Wrappers, are automatically versioned. Any system provided datasets will automatically upgrade on new versions. Also you can export/import datasets across systems safe with the knowledge you won't overwrite newer versions.

Designing

Creating a dataset is very easy and follows the simple premise that Datasets are designed to get one set of data that you want to work with. If you want multiple sets of related data we have other tools that can combine datasets to do that for your. Such as our VennSets that let you merge multiple SQL Wrappers together. If you want to create hierarchical type of reporting see our GL Rollups.

A dataset lets you add Queryable Items. A Queryable Item can be one of the following:

• Entities - Any entity in the system
• Audit History - Audit history of any item in the system
• Queries - Any public SQL Wrapper in the system

When you add more Queryable Items to a Dataset the system will automatically join the together where possible. You always have the ability to manually edit joins across QueryableItems. These joins allow you to join on parameters for SQL Wrappers (If the queryable item is a query itself) as well as any field.

You can then add as many fields from every Queryable Item as you like into the Column list of the dataset itself. These fields can be visible, hidden or aggregated. You can also add custom calculation fields to perform your own calculations.

Datsets allow you to add custom parameters to the query, which the system will automatically prompt the user for. You can also filter the query result set by creating as many hierarchical filter criteria as you like. These filters can refer to any field, value or hard coded value you like, not just the fields you've added to the result set. 

Importantly. this easily allows you to create queries that get to the data you want in a way that can filter the data to precisely what you need and automatically provide the correct set of parameters to the user.

Using a Dataset

Datasets automatically create and write a SQL Wrapper for you. This allows you to easily see the SQL the system is generating in a human readable format. The core reason datasets do this is because SQL Wrappers are integrated into the core of the LemonEdge platform. For instance they can automatically be dragged+dropped as grids into any layout and made context sensitive to run specifically for that layout they are running in. There is a huge amount of functionality centered around SQL Wrappers such as Excel exports, Reporting, Charts, Pivots, etc. See here for more info. 

Should you ever need to manually tweak queries you can do that by copying the SQL Wrapper itself and manually making those changes in SQL.

To run a Dataset and see the results you simply need only run the SQL Wrapper the system as generated for you. Equally you can take advantage of all the SQL Wrapper functionality and display the dataset as a context sensitive grid for KPI's, or as a chart/pivot or include it in exports, reports, or even other datasets.

Example

For example creating the following simple Dataset to show all teams for a user simply involves adding the User, UserTeam, and Team Queryable Items to the Dataset. The system will automatically infer and add the necessary joins, you needn't do anything. If we want the user to be able to select a user (rather than returning every user) we can add a user parameter and add a where filter to filter on it. 

It's as simple as that, and would look like this when added:

Running this query would popup the following parameters (if you added a user parameter):

Which in turn would give the following results (if we selected no user in the parameter as we made it allow nulls and wrapped the where filter in an IsNull):

You can even see the sql the Dataset has generated for this query in a human readable format:

Alter Function dbo.UserTeams
(
	@accountID BigInt, 
	@canvasID UniqueIdentifier = null, 
	@teamID UniqueIdentifier, 
	@lastUpdated DateTimeOffset = null, 
	@userID UniqueIdentifier = null
)
Returns Table
As
Return
(
	Select	t1.[Name] as [UserName], t1.[EmailLogin] as [UserEmailLogin], t3.[Name] as [TeamName]
	From	dbo.fn_Users(@accountID, @canvasID, @teamID, @lastUpdated) as t1 
	INNER JOIN dbo.fn_UserTeams(@accountID, @canvasID, @teamID, @lastUpdated) as t2 on t2.[UserID] = t1.ID
	INNER JOIN dbo.fn_Teams(@accountID, @canvasID, @teamID, @lastUpdated) as t3 on t3.[ID] = t2.TeamID
	Where
			t1.[Suspended] = 0 And t1.[ID] = IsNull(@userID, t1.ID)

)

Views

Datasets are managed using the following views:

• 

  Datasets

• 

  Version

• 

  Queryable Items

• 

  Where Filters

• 

  Columns

To run a DataSet see SQL Wrappers.