A common use case we see is the combining (union) of data from multiple connections of the same platform.

An example is an accounting firm that needs to build BI dashboarding over different legal companies, each having their own accounting done in a separate accounting system e.g. PowerOffice.

In the Peliqan Data App templates you’ll find an example app that unions data an makes it available for further analysis and BI. This article gives more details on how to implement and adapt to your needs.

Data lineage view of a generated union query over 7 PowerOffice connections.

Activating the template

In the data app templates, select ‘union connection data’, open and and run the app:

Streamlit modal to select the connection type, the connections and the tables to union.

The data app will generate for each selected table and connection a union query in Peliqan in a new folder (schema) “union_<connectiontype>” and also activate this query as ‘view’ in your data warehouse for BI systems to connect to.

Once ‘generate queries’ is selected, you will see the script executing an showing per table the actual query you can easily copy for customization.

Data App

The data app has 4 main blocks:

• Start: database connections and helper functions. The critical one is ‘generate_sql_union’ that is responsible for the actual SQL creation.