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. Visual Studio (VS) is a platform that enables the functionality to create Analysis Services, Integration Services, or Reporting Services projects.

  • 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 2019 Community Edition
  • Visual Studio 2019
  • SQL Server Data Tools (SSDT) for Visual Studio 2019
Partner Product Platform

Microsoft Windows Server 2019

Vertica Client

Vertica Client Package 11.1.1
Vertica Server

Vertica Server 11.1.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 2019. 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 Visual Studio.
    1. Check the Visual Studio 2019 System Requirements.
    2. Follow the instructions for Installing Visual Studio 2019.

    3. Download and install Microsoft Analysis Services Projects.

Installing the Vertica Client Drivers

SQL Server Analysis Services Multidimensional (SSAS MD) uses OLEDB to connect to Vertica.

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
    • Visual Studio Plug-in
    • Microsoft Connectivity Pack
    • vsql Client (Vertica recommends installing vsql client, but it's optional)

Connecting SQL Server Analysis Services Multidimensional to Vertica using OLEDB

  1. Open Visual Studio 2019 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.

Troubleshooting

  • If Vertica does not appear as a connection choice in Visual Studio 2019, Vertica driver may not be correctly installed.

Known Limitations

  • CHAR and VARCHAR data types are
    • Displayed with a maximum column definition size of 8192.
    • Displayed up to a length of 2048 characters.

  • LONG VARCHAR data type is not displayed.

  • NUMERIC data type

    • SSAS displays 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 displays 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).
  • BINARY, VARBINARY, and LONG VARBINARY data types are not read or displayed.
  • For TIME, TIMESTAMP, TIMETZ, and TIMESTAMPTZ data types, milliseconds are truncated.
  • For TIMETZ and TIMESTAMPTZ data types, timezone is truncated.

For More Information