Getting started with the Query Editor

Getting data from your cloud service is only the first part of your data journey with SyncHub. Next, you need to begin reporting on it. Many customers use their existing reporting tools, like Power BI or Google Sheets, to help understand their data - and this is the most typical use case for advanced reporting or integration with your existing BI dashboards.

But sometimes you have just a quick question, which doesn’t really justify a powerhouse reporting tool. And for this, we have our Query Editor - a powerful exploration tool with a few surprises you won’t find anywhere else.

Querying your cloud data using regular SQL queries

The SyncHub Query Editor is just as it sounds - a web interface which lets you write & execute SQL queries against your data store. If you’ve used editors like SQL Server Management Studio, or PGAdmin before, then you’ll know what we’re talking about.

Did you know? The Query Editor uses your provided read-only database credentials to directly query your data warehouse.

A quick Share button will also let you share those results with anybody you like, via email - super handy for ad-hoc queries and insights. But unlike other editors, ours has a few extra tricks up it’s sleeve, which you won’t see anywhere else:

• AI-powered SQL assistance

• Embedded queries

• Dynamic parameters

Let’s take a detailed look at them now…

Let our AI Assistant write your SQL queries for you

If your SQL skills are a little rusty (or non-existent), do not fear. Our Query Assistant is only a click away. Our assistant understands the data model of your cloud service and can generate SQL statements based only on your descriptions.

You can carry out a complete conversation with our assistant, refining your query and adding additional levels of complexity. Our Query Editor will even feedback the results of the queries it recommends, allowing you to quickly iterate and get to the data you need.

Pretty cool, huh?

Embedded queries

If you have a SQL statement which you use often, you can save this as an embeddable query, and integrate it directly into your other queries. At its most basic, you can consider this analogous to how VIEWS work in regular relational databases. But as you’ll see, that is only scratching the surface - let’s have a look with an example…

Consider that you are querying your Harvest data, and you are trying to convert times from UTC into your local time zone. The SQL to do this uses the at time zone function in SQL Server:

If you’re anything like me, you can get pretty sick of sprinkling this all through your queries. This statement uses it four times - imagine what it is like across your entire query library?

Let’s tidy things up with a re-usable parameter instead. Simply copy your at time zone string into a new query (1), then give it a nice simple embeddable name (2) to refer to it by…

…and now you can replace your original statement with this tidy snippet:

Ah…much nicer!

But it gets even better. Imagine head office wants to view your reports, but need them in AEST instead of New Zealand Standard Time? Instead of having to trawl through all your queries with your find & replace skills, you can just update the embedded query once, and voila - all your reports are up to date.

And that’s just a trivial example. Your embedded queries can be as long and complex as you like. At SyncHub, for our own internal reporting (yes, we eat our own dog food), we use embedded queries to encapsulate extremely complex business logic involving dozens of lines of code and multiple select statements. Views like Top 20 customers or Stock expiring within 7 days become a cinch to re-use and maintain throughout our BI dashboards.

Dynamic parameters

Often times, you’d like to run the same SQL query multiple times, but with a differentiating variable. For example, you may wish to see last week’s Chargeable Time, for each of your staff members. Or Last Month’s Sales, for each of your product lines. But how exactly do you do this, when your staff members and products are evolving over time?

This is where our Dynamic Parameters come in - again, let’s demonstrate by stepping through an example.

Meet Sherry

Sherry has just joined Mega Corp - a provider of vague services - as their in-house business analyst. Sherry has been assigned an easy requirement for her first week - a simple breakdown of invoices charged to each customer in the last couple of years. The C-suite would like a separate report for each of their customers, so they can disburse the results to the appropriate department heads.

No problem for Sherry. She quickly whips out the SyncHub Query Editor and grabs the invoices from Xero for the last couple of years:

Hmmm, well as expected, all the customers are mixed up together. She has the data, sure, but it’s not usable. Sherry decides to add another filter, to get the results for just one client:

Well, that’s better - Sherry now has all the past invoices for just PowerDirect. But there are a couple of obvious problems here:

1. She has another X customers to query for - but how does she even know who those customers are? Even if she had a list of Mega Corp’s customers, she wouldn’t know which ones were actually invoiced recently until she ran her query - it’s a little chicken and egg, really.

2. Not to mention that she’s hard-coded the company name - it would be much more prudent to use the ID of the customer, but these are GUIDs particular to Xero - her list from Mega Corp definitely does not include these.

Getting recent customers, and their UUID

As it happens, both of these problems are easily addressable using SyncHub’s Query Editor. Sherry opens a second tab, and writes the following query:

Awesome! There’s her list of recently invoiced customers, and the unique GUID that Xero assigns to each of them. There are 22 customers, so all Sherry has to do now is copy/paste the ContactID into her Invoice query 22 times…

Er…yeah, nah

Sherry’s a coder - she likes to keep things DRY. Plus, she knows that this list of “active” customers will fluctuate over time, as customers come and go. That customer query will need to be executed and evaluated every time she wants to generate her invoices - no way.

Gosh, if only there was some way to integrate this Customers query with the previous Invoices query….

….hmmmmm….

….if only….

Enter - dynamic parameters!

Well, what do you know? There is a way to do this. Which is very convenient, as this would be a pretty anti-climactic blog article otherwise.

Sherry returns to her Customers query and configures it for “parameter injection”.

To do this injection, SyncHub needs to know a couple of other things about your query:

1. Which value does she wish to inject into the query? In this case, it’s her ContactID column

2. Displaying a list of ContactIDs to the user isn’t very useful, so SyncHub also lets you describe each parameter value. In this case, Sherry elects to render the customer Name - much easier to understand.

Sherry can now return to her original Invoices query and instructs SyncHub to inject values from Customers into her report.

Using your parameter

The hard part is done now. When Sherry continues editing her Invoices query, she’ll have her new parameter available via intellisense. SyncHub will detect its incorporation into the query, and prompt the user accordingly. The short animation below explains it a lot better than I can though:

Fantastic! Above Sherry’s Invoices query, she now has a drop down list of active customers which she may filter her report by. It couldn’t be much simpler. And best of all, because the customer list is drawn from the live data too, it will dynamically adjust as customers come and go over the years.

SyncHub Insights

Finally, for distributing these queries, or pre-calculating long-running queries, we recommend checking our Insights platform.

To conclude…

The SyncHub Query Editor is a powerful and flexible alternative to your third-party reporting tool, allowing you to write & share ad-hoc queries on your data warehouse.

Happy reporting!