CogBI Server: SQL Queries

Welcome to the ‘SQL Queries’ tutorial.

In this tutorial you’ll learn to use SQL queries to send data in real-time from your SQL databases to the CogBI iOS app using the CogBI Server. We strongly suggest that you go over the 'SQL Databases' tutorial before this one.

You can add SQL queries to each database connected to the CogBI Server. Each SQL query added to the CogBI Server will be executed against its designated database and the results are will be sent to the CogBI iOS app to be used as data for Business Intelligence visualizations.

Below you will learn how to:

  1. Add a SQL Query to the CogBI Server.

  2. Modify an existing SQL Query in the CogBI Server.

  3. Delete an existing SQL Query in the CogBI Server.

Adding a SQL Query

Follow the steps below to add a new SQL query to the CogBI server:

  1. Open and Log In to the CogBI Web Administrator by launching it from the CogBI Service Monitor or by typing the Host Name/IP Address that you assigned to the CogBI Server directly in a browser:

    Launch the CogBI Web Administrator Log in to the Web Administrator

  2. Click on the SQL Queries tab located in the main toolbar:

    Click on 'SQL Queries'

  3. Click on ‘Add Query’:

    Click on 'Add Query'

  4. The following fields are available for every new SQL query that is added to the CogBI Server (the required fields are underlined):

    1. Query Nickname: A nickname to identify the particular query you’re adding throughout the CogBI Server and the CogBI iOS app.

    2. Description: An optional description to help you identify the purpose of this particular query in the ‘Data Source’ menu of the CogBI iOS app.

    3. Database: Select the database against which this query will be executed.

    4. Execution Type: Execution of queries happens every time a Dashboard is opened or reloaded in the CogBI iOS app. The CogBI Server will execute the query as defined in this screen and send the results back to the iOS app. There are 2 different methods of execution available and each one offers different advantages, please read the following explanation of each to better understand which one to use.​

      1. Per Chart: The query will be executed once for every chart in a Dashboard. This will be the faster option when the query results are small and it may also require less bandwidth. The query will be executed in the server (once for every chart) and only the necessary values to render a chart will be sent to the CogBI iOS app.

        This execution type can require significantly more server resources, specially when using complex queries and/or when a Dashboard contains many charts.

      2. Per Dashboard: The query will be executed only one time (every time a Dashboard is opened or manually reloaded) and the complete results will be sent to the CogBI iOS app. The app will cache the results and use them to calculate the values necessary to render all of the charts in a Dashboard.

        ​Depending on the number of results of a query this can require more bandwidth than the previous option. It provides the advantage of requiring less server resources and of providing the CogBI iOS app with a cache of the query’s results.

    5. SQL Query: This where the actual query is entered. The standard SQL syntax should be followed. The queries may be copied and pasted from other sources or they can be edited directly in the provided text area.

    New Query Details

  5. Fill out all the fields and click on the ‘Continue’ button at the bottom:

    Complete & click 'Continue'

  6. The query will be executed against the database that was selected, if the execution is successful you’ll be presented with all the field names that were returned and their respective data types. You should review that all the returned columns and data types are correct and then click on ‘Save’. You can also click ‘Back’ and change anything if necessary.

    Verify & click 'Save'

  7. You will be presented with a success message and the query will have been added to the CogBI Server and will be ready to be used in the CogBI iOS app.

    Query created successfully!

Modifying a SQL Query

  1. To modify an existing SQL query in the CogBI Server, click on the ‘Edit Button’ next to the SQL query that you wish to edit (in this example we will edit the ‘Sales’ query at the bottom of the list):

    Click on the Edit Button of the desired query

  2. In the ‘Edit Query’ screen you will be able to modify all the query attributes. Please keep in mind that the following is not possible when modifying the actual SQL query code:

    1. Deleting an existing field.

    2. Renaming an existing field.

    3. Changing an existing field’s data type.

    Modify query as needed

  3. In our example we will add a WHERE clause to our query. Click on the ‘Continue’ button when you are have finished modifying your query:

    Click on 'Continue' when ready

  4. Review that the fields that your query will return are correct and if so, click on the ‘Save’ button:

    Review changes and click 'Save'

  5. You will receive a success message and be returned to the main SQL Queries menu after your query has been modified:

    Query modified successfully!

Deleting a SQL Query

  1. To delete an existing SQL query in the CogBI Server, click on the ‘Edit Button’ next to the SQL query that you wish to delete (in this example we will delete the ‘Sales’ query at the bottom of the list):

    Click on the 'Edit Button' of the desired query

  2. You will find the ‘Delete Button’ at the bottom ot the Edit Query’ screen, click it in order to delete the query:

    Click on the 'Delete Query' button

  3. You will be asked to confirm that you wish to delete the query. Click the ‘Delete’ button to confirm and delete it permanently:

    Confirm to delete the query

  4. You will receive a confirmation message after the query has been deleted:

    Query deleted successfully!

This covers the SQL queries management in the CogBI Server. Continue learning about CogBI using the sidebar or the buttons below to navigate the support section.

| | CogBI Server |
« CogBI Server: SQL Databases CogBI Server: Users »