The Data Mining Module provides an advanced interface for querying and returning data from ChilliDB. It utilises the Reporting View database interface which provides user-friendly naming of objects and familiarity for existing report writers.
The Data Mining Module contains several parts which work together to allow the user to configure and retrieve information.
1. Search
Searches are the basis of the module's functionality and are used to select, filter, group and sort information from the underlying database.
2. Data Set
Data Sets offer the ability to perform advanced queries and even combine the results of several queries into a single result set. Data Set queries offer similar features like Searches but with more flexibility in structuring the result data into a group summary or normal listing.
3. Batch
Finally, Search items can be collated together into a Batch which allows end-users to easily execute one or more searches to retrieve up-to-date information from the system.
Searches
A Search item allows the Data Mining user to retrieve the data they are interested in seeing by allowing selection, filtering, grouping and sorting to be specified on a particular area of the system.
Previously specified and saved Search items are stored so that you may utilise them from multiple Batch items for reporting purpose
Result Types
Each Search or Query item created within Data Mining module will return a particular type of output. The type of output you choose alters the features which are available when designing the query. There are three Result Types which are available in Data Mining Module.
Notes: One of the result types described below will be only available when building a Data Set Query
"List All" Type
This Result Type simply returns a list of all the records it can find which match your filter options
"List Top" Type"List Top" works in a similar way to "List All", however for this Result Type you must also specify a number of rows to return. In combination with appropriate Sorting and Filtering options you can answer questions such as "What are the last twenty Notes created by the super_user?"
"Summary" Type (Only Available in Data Set Query)
When a Data Set Query is designed to return a "Summary" Result Type the Query author is able to select one or more columns to split the data into groups. For example, if you are looking to count the number of male and female Contacts you have in the system you would specify "Summary" and select "Gender" as your "Grouping" column.
Data Sets
Each Search item is based on a "Data Set" selection. There are a number of pre-configured Data Sets which are available in the system. These pre-configured Data Sets correspond to Reporting Views which are available in the system.
To base your Search item on one of these pre-configured Data Sets, you simply select the Module you are interested in, such as "Contact", from the first drop down. This will populate the second drop down list with the items of information which are available for that module. For the "Contact" module this includes the base Contact information, Communications, Addresses and Dynamic Data.
The final option in the "Module" drop down is "Data Set". Selecting this option will populate the second drop down list with the advanced Queries configured through the "Data Set" screen of the Data Mining Module.
Building a Search item The following steps outline the building of a simple query for searches using the Data Mining module.
Select "Modules" -> "Data Mining" -> "Searches and Batches" -> "Create Search" from the ChilliDB system main menu
Enter a name in the "Search Name" box. You will use this name to remind you what the query is returning.
Select one of the two "Result Type" options available ("List All" or "List Top").
Find the data you wish to query by selecting first a Module then a View from the two "Data" field drop down lists
Specify Filters to narrow down the data you wish to return in the "Filters" section.
Click "Add New Filter" link and a table with several empty columns will appear under the link
Select the Column, Operator and Value (if required) for the new filter row.
For all Filters except the first you must specify how the filter is combined with the one before by specifying "AND" or "OR".
Note: You must click the "Add New Filter" link to display and edit a new Filter row. You can also delete the Filter row by clicking on the delete icon on the very right side of each Filter row displayed on the "Filters" section.
6. "Display Columns" define what will be what is presented in the result grid from your query. They are managed in a similar way to Filters.
Click "Add New Display Column" link to add your new Display Column to the Search result. A new table with several empty columns will appear under the link.
Select the "Column" you wish to display data from.
Finally specify the "Display Name" which will be shown in the header of the column in the output.
Each Display Column is added to the end of the columns list. You can change the order of the columns by altering the "Order" drop down once you've added your Display Column to the Search item.
Note: You must click the "Add New Display Column" link to display and edit a new Column row. You can also delete the Column row by clicking on the delete icon on the very right side of each Column row displayed on the "Display Columns" section.
7. The last step is to specify how you would like the results sorted. This is especially important when the "List Top" value is chosen in "Result Type". Choose the Column, Summary Function if you are Grouping and then tick the box to have the sort go in ascending order (e.g. A to Z) or clear the tick to sort in a descending order (e.g. Z - A).
8. Click "Save" button. Your Search item is now saved and can be used from a Batch.
Data Sets
Often simple selection of data cannot provide the information required. Most often this is because information is stored in two separate areas of ChilliDB are representative of a single business concept. An advanced Data Mining user may use the "Data Set" area to combine information from across areas of ChilliDB making it available for the Search screen.
Sometimes the user would also like to summarise the selected data into groups. For this purpose, a Data Set can be used.
A Data Set is implemented in two main steps. One or more Queries are created using a screen similar to the one described in the "Building a item" section and then their results are combined to create one list of Columns which represent the output of the Data Set.
Data Set Queries
There are four differences between creating stand-alone Search items and a Data Set Query. Those differences are:
Data Set Queries are slightly more advanced and allow you to select multiple Views. These Views are joined back to the initial View. This allows you to select related items of information (e.g. an "Event Session" and all the "Event Registrations")
Data Set Queries has the ability to form a "Summary" Result Type which is not shown when building stand-alone Search items.
The “Summary" Result Type that the Data Set Query author is able to select one or more columns to split the data into groups. For example, if you are looking to count the number of male and female Contacts you have in the system you would specify "Summary" and select "Gender" as your "Grouping" column
3. Data Set Queries do not include a "Sorting" section.
4. Data Set Queries will not appear in the general list or be available directly for Batches
Other than these differences you create each Query in the same way as for stand-alone Search item.
Building a Data Set Query
The following steps outline the building of a Data Set Query within a Data Mining Data Set.
1. Select "Modules" -> "Data Mining" -> "Data Sets" -> "Create" or "Maintain" from the ChilliDB system main menu.
Notes: Choosing between "Create" and "Maintain" will be depending on the nature of the Data Sets (new or existing Data Sets).
2. In the Data Set Maintenance screen, after giving the name of the Data Sets and set up the Data Set available for Data Mining Searches, click on the "Add New Query" link to add a new Data Set Query.
3. The Data Set Query Maintenance pop up window will appear on your screen. You will notice that the result type field contains one more option called "Summary" and it become the default selection for building the Data Set Query.
4. Click on the "Add New View" link to add the data view into your Data Set Query. If this is a new Data Set, a new view row will appear below the "Add New View" link showing two empty columns. Find the data you wish to query by selecting first a Module then a View from the two "Data Set" drop down lists.
Notes: Since you can add more than one view in Data Set Query, for all view data except the first you must specify how the view data is combined with the initial view (the one on the top row) by specifying "AND" or "OR". For all these rows, you also need to specify which view that you are going to join from as well as the columns which will be used by both view data as the connecting column for associating the view records. You can also delete the view row by clicking on the delete icon on the very right side of each view row displayed on the "Views" section
5. Specify Filters to narrow down the data you wish to return. Click “Add New Filter” link and a table with several empty columns will appear under the link. Select the Column, Operator and Value (if required). For all Filters except the first you must specify how the filter is combined with the one before by specifying "AND" or "OR".
Note: You must click the "Add New Filter" link to display and edit a new filter row. You can also delete the filter row by clicking on the delete icon on the very right side of each filter row displayed on the "Filters" section.
6. If you have specified "Summary" in the "Result Type" field you will next setup Grouping. If you have "List All" or "List Top" specified you should skip this step and continue with the next step.
To choose which columns specify your Groups, move them one at a time from the "Available Columns" box into the "Chosen Columns" box. Within the "Chosen Columns" box you can alter their order by using the "Move Up" and "Move Down" buttons.
Note: Grouping columns will automatically be added to the "Display Columns".
7. "Display Columns" section defines what will be what is presented in the result grid from your query. They are managed in a similar way to Filters.
Select the "Column" you wish to display data from. If you are creating a "Summary" Query you will have to specify how you wish to summarise the data from that column, unless it is one of the "Chosen Columns" in the Grouping section.
Finally specify the "Display Name" which will be shown in the header of the column in the output. Click "Add" to add your new Display Column to the Query.
8. The "Show Result" button can be used to show the result of your query. While you change your query view, filters, grouping, and columns, you can always preview the result by clicking the "Show Result" button.
The "Clear Results" button can be used to clear the result shown on the screen and can allow you to maintain your query faster rather than having the result shown all the time during maintaining your query. After clicking the "Show Result" button and see the result, it is always recommended to clear the result first before making any changes to the query structure. When you finish making changes, you can see the result back by clicking the "Show Result" button.
9. Click "Save" to save your Data Set Query and return to the Data Set Maintenance screen. You will see that the Data Set Query has been added into the Queries section. When you need to make changes to the Data Set Query, you can select the name link to open the Data Set Query Maintenance window, make the changes and save it back to the system
10. You can have one or more Queries specified in one Data Set. You use multiple queries to UNION result sets. In that sense, you can combine the results of two or more Queries to build your own Data Set structure.
Columns
Once you have one or more Queries specified you need to define the output of your Data Set. This involves specifying the Columns you wish to expose. Each of these columns can combine data from one or more of your specified queries, as long as the data is of the same type. For example, you cannot combine a number column (such as an "Id" column) and a date column.
To specify your Columns you should:
Click "Add New Column"
A row will appear to specify the Display Name and choose which Display Column from each of your Queries should contribute data to that Data Set Column.
Select either a Display Column or the special "" value which returns no data.
Due to the values selected from other Query's Display Columns not all of a particular Query's Display Columns will appear in every drop down. In some cases "<empty>" will be your only option.
Batches
In order to display the results of your Search items you combine them into a "Batch" item. Each Batch item has a "Type", "Category" and "Sub Category" classification system identical to other parts of the ChilliDB system.
Creating a BatchThe following steps outline the building of a Data Set Query within a Data Mining Data Sets.
1. Select "Modules" -> "Data Mining" -> "Searches and Batches" -> "Create Batch" or "Maintain Batches" from the ChilliDB system main menu.
Notes: Choosing between "Create Batch" and "Maintain Batches" will be depending on the nature of the Batches (new or existing Batches).
2. Enter the Title and Categorization selections in the "Batch Header" section.
3. In the "Searches" section of the "Data Mining Batch Maintenance" screen, click "Add New Search" link to add an existing Search item previously created and stored. For each Search item you may give it a "Display Name" which will become a sub heading on your Batch output.
4. Once the Search item has been added into "Searches" section, you can edit the Order (If you have more than one search items) and "Display Name" for the Search item selected in the Search column.
5. To delete a particular Search item, click the delete icon on the right side of the grid for every Search item row that you would like to delete.
6. The "Show Result" button can be used to show the result of your query.
7. If you need to print out the batch result, click on the "Print" icon .
8. Once you are done, click the "Save" button.
Running a Batch
Once you have created your Batch you will see the "Batch Display" screen. This screen gives you a read-only view of the Batch and also allows you to view the output it creates.
First on the screen you will see the "Header Information" which tells you the Title, categorisation and expiry information.
Next, in the "English Description" section, you will see a list of the Query items which the report contains along with a plain English description of the information they are returning.
Finally, the "View Results" section allows you to see the results of the Report. When you click the "Show Results" button each Query is run and the results are returned to the screen. You can view them there or click the "Print" icon on the top right of the result box to get a version of the output suitable for printing.
Known Limitations
Data Set Queries
When using multiple views all joins are fixed as "INNER JOIN"
Joining is only supported between the initial view and each additional view, you cannot join between two additional views (e.g. selecting "Event Session", "Event" and "Event Registration" in that order will join "Event Session" to "Event" and "Event Session" to "Event Registration", you cannot join "Event" to "Event Registration")