GRIP in a Nutshell ...

Introduction of GRIP ETL-toolkit

The ETL toolkit from Grip op Data includes a set of powerful building blocks that allow you to quickly develop any desired data warehouse. Additionally, these building blocks can be used for data migration, data replication, data quality, and data warehouse automation. The GRIP ETL building blocks run both on-premise and in the cloud. From any Windows computer with database connections, GRIP can access your sources. Using the GRIP editor, developers, administrators, and business analysts can easily execute GRIP commands and SQL queries on the selected database.

GRIP ETL-automation

ETL and ELT tools generate SQL code for moving and transforming data. Data warehouse automation goes a step further and follows the ELT principle, where database objects are generated, including tables, indexes, views, dimensions, and insights. GRIP is capable of breaking down complex and time-consuming operations into smaller steps. Advanced flow control monitors the process, logging each step. This is complemented by comprehensive business and technical documentation logging, including complete data lineage and version control. GRIP is the blue engine.

GRIP simplicity versus graphical complexity.

Most ETL tools use a graphical development environment where the data warehouse is modeled. Code is generated and deployed from complex visual designs. One advantage mentioned is that a developer with little SQL knowledge can quickly get started. However, complex models with numerous lines and control checkboxes can quickly become difficult to oversee. After code generation and deployment, the generated SQL code can only be corrected through the model

With GRIP, this visual layer is bypassed, but it offers a set of ETL functions. The BI specialist only needs to define their data views in SQL and submit them to a suitable ETL function. Based on the view, all necessary SQL code is generated and executed. If the target table does not exist yet, it is created based on the structure of the source view. Three log tables provide adequate information logging for the ETL processes. So, the VIEWs are the foundation, and GRIP takes care of the rest. Ultimately, developing with GRIP is 10x faster than with other tools.

De ETL-functions

With the following GRIP ETL functions, any imaginable data warehouse can be created.

record_on('| FLOW MyFirstFlow | CLEANUP | ')
  trunc_insert  ('| SOURCE xx | TARGET yy | TAREGT_DB tt | WHERE wwww |') 
  insert_append ('| SOURCE xx | TARGET yy | TAREGT_DB tt | WHERE wwww |') 
  actualize_t1  ('| SOURCE xx | TARGET yy | TAREGT_DB tt | WHERE wwww | BUSINESSKEY k1,k2,k3  |') 
  actualize_t2  ('| SOURCE xx | TARGET yy | TAREGT_DB tt | WHERE wwww | BUSINESSKEY k1,k2,k3  |') 
  actualize     ('| SOURCE xx | TARGET yy | TAREGT_DB tt | WHERE wwww |') 
  table_compare ('| SOURCE xx | TARGET yy | TAREGT_DB tt | WHERE wwww |') 
  rid_update    ('| SOURCE xx | TARGET yy | TAREGT_DB tt | WHERE wwww |') 
  rid_delete    ('| SOURCE xx | TARGET yy | TAREGT_DB tt | WHERE wwww |') 
record_off()

"Each function has a specific LOAD task, generates specific SQL code, but shares the same parameters:

  fie('| SOURCE ss | TARGET tt | WHERE | LIMIT 10  | LAST_DATE_PROCESSED date_created |  |'  )

For example, the actualize_t2() is a GRIP-ETL function that updates the TARGET table with the current source table. The SOURCE is a view containing a simple or more complex SQL query. This SQL query is based on the business rules of the functional or technical design. Through parameters, the desired SQL code is generated with this ETL call."

    actualize_t2  ('| SOURCE GRIP_METADATA_V  | TARGET METADATA_JAG3  
                          |  MERGE_KEYS SCHEMA_NAME,TABLE_NAME,COLUMN_NAME |')

The actualize_t2 updates the TARGET table: it closes modified occurrences and adds new occurrences. This routine, for example, can be used to create a history of a source system that does not inherently maintain it."

Another parameter is, for example, LAST_DATE_PROCESSED, allowing you to automatically filter data that you have already retrieved with the previous ETL, making it easy to fetch delta sets instead of the entire set. And with daysback 10, you can easily retrieve the" (the message seems incomplete, could you provide the rest of the sentence?)

Customers

GRIP is responsible for processing data to usable information for reporting purposes to varying degrees at the following customers:

• Schiphol (Flights, departure hall, shops, parking, security, maintenance, access, financial, and more...)
• Achmea Syntrus (real estate)
• Royal Smilde Foods (warehouse behind JD-Adwards, financial, commodity, forecast)
• RIVM (heel prick tests and COVID vaccination)
• Martini Ziekenhuis (HIXS POC real-time free bed overview)
• Lekkerkerker Rotterdam (rental of steel plates)
• PROBO Dokkum (printing, flags, various cloud environments)
• Royal Koopmans (flour, JDE/SqlServer)
• Westerkwartier (municipality, social domain, and other integrations)"

practical case RIVM:

The practical case at RIVM involved the migration of a data warehouse related to heel prick tests. Here are the key points:

• The migration was from Oracle Warehouse Builder/ODI to GRIP.
• The original data warehouse comprised approximately 240 source tables.
• Source replication to the STAGE was generated 100%.
• The ODS (Operational Data Store) was in Type-2 3rd normal form and could be generated 100%.
• Datamarts needed to be transferred one by one, copying ETL packages from generated ETL packages to Oracle ETL views.
• The new data warehouse underwent a comparison with the old system using GRIP_tablecompare.
• The image representation showed a BLUE datapump for GRIP.
• The refresh time for the entire warehouse was reduced from 24 hours to 1 hour.
• This migration resulted in improved efficiency and performance in data processing for RIVM's heel prick test data warehouse.
• The new data warehouse could be compared to the old phased-out system using GRIP_tablecompare. In the above image, the BLUE datapump represents GRIP. The refresh time for the entire warehouse was reduced from 24 hours to 1 hour.

practical case Schiphol:

Initially, there were four Oracle Warehouse Builder data warehouses for the processes of Parking, Retail, Flights, and Access Control. These systems were migrated to GRIP in a very short time. Due to the speed and cost-effectiveness of implementing warehouses with GRIP, approximately 15 additional data warehouses were developed in a short period. In total, there are now about 20 data warehouses in operation. One of these warehouses is ONEXS, which registers the congestion in departure halls/check-in counters and provides KPI reporting based on that data.

practical case Smilde Foods:

Migrating an Oracle Warehouse Builder/ODI data warehouse to GRIP involved seamlessly transferring the STAGE and ODS using ETL automation. The code was generated 100%. Each component of the Datamart was moved to GRIP through a copy/paste process. This warehouse sourced data from JD-Adwards and prepared it for reporting with OBIEE. The simplicity of expanding the warehouse with new sources became evident at Royal Smilde as well.

practical case Achmea Syntrus:

At Achmea, where an Oracle Warehouse Builder/ODI data warehouse reached the end of its life, the migration to GRIP was easily performed by a Business Analyst. Due to the self-sufficiency of the organization, the analyst practically executed the migration independently and added various datamarts. The processing time of the primary warehouse was reduced from 14 hours to 30 minutes. This showcases the efficiency gains achieved by migrating to GRIP.

practical case PROBO:

Unlocking various (cloud, Amazon) sources into a SQL Server cloud data warehouse was achieved. The functional administrator, equipped with some SQL knowledge, quickly became proficient in independently designing GRIP-ETL processes. In a short period, the first datamart was rolled out. Work on the next datamart is already in progress. This illustrates the user-friendly nature of GRIP, enabling individuals to swiftly design and implement ETL processes.

GRIP Databases

The GRIP-ETL tool seamlessly operates on systems such as ORACLE, SNOWFLAKE, SQLSERVER, and POSTGRESQL or MySQL, as well as on MSACCESS or CSV files. With minimal effort, the ETL functionality can be adapted for other databases.

Recently, SNMP-ETL has been added for reading data from temperature sensors or laser printers for paper consumption. This highlights the versatility of GRIP in handling various databases and integrating with diverse data sources.

Your requirements

GRIP has been deployed at various clients, meeting the following requirements:

• Connection with Cloud applications using ODBC or API gates (ITSM, Salesforce, Jira).
• Hybrid architecture is possible: Cloud and on-premise work well together.
• Simple installation and setup.
• Operates in a basic Windows environment from 16GB, 2 cores.
• Easy incremental and full loads are possible using parameters.
• Duplicate and null-value checks on BUSINESSKEY.
• Target tables are automatically generated if they do not exist.
• As a source, MS SQL, Snowflake, Oracle, PostgreSQL, MySQL can be selected with only select rights. Complex sql-queries on these sources are encapsulated in GRIP scripts.
• Any of these databases can also be selected as the target datawarehouse.
• Very fast ETL processes. Handling quantities such as 20,000,000 to 200,000,000 is feasible.
• The number of tables can vary from 30 to 3000 pieces.
• Simple parallel processing can be implemented if quantities are too large.

Proof of Concept

To get a good understanding of the power and simplicity of GRIP, we can provide you with a VPS environment with GRIP and, for example, a MySQL or PostgreSQL database. This VPS environment can be easily and securely added to your network so that you can gain hands-on experience with GRIP! Trying out a trial course is definitely worthwhile!