Share this article:

Vertica QuickStart for SSRS

Applies to Vertica 7.2.x and earlier 

What is a QuickStart Application?

The Vertica QuickStarts are sample applications that show how complementary technologies can work together to deliver outstanding benefits to end users. Each QuickStart uses Vertica Analytic Database with a different BI or ETL tool from a Vertica technology partner. The QuickStarts are available for download free of charge on the Big Data Marketplace in the QuickStart Examples category.

About this Document

This document explains how to deploy and use the Vertica QuickStart for business intelligence with SQL Server Reporting Services (SSRS). The document includes the setup information that you need to get up and running, and it provides an overview of the SSRS dashboards and the Vertica data sources.

SSRS is a server-based report generation software from Microsoft. SSRS uses the ADO.NET driver to connect to Vertica. The deployment of this QuickStart was tested on SQL Server BI 2012 using Vertica 7.1.x and on SQL Server 2014 using Vertica 7.2.x.

Vertica QuickStart with SSRS Overview

The Vertica QuickStart for SSRS is a sample BI application implemented as a set of SSRS dashboards powered by the Vertica Analytic Database. The dashboards present sample retail data for analysis. The QuickStart shows how retail companies could use Vertica and SSRS to quickly explore, visualize, and gain insight into their data stored in Vertica.

For a quick introduction to Vertica QuickStart with SSRS, watch this short video.

Requirements

The Vertica QuickStart for SSRS requires a Vertica database server with a standard installation of the VMart example database, a Vertica client driver, and SQL Server Business Intelligence 64-bit with SQL Server Data Tools 2012 or later.  

Installation and Setup

To install the software that is required for running the QuickStart, follow these steps:

Install the Vertica Database Server

The Vertica database server runs on Linux platforms. If you do not have Vertica, you can download the Community Edition free of charge from the Big Data Marketplace or directly from the Vertica Community Portal.

To download and install from the Big Data Marketplace:

  1. Navigate to https://marketplace.saas.hpe.com/big-data.
  2. Sign in with your Marketplace credentials.
  3. Select Vertica Platform.
  4. Select Vertica Analytic Database Server.
  5. Click GetIt.
  6. Under Register Now, provide your information to register for a Community Edition account.
  7. Click Signup.
  8. Follow the on-screen instructions to download and install the Vertica Community Edition.

To download and install directly from the Vertica Community portal:

  1. Navigate to https://my.vertica.com/.
  2. Under Register Now, provide your information to register for a Community Edition account.
  3. Click Signup.
  4. Follow the on-screen instructions to download and install the Vertica Community Edition.

Install the VMart Example Database

The Vertica QuickStart for SSRS assumes a default installation of the Vertica VMart example database. Follow the tutorial in the Vertica documentation for Installing and Connecting to the VMart Example Database to install VMart.

The VMart example database includes three schemas: Public, Online Sales, and Store. The schemas are interrelated and share many dimensions. For details, see Appendix: VMart Example Database Schema, Tables, and Scripts in the Vertica documentation.

Install SSRS

Use your MSDN subscription to download SQL Server BI 2012 or later. Follow the instructions in the Microsoft documentation to install SSRS in Native mode.

Download and Install Vertica Client Drivers

SSRS uses ADO.NET to connect to Vertica. Before you can connect to Vertica using SSRS, you must download and install the Vertica client package. This client package includes the ADO.NET driver, Visual Studio plugin, and SQL Server integration components. For earlier versions, it is part of the Vertica Microsoft Connectivity pack.

To download and install the Vertica client package:

  1. Go to the Vertica Client Drivers page on the myVertica portal.
  2. Download the Vertica client package that matches your operating system and the version of Vertica that you are using.
  3. Follow the steps for installing the Vertica client as described in the Vertica documentation.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information, see Client Driver and Server Version Compatibility in the Vertica documentation.

Download the QuickStart

  1. Go to the Big Data Marketplace and log in with your Marketplace credentials.
  2. Select QuickStart Examples.
  3. Select Vertica QuickStart for SSRS.
  4. Click Download.

Deploy the QuickStart for SSRS Dashboards

To deploy the dashboards on SSRS native mode using ADO.NET, you must:

  • Configure connection information
  • Specify a target URL for web deployment
  • Build and deploy the shared data source

Configure Connection Information

Note The steps that follow are an example of a default installation using SQL Server BI 2014.

  1. Place the VMart QuickStart (VerticaQuickStartforSSRS.zip) in the default SSRS project location, such as: C:\Users\<windows_user>\Documents\Visual Studio 2013\Projects\.
  2. To open your project from SQL Server data tools, navigate to Start > SQL Server 2014 > SQL Server data tools for Visual Studio 2013. Visual Studio opens.
  3. Click File > Open > Project/Solution.
  4. Select the .sln file. The VerticaQuickStartforSSRS.sln project opens.
  5. In the Solution Explorer panel on the right side of the screen, double click VerticaDataSource.rds. This opens the Shared Data Source Properties window.
  6. In this window, click Edit next to the Connection String field to enter your connectivity information.
  7. Enter the following required:
    • Database: Your database name
    • Host: Name or IP address of your Vertica server
    • Password: Password of database user
    • Port: Port used to connect to Vertica. By default, 5433
    • User: User name to connect to your database
  8. To verify a successful connection, click Test Connection. You should receive confirmation of a successful test.
  9. Click OK to close the Connection Properties window.
  10. Click OK to close the Shared Data Source Properties window.

Specify a Target Server URL for Web Deployment

  1. Open the Reporting Services Configuration Manager.
  2. Click Connect.
  3. In the left panel, select Web Services URL.
  4. Copy the Report Server URL.
  5. Navigate back to SQL Server data tools.
  6. In the Solution Explorer panel, right-click on your project and select Properties.
  7. In the TargetServerURL field, paste the Report Server URL.

    TargetServerURL.png

  8. In the same dialog box, set OverwriteDataSources to True.
  9. Click OK

Build and Deploy the Project

  1. In the Solution Explorer panel, right-click on your project. 
  2. Select Build.
  3. Select Deploy.
  4. The output at the bottom of the screen displays a deploy link. Copy and paste the URL into a web browser to open the reports.
  5. output.png

QuickStart Example Dashboards

The QuickStart dashboards present sample business and operational data from the VMart example database that a large retail chain might track over time. The chain operates brick-and-mortar stores and an online marketplace. It sells a wide variety of products that it purchases from different vendors.

Note The data in your dashboards will not match the data in the screenshots in this document. This is because the VMart data generator generates data randomly.

Executive Dashboard

The Executive Dashboard presents a high-level view of business data. You can use this dashboard to analyze the overall functioning of the business over time.


In this instance, we can see data about both store and online sales from 2003 to 2007.

executivedashboard.png

Online Sales Dashboard

The Online Sales Dashboard presents an overview of the online business. You can use this dashboard to analyze the performance of the chain’s online sales over time.


In this instance, we see the year 2005 showed a major dip in store sales around July. We also see that the highest number of online sales occurred in California and Texas.

onlinesales.png

Store Sales Dashboard

The Store Sales Dashboard presents an overview of the traditional business conducted in the brick-and-mortar stores owned by this retail chain.


In this instance, we see similar results to those shown in online sales. Again, we see the year 2005 showed a major dip in online sales around July. We also see that the highest number of store sales occurred in California and Texas.

storesales.png

For More Information

For More Information About… See…

SSRS

https://msdn.microsoft.com/en-us/library/hh231681.aspx

QuickStart Video

https://www.youtube.com/watch?v=pFFAO7fs9hM&index=1&list=PLzTQAznhqLUaND7aMRx7h7-AyPvCIWVgb

Vertica Community Edition

https://my.vertica.com/community/

Vertica 7.2

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Contact Us

The Vertica QuickStart for SSRS is intended as an example of complementary technologies: SSRS with Vertica Analytic Database. As such, it is freely available for demonstration and educational purposes to anyone wishing to explore these technologies. The QuickStart is not a product, and is not governed by any license or support agreement.


We welcome your feedback. If you have questions, comments, or suggestions, please contact us by clicking the Contact developer button on the Big Data Marketplace.

Share this article: