Data Mining Module User Manual | | chillidb

Contact Us

Polymorphic Solutions

Level 13, 97 Creek Street
Brisbane, QLD, 4000

Australia


info@chillidb.com

Tel: 1300 65 72 43

About

Newsletter

Video

Partners

Pricing

Terms of Use

Privacy

Security

ChilliDB is a Registered Trademark of Polymorphic Solutions © 2019

Nov 5, 2018

Data Mining Module User Manual

0 comments

Edited: Mar 14

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.

  1. Select "Modules" -> "Data Mining" -> "Searches and Batches" -> "Create Search" from the ChilliDB system main menu

  2. Enter a name in the "Search Name" box. You will use this name to remind you what the query is returning.

  3. Select one of the two "Result Type" options available ("List All" or "List Top").

  4. Find the data you wish to query by selecting first a Module then a View from the two "Data" field drop down lists

  5. 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:

  1. 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")

  2. 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:

 

  1. Click "Add New Column"

  2. 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.

  3. 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")

Was this article helpful?

 

New Posts
  • Sometimes you want to secure certain Reports to only certain Users or Roles of Users. ChilliDB allows you to do this through Report Security, which you find under the Reports menu, the menu item is called Report Security. Report Security allows you to configure Report Types to certain User Roles. By default in ChilliDB, all Report Types are visible to all User Roles. When you start securing reports, you need to then review and likely set permissions for every Report Type. You do this by selecting each Type from the Drop Down List, then you can uncheck the "All Roles" checkbox and choose zero, one or more User Roles who can see reports of that Type. Work through all Types in your System setting permissions. Was this article helpful?
  • This article describes how to quickly send reports to contacts in ChilliDB. This is functionality is for SSRS (.rdl) type reports only. It is not available for Crystal Reports. With SSRS reports we offer the functionality to quickly send an email with the currently loaded report attached. 1. Load your report and click the email report link located at the top right of the screen 2. This will open the send message window with the report attached 3. Complete the form like normal, all recipients will receive the report as an attachment Was this article helpful?
  • Did you know that you are able to integrate reports into many screens throughout ChilliDB and then access those reports from screens within ChilliDB. This is in addition to the reports you run from the standard report listing available under Reports > Display. Some examples of where this will be most useful include: Print reports related to a specific Event such as certificates or signatory sheets from the Events display screen Print Summaries of Notes related to a Contact or Organisation from the Contacts or Organisation display screens Print a Contact sheet for all Contacts belonging to an Organisation from the Organisation display screen Print Membership renewal reminders for a Membership Package from the Membership Package display screen This Fact Sheet will help you integrate your reports into specific screens within ChilliDB On the screens that you selected to link your report with, ChilliDB will pass the record identifier for the record being displayed on the screen to the report. All you need to do to take advantage of this is: Create your report with a parameter to accept that record identifier. Your parameter should be specially named based on the available parameter named found in the next section of this article. Upload your report into ChilliDB, flagging the report as an integrated report and select which screen it should be linked to. Your reports may have additional parameters and ChilliDB will prompt you for only those additional parameters and pass the record identifier automatically. For example, if you were printing a Contact sheet for all Contacts for an Organisation, you may like to have a second parameter to prompt the user to filter down the Contacts based on their Type. Available Parameter Names for Integrated Reports ChilliDB needs to know which screen is running the report so that it can find the record identifier for that screen to pass to your report and finally, the name of the parameter in your report to place the record identifier into. Therefore, when you create your report you need to create parameters named in a special way to accept that record identifier if you would like your report to be an integrated report. ChilliDB can only pass the record identifier which identifies a Contact, Organisation etc. that is being displayed in ChilliDB. It is not able to pass identifiers from records within a grid, or identifiers associated with the record being displayed in ChilliDB. The following table outlines the parameter names which you are able to use in your integrated reports. All parameters are of data type “Number” ("Integer" if you are using report builder) Linking to ChilliDB screens You can associate the report with a ChilliDB screen in a similar manner to uploading any standard report, with some simple additional steps. In ChilliDB, navigate to the Reports > Upload. Fill out the form as you normally would, then select “Integrated into the following screen” for the “Access Method” field. You will then see a second drop down appear below that field and you can then select the screen in ChilliDB to link the report with. Select the screen you would like to link your report to and save your changes. Viewing Integrated Reports If you navigate to one of the screen into which you linked your report (e.g. The Contacts display screen), you will see a Quick Actions menu near the help icon in the top right corner of that screen. Within that Quick Actions menu, you will see a Reports sub menu and you will see your report listed within that menu. Selecting your report will pop up the report viewer to display your report. Managing Integrated Reports By default, integrated reports are not included in the standard report listing screen (Reports menu > Display). The report listing screen includes a filter called “Show Integrated Reports”. Checking this box will reveal your integrated reports in the listing. You can then perform the usual maintenance options with that report. If you change a report’s “Access Method” from “Integrated into the following screen” to “Run from Report Listing”, your report will then only appear in the default report listing screen. When you run an integrated report from the report listing, you will see a prompt for the parameter which is normally set by ChilliDB when running the report. Troubleshooting If you see your parameter when running your integrated report, check the name of the parameter is correct and check that the data type for your parameter is set to “Number”. Also ensure that you have linked your report to the correct screen. Was this article helpful?