Seamless Cloud Data Integration for Finance Professionals
The new Invantive UniversalSQL Server expands seamless real-time access to financial and operational data from cloud applications using the Microsoft SQL Server TDS protocol.
With this innovation, finance professionals and organizations can now directly connect cloud-based accounting, marketing and ERP platforms to a wide range of SQL Server-compatible tools and applications. Previously, Invantive supported around 10 standard client products along with OData4 consumers such as Power BI, Qlik and Tableau. Now, the reach extends to a vast ecosystem of SQL Server-integrated solutions, including Microsoft Access, ODBC, ADO.NET, and countless BI and reporting tools.
Currently in development, Invantive UniversalSQL Server will first be rolled out to a select group of early adopters for testing. This preview offers a glimpse into the future of effortless financial data connectivity - eliminating manual exports, enabling real-time reporting, and empowering finance teams with instant access to cloud data directly from familiar SQL environments.
Samples
Finance professionals can now retrieve and upload data directly with over 80 cloud platforms using SQL Server Management Studio (SSMS) - just as if they were working with a traditional SQL Server database.
For example, the following query demonstrates how a Data Dictionary table function can be executed with two simple parameters, 10 and 1, showcasing how effortlessly cloud-based financial and operational data can be accessed in real time:
In the example below, a table is created using the in-memory storage of Invantive UniversalSQL, combining data from a Data Dictionary table function and the list of projects from the Sample database driver:
The red accents in the example highlight syntax differences between UniversalSQL and SQL Server’s native parser. For instance, string concatenation in UniversalSQL uses ||, a common approach in various database platforms, whereas SQL Server requires concat() or +.
Additionally, the red squiggly line after pjt indicates a missing join condition. While UniversalSQL allows optional joins, most relational SQL platforms, including SQL Server, require explicit join conditions.
Once the table is defined, the next step queries the in-memory storage, retrieving and analyzing the structured data in real time:
Similarly, the SQL Server Management Studio can also be used to real-time query for instance Exact Online, AFAS or Twinfield as if a copy of the platform data had just been made in a SQL Server database.
Advantages
The new TDS support for Invantive UniversalSQL has the following advantages:
- Seamless integration with SQL Server Tools: users can leverage SQL Server Management Studio (SSMS), ODBC, Access and other SQL Server-compatible tools to directly query over 80 cloud and on-premises platforms without additional connectors.
- Real-Time access to cloud data: instead of relying on batch imports or data replication, queries fetch the latest available data on demand, ensuring up-to-date financial and operational insights.
- Native DirectQuery support in Power BI: with Invantive UniversalSQL Server, Power BI can now use DirectQuery mode to connect to cloud accounting platforms in real-time. Users can analyze live cloud data without importing it into Power BI, leading to faster insights and more responsive reporting.
- Standard SQL Interface for Heterogeneous Sources: developers and analysts can interact with cloud accounting data using familiar SQL syntax, reducing the learning curve and eliminating the need to master each platform’s API.
- Broad application compatibility: any application that supports SQL Server, ODBC, ADO.NET, or Access can exchange data with cloud platforms without requiring vendor-specific drivers or custom development.
- Faster decision-making: with real-time data retrieval, businesses can make data-driven decisions faster rather than waiting for scheduled data updates to update their data warehouse.
- Reduced ETL complexity: traditional ETL (Extract, Transform, Load) processes become unnecessary or minimal, reducing maintenance overhead and potential errors from data replication.
- Real-time data integration using Invantive’s extensions such as
synchronizeease the integration of new cloud applications in your business processes. - Cross-platform querying: users can join data from multiple cloud accounting systems (such as Exact Online, AFAS, and Twinfield) within a single SQL query, facilitating cross-system reporting and analysis.
- No need for local data warehousing: since the data is retrieved in real-time, there’s no need to maintain an additional data warehouse, reducing storage and processing costs, and security risks.
- Consistent data governance: direct queries ensure that data always reflects the latest rules and restrictions enforced by the cloud platform, avoiding compliance issues that arise from outdated snapshots.
- Simplified development & maintenance: by using the TDS protocol, existing applications designed for SQL Server can interact with cloud data without modifications, significantly reducing development and support efforts.
These advantages make Invantive UniversalSQL Server an essential tool for organizations looking to simplify cloud data access, enhance reporting, and improve operational efficiency with real-time insights.
Performance and Scalability
The TDS protocol adds little to almost no overhead to the unique performance features of Invantive UniversalSQL.
SQL statements are executed in parallel on many cloud platforms, solely restricted by their rate limiters and the number of processors. In test cases, it was found to scale nearly linear up to 64 processors. Additionally, multiple SQL statements from a single or many users can be executed in parallel.
Data is returned as a stream, interleaving cloud API activity with data retrieval.
The caching mechanisms, query optimizer and driver optimizers provide excellent real-time performance. The use of table functions and server-side filtering simplifies writing performant queries.
Availability
The initial production release of Invantive UniversalSQL Server will be available for on-premises use. Depending on perceived quality it will be available for users with a Premium Finance or Invantive Office Premium plan around Summer 2025.
In a future release, support for the TDS may be added to Invantive Cloud.
Security and Audit
Security and audit are identical to the features provided by Invantive UniversalSQL itself. Encrypted data transport between Invantive UniversalSQL Server and the client that supports SQL Server protocol requires a TLS-certificate.
Security can be extended by label-based access control.
Extensive on- and off-site audit logging is available on various levels.
Enterprise Features
Invantive UniversalSQL Server can be extended by custom drivers and features.
Prerequisites
Invantive UniversalSQL Server runs on Windows, Linux and MacOS using Microsoft .net 8.0.