Vertica QuickStart for SSRS

To read this document in PDF form, click here.

The Vertica QuickStart for Microsoft SQL Server Reporting Services (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.

You can download the Vertica QuickStart for SSRS from the following location:

https://www.vertica.com/quickstart/vertica-quickstart-for-ssrs/

About the Vertica QuickStarts

The Vertica QuickStarts are free, sample applications created using front-end products from Vertica technology partners. For an overview, watch this short video:

The QuickStarts are posted for download on the Vertica QuickStart Examples page.

Note The Vertica QuickStarts are freely available for demonstration and educational purposes. They are not governed by any license or support agreements and are not suitable for deployment in production environments.

About SSRS

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. SSRS is part of the Microsoft Business Intelligence suite of tools for creating BI applications for SQL Server. For details, see the Microsoft SQL Server Business Intelligence website.

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.  

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.

Installation and Setup

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

Install the Vertica Database Server

If you do not already have Vertica, you can download the Community Edition free of charge:

  1. Navigate to Vertica Community Edition.
  2. Log in or click Register Now to create an account
  3. 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.

To install VMart, follow the instructions in the Vertica documentation:

For details, see VMart Example Database Schema, Tables, and Scripts.

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.

Install the Vertica Client Driver

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. Navigate to the Vertica Client Drivers page on the Vertica website.
  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. Navigate to vertica.com/quickstart
  2. Select Vertica QuickStart for SSRS.
  3. Log in or create an account.
  4. Click Download.

Deploy the QuickStart 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.

    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