MS SQL

An MS SQL connector, also known as an SQL Server connector, is a software component or driver that enables communication and interaction between applications or systems and Microsoft SQL Server databases. It acts as a bridge, facilitating the exchange of data and queries between the application and the SQL Server database.

The steps for adding an MS SQL data source differ from adding other data sources, so if you would like to add MS SQL as a data source follow these steps:

Adding an MS SQL data source 

1. Click on the Data Sources link on the left menu
2. Select MS SQL
3. Paste in the connection string and click validate.
   
   An MS SQL connection string is a configuration setting that contains the necessary information for an application to establish a connection with a Microsoft SQL Server database. It typically includes details such as the server name, database name, authentication credentials, and additional parameters. You can find the connection string in the application’s configuration files, such as the web.config or app.config file, or it may be provided by the administrator or developer who set up the database connection.
   Sample connection string to test this feature: Server=tcp:sintelbidemodata.database.windows.net,1433;Initial Catalog=SintelBIDemoData;Persist Security Info=False;User ID=sintelBI;Password=na576ayUKsuLCuflE4fP;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
   
   
4. Select the table/View
5. Give the data source a name
   Some SQL servers may require you to add allow an IP address in order for your data to connect. The IP address for Sintel BI is: 20.229.106.231

Sintel BI does not to import MS SQL data by default due to a number of  factors such as performance optimisation, data quality considerations, access control, and the ability to selectively import relevant data based on user needs.

Therefore, the next thing you need to do once you have added the data source is select it and select configure.

Here there are 2 buttons: Add column and Read from data base  

Add column

The “Add from column” button allows users to add new columns to a data set. This approach offers flexibility in data modeling, enabling users to curate and shape the data according to their specific reporting or analysis requirements. It allows for customisation and tailoring of the data set to suit different visualization and analysis needs.

If you choose Add Column:  You will add the name of the column and the column type.

The options for column type are: Single line of text, Multiple Lines of text, Yes/No, Number and Date/Time

Once you have added each of your columns click save.

Read from Database

If you select Read from Database, it will pull in all columns from MS SQL.

You will then have to go through each column and assign it a Data Type or delete it, depending on its relevance.

Data Source Properties

Once you have added a data source there are a number of options available to you including:

1. Editing
2. Cloning
3. Viewing source data
4. Activating/Deactivating
5. Deleting

Editing

You can make numerous changes to a data source including:

• Renaming a data source
  • Select the current title
  • Rename it
  • Click Save

    

    Renaming a  Data Source

• Showing and hiding fields
  • Navigate to the data source
  • Select the three dots … and click edit
  • By default, all fields are shown you can click the eye icon to hide any fields you do not want to show.
  • Lastly, click Save & Exit

    

    Showing and hiding Fields

• Changing field order
  • Navigate to the data source
  • Select the three dots … and click edit
  • Drag & Drop the fields in the order you wish them to appear
  • Lastly, click Save & Exit

    

    Changing field order

• Renaming fields
  • Navigate to the data source
  • Select the three dots ‘…’ and click edit
  • Click on the three dots ‘…’ beside the field name
  • Select Rename
  • Change the fields name
  • Click OK

    

    Renaming Field-Note that a pencil icon highlights that a field has been renamed

• Preview data
  • Navigate to the data source you want to preview
  • Select the three dots ‘…’ and click edit
  • Select Refresh

    

    Preview Data

Other things to note: 

The different icons displayed represent the different field types Number, User, Text, Date, Choice, and Attachments.

Cloning

It is incredibly simple to clone your Data Source. This can be helpful when you need to duplicate similar data sources for different regions or departments.

To clone a data source:

1. Navigate to the data source you wish to clone
2. Click on the three dots …
3. Select Clone

By default, the data source name will inherit the source components name, appended with the word ‘Copy’. We recommend that you change it to a name that suits you.

Activating / Deactivating

When you deactivate a data source, it will no longer send data to your Sintel BI dashboards.

To deactivate a data source:

1. Navigate to the data source you wish to deactivate
2. Click on the three dots … →
3. Select Deactivate

If you wish to activate a data source after deactivating:

1. Navigate to the data source you wish to activate
2. Click on the three dots …
3. Select Activate

Deleting a data source

Sometimes you no longer have use for a data source and there is no point in having it cluttering up your screen.

To delete a data source:

1. Navigate to the data source you wish to delete
2. Click on the three dots …
3. Select Delete