Share this article:

Vertica Integration with Microsoft SQL Server Multidimensional Analysis Services: 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.

About SSAS

SQL Server Analysis Services (SSAS) is a platform for building analytical applications. SSAS supports two separate modes of analysis:

  • multidimensional—modeling based on cube structures
  • tabular—modeling based on rows and columns

Note In this document, we address the Vertica connection with SSAS multidimensional modeling only.

SSAS is part of the Microsoft Business Intelligence (MSBI) suite of tools for creating BI applications for SQL Server, Microsoft's relational database.

MSBI Overview

MSBI is part of SQL Server Data Tool (SSDT), a tool for creating applications within a Visual Studio shell. MSBI includes the following:

  • SQL Server Integration Services (SSIS)—Data migration, workflows, and ETL.
  • SQL Server Analysis Services (SSAS)—Business intelligence using either multidimensional (MOLAP) or tabular, relational (ROLAP) analysis.
  • SQL Server Reporting Services (SSRS)—Report generation.

Vertica and SSDT: Latest Versions Tested

This document provides guidance using the software listed as follows:

Software Version
Client tools
  • SSDT 2015 (If you install SSDT without Visual Studio, then the free edition of Visual Studio is included with SSDT.)
  • Visual Studio 2015
  • Microsoft SQL Server 2016 Enterprise Edition
Client Platform

Microsoft Windows Server 2012

Vertica Client

Vertica Connectivity Pack 9.1.1

Vertica Server

Vertica Server 9.1.1

Install SSAS MD

Download the software components from the Microsoft website and follow the installation instructions in the Microsoft documentation. Follow these steps:

  1. Download and install SQL Server 2016.

    Important Select the option for multidimensional analytics during installation.

  2. Download Visual Studio.
    1. Check the prerequisites.
    2. Follow the installation instructions.

      We recommend Visual Studio Community Edition, but you can also use Visual Studio Express Edition.

  3. Download and install SSDT.

Install the Vertica Client Drivers

SSAS MD can use OLEDB and ADO.NET to connect to Vertica 9.1.x. SSAS MD cannot use ODBC, which is supported by MSBI in addition to the other two types of drivers. (See Known Limitations.)

Vertica provides a connectivity pack that includes all the drivers for connecting to SQL Server components. The connectivity pack includes the drivers for both 32- and 64-bit Windows.

To download the drivers:

  1. Navigate to the download page for Vertica client drivers:

    https://www.vertica.com/download/vertica/client-drivers/

  2. Download the driver package for Windows.
  3. Start the installer and select the options you intend to use. By default, all options are selected.

Create a Connection Using ADO.NET or OLEDB

Follow the instructions in the following topics to create a connection to Vertica from SSAS MD:

Create an ADO.NET Connection

  1. Open SSDT and create a new SSAS project.

  2. In Solution Explorer, right-click Data Source and select New Data Source.

  3. In the Data Source wizard, click New.

    The Connection Manager opens.

  4. Expand the Provider drop-down list and select Vertica Data Provider.

  5. Provide the connection information for Vertica and click Test Connection.

  6. When the connection is successful, click OK.
  7. In the Data Source wizard, select the name of the data source and click Finish.

  8. Click Finish again.

Create an OLEDB Connection

  1. In Solution Explorer, right-click Data Source and select New Data Source.

  2. In the Data Source wizard, click New.
  3. Expand the Provider drop-down list and select Native OLE DB.
  4. Select Vertica OLE DB Provider and click OK.

  5. Click the All button.

  6. Provide the connection information for Vertica:
    • Type the user ID, password, and port.
    • For Data Source, type the IP address of the server.
    • For Initial Catalog, type the database name.
  7. Click Test Connection.

  8. When the connection succeeds, click OK.

    The Data Source wizard opens.

  9. Click Finish.

  10. In the Data source name box, type the name of the data source.

  11. Click Finish.

Troubleshooting

  • If Vertica does not appear as a connection choice in SSDT, the Vertica driver may not be correctly installed.
  • When editing an existing connection, you may need to re-enter the password.

Known Limitations

SSAS connections to Vertica work only with ADO.NET and OLEDB. ODBC connections are not supported.

Data Type Incompatibilities with ADO.NET

  • SSAS displays a checkbox instead of True or False for BOOLEAN data.
  • SSAS is unable to read BINARY, VARBINARY, and LONG VARBINARY data types. SSAS displays a blank value for these data types and does not return an error.
  • SSAS does not support the Vertica INTERVAL YEAR TO MONTH data type. Interval values are not displayed correctly. For example, 9-10 is displayed as 3595.00:00:00.

  • Milliseconds are truncated in the TIME, TIMESTAMP, TIMETZ, and TIMESTAMPTZ data types.
  • Seconds are truncated in TIMETZ and TIMESTAMPTZ data types.

Data Type Incompatibilities with OLEDB

  • SSAS displays a checkbox instead of True or False for BOOLEAN data.

  • CHAR and VARCHAR data types are supported up to a length of 8192.

  • The LONG VARCHAR data type is not supported.

  • SSAS supports numeric data up to 28 digits of precision. When reading numbers with a higher precision, SSAS returns the following error:

     The numeric value is too large to fit into a 96 bit decimal.
  • SSAS cannot read BINARY, VARBINARY, and LONG VARBINARY data types. SSAS displays a blank value for these data types and does not return an error.
  • Milliseconds are truncated in the TIME, TIMESTAMP, TIMETZ, and TIMESTAMPTZ data types.
  • Timezone is runcated in TIMETZ and TIMESTAMPTZ data types.

For More Information

Share this article: