OData Local Server - Install and configure

OData requires a previous setup. This has to be done once only.

Step 1. Install the OData Local Server

Since SQL for Cloud is a JavaScript database running in the browser, it needs some extra help to listen to connections. Hence we have developed an OData Local Server to connect the browser with the analytics tools of your choice (like MS PowerBI, Tableau, etc.) running on your PC.

Please download the library below and install it on your PC to get it working:

(Java 1.8 or above required)

Open a console from your desktop (you might run it as an administrator) and run the installer on Windows or MacOS:

C:\ .. > java -jar install_sqlc_odata_server_1.0.0.jar

and complete all the assistancesteps:

Step 2. Ask your Jira Administrator for permission to connect

Only uses with the SQL Cloud Connect global permission can connect and share data with third parties.

This is controlled by Jira Administrators, so if you do not see the SQL Cloud - Query Server option under the Apps menu, please ask your Jira administrator to grant ityou. (Read more about the global permission here)

Step 3. SQL Cloud database credentials

As with most databases, SQL for Cloud requires user credentials before sharing any data with third parties, including the OData Local Server above.:

Open the SQL Cloud Query Server page and set your password:

• Username: It is read-only and it matches the name of the Jira Cloud instance (https:<instance>.atlassian.net)

• Password: A random password is generated automatically, but you can change it by clicking “Edit password” link and set your own. Never use the Jira password here as this is instated to control the SQL Cloud database access and not for Jira login.

Step 4. Enable the Query Server feature

Furthermore, the Jira Administrator grant as described above, the server feature is disabled by default for all the users and it will not accept any third-party connection until the Jira user enables it explicitly:

Please, select the checkbox “ENABLED”, so the local server could connect to it.

Convert your SQL query into an OData feed URL

Step 1. Run the OData Local Server

The installer created quick access in the Windows start menu:

The OData Local Server will open a console window at the start-up.

Copy the URL from the console:

Step 2. Login to the OData Local Server

Insert copied URL into the search bar in your Browser and press enter. It will ask you to insert the login and password that you have already set in SQL Cloud Query Server

If the password and login are set correctly, you will see this message in your browser

A new page is automatically opened in your browser and connected:

Now the OData Local Server and the SQL Cloud database are connected and ready for sharing data!

Pay attention to the OData feed builder button on the top right corner as it will be useful for the next steps:

This button is only visible from pages connected to the OData Local Server!

Step 3. Open the OData feed builder dialog

Now you can go back to SQL Cloud - Query Server page

In the right upper corner will appear a button that will bring you to OData feed builder pop-up dialog:

Step 4. Insert your SQL Cloud query

You can insert your SQL query there:

Step 5. Select a Primary Key (if any)

Select one of the columns of your SQL query as the primary key. A primary key is always required by OData. If your query does not have any column as primary key then leave it blank and the OData Local Server will automatically create a column as a primary key with random values (RANDOMID column):

Step 6. Convert your SQL Cloud query into an OData feed URL

Finally, click on the Build Feed URL button to convert your SQL query into an URL and copy it to the clipboard for later use. This URL can be pasted into your analytics tool to import the data from Jira:

Example (MS PowerBI)

Let's connect it to MS Power Bi

Step 1. Select the type of Data (ODATA Feed)

Step 2. Insert copied URL from ODATA feed Builder

Step 3.

Provide the login and password

that you previously set in your SQL Cloud - Query Server and select the name of your local host.

DONE!!!

Now your query is transformed into tables that are ALREADY interconnected and you can build your best report!