Create MSSQL Dataset

Perform the following steps to create a MSSQL Dataset:
  1. Navigate to the Connections tab and select the folder where the connection is added.
  2. Click the [] icon and click [Add Dataset].
    Create MSSQL Connector Dataset - Select Connector
  3. The Add Dataset page displays. Provide the following details:
    • Dataset Name
      : Provide a unique Dataset name.
      NOTE:
      If the Dataset with the provided name already exists, an error message displays and also maintains XSS validation.
    • Connection Name
      : Displays the selected Connection Name.
    • Direct Query
      : Toggle the Direct Query option using the slider to enable Direct Query. Refer to Appendix A, “Supported Features - Direct Query and Non-Direct Query” for further details.
      • Direct Query is a mechanism to fetch the updated data directly from the data source (i.e. connector).
      • When Direct Query feature is enabled, the Storyboard data will not be stored in Elasticsearch.
  4. Select the desired MSSQL tags from any one of the following options:
    Tables & Views
    • Drag and drop the desired data tables from the list.
      (OR)
    • Select the desired data tables from the list and click [Add Select Data] to the Selected data section.
    Create MSSQL Connector Dataset - Select Tables & Views
    Stored Procedure
    : User can drag and drop or select from Stored Procedures.
    Create MSSQL Connector Dataset - Stored Procedure
    Custom Query
    : Select [Custom Queries] and write the Custom Query in the “Write your query below” area.
  5. Click [Next]. The Customize Data page displays.
  6. Click [Add Join] to generate multiple joins and select the respective columns from table with primary key and foreign key relationship.
  7. Click [Next] the Modify Entities page displays.
  8. Select the Source Time Zone from the drop-down list.
  9. Select one column name as a unique id in the Unique_ID column.
    • User can change the column display name on the Modified entities page, and the same name displays in all sections of the dataset. The same name displays on the Preview dataset page and Configuration page while creating a dataset. On the Storyboard created with the dataset, the same column name displays in Chart settings, filters, legend series, tooltip, Sorting & Ranking, etc.
  10. Select the desired columns to generate the Dataset.
    NOTE:
    Dataset created with custom query will not have Unique ID column.
    • Click the [] icon to clear all the visible columns.
      Create MSSQL Connector Dataset - Modified Entities
  11. Click [Next]. The Preview Dataset page displays the date and time as per selected Time Zone in the Modify Entities page.
    NOTE:
    Maximum of 1000 records will be displayed, from the available records.
  12. Hover the mouse over the column header and click the [] icon to sort the data or arrange the column size or to apply aggregation.
    NOTE:
    User needs to select the [Group by <column_name>] on numeric field to view the aggregation option.
    Create MSSQL Connector Dataset - Preview Dataset
    IMPORTANT:
    If the table contains numeric data in format like 1, 2, 3, 10, 12, 13, 22, 23, 24, 99, 100 and user tries to apply the filter then the data displays in the following sequence: 1, 10, 100, 12, 13, 2, 22, 23, 24, 99.
  13. Hover the mouse over the column header and click the [] icon and then click the [] icon to apply filters.
  14. Hover the mouse over the column header and click the [] icon and then click the [] icon to select the number of columns to display.
  15. Click [Next]. The Configure Dataset page displays.
  16. Provide the following details:
    • Data Sync Configuration
      : Select All Data or Selected Data (data for the Views).
      • All Data
        : Select the following:
        • Full Refresh
          : Display updates only after the entire data is updated.
          TIP:
          After deploying the application, when the User creates the first Storyboard with Incremental Refresh option, empty storyboard is saved. However, the Storyboard will be saved with full data, if user creates a full refresh Storyboard and then switches to Incremental Refresh.
        • Incremental Refresh
          : Display updates only additional data for a specific entity. Select the column(s) from the drop-down list to update the data.
          NOTE:
          For a Storyboard with Custom Query, do not select the column with alias name for Incremental Refresh. This is because the alias name column does not exists in database, it displays error.
          NOTE:
          Only full refresh option is applicable for the Storyboard created with store procedure.
        • Configure Incremental refresh for all the connectors. Always insert Max ID+1 into the database.
          Example: In a table, if one column contains 1, 2, 3 IDs, and if User needs to add some more records for checking data sync, then User should insert the column ID as 4 which is greater than the last max value 3.
          (OR)
        • Date Range
          : Select the Date column and the date range.
          Click in the Date field to select the Start Date and End Date from the calendar. User can select the Start Date and End date from the calendar (OR) type the date in Date field in Mmm/dd/yyyy format (e.g. Sep/14/2022).
          NOTE:
          The calendar date picker icon is disabled here while configuring the Data Sync with Date Range.
          (OR)
        • Numeric Range
          : Select the numerical column and the numeric range.
          NOTE:
          For any timezone, system will sync the data from the last date available in Elasticsearch. Due to any error, if the DataSource has the future timestamp, data sync will not happen.
    • Data Sync Schedule
      : Select the following:
      • Default
        : 30 minutes
      • Custom
        : Time interval to sync data.
        NOTE:
        The minimum custom time interval is 5 seconds.
      • Custom Cron Expression: Enables the Users to setup the data synchronization schedule based on the User defined time. For more information on the Custom Cron Expression, refer to User Guide Appendix A, “Custom Cron Expression”.
        • Specify the valid Cron expression and click [Validate].
        • The “Valid Cron Expression” success message displays.
    • Default Display Count: From the drop-down list (100, 1000, 10000 or All Available) select the default number of records to display for Charts using this Dataset. By default, 100 records is selected.
      NOTE:
      Tested till 99999999 number of records to display on Charts.
    • Advanced Settings:
      • Shards And Replicas: Provide the required number of shards and replicas.
        NOTE:
        The default number of shards are one and the replicas are zero. The number of shards and replicas are editable only before saving the Storyboard. Configure Dataset
        Configure Dataset
  17. Click [Finish] the “Dataset Saved Successfully” message displays and “Create a Content for this Dataset.” dialog displays.
    NOTE:
    The Dataset and the folders created are arranged in ASCII order.
    Configure Dataset
  18. If user does not want to continue with creating the Content, click [Not Now]. This saves the dataset.
    (OR)
    Click [Sure] to continue creating the Content. The Add Content page displays.
  19. Dataset information
    : Click the new Dataset, navigate through the tabs to view respective information:
    • Details tab: Displays the Dataset created information and Dataset Sync information.
    • The following details are displayed for the dataset created with Indirect query:
      • Dataset Information: Dataset Name, Data Source, Connection Name, Query Type, Source Time Zone, Created By, Created On, Updated By, Last Updated, Replicas, and Shards.
      • Dataset Sync Information: Data Sync Configuration and Data Sync Schedule.
      Dataset Information
    • Custom Fields: Displays the created Custom fields for the dataset created with indirect Query only. Click [Add Custom Field] to create a new custom field.
      Custom Field
      • The Custom Fields dialog displays. Provide the following details:
        • Provide the Custom Field name.
        • Define the formula in the Add Formula editor box. User can create the Custom Field in dataset with Numeric or String or Date type data.
          NOTE:
          User can paste the formula in the Add Formula Editor box.
        • Click [Validate] to validate the formula. The “
          Formula Validated Successfully
          ” message displays.
        • Click [Add]. The “
          Custom Field saved Successfully
          ” message displays.
          Add Custom Field
    • Preview Dataset
      : Displays the Dataset preview for 1000 rows by default. Click the column header to apply filter to the columns or to select the columns to display on the Preview Dataset panel.
      NOTE:
      If Custom Field is created the column is displayed on the Preview Dataset page.
      Preview Dataset
Provide Feedback
Have questions or feedback about this documentation? Please submit your feedback here.