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.

SQL Server Analysis Services (SSAS) Overview

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

  • Multidimensional and Data Mining Mode — Modeling based on cube structures.
  • Tabular Mode — Modeling based on rows and columns.

Note In this document, we address the Vertica connection with SSAS Multidimensional (SSAS MD) modeling only.

SQL Server Business Intelligence Overview

SQL Server Business Intelligence is a suite of tools for creating BI applications on your SQL Server deployment. SQL Server Data Tools (SSDT) for Visual Studio (VS) is a part of the platform that enables the functionality to create Analysis Services, Integration Services, or Reporting Services projects within Visual Studio:

  • 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 SQL Server Analysis Services Multidimensional Mode: Latest Versions Tested

This document provides guidance using the software listed as follows:

Software Version
Partner Products
  • Microsoft SQL Server 2016 Enterprise Edition
  • SQL Server Data Tools (SSDT) for Visual Studio 2015 (If you install SSDT without Visual Studio, then the free edition of Visual Studio is included with SSDT.)
  • Visual Studio 2015 (If not installed by SSDT)
Partner Product Platform

Microsoft Windows Server 2016

Vertica Client

Vertica Client Package 9.3.1

Vertica Server

Vertica Server 9.3.1

Installing SQL Server Analysis Services Multidimensional Mode (SSAS MD)

To install SQL Server Analysis Services Multidimensional Mode (SSAS MD)

  1. Download and install SQL Server 2016. Follow the installation instructions in the Microsoft documentation.

    Important Select the option for Multidimensional Server Mode during installation in Analysis Services Configuration step.

  2. Download and install SQL Server Data Tools (SSDT) for Visual Studio 2015.
  3. Download Visual Studio if not installed by SSDT.
    1. Check the Visual Studio 2015 System Requirements.
    2. Follow the instructions for Installing Visual Studio 2015.

Installing the Vertica Client Drivers

SQL Server Analysis Services Multidimensional (SSAS MD) uses OLEDB or ADO.NET to connect to Vertica. SSAS MD cannot use ODBC.

To download the drivers:

  1. Navigate to Vertica Client Drivers.

  2. Download the driver package for Windows.
  3. Start the installer and select the following options:

    • OLE DB Provider
    • ADO.NET Provider
    • Visual Studio Plug-in
    • Microsoft Connectivity Pack
    • vsql Client (Vertica recommends installing vsql client, but it's optional)

Connecting to Vertica Using OLEDB or ADO.NET

Follow the instructions in the following topics to connect SQL Server Analysis Services Multidimensional (SSAS MD) to Vertica:

Connecting to Vertica Using OLEDB

  1. Open SQL Server Data Tool (SSDT) and create a new SQL Server Analysis Services (SSAS) project.

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

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

  6. Click the All button.

  7. Provide the connection information for Vertica:
    • Type the Password, User ID, and Port.
    • For Data Source, type the Vertica server name or IP address.
    • For Initial Catalog, type the database name.
  8. Click Test Connection.

  9. After a successful connection, click OK.
  10. In the Data Source Wizard, click Finish.

  11. In the Data source name field, type the name of the data source.

  12. Click Finish.

Connecting to Vertica Using ADO.NET

  1. Open SQL Server Data Tool (SSDT) and create a new SQL Server Analysis Services (SSAS) project.

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

    s

  3. In the Data Source Wizard, click New.

    The Connection Manager window is displayed.

  4. From the Provider drop-down list, select Vertica Data Provider.

  5. Provide the following connection information for Vertica and click Test Connection.
    • Database
    • Host
    • Password
    • Port
    • User

  6. After a successful connection, click OK.
  7. In the Data Source Wizard, select the name of the data source and click Finish.

  8. Click Finish again.

Troubleshooting

  • If Vertica does not appear as a connection choice in SQL Server Data Tool (SSDT), Vertica driver may not be correctly installed.

Known Limitations

SQL Server Analysis Services (SSAS) connections to Vertica work only with OLEDB and ADO.NET. ODBC connections are not supported.

Data Type Incompatibilities with OLEDB

  • CHAR and VARCHAR data types are
    • Displayed with a maximum column definition size of 8192.
    • Supported up to a length of 2048 characters.
  • LONG VARCHAR data type is not supported and no error is displayed.

  • NUMERIC data type

    • SSAS supports numeric data up to 28 digits of precision above which the following error is displayed:
     The numeric value is too large to fit into a 96 bit decimal.
    • SSAS supports a numeric column definition in Vertica up to 1023 digits of precision above which the following error is displayed:
  • 'VerticaOLEDB' failed with no error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).
  • 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.
  • For TIME, TIMESTAMP, TIMETZ, and TIMESTAMPTZ data types, milliseconds are truncated. For TIMETZ and TIMESTAMPTZ data types, TIMEZONE is truncated.

Data Type Incompatibilities with ADO.NET

  • SSAS supports NUMERIC data type up to 28 digits of precision. If a number exceeds 28 digits to the left of the decimal point, SSAS does not display the value and displays the error Specified argument was out of the range of valid value. If the precision is greater than 28 digits, SSAS rounds off the digits to the right of the decimal point.

  • 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.

  • SSAS truncates milliseconds in TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ data types.
  • SSAS truncates seconds in TIMETZ and TIMESTAMPTZ data types and the time is not displayed if the value is '00:00:00'.
  • SSAS appends a date supported by the tool in TIME and TIMETZ data types.

For More Information

Share this article: