Vertica Integration with Microsoft SQL Server Tabular 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 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.

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 guide, we address the Vertica connection with SSAS Tabular modeling only.

How SQL Server Analysis Services Tabular Mode Works with Vertica

SSAS Tabular Mode is an in-memory application. It fetches data from Vertica and stores it in memory. DirectQuery to Vertica is not supported. SSAS Tabular Mode connects to Vertica using Vertica's OLEDB driver.

Vertica and SQL Server Analysis Services Tabular 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 Analytic Database v11.1.1-0

Installing SQL Server Analysis Services Tabular Mode

To install SQL Server Analysis Services Tabular Mode

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

    Important Select the option for Tabular 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 Tabular Mode 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 is optional)

Creating a Connection Using OLEDB

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

Creating an OLEDB Connection

  1. Open Visual Studio 2019.
  2. Create a new project or open an existing project.

  3. If creating a new project, select the template Analysis Services, then select Analysis Services Tabular Project.

  4. In the Tabular model designer, select Integrated workspace or Workspace server.

    Note In this guide, we tested SSAS Tabular Mode and Vertica using Integrated workspace.

    • The Integrated Workspace option was introduced in SQL Server 2016. When this option is enabled, SQL Server Analysis Services does not need to be installed on the machine. SSDT creates a local instance of SQL Server Analysis Services for the execution of the SSAS Tabular Model project and stores all the database data in it.
    • The Workspace Server option has been available since SQL Server 2012. When this option is enabled, SQL Server stores the database data used in SSAS Tabular Model in SQL Server Analysis Services.
  5. Select the SQL version in the Compatibility Level drop-down list.
  6. Click OK to open the project.
  7. In the Tabular Model Explorer, right-click Data Sources and click Import From Data Source.

    The Table Import Wizard opens.

  8. From the Connect to a Data Source list, select Others (OLEDB/ODBC).

  9. Click Next.
  10. Type a Friendly name for this connection.

  11. Click Build.

    The Data Link Properties page opens.

  12. Click the Provider tab.
  13. Select Vertica OLE DB Provider and click OK.

  14. Click Data Links.
  15. In the Connection tab:
    • Specify the connection information:
      • Data Source: IP address or host name of the database machine.
      • User Name: User name for connecting to the database.
      • Password: Password for the connection .
    • Check Allow saving password.
    • For Initial catalog to use, type the database name.
  16. In the ALL tab, specify the port.
  17. Click Test Connection.
  18. When the connection is successful, click OK. Then click OK again to close the Data Link Properties page.

  19. Click Next.
  20. From the Impersonation Information list, select Service Account and click Next.

  21. Identify the data to import into SSAS Tabular Mode. Either choose tables to import or provide a query.

    Note If the tables are large, we recommend selecting the second option to write a query. This way, you only import the data you need and you avoid importing too much data. Follow the instructions in Importing Data by Writing a Query.

  22. Follow the instructions in Importing Data into SSAS Tabular Mode with OLEDB Connection to import data into SSAS Tabular Mode.

Importing Data into SSAS Tabular Mode with OLEDB Connection

Follow the instructions in one of the following sections to import data into SSAS Tabular Mode:

Importing Data by Selecting Tables

  1. In the Table Import Wizard, in the Choose How to Import the Data step, click Select from a list of tables and views to choose the data to import.
  2. Click Next.
  3. In the Select Tables and Views step, select the tables and views and click Preview & Filter.

    Important The Select Related Tables button does not work and is a known issue. You must manually select the fact table and related dimension tables that you want to import.

  4. In the Preview Selected Table step, click the columns you want to include and click OK.

  5. In the Select Tables and Views step, click Finish.
  6. When the data is imported, click Close.

Importing Data by Writing a Query

  1. In the Table Import Wizard window, in the Choose How to Import the Data step, click Write a query that will specify the data to import.
  2. Click Next.
  3. In the Specify a SQL Query step, provide a name for the query in Friendly Query Name box and type (or paste) the query in the SQL Statement box.

    The query shown in this example is as follows:

    SELECT sales_quantity, sales_dollar_amount, transaction_type, cc_name
    FROM online_sales.online_sales_fact
    INNER JOIN online_sales.call_center_dimension
    ON (online_sales.online_sales_fact.call_center_key
    = online_sales.call_center_dimension.call_center_key
    AND sale_date_key = 156)
    ORDER BY sales_dollar_amount DESC;
    
  4. Click Validate to validate the syntax of the query and to verify that the referenced tables and columns exist.
  5. Click Design to check the query output, then click OK to return to the Specify a SQL Query page.
  6. Click Finish to import the data.
  7. When the data is imported, click Close.

Known Limitations

  • ADO.NET connections are not supported.
  • SSAS Tabular Mode does not support DirectQuery connections to Vertica.
  • The Select Related Tables option in the Select Tables and Views step of the Table Import Wizard does not work and is a known issue. You must manually select the fact table and related dimension tables that you want to import into SSAS Tabular Mode.

Data Type Incompatibilities

We found these data type incompatibilities with both OLEDB connections:

  • Vertica CHAR, VARCHAR, and LONG VARCHAR data types are displayed up to 32766 characters beyond which the following error is displayed:
    The size of a data value in table <TableName> column <ColumnName> was too large to fit in that column.
  • For INTEGER data type, the largest displayed value is 9,223,372,036,854,775,806 above which the following error appears:
    Table contains a value, <value>, which is not supported
  • NUMERIC data type displays a precision of up to 15 digits above which the value is rounded off.
  • BINARY, VARBINARY, and LONG VARBINARY data types are not displayed.
  • For DATE and TIMESTAMP data types, the minimum value supported is 02-01-0001.
  • TIME and TIMETZ data types are not displayed.
  • For TIMESTAMPTZ data type, time zone offset is not displayed.

For More Information