Vertica Integration with BI4Web Suite: 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 testing with specific versions of Vertica and the partner product.

Vertica and BI4Web Suite: Latest Versions Tested

Software Version
Partner Product

BI4Web Suite 18.1

Desktop Platform

Windows Server 2012 R2

Vertica Client

Vertica ADO.NET 9.0.1-1 bundled with BI4Web Suite

Vertica Server Vertica Server 9.2

BI4Web Suite Overview

BI4Web Suite is a tool for data analysis and reporting. With BI4Web's user-friendly web interface, you can analyze centralized data from a variety of sources. BI4Web supports projects ranging from traditional Business Intelligence solutions to Big Data, IoT, and Smart Cities.

For details, visit the BI4Web website.

Install BI4Web Suite

BI4Web does not have a trial version available for download. An online demo of the web-based Viewer for end users is available at the following URL:

http://freelogin.demo.bi4web.com/

For other demo/trial options, contact BI4Web directly at www.bi4web.com.

About the Vertica Client Driver

BI4Web uses Vertica's ADO.NET driver to connect to Vertica. The BI4Web installation supplies the Vertica ADO.NET driver, so you do not need to install it separately.

BI4Web is AnyCPU-based. This means that the BI4Web Wrapper DLL embeds both Vertica x86 (32-bit) and x64 (64-bit) ADO.NET DLLs and determines which one to use based on your IIS configuration. Therefore, you can set up the Viewer site to work with either an x86 or x64 architecture (32- or 64-bits).

Connect BI4Web Suite to Vertica

Follow these steps to connect BI4Web Suite to your Vertica database:

Note  

These steps assume a local installation of BI4Web.

  1. Start BI4Web Designer.
  2. Create a new project.

    For details about creating projects, see the BI4Web documentation:

    https://documentation.bi4web.com/wiki/93/usr0101-crear-un-proyecto-v18

  3. Create a new connection object for the project.

    For details about establishing a connection to Vertica, see the BI4Web documentation:

    https://documentation.bi4web.com/wiki/43/usr0105-gestion-de-conexiones

    The basic steps for creating a connection are:

    1. On the Environment tab, select Connections.
    2. Click Add+.

      The New Connections page opens.

    3. On the New Connections page:

      • Provide values for Connection identifier and Connection description.
      • Click the ellipse in the Connection provider box.
      • Select Vertica.

      • Click Accept.

    4. From the Connection Mode drop-down list, select Vertica.
    5. Click the ellipse in the Connection string box.

      The Vertica page opens.

    6. Provide the connection information and click Accept.

    7. Back on the New connection page, verify the information.

    8. Click Accept.
    9. When prompted to test the connection, click Yes and confirm the successful connection.
    10. Back in the Connections dashboard, select your new connection from the list of connections.
    11. On the Databases tab, click Update structure.
    12. Verify that the update process retrieved the tables from the database referenced in the Connection object.
    13. In the top right corner of the Database schema and Connection info boxes, click the Save icon.

At this point, you have created a valid connection object and captured the Vertica schema design in BI4Web. To learn how to create users, roles, and documents in your BI4Web project, consult the BI4Web documentation.

Known Limitations

General UI Limitations

  • The Show system tables check box in the Connection dashboard currently doesn’t filter out the v_catalog and v_monitor system tables.

    This issue has been logged and will be corrected in a future release.

  • There is no query builder on the screens where you provide SQL to populate a report or chart. This means that you have to write the SQL statement, which might be complex—especially when including joins to return dimension value information in the result set.
  • BI4Web was originally developed in Spain. The documentation is in Spanish, but it is published as HTML so you can use translation tools in your browser to read it. Unfortunately, there are many screen shots embedded in the documentation, and those do not translate. The Designer app still has some Spanish text in some of the screens and menus, even if you set the user profile to a different language. The Viewer is a web app, so the browser translates everything fairly well.

Data Type Limitations

Data type support is not always consistent across the Designer and Viewer. Inconsistencies are described in the following sections.

Chars

In Designer, the Results box on the SQL query page has a limit of 5486 characters. If the length of a Char/VarChar/LongVarChar value is greater than 5486, then that value displays as blank.

Note

If a column includes a value that exceeds the length limit and displays as blank, then other values in the column may also display as blank even though they are within the length limit.

Date

The web-based Viewer adds hh:mm:ss to dates by default when displayed in a grid.

You can change the format for the grid in Control Settings. On the Column tab, edit the value for Format string. For example, use {0:yyyy-MM-dd}.

Decimal

In Designer, attempts to read decimal data with more than 18 digits to the left of the decimal point return this error:

Could not correctly interpret the server data

However , the value will actually save and display correctly in the Viewer. You can use to_char in Designer to get it to correctly display there.

Intervals

In Designer, attempts to read Interval Second data generate an UnmappableColumnException error. The data will actually save and display correctly in the Viewer.

Interval Month data also generates an UnmappableColumnException error. The data actually saves, but displays incorrectly in the Viewer.

Binary

Binary and LongVarBinary are not supported in BI4Web. In Designer, the values display as No Image Data. In Viewer, the values display as blank.

Time

In Designer and the web-based Viewer, Time values display with a date preceding the time. This is due to the default Date/Time format. You can change the Date/Time format string for the column to get the correct display.

Milliseconds and Military time are not supported in the formats. Milliseconds are stripped, and military time is converted to regular time with AM or PM.

TimeTz

In Designer, attempts to read TimeTz data generate an UnmappableColumnException error. By default, TimeTz columns are converted to String and display correctly in the Viewer.

TZ is not supported in the Date/Time format strings.

Timestamp

In Designer and the web-based Viewer, Timestamp values simply display as dates. You can change the Date/Time format string for the column to get the correct display in Viewer.

Milliseconds and Military time are not supported in the formats. Milliseconds get stripped, military time is converted to regular time with AM or PM.

TimestampTz

In Designer, attempts to read TimestampTz data generate an UnmappableColumnException error. By default, TimestampTz columns are converted to String and display correctly in the Viewer.

TZ is not supported in the Date/Time format strings.

Version 18.2.2 Updates

Key features and Enhancements

The latest version 18.2.2, released after this document was published, has several fixes and enhancements related to the issues and limitations listed above.

They include but are not limited to:

  • Updated the Vertica ADO.NET driver to 9.2
  • The "Show system tables" checkbox found on "Connections" to filter system tables should now allow control over filtering the v_catalog and v_monitor system tables.
  • Fixed the decimal data types mapping error that crashed BI4Web Developer Studio and displayed the error "Could not correctly interpret the server data".
  • Improved special decimal values. The program successfully handles infinities and NaN.
  • If there is a string too long to display, for example, 8000 characters, it will be truncated to be displayed on the available screen space and it won't be nulled or blanked anymore.

Additionally improved translations are being worked on in the next iteration of the product.

For More Information