What is "Incorta on Incorta"?
It is well established that Incorta can serve up data to third party visual tools or downstream systems by way of its SQL interface. But can Incorta consume from its own tables? And, if it can, why would you want to?
Simply put, yes. We call this "Incorta on Incorta". In the article below, we will explore how to set this up and why this can be a welcome feature.
How Incorta connects to itself...
Because Incorta installs with a SQL interface that makes it "appear" to third party tools like a Postgres database, we can have Incorta connect to its own tables by leveraging its own Postgres JDBC driver.
A few technical tidbits to call out from the screenshot above. In practice, we typically call the connection "Incorta" because, after all, that is what we're connecting to. We find this name most apt, but feel free to name it whatever you like. As for all Postgres-JDBC-driver- to-Incorta connections, our JDBC connection string is composed as such:
jdbc:postgresql://<incorta server>:<port for SQL interface>/<incorta tenant>
Because Incorta is connecting to itself, the server name here will almost always be localhost and the default port is 5436 (the default port can be changed from the admin UI, if necessary). You use the same username and password you use to login to Incorta through the web browser. Note that this Incorta connector will only be able to "see" the Incorta tables that the user can see when logged in to the web browser.
How to use the connection...
Once our Incorta connector is established, we're going to use it the same way we use any other SQL-based connector. We will use it when defining new tables. This allows us to generate new tables in Incorta by querying Incorta tables we've already loaded.
By way of example, see this new "CustomerSummary" table that was created using our new Incorta connector by querying some of our point-of-sales tables previously loaded into Incorta.
This table is defined based on a fairly straightforward SQL SELECT statement seen below which groups by each customer ID and produces some customer-level metrics about each customer, like "total amount spent" and "total number of orders".
It is worth noting that you have to specify the schema and table name when referencing Incorta tables and they are case sensitive. Thus, in my SQL example above, the "AW.product", for example, is all required.
Once I have loaded this table in Incorta, it can simply be joined back into my schema's customer table by customerID to bolster my analysis.
Why use Incorta on Incorta?..
The example given above is pretty straightforward. It would be easy enough to build an Incorta insight on a dashboard that produced the same metrics with just a few clicks. So when is this feature really useful? While not exhaustive list at all, I have listed a few scenarios below where this can be a very welcome feature.
- When you need summary level metrics to also be provided as filters
- When delivering level-based measures
- When there is complex business logic that gets applied during aggregations that are better suited for SQL, the SQL already exists, or there isn't an elegant way to deliver that business logic within an insight at query time.
Think of "Incorta on Incorta" as ELT (extract, load, and transform), instead of ETL (extract, transform and load). While Incorta's direct data mapping engine eliminates the need to reshape data mindful of performance, there are still situations where you'll find this feature is a better "solve" than leveraging some other Incorta features (like formula columns or aggregated insights).
The "Incorta way" remains to always attempt to deliver the required insight without any data reshaping. So while this ELT approach isn't always needed, we're always eager to hear from our customers how they are using this in field in the wild.