Vertica Integration with Speedbase: Connection Guide
About Vertica Connection Guides
Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our test ing with specific versions of Vertica and the partner product.
Vertica and Speedbase: Latest Versions Tested
| Software | Version |
|---|---|
| Partner Product |
Speedbase 3.16.0 |
| Partner Product Platform | Windows Server w/IIS and .Net 2012 R2 |
| Partner Product Desktop |
Windows 10 Edge Browser 41.16299.1004.0 |
| Vertica Client |
ADO.Net 9.2.0 |
| Vertica Server |
Vertica Analytic Database 9.2.1 |
Speedbase Overview
SpeedBase BI™ is an easy-to-use framework to record business users’ inputs and updates into the Data Warehouse. The solution provides a secured and consistent way to integrate data that does not usually exist or is located on the source systems, and entered manually by the users.
SpeedBase BI™ offers intuitive tools to ultra-fast build and deploy data-entry screens for business users to record their updates.
Installing Speedbase
Currently there is no downloadable release of Speedbase available. If you need a demonstration, fill out the request on the contact us page of the Speedbase website at https://speedbase.co.il/contact-us/?lang=en. For in-house demonstrations, Speedbase personnel do the installation on your accessible equipment.
Installing the Vertica Client Driver
Speedbase uses ADO.NET to connect to the Vertica database. The Speedbase installation installs the supported Vertica ADO.NET driver and named connector, so you do not need to install the driver separately.
Connecting Speedbase to Vertica
Assuming you have a working local instance of Speedbase, the following steps apply. To connect to Vertica you need to create a Connection object in Speedbase.
Note You need to create one connection object per schema in the Vertica database you wish to work with.
- On the Home screen, click the Connections tab.
- On a blank line with a * in the second column, create a new connection object by adding:
- NAME
Connection String noting the content pieces
- Database Password for the user in the connection string.
In the Database Server Type drop-down, choose Vertica.
- Click Save.
- To test, click Test Connection.
Note You do not need to populate the ID, Project, or Authority fields.
Note This only tests the ability to connect to the host with ID and password and does not check if the schema is valid or accessible by the user in the connect string.
If you wish to test the actual data being displayed in Speedbase, then follow these steps:
- On the Home screen, click the Forms tab.
- On a blank line with a * in the second field, create a new form object by adding:
- NAME
- Use the magnifying glass icon on the right end of the Form Type field, select Grid, and click Ok.
- Use the magnifying glass icon on the right end of the Connection field, select the connection object you just created, and click Ok.
-
Use the magnifying glass icon on the right end of the Table Name field, select a table, and click Ok.
Only tables that are in the Default schema and visible to the user specified in the connection object will show up.
-
Click Save.
-
Select the form and click the Preview Form button.
The form should look similar to the following image:
A new window opens with a grid containing the data as shown in the following table:
Troubleshooting
If the connection object tests Ok but the schema or its tables are not visible when creating forms, check in Vertica to ensure the user ID used in the connection has the right privileges to read and write to the schema tables.
Known Limitations
- Binary, Varbinary, and LongVarBinary were identified as LOB but not displayable. In Speedbase type, LOB expects a document, image, or other binary media to be referenced. Vertica’s data type test tries to manufacture binary data via HEXTOBINARY functions during load which is incompatible with the Speedbase type.
- Time and Timestamp data types truncated minutes and milliseconds. You can resolve this through the Time format in the connection object. The default is HH24:MM. Changing it to HH24:MI:SS:MS restores minutes and milliseconds. You can also modify the Date format for Timestamp as desired in the connection object. The default is DD/MM/YYYY.
- TimeTZ and TimestampTZ data types truncated minutes, millseconds, and dropped TZ. As above for Time, you can resolve the minutes and milliseconds through the Time format in the connection object and you can also modify the Date format for TimestampTZ as desired in the connection object. The default is DD/MM/YYYY. The TZ is not supported.
- Decimal limit is + or - 9999999999999999999999999999.9999 rounds to + or – 10000000000000000000000000000
