As businesses’ data access, migrations, and federation need to grow and expand, workforces are becoming increasingly remote and decentralized. Employee access into the data environment is achievable in several ways, including VPNs and remote desktop solutions, but these strategies are inadequate solutions to the need for efficient data movement. Even in optimal networking environments, schema conversion, data translation, and ELT processing can be cumbersome and technically challenging.
Nexus Server is a software solution that offers a complete data migration and data virtualization solution between database platforms. A local installation of Nexus Desktop client allows users to manage high volume data movement regardless of their physical proximity to the data. The Nexus Server converts database schemas and appropriately translates table DDL and data between most modern database platforms to limit excessive administrative hours.
Coffing Data Warehousing offers personalized assistance and recommendations in setting up and administering a Nexus Server Proof of Concept, and in most situations, can customize development based on POC feedback.
The Nexus Server manages the data movement between all database platforms on a high-speed network. Users can move or join data from their Nexus desktop, and the Nexus Server runs the job for the user using the user’s login credentials.
Data migration typically requires schema, table definitions, data type conversion, data translation, and load utility script creation for each table included in the migration.
A project that involves the migration between two database platforms can take months to prepare. Resolving issues and errors during the load process can be time-intensive, and data loss can occur.
Nexus Server assists in the migration from legacy platforms to on-premises and cloud platforms. Nexus generates ETL based on the source and target systems and supports custom ELT scripts. Nexus allows users to make changes to the columns and rows they want to move, the target table definition (DDL), indexes, and distribution. It also provides the ability to transform data before or after the migration occurs.
The Nexus has many options for transferring data. A user can receive a result set from a query and drag the resultset to their system tree, where Nexus creates a table automatically. The user can also right-click on a single-table in their system tree and choose a database platform to move the data, which results in the data migration. The user or ETL teams can use the Nexus Database Mover to migrate an unlimited amount of tables to a target system.
Converting table structures, a table’s Data Definition Language (DDL), and the associated data types between dozens of database systems has infinite possibilities that take experts months to convert between two database systems. The Nexus Desktop client and the Nexus Server both have the intelligence to automate the conversion task instantly. Using Coffing Data Warehousing’s conversion program, each source database performs a Rosetta Stone conversion into a common DDL standard to convert tables into the target database format. Although the Nexus converts table structures and data types automatically, the user can alter the DDL manually.
To achieve the highest possible loading speeds, the Nexus Server uses the load utilities implemented by each database vendor. The Nexus Server has the option of using multi-insert statements for small data movements. However, for medium to large data movement, the Nexus Server automatically builds load utility scripts provided by each vendor.
For example, when moving data from one data source into Teradata, the Teradata Parallel Transport (TPT) utilities provide TPT Insert, TPT Stream, TPT Update, TPT Load, or QueryGrid to account for small, medium, and large table loads.
The Nexus Server utilizes hundreds of load utility options based on the source and target systems. The user points-and-clicks on the source tables they want to move. Although Nexus automates the appropriate utility to use, the user can choose from the multiple utilities the source and target vendors use. One of the most challenging and time-consuming responsibilities of experts in data movement is building load-scripts. Each vendor uses proprietary loading techniques with many different options that can take years to learn. The Nexus capabilities of mastering each vendor’s load utilities allow users to point-and-click and let Nexus handle the table DDL and data type conversions and build the appropriate load utility scripts in subsecond time.
The Nexus design automates data movement between any two systems and joins tables across database platforms. Although the user has no requirements other than to pick the source and target systems and the tables they want to move, the user can also transform the data. The Nexus provides many different options for transformation.
The user can change the recommended utility moving the data or change the source and target data types individually. The user can remove specific columns or add a WHERE or LIMIT clause to move particular rows. The user can change the target table definition and the distribution key. The user can choose to transform the data before (ETL) or run a script for transformation after the data lands (ETL). The idea is to automate data movement so users can move between one or thousands of tables with a button click or customize the data movement options down to the smallest detail.
What makes the Nexus product line so unique is its versatility. The Nexus Desktop client can migrate and move tables between any two systems appropriate for smaller amounts of data ranging in size up to 500,000 rows. When speed is a necessity or the size of a table can saturate the local network or the desktop capabilities, the user has the option of executing the job on the Nexus Server. There are only a few differences between the Nexus Desktop client and the Nexus Server software. Both are identical except for two significant differences:
- The Nexus Server has a global calendar, where the Nexus Desktop has a local calendar.
- The Nexus Server uploads the user’s credentials to execute jobs as the user.
The Nexus Desktop client has the intelligence built-in to manage a multi-database environment. Still, the coordination between the Nexus Desktop and Nexus Server provides the versatility to handle the smallest to largest data sets appropriately.
Customers can place a single Nexus Server or multiple Nexus Servers on-premises or on any cloud. The Nexus Server from Coffing Data Warehousing is a software solution only. Therefore, users who want an on-premises Nexus Server will need to provide a Windows Server or a Linux Server with a Windows VM and install the Nexus Server software.
When data load scripts are initiated from a user’s Desktop to move data between a source and target system, the source’s data extracts go through the user’s Desktop and then move to the target database. When the user chooses to execute via the Nexus Server, data extracts from the source through the Nexus Server and then onto the target. The ideal concept between two on-premises database platforms is to have a Nexus Server close in proximity to both platforms on a high-speed network. The same idea for moving data applies to cloud platforms by provisioning a server on the cloud and then loading the Nexus Server software. Having a single Nexus Server to manage the data movement between all cloud and on-premises systems will speed up movement magnitudes of order. Still, customers can add as many servers as they need to enhance speeds between platforms. The concept is similar to how individual cellphones work in conjunction with cellphone towers. Placing individual Nexus Servers in regions near database platforms will enhance speeds even further.
The majority of utility-based data migration is processed locally to the user. Local processing can create issues when businesses rely more on remote workforces and could route the data through a throttled VPN or even the public internet.
Even in migration processes executed on-site, user-level networking can quickly become saturated by extensive data migration processes competing with other local area network needs. Nexus Server allows the execution of data movement in a location close to the database as possible using networking dedicated to data movement. Working from any site, ETL teams can prepare migrations, business users can transfer data or set up federated queries, and can schedule these processes. Nexus Server will honor individual user credentials at the database and domain levels to retain user access rights.
As a business’s data model grows in complexity, keeping users trained on the most recent version of the model is challenging. Users may not know table relationships, and continued data training can reduce a business’s productivity. Nexus allows sharing a data model definition among all Nexus users, which the Nexus Super Join Builder utilizes to build complex queries based on relationships found in the data model.
The Nexus Super Join Builder shows tables, views, Excel worksheets, and Microsoft Access tables visually. Users merely click on the columns they want on their report, and Nexus builds the SQL automatically. Users can point-and-click to customize the report columns, ORDER BY statements, WHERE clauses, inner or outer join, and add analytics.
Because the Nexus can automatically convert table structures and automatically build load-scripts, the Nexus can join tables across platforms, thus automating objects’ integration across the enterprise.
The most impressive and challenging capability of the Super Join Builder is that users can choose where they want the join to process, which we refer to as the “Hub.”
Users can choose to join the data on any database platform, inside their PC, or the Nexus Server. Users can also choose to have the Nexus Desktop move the data to the “Hub” location or have the Nexus Server move the data to the “Hub.”
For example, a Teradata table can join an Oracle table and process the join on a SQL Server system. The “Hub” can be any database system in the enterprise.
Only the data necessary (columns and rows) move to the “Hub,” and users can provide WHERE clause statements to each table to tune the join to the sufficient granularity. The user can also override the default utility method for moving the data and choose from available utilities. Once a user establishes a join, they can save it, share it, and even execute the join from their Nexus System Tree for a one-click process.
As businesses’ data needs grow and change, data “silos” can naturally develop. Different departments choose database platforms based on their own needs, but this can cause problems when business decisions need information from these multiple sources.
The Nexus client software allows users to view all of their database sources in a unified Systems Tree. Users can use the Cache Tree feature to search every connected data source “silo” for metadata such as object names, column names, and data types.
The term Federated Query refers to the joining of tables and views across database platforms.
With federated queries, data must move to a common platform to join with existing data. Nexus can execute federated queries between most modern database platforms on-premises and in the cloud. Users can design reports that pull data from multiple data sources in a drag-and-drop style interface. Federated queries can execute on the database platform of their choice, or the join can process locally in memory. The join can process on the Nexus server for large data and any on-premises or cloud platform within the enterprise.
The Nexus can capture object metadata from all systems so a user can search for object names across the enterprise. The Nexus Cache Tree holds the information and updates object names at time intervals the user determines. For example, when a user does a Cache Tree search on a column name such as SSN, the Nexus changes the system tree into a Cache Tree displaying each system, schema, table, and data type for all database platforms. The Cache Tree is an active tree that allows users to perform Quick Selects to run a query, move data to other systems, or join the table via the Nexus Super Join Builder.
The Nexus Server can perform queries and return the answer set to the user, or the user can choose to create a table from the answer set on any table in the enterprise. For example, a user can schedule a complicated SQL statement on the Nexus Server to run on a Teradata system but automatically save the resultset as a table on Oracle, SQL Server, DB2, etc.
Nexus users can simultaneously query all systems in their enterprise, with each resultset returning in tabs that use the chosen system color. The Nexus gathers all answer sets and places them in the “Garden of Analytics.” In the “Garden,” the user will see all resultsets in individual tabs from all systems. The user can graph or chart each answer set in personal dashboards or join answer sets together. The user can then also use point-and-click templates that to perform analytics. A user can take a result set and produce up to 80 additional analytic reports calculated by Nexus inside the user’s Desktop. Users no longer need to write SQL to query systems but can bring back resultsets and have Nexus create the analytic reports. This concept turns the user’s Nexus into a drop-and-drag analytical database. Instead of using overcrowded database systems to perform analytics, the Nexus “Garden of Analytics” calculates and delivers reports faster.
Nexus users can share answer sets or export them to various formats, including Excel, Access, DSV, XML, text, etc. The user can also use the Nexus Pivot Tool and the Nexus Worksheet to pivot data with easy-to-use drop-and-drag functionality. The Nexus Worksheet allows the user to work with answer sets similar to an Excel environment and automates formulas. Users can even schedule Federated queries to run and automatically trigger additional analytics to calculate inside Nexus to enhance the answer set with advanced analytics.
Since the beginning of database platforms, the vendor has had the upper hand, and the customer has had limited options. The Nexus Server manages the conversion and movement of data putting all of the power to the customer. Companies no longer have to spend enormous time, money, and effort to migrate data to a single-platform to join, but instead, join data across systems.
Individual users have traditionally spent days or weeks gathering data from disparate systems, or they have had to wait for their IT staff to do it for them. Nexus places the business user on a higher playing field than an ETL expert or data scientist by automating the joining or transferring of data by each Nexus user.
Database vendors make it nearly impossible for their platforms to communicate with databases from another vendor. DDL conversion can take months, and data movement scripts are complicated and challenging to write. Networks can quickly saturate, and management of networks takes a delicate balancing act. In our quest to integrate all database platforms, we understand there is only one architecture that works. That architecture is to have intelligent software that automates everything for the user and giving the user the option to execute the work on a high-speed server.
No other company has either seen the vision or have been willing to spend the 15 years of development dedicated to accomplishing the goal. Coffing Data Warehousing has taken the road less traveled and built the best data virtualization environment through an iterative process. Companies can now purchase an unlimited amount of Nexus Desktops and hundreds of Nexus Servers for today’s needs and expansion tomorrow.
Tom Coffing, better known as Tera-Tom, is the founder of Coffing Data Warehousing where he has been CEO for the past 25 years. Tom has written over 75 books on all aspects of Teradata, Netezza, Yellowbrick, Snowflake, Redshift, Aurora, Vertica, SQL Server, and Greenplum. Tom has taught over 1,000 classes worldwide, and he is the designer of the Nexus Product Line.