SqlConnectors - Connect to your data in Excel

Let me tell you about the best secret I sneaked into SeoTools 5.0! This is a life-saving feature that I use almost every day at my day job as a growth hacker.

Wouldn't it be nice to be able to access data from your database in a structured and user-friendly way directly in Excel?

Say for example that you have a list of user ids (maybe from a custom dimension in GA?) and you need to quickly figure out the name of each user? What if you could create a SeoTools Connector based on a SQL-query that produces an user-friendly UI where you can input the id and get the name as a result through either a formula or a wizard.

Enter SqlConnectors.

SqlConnectors is something you can create yourself if you know how to query your database using SQL.

To demonstrate this I've created a SqlConnector using the classic Northwind database example.

View the source code for the Northwind example on GitHub.

This is what your get when Northwind.xml is added to the /connectors/ directory:

How to make your own SqlConnectors

Connectors are written using XML. One or more connectors are collected in a connectors suite. A Suite must have an Id and a Title. Suites with SqlConnectors also has to have a setting with the Id="ConnectionString".

<?xml version="1.0" encoding="utf-8" ?>    
<Suite Id="Northwind" Title="Northwind">
    <Settings>
        <Text Id="ConnectionString" Title="Connection" DefaultValue="Data Source=,1433;Initial Catalog=;User Id=;Password="/>
    </Settings>
    ...
</Suite>

SqlConnectors have 3 parts: Parameters, Sql-template and Columns:

<SqlConnector Id="Customers" Title="Customers">
    <Parameters>
        <Text Id="Id" Title="Id"/>
    </Parameters>
    <Sql OrderBy="CompanyName">
    <![CDATA[
        SELECT
        *,
        (
            SELECT
            ISNULL(SUM(UnitPrice*Quantity*(1.0-Discount)),0)
            FROM
            [Order Details]
            INNER JOIN Orders ON Orders.OrderID = [Order Details].OrderID
            WHERE Orders.CustomerID = Customers.CustomerID
        ) AS TotalOrderValue
        FROM
        Customers
        WHERE
        1=1
        @if(Model.Id != "")
        {
           @: AND Customers.CustomerId = '@Model.Id'
        }
    ]]>
    </Sql>
    <Columns>
        <Column Id="CustomerID" Title="Id"/>
        <Column Id="CompanyName" Title="CompanyName"/>
        <Column Id="TotalOrderValue" Title="Total order value"/>
        <Column Id="ContactName" Title="Contact name" Checked="false"/>
        <Column Id="ContactTitle" Title="Contact title" Checked="false"/>
        <Column Id="Address" Title="Address" Checked="false"/>
        <Column Id="City" Title="City" Checked="false"/>
        <Column Id="PostalCode" Title="PostalCode" Checked="false"/>
        <Column Id="Country" Title="Country" Checked="false"/>
        <Column Id="Phone" Title="Phone" Checked="false"/>
    </Columns>
</SqlConnector>

A Connector also must have an Id and a Title.

The Sql-template is written in the powerful Razor template language . When compiling the Sql-query, the @Model is populated with the values of the connector-parameters and suite-settings set by the user.

See other open-sourced Connectors for more examples on how to specify parameters and work with Razor templating.

I hope you also think this is as exciting as I do. By building SqlConnectors I can automate and simplify my data-juggling extensively. Let me know what you think in the comments below!

Limitations

Only been tested with MS-SQL.