Guide: Using Azure with Databox

    Access your SQL query results right on your mobile device, big screen or computer

    Azure SQL Database is the intelligent, fully managed relational cloud database service that provides the broadest SQL Server engine compatibility, so you can migrate your SQL Server databases without changing your apps. Accelerate app development and make maintenance easy and productive using the SQL tools you love to use. Take advantage of built-in intelligence that learns app patterns and adapts to maximize performance, reliability, and data protection.

    Source: https://azure.microsoft.com/en-us/services/sql-database/

    What will we accomplish in this tutorial?

    In this article, we will first configure the Azure SQL Database, add a sample database, add a remote user, add a foreign IP to the firewall and run a query on a sample AdventureWorks database. We will then connect to the database via Databox and then visualize the data. The end result will be Databoard displaying the data from the Microsoft Azure SQL Server database.

    For this example we’ll first create a new Azure SQL database.

    IN THIS ARTICLE

    1. Create a new SQL Database

    Login to the Azure portal https://portal.azure.com/, choose the SQL databases tab, click Add and populate the form like below.

    The database will need to run on a server, so if you don’t have one already, let’s create one:

    Populate the fields with your own data. The server name must be unique. Click Select so the server will be used to run our database. 

    Create a new Resource group if needed, we’ll call it rg1 and for Source select the Sample (AdventureWorksLT) database.

    The final New Database form looks like this:

    With everything set, let’s create the database. After a few minutes the newly created database should appear:


    2. Configure remote access to SQL Server

    We now need to enable remote access to this database. Click on the newly created database, Overview tab and then Set server firewall.

    Firewall settings panel will open, let’s enter the Databox’ public IP 52.4.198.118, which will connect to this database with credentials set.

    Click on the Save button above and wait until the firewall rules are updated:

    For further security and configuration considerations, please consult the official  Microsoft Azure SQL Database documentation.

    That’s it! We’ve successfully set up a demo database and allowed a single outside IP to connect to it. Our server should be remotely accessible now.  In the next step, we’ll be moving over to Databox to establish the connection.

    3. Connect the Azure SQL data source to Databox

    Our database is now ready. The next step is to connect it and test that it’s returning the data we need for our visualizations:

    To do this, log in to your  Databox account. Navigate to Data Manager and select the + New connection button.

    Filter this next screen by typing Azure in the Search box on the right-hand side. The Microsoft Azure SQL connector will now filter to the top.

    Hover over the connector tile and click on the Connect ribbon. This will open the Connect screen:

    Enter a recognizable name for this connection and all of the additional information you’re prompted to add.

    Click Activate. If all went well, you should see the message  “[name] connected” after a few seconds. If that doesn’t happen, double check that you’ve followed the article correctly.

    If a timeout error occurs, we couldn’t connect to the server. This is possibly due to a firewall issue.

    If all else fails, contact support ([email protected]), so we can debug the issue.

    4. Running a custom query

    We will create a custom query to test this connection. Databox will run the query and display the data returned in a tabular format. There are a couple of things we need to make sure of:

    • The query must limit a query result to maximum of 1000 rows. LIMIT the query if you’re not sure if you will exceed this number.
    • The query must contain a column named date (using AS when SELECT-ing data is perfectly acceptable)

    In Databox, navigate to the Data Manager and select the Query Builder submenu item. Click the + New Query button.

    Choose the newly connected MSSQL datasource in the dropdown, and enter the following query:

    SELECT TOP 20 p.ModifiedDate AS date, p.Name, sd.OrderQty
    FROM SalesLT.SalesOrderDetail sd
    INNER JOIN SalesLT.Product p ON sd.ProductID = p.ProductID
    ORDER BY sd.OrderQty DESC

    Click Run query. After a few seconds, you will see the results in the Data Preview section.

    Databox summarizes the results by Dimension and include a Total value, below the line.

    Since we’re successfully receiving results from the remote database, you’ll want to give the query an appropriate name, we’ll call it “Azure Test”  and click Save query. New query now appears in the Query list with info when it was last synced.

    You have now validated the connection and run a test query. Databox has successfully pulled the data from the Azure SQL Server database. Once we save the custom query, data will be fetched regularly (usually either once an hour or once daily depending on your Databox plan).

    In the last step we will visualize the data using the Databox Designer tool.

    5. Visualize Your Data with the Databox Designer

    Now that the database is connected, we will use the Designer to query, shape and display the data in a format that’s most appropriate for our needs:

    Navigate to Databoards. You can either choose an existing databoard or create a new one. Once you’re in the Designer, click on the Visualization Type icon on the left hand side.

    For this example, we will display our data in a tabular format. Drag & drop the Table  Datablock onto your databoard.

    Click on the new Table Datablock and enter the Properties in the right hand Properties Panel like below:

    We have selected our "Databox Test" data source, chosen the "Azure Test" metric and added All Time and Today as Date Range intervals in the Properties Panel.

    After entering the data, please wait a few seconds, until data is pulled from our warehouse storage and recalculated to reflect the chosen settings.

    The table now looks like this, All Time interval is selected:

    Well done! Your database is now connected to Databox. Queries can be executed and then displayed on your mobile device, TV or computer.

    Go ahead and explore further. Add more custom queries, create new Datablocks, play around with different types of Visualizations.... Make the perfect Databoard that you always needed but didn't know how to create.

    If you need further assistance, please contact Support at [email protected] 

    Troubleshooting:

    • Make sure all data entered is correct and the roles are appropriately set.
    • One column must contain the Date value. You can cast any column name to name “date” using the “AS” SQL syntax
    • One column should always contain the numeric value
    • Make sure your firewall is open to our connector IP.

Still need help? Contact Us Contact Us