Share this article:

Vertica Integration with Microsoft SQL Server Tabular Analysis Services: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, we may not have tested these other versions. See Vertica and SSDT Software Versions.

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 Tabular SSAS only.

MSBI Overview

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

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 or tabular analysis
  • SQL Server Reporting Services (SSRS): Report generation

Vertica and SSDT Software Versions

This document provides guidance using the following software versions.

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

Vertica Server

Vertica Server 9.1.x

Install Tabular SSAS

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

Tabular SSAS can use OLEDB or ODBC to connect to Vertica 9.1.x. It cannot use ADO.NET, which is supported by MSBI in addition to the other two types of drivers.

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. Log in to vertica.com.
  2. Navigate to the downloads page for client drivers:

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

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

How Tabular SSAS Works with Vertica

Tabular SSAS is an in-memory application. It fetches data from Vertica and stores it in memory.

Tabular SSAS connects to Vertica using Vertica ODBC or OLEDB drivers.

Create a Connection Using ODBC or OLEDB

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

Create an ODBC Connection

Important To connect using ODBC, you must first create a 32-bit and a 64-bit DSN with the same name and connection details. If you do not create these DSNs, then an error will be returned when you attempt to create a connection using ODBC.

For details, see Known Limitations.

  1. Create a 32-bit and a 64-bit DSN with the same name.

    To create a 32-bit DSN, use the executable located here:

    C:\Windows\SysWOW64\odbcad32.exe

    To create a 64-bit DSN use the executable located here:

    C:\Windows\System32\odbcad32.exe

  2. Open SSDT.
  3. 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.

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

    • 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. Check 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 select Import From Data Source.

    The Table Import Wizard opens.

  8. Select Others (OLEDB/ODBC).

  9. Click Next.
  10. Type the DSN name for Friendly name for this connection.

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

  11. Click Build.

    The Data Link Properties page displays.

  12. Expand the User data source name drop-down list and select the DSN name.

  13. Click Test Connection.

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

  15. Click Next.
  16. In the Impersonation Information step of the Table Import Wizard, select Service Account and click Next.

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

    Note If the tables are large, we recommend querying them. This way, you only import the data you need and you avoid importing too much data. Follow the instructions in Import Data by Writing a Query.

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

Import Data by Selecting Tables

  1. In the Choose How to Import the Data step of the Table Import Wizard, 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. Back in the Select Tables and Views step, click Finish.

  6. When the data is imported, click Close.

Import Data by Writing a Query

  1. In the Choose How to Import the Data step of the Table Import Wizard, 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.

Create an OLEDB Connection

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

    • 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. In the Connect to Data Source step of the Table Import Wizard, select Others (OLEDB/ODBC).

  8. Click Next.
  9. Click Build.

    The Data Link Properties page opens.

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

    The Connection tab opens.

  12. On the Connection tab:
    • Specify the connection information:
      • Data Source: IP address or host name or 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.
  13. Click Test Connection.
  14. When the connection is successful, click OK. Then click OK again to close the Data Link Properties page.

  15. Click Next.
  16. On the Impersonation Information step of the Table Import Wizard, select Service Account and click Next.

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

    Note If the tables are large, we recommend querying them. This way, you only import the data you need and you avoid importing too much data.

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

Import Data by Selecting Tables

  1. On the Choose How to Import the Data step of the Table Import Wizard, click Select from a list of tables and views to choose the data to import.
  2. Click Next.
  3. 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. On the Preview Selected Table step, click the columns you want to include and click OK.

  5. Back on the Select Tables and Views step, click Finish.

  6. When the data is imported, click Close.

Import Data by Writing a Query

  1. On the Choose How to Import the Data step of the Table Import Wizard, click Write a query that will specify the data to import.
  2. Click Next.
  3. On the Specify a SQL Query step, provide a name for the query in the 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 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

  • 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

  • Tabular SSAS connections to Vertica work only with ODBC and OLEDB. ADO.NET connections are not supported.
  • Tabular SSAS does not support DirectQuery in Vertica.
  • ODBC connections to Tabular SSAS require two DSNs with the same name and connection details: one a 32-bit DSN, the other a 64-bit DSN. If you do not create these DSNs, the following error will be returned:
    OLE DB or ODBC error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application; IM014.

    To create a 32-bit DSN, use the executable located here:

    C:\Windows\SysWOW64\odbcad32.exe

    To create a 64-bit DSN with the same name and details, use the executable located here:

    C:\Windows\System32\odbcad32.exe
  • 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 Tabular SSAS.

Data Type Incompatibilities

We found these data type incompatibilities with both ODBC and OLEDB connections:

  • Vertica CHAR, VARCHAR, and LONGVARCHAR data types are supported up to a length of 32766. Longer strings return this error:
    Too large to fit in that column
  • The Vertica INT data type is supported for values up to 9,223,372,036,854,775,806. Larger integers return this error:
    Table contains a value, <value>, which is not supported
  • Tabular SSAS supports Vertica numeric data with up to 15 decimal points of precision.
  • Tabular SSAS does not support Vertica BINARY, VARBINARY, and LONGVARBINARY data types . Blank values are displayed. No error is returned.

For information about data type support in Tabular SSAS, see:

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

Data Type Incompatibilities with ODBC

We found these data type incompatibilities with ODBC connections only:

  • Tabular SSAS does not support Vertica INTERVAL, INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, and INTERVAL YEAR TO MONTH data types. When you preview or filter this data, the following error is returned:
    Failed to retrieve data from <Table>. Reason: Unknown SQL type - 112.

    No error is returned when this data is loaded. Incorrect values are displayed.

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

  • Tabular SSAS does not support the Vertica DATE and TIMESTAMP minimum value, 0001-01-01. The following error is returned:
    Data overflow converting to the data type for table <TableName> column <ColumnName>

    All other DATE and TIMESTAMP values are supported.

  • When reading the TIME data type, Tabular SSAS appends the project creation date before the value. Milliseconds are not supported.
  • Tabular SSAS does not support BC values in TIMESTAMP and TIMESTAMPTZ data. A Preview and Filter operation returns:
    Failed to retrieve data from <Table>. Reason: Year, Month, and Day parameters describe an un-representable DateTime.”.

    Attempts to load these values fail with the following error:

    Error returned: Data overflow converting to the data type for table <TableName>' column <ColumnName>”.
  • Tabular SSAS does not support milliseconds and time zones in Vertica TIMETZ and TIMESTAMPTZ data.

Data Type Incompatibilities with OLEDB

We found these data type incompatibilities with OLEDB connections only:

  • Tabular SSAS does not support the Vertica TIME data type. Blank values are displayed, and no error is returned.
  • Tabular SSAS does not support Vertica DATE and TIMESTAMP minimum value, 0001-01-01. The value is not displayed, and no error is returned.
  • Tabular SSAS does not support TIMESTAMP and TIMESTAMPTZ values that include BC data. When you preview or filter this data, the following errir is returned:
    Failed to retrieve data from <Table>
    Reason: Year, Month, and Day parameters describe an un-representable DateTime..

    BC values in TIMESTAMP and TIMESTAMPTZ data are not loaded, and no error is returned.

  • Tabular SSAS does not support milliseconds in Vertica TIMESTAMP data.

Share this article: