How to Create a SQL Dataset

Prerequisites

---

• Successfully connected at least one of the following integrations that serves as the data source for the SQL dataset being created.
  • SQL Agent
  • N-able N-Central SQL
  • ConnectWise Manage - SQL
  • ConnectWise Automate On-Premise
  • E-Automate On-Premise
  • Kaseya VSA - On-Premise
  • QuickBooks On-Premise
• Only admins can create SQL datasets.

How to create a SQL dataset

---

1. Log in to MSPbots as an admin. 
2. Go to Datasets.
   
   
   
   
3. Click New SQL Dataset.
   
   
   
   
4. When Warning popup opens, click OK. 
   
   
   
   
5. Fill in the following fields.
   • Name
   • Integration
   • Role
   • Remind user to link integration
   • For Default,
     
     • Data Source Type
     • Description
       
       
       
       
6. Next, go to the Data Cleaning tab.
   1. Select a database from the DB dropdown list.
      
      
      
      
      For an existing SQL dataset, be cautious when re-selecting a DB in Data Cleaning, as switching to a different DB will clear all current data.
      
      
      
      If you save the changes, MSPbots will clear the current data and re-sync. The new data will appear in the next sync cycle.
      
      
      
      
   2. Fill in the SQL field. The most common SQL statement here is:
      

      select * from TABLENAM

      
      
      
      
      For an existing SQL dataset, be cautious when modifying the SQL query in Data Cleaning, as any changes to the SQL query will clear all current data.
      
      
      
      If you click Confirm to save the changes, once you click OK, MSPbots will clear the current data and re-sync. The new data will appear in the next sync cycle.
      
      
      
      
   3. Click Validate SQL. If an error message appears, see Error Prompts for Validating SQL When Creating a Dataset.
   4. Input your desired Sync Frequency in minutes. 
      
      Sync Frequency is currently limited to 30 minutes and can only be decreased for smaller datasets. For larger datasets, the frequency may be limited due to a large amount of data ingestion and the constraints it would place on both your server and ours.
      
      
      
7. Click Confirm when done. This will show a preview of the data for your validation.
   
   • You can customize or use the default character length for string-type fields, and the time field can be set with a timezone. 
     
     
     
     
8. Set a primary ID, for more detailed information refer to How to Set a Primary ID in SQL Datasets.
9. Once you confirm the data is correct, click OK to create and save the dataset. The data will be synchronized in 10 minutes. 
10. The new dataset will appear in the Datasets list. 
    
    

Other available actions

---

Last Synced

For the Last Synced field on the Datasets page,

• If your dataset is a SQL dataset, since a SQL dataset cannot set the data source, the SQL dataset does not currently support the Last Synced field displaying the dataset's most recent sync time function.
• If your dataset is an API dataset, the Last Synced field displays the dataset's most recent sync time.
  
  • When it shows NA, it means the most recent sync time has not been obtained yet.
  • When it shows Set Data Source, it means the dataset has not been set with the data source.
    
    • If you want to view the date and time of the most recent sync for the dataset, submit a request to our Support team.
      
      
      
      
    • If your dataset doesn't appear in the dataset list, you can click Show all integrations to make the dataset visible.
      
      
      
      

Fields Description

For Fields Description tab, the following fields will be generated automatically after Dataset be saved.

• Column Name
• DB Type
• Type
• Fields Description
  
  
  
  

Filtering Data in the Dataset

Adding a filtering condition for filtering dataset data

1. Click  .
   
   
   
   
2. Select a field you want to filter.
3. Set a condition for the field using the subsequent two dropdowns.
   
   • For guidance on each option, refer to the article, refer to Conditions and Formats Available for Creating Widgets.
   • To add multiple filtering conditions in the same group, see Adding multiple filtering conditions in the same group.
   • For adding multiple groups, see Adding multiple groups.
   • To configure advanced settings for the condition, see Configuring advanced settings for a condition.
   • To check the filter settings by viewing the filter expression and filter SQL, see SQL Preview.
     
     
     
     
4. Click Save.
   
   
   
   

Adding multiple filtering conditions in the same group

1. Click + Condition.
   
   
   
   
2. Select the logical operator AND or OR for the filter conditions.
   
   
   
   
3. Select a field you want to filter.
4. Set a condition for the field using the subsequent two dropdowns. 
5. Click Save.

Adding multiple groups

1. If you want to add multiple groups, click + Group to add.
   
   
   
   
2. Select the logical operator AND or OR for the filter groups.
   
   
   
   
3. Select a field you want to filter.
4. Set a condition for the field using the subsequent two dropdowns. 
5. Click Save.

Configuring advanced settings for a condition

Click   to set the advanced settings.

• Case SenSitive - If you want the condition to be case-sensitive when filtering the fields, please turn on the Case SenSitive switch.
  
  
  
  
• Slicer
  1. If you want to add a slicer for a condition, turn on the Slicer switch.
     
     
     
  2. Click Select Slicer, then select a slicer from the dropdown list.
     
     
     
     
     • Ignore Filter - When the Ignore Filter option is checked, the value of the condition will be grayed out. Once checked, you only need to fill in the field and query string. And only the slicer will be effective.
       
        
       
       

SQL Preview

Click SQL Preview to check the filter settings by viewing the Filter Expression and Filter SQL.