Vertica Integration with OpenText Magellan: 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.
Vertica and OpenText Magellan: Latest Versions Tested
Magellan Analytics Designer Cloud Edition (CE) 22.4
Magellan BI and Reporting Cloud Edition (CE) 22.4
Windows Server 2019
Vertica JDBC Driver 12.0.3
|Vertica Server||Vertica Analytic Database 12.0.3|
|Server Platform||Linux RHEL 8.7|
OpenText Magellan Overview
OpenText Magellan Analytics Designer allows developers to design and create dynamic reports with embedded analytics, data visualizations, interactive web applications, and customizable dashboards for personalized insights and better end user experiences.
OpenText Magellan BI & Reporting seamlessly embeds insightful interactive self-service reports and dashboards into enterprise applications to increase their value and improve data-driven decision-making.
Contact Magellan Support from OpenText Magellan website to get the Magellan software. We installed Magellan Analytics Designer and Magellan BI & Reporting.
Installing the Vertica Client Driver
Magellan uses JDBC driver to connect to your Vertica database. To download and install the Vertica client package
- Go to the Vertica Client Drivers page.
- Download the JDBC driver that is compatible with the Vertica server version.
Note For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
Connecting Magellan Analytics Designer to Vertica
Creating a Project
BIRT Report Designer Professional organizes files by projects. You can create one project to organize all your reports or create multiple projects to organize your reports by categories. For each project that you create, BIRT Report Designer Professional creates a folder in your computer’s file system and adds default content such as a BIRTApplication.xml file and an HTML landing page. The contents of this project become the BIRT Application.
- Choose File > New > Project.
Select a wizard and click Next.
In the New Project dialog box, enter a project name and select the project type as Blank or Report. We selected Report. Click Next.
If you select Blank Project, a report must be created separately by following the Create a Report steps below.
If you select Report, a report is created automatically in the project.
- Select the type of report and click Finish.
You should see the project in the Navigator view.
Creating a Report
Before building a Data Source to connect to Vertica, you must create a Report in the project.
- Select File > New > Report.
Select the project from the list. Click Next.
A New Report window appears. Provide a file name and click Next.
Select the report template and click Finish.
After the report is created, the Data Source option becomes visible in the Data Explorer tab.
Building a Data Source
To build a data source, you need to specify the connection information that is specific to that type of data source. For Vertica, you must provide the JDBC driver .jar file for use with BIRT Report Designer. Follow these steps to build a data source.
- In Data Explorer, right-click Data Sources and choose New Data Source from the context menu.
- Select JDBC Data Source from the list of data source types. Enter a Data Source Name and click Next.
Click Manage Drivers and select the Vertica JDBC .jar. Click OK.
- In the New JDBC Data Source Profile dialog box, the following fields are required:
Click Test Connection. If the connection is successful dialog box appears, click Finish.
The BIRT Report Designer creates a new data source that connects to the Vertica database. It appears within the Data Sources in Data Explorer.
Building a Data Set
A data set identifies the data to retrieve from a specified data source. To build a data set
- In the Data Explorer tab, right-click Data Sets. Select New Data Set from the context menu.
- In the New Data Set dialog box, specify the Data Set Type and Data Set Name. Click Next.
- From the list of Available Items, select the table you want to query, and write the appropriate query. Click Finish.
- You can create data sets for other tables by following the same steps. After all data sets are created, you can double-click them, and click Preview Results to view the data.
- You can also use the data sets to build and create reports that represent the data.
Connecting Magellan BI & Reporting to Vertica
Log in to Magellan BI & Reporting using your credentials.
Creating a Connection Profile
A connection profile is a set of connection properties and values sufficient to connect to a Vertica database. We created a connection profile file using Magellan Analytics Designer.
Right-click Vertica connection data source from the Data sources. Select Externalize to Connection Profile.
Check the option as shown and enter a connection profile name.
Select the Connection Profile and click Browse.
Select the folder and specify the filename. Click OK.
The connection profile is created in the workspace of the folder selected previously.
Adding Connection Profile in Magellan BI & Reporting
Create Predefined Connections folder in the Resources folder. You must have visible, read, and execute privileges on this folder and its contents.
Click Add Folder.
Provide the folder name as Predefined Connections and click OK.
Predefined Connections folder is created in Resources folder.
Place the connection profile file that was created using Magellan Analytics Designer in the Predefined Connections folder.
Open Predefined Connections folder and click Upload.
Choose the connection profile file and click OK.
Connection profile file is now added.
Installing Vertica JBDC Driver
A connection profile file does not contain database drivers. Vertica database requires a driver that is not installed with BI & Reporting.
The administrator must add the driver in BI & Reporting using the Data Source Management page in Administration Console.
Click Administration from the profile icon to open the Administration console.
A new window appears for administration console, click Data Source Management.
Click Add Driver.
Select the Driver as JDBC, specify the driver display name and browse for the JDBC driver file. Click Save.
Vertica JDBC driver is added now.
OpenText Magellan BI & Reporting must be restarted using Services Manager after the driver is added.
Creating New Data
New Data is used to add data from the database tables. To connect to the Vertica database, you must have a connection profile file that contains database information and it should placed in the Predefined Connections folder.
Click New Data from the Create dropdown.
A new window opens, click Predefined connection.
Select the connection profile, click Test Connection. Once the connection is successful, click Connect.
Select the schema and a list of tables appears. Click Preview to view the data of each table.
Select the required tables and click Save data design. Specify the data design name and click Save.
These data designs can used for creating Reports and Dashboards.
Magellan Analytics Designer
BINARY, VARBINARY, and LONG VARBINARY values are displayed in Hexadecimal.
For TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ data types, timezone, seconds, and milliseconds are not displayed.
Magellan BI & Reporting
BINARY, VARBINARY, and LONG VARBINARY values are displayed in Hexadecimal.
For TIME data type, milliseconds are rounded off after 3 digits.