Share this article:

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

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 or ODBC 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 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 Tabular Mode

To install SQL Server Analysis Services Tabular Mode

  1. Download and install SQL Server 2016. 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 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 Tabular Mode uses OLEDB or ODBC to connect to Vertica. It cannot use ADO.NET.

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

    Note For more information on setting up the ODBC Data Source Names (DSNs), see Creating an ODBC Connection.

Creating a Connection Using OLEDB or ODBC

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

Creating an OLEDB Connection

  1. Open SQL Server Data Tools (SSDT).
  2. Create a new project or open an existing project.

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

  3. 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.
  4. Select the SQL version in the Compatibility Level drop-down list.
  5. Click OK to open the project.
  6. In the Tabular Model Explorer, right-click Data Sources and click Import From Data Source.

    The Table Import Wizard opens.

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

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

  10. Click Build.

    The Data Link Properties page opens.

  11. Click the Provider tab.
  12. Select Vertica OLE DB Provider and click Next.

    The Connection tab opens.

  13. 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.
  14. Click Test Connection.
  15. When the connection is successful, click OK. Then click OK again to close the Data Link Properties page.

  16. Click Next.
  17. From the Impersonation Information list, select Service Account and click Next.

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

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

Creating an ODBC Connection

Important SSAS Tabular Mode provides the ability to test the connection, import data, and choose the location of the authoring workspace. Each of these has slightly different ODBC DSN requirements. You must create a 32-bit DSN and a 64-bit DSN with the same name and connection details. We recommend using Windows 64-bit ODBC Data Sources application (C:\Windows\System32\odbcad32.exe) and create one User DSN and one System DSN. This will satisfy all requirements regardless of the action being taken or choice on workspace location. If you do not create these DSNs, then an error is returned when you attempt to create a connection using ODBC.

For more information on how to create these DSNs correctly, see Troubleshooting.

  1. Open SQL Server Data Tool (SSDT).
  2. Create a new project or open an existing project.

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

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

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

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

  4. Check the SQL version in the Compatibility Level drop-down list.
  5. Click OK to open the project.
  6. In the Tabular Model Explorer, right-click Data Sources and select Import From Data Source.

    The Table Import Wizard opens.

  7. Select Others (OLEDB/ODBC).

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

  10. Click Build.

    The Data Link Properties page displays.

  11. From the User data source name drop-down list, select the DSN name.

    Note Remember that the same DSN name must be used for both a 32-bit and a 64-bit DSN, as described in Troubleshooting.

  12. Click Test Connection.

  13. When the connection is successful, click OK. Then click OK again to close the Data Link Properties page and return to the Table Import Wizard.

  14. Click Next.
  15. From the Impersonation Information list, select Service Account and click Next.

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

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

Importing Data into SSAS Tabular Mode with OLEDB or ODBC 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:

    -- Inventory analysis
    SELECT
    date,
    full_date_description,
    day_of_week,
    calendar_month_name,
    calendar_month_number_in_year,
    calendar_year_month,
    calendar_quarter,
    calendar_year_quarter,
    calendar_year,
    public.product_dimension.product_key || public.product_dimension.product_version as product_version_key,
    product_description,
    sku_number,
    category_description,
    department_description,
    package_type_description,
    package_size,
    fat_content,
    diet_type,
    warehouse_name,
    warehouse_city,
    warehouse_state,
    warehouse_region
    qty_in_stock
    FROM
    public.inventory_fact INNER JOIN public.date_dimension
    ON inventory_fact.date_key = date_dimension.date_key
    INNER JOIN public.warehouse_dimension
    ON inventory_fact.warehouse_key = warehouse_dimension.warehouse_key
    INNER JOIN public.product_dimension
    ON inventory_fact.product_key = product_dimension.product_key AND inventory_fact.product_version = product_dimension.product_version
    WHERE
    public.date_dimension.date >= (DATE '2003-01-01') AND public.date_dimension.date <= (DATE '2017-12-31') AND
    discontinued_flag = 0
  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.

Troubleshooting

ODBC connections to Vertica from SSAS Tabular Mode require two Data Source Names (DSNs)

Issue: ODBC connections to SSAS Tabular Mode require two DSNs with the same name and connection details: One User DSN and one System DSN, both created using the Windows 64-bit ODBC Data Sources tool. You may see the following error when the DSNs are not using the appropriate bitness for the connection:

OLE DB or ODBC error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application; IM014.

Solution: Ensure you created a User DSN and a Sytem DSN with exactly the same name and connection details using the Windows 64-bit ODBC Data Sources tool (C:\Windows\System32\odbcad32.exe). Also, check under the Windows 32-bit ODBC Data Sources tool (C:Windows\syswow64\odbcad32.exe) and ensure the User DSN shows up there. Connect to Vertica from SSAS Tabular Mode using the DSN name you created above.

Connection Setting “Convert Square Bracket Identifiers” not enabled by default

Issue: The ODBC connection setting “Convert Square Bracket Identifiers” is required to interpret the SQL Server query syntax correctly. This setting is not enabled by default. If the Data Source Name (DSN) doesn’t have this setting checked, the following error message is displayed when importing data from Vertica:

Solution: Configure the connection property “Convert Square Bracket Identifiers” as follows:

  1. Select Start > Control Panel > Administrative Tools > ODBC Data Sources (64-bit)
  2. Click User DSN or System DSN.
  3. Select your DSN and click Configure.
  4. Click the Client Settings tab.
  5. Select Convert square bracket identifiers. This setting tells the ODBC driver to interpret the SQL Server query syntax correctly.

    Note This property should be enabled in all DSNs used in your SSAS Tabular mode applications.

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 and ODBC connections:

  • Vertica CHAR, VARCHAR, and LONG VARCHAR data types are supported up to a length of 32766 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 supported 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 supports a precision of up to 15 digits above which the value is rounded off.
  • BINARY, VARBINARY, and LONG VARBINARY data types are not supported.

Data Type Incompatibilities with OLEDB

We found these data type incompatibilities with OLEDB connections only:

  • TIME and TIMETZ data types are not supported.
  • For DATE and TIMESTAMP data types, the minimum value supported is 02-01-0001.
  • For TIMESTAMPTZ data type, time zone offset is not supported.

Data Type Incompatibilities with ODBC

We found these data type incompatibilities with ODBC connections only:

  • INTERVAL, INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, and INTERVAL YEAR TO MONTH data types are not supported.

    Note If your data includes INTERVAL data types, we recommend using the OLEDB driver.

  • For DATE and TIMESTAMP data types, the minimum supported value is 01-01-0100 below which the following error is returned:

    Data overflow converting to the data type for table <TableName> column <ColumnName>
  • For TIME data type, the systems date is appended.
  • For TIMESTAMP and TIMESTAMPTZ data types, milliseconds are not supported.
  • For TIMETZ and TIMESTAMPTZ data types, time zone offset is not supported.

For information about data type support in SQL Server Analysis Services Tabular Mode, see:

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/data-types-supported-ssas-tabular#bkmk_data_types

Share this article: