Share this article:

Vertica QuickStart for OBIEE

Click here for a PDF version of this document.  

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 the 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 Oracle Business Intelligence Enterprise Edition (OBIEE). The document includes the setup information that you need to get up and running and it provides an overview of the OBIEE dashboards and the Vertica data sources.

OBIEE is a Business Intelligence platform that includes capabilities such as ad-hoc querying, interactive dashboards, and mobile analytics. OBIEE helps businesses analyze data to make strategic business decisions.

Vertica QuickStart with OBIEE Overview

The Vertica QuickStart for OBIEE is a sample BI application implemented as a set of OBIEE 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 OBIEE to quickly explore, visualize, and gain insight into their data stored in Vertica. The QuickStart sample application is available as a .zip file that contains the OBIEE application.

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

Requirements

The deployment of this QuickStart was tested on OBIEE 11.1.1.7.0 using Vertica 7.2.2. All versions of 7.2.x should work with the QuickStart application, but other versions may not have been tested. For more information about OBIEE, see the product site.  

The Vertica QuickStart for OBIEE requires a Vertica database server with a standard installation of the VMart example database, a Vertica client driver, and OBIEE 11.1.1.7.0 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 the Community Edition 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 Get it!
  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 the Community Edition directly from the Vertica Community portal:

  1. Navigate to https://my.vertica.com/.
  2. Under Register, 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 OBIEE 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 OBIEE

You can download the latest version of OBIEE from the Oracle Business Intelligence 12c Downloads page. Follow the instructions to install OBIEE as an administrator.

Download and Install the Vertica Client Driver

OBIEE uses the ODBC driver to connect to Vertica. Before you can connect to Vertica using OBIEE, you must download and install the Vertica client package. This client package includes the ODBC driver that OBIEE uses to connect to Vertica.

To download and install the Vertica client package:

  1. Go to the Vertica Client Drivers page on my.vertica.com.
  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.

Create an ODBC Data Source Name (DSN)

You must create a DSN to use the ODBC driver to connect to Vertica. You can set up either a 64-bit DSN or a 332-bit DSN, depending on your operating system.

Follow these steps to create a DSN:

  1. Navigate to the appropriate folder, depending on which DSN you need to install:
    • 64-bit DSN: Start > Control Panel > Administrative Tools > Data Sources (ODBC)
    • 32-bit DSN: Navigate to C:\Windows\SysWOW64 and locate the program file called exe
  1. The ODBC Data Source Administrator window displays. In the System DSN tab, click Add.
  2. In the Create a New Data Source dialog box, select Vertica driver and click Finish. The Vertica ODBC DSN configuration dialog box appears.
  3. In the Basic Settings tab, enter the following required fields:
    • DSN Name
    • Server
    • Database
    • User Name
  1. Click OK. The Vertica ODBC DSN configuration window closes. Your new DSN is listed in the ODBC Data Source Administrator window.
  2. Click OK to close the ODBC Data Source Administrator window.

For more information, see Setting Up a DSN 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 OBIEE.
  4. Click Download.
  5. Navigate to the folder where you downloaded the compressed file and extract the contents.

Deploy the QuickStart for OBIEE Dashboards

The VerticaQuickStartforOBIEE file contains the following:

  • VMart BI0011.rpd: The RPD (repository) file contains the metadata information and the information about the defined data model of VMart.
  • VerticaQuickStartforOBIEE.catalog: The catalog file. The catalog is a storehouse where all the reports, prompts, and filters used in the VMart dashboards are placed.

Upload RPD File

To upload the RPD file, follow these steps:

  1. Verify that the OBIEE server is running and all BI components are up. Navigate to your web browser.
  2. Navigate to the Enterprise Manager located at http://hostmachineIP:7001/em to upload the RPD file that is kept in the VerticaQuickStartforOBIEE folder.
  3. Enter your username and password.
  4. Click Sign In.
  5. From the left panel, click the Business Intelligence Folder and select coreapplication.
  6. Click the Deployment
  7. Click the Repository
  8. Click Lock and Edit Configuration. The Lock and Edit confirmation window appears. Close the window.
  9. From the Upload BI Server Repository section, click Browse.
  10. Select the repository file, named rpd.
  11. Enter the password Admin123.
  12. Confirm the password by entering it again.
  13. Click Activate Changes. The Activate Changes confirmation window appears. Close the window,
  14. Click Restart to apply recent changes.

 restarttoapplychanges.png

  1. Click the Restart tab.
  2. When asked if you want to restart all BI components, click Yes.

The RPD is successfully uploaded.

Deploy the VMart OBIEE Catalog

  1. Open your web browser.
  2. Navigate to http://hostmachineIP:9704/analytics to upload the VMart catalog file.
  3. Enter your username and password.
  4. Click Sign In.
  5. On the top of the dashboard, click the Catalog
  6. On the left Folders panel, click Shared Folders > Dashboards.
  7. Click the Unarchive tab. 

 unarchivetab.png

  1. Click Browse.
  2. Browse for the catalog file. Click Open. The VMart dashboard appears on the Folders panel.
  3. Click Open.

vmartdashboard.png 

The VMart dashboard opens.

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. This section shows some of the example dashboards.

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, you can see a summary of sales purchases between 2003 and 2004. You can filter selections using the dropdown menus at the top of the dashboard.
 

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 dashboard, you can see that 2004 had higher sales over time than 2003. You can also see that online sales are highest in California and Texas, with Virginia, Georgia, and North Carolina showing the lowest sales.

onlinesalesdashboard.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 dashboard, you can see that store sales are clustered in California and Texas. You can also see that store sales are higher in 2004 than in 2003. This dashboard also shows information about products, and we can see that the distribution of store sales in the top 5 products is relatively even.

 storesalesdashboard.png

Call Center/Employee Overview Dashboard

The Call Center/Employee Dashboard presents a look at both employee regional data as well as information about the different call centers.

In this dashboard, you can look at information about the top call centers and top employees. You can also compare information about online and store sales.

 callcenterdashboard.png

For More Information

For More Information About… See…

OBIEE

http://www.oracle.com/us/solutions/business-analytics/business-intelligence/enterprise-edition/overv...

QuickStart Video

https://youtu.be/FOcJUOhYiz8

Vertica Community Edition

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

Vertica 7.2 Documentation

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 OBIEE is intended as an example of complementary technologies: OBIEE 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.

Share this article: