Venn Sets

Venn Sets allow you to combine multiple queries together into one query, through set operators such as union, intersect and except. Like Datasets, this also automatically creates an underlying SQL Wrapper for the resulting query that can be incorporated and called throughout the LemonEdge platform.

Venn Set Queries

Venn Sets are designed to allow you to merge multiple disparate data sets together into one cohesive result set. This works with data that is related and needs to be combined in a cohesive form. Data that has completely different structure is best kept as multiple different datasets that can be executed together with Query Runners where each different result set can be opened as a separate sheet in excel.

Venn Sets can combine any data set from the following sources:

  • All system entities, custom entities, and entities created through the API.
  • Any associated permissions table
  • The history of all entities and their permissions
  • The ability to query all existing SQL Wrappers (which includes Datasets, and Venn Sets themselves, as they all create internal SQL Wrappers to produce their results)

The multiple sources of data can be combined using unions, intersections, and exception set aggregation methods.

Important

When you've designed a Venn Set 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 Venn Sets automatically have permissions enforced throughout them and can not be bypassed

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

Fundamentals

Venn Sets 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 VennSets/Datasets), or hand crafted by yourselves in SQL. Either way they can be fully integrated into the platform. For example whether a SQL Wrapper was created by a Venn Set or not, it is still available in the set of items that a Dataset or VennSet can query.

Benefits

By using the Venn Set 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 and how you want to combine them into one result set.
  • You don't have to worry about handling permissions or canvases, the venn set writer takes care of that for you
  • Whenever you receive a new version of LemonEdge any changes to the venn set engine will automatically re-write all your queries to immediately benefit from improved performance, and any schema changes that have been made.

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

Designing

Creating a Venn Set is as easy as choosing the multiple sources of data, how you want to combine them, and making sure they all return the same data structure so they can be combined into one set. If you don't yet have data in the shape you want to combine with other sets of data yet, you can use the Datasets query tool to create as many sets of data as you require. You can then use Venn Sets to combine these together according to your requirements.

Venn Sets let you add Queryable Items together into one set of data. 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

You can then add as many fields from every Queryable Item as you like into the Column list of the Venn Set for that item. These are the results for that item that are to be merged with the results (of the same structure) of other Queryable Items in the Venn Set. You can also add custom calculation fields to perform your own calculations, including accessing our SQL Functions for custom IRR, or other, metrics. Each set of data can also be filtered however you like before merging with other sets of data in the Venn Set for the final combined result.

Venn Sets allow you to add custom parameters to the query, which the system will automatically prompt the user for. You can also filter any of the Queryable Item sets 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.

Note

Importantly. this easily allows you to create combined sets of data that get to the overall picture the way 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 Venn Set

Venn Sets 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 Venn Sets 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.

Tip

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. The new SQL Wrapper will hold all of your changes and enable them to be used seamlessly in the application itself.

To run a Venn Set 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 venn set as a context sensitive grid for KPI's, or as a chart/pivot or include it in exports, reports, or even other venn sets.

Views

Venn Sets are managed using the following views: