Thursday, August 29, 2013

Teiid (Data Virtualization) and Business Intelligence


This article describes Data Virtualization and Business Intelligence including a step by step demonstration of multisource, federated data virtualization with Teiid/Teiid Designer and the Teiid Dashboard.

Overview

As business intelligence (BI) teams face increasing pressure to perform faster, organizations are quickly adopting agile development methodologies to try and keep up. Join us to learn how virtual, integrated views of data make agile BI development easier.
Respond faster, smarter to business demands

Successful BI teams must be able to implement projects fast and respond quickly to changing business requirements. Agile development can help your business respond to these needs, but as the methodologies are updated, you also have to update the tools and techniques for implementing BI.

Red Hat® JBoss® Data Services is perfectly suited for agile BI teams.

Webinar Recording - http://www.redhat.com/about/events-webinars/webinars/2013-08-29-business-intel-using-data-virt

Slides from the Webinar - http://www.slideshare.net/opensourcementor/delivering-agile-business-intelligence-using-data-virtualization



Demonstration

Source Code - https://github.com/kpeeples/dv-and-bi

Section 1 – Setup
Step 1 - Download JBoss EAP 6.1 from JBoss.org and JBoss Developer Studio 7 (Kepler) http://www.jboss.org/products

Step 2 - Install JBoss Tools which includes Teiid Designer/Runtime 8.2.
http://download.jboss.org/jbosstools/updates/integration/kepler/integration-stack/aggregate/4.1.2/

Step 3 - Install Teiid 8.2, Web Console and Teiid Dashboard into JBoss EAP

(I am using an early release of Data Virtualization 6)
http://www.jboss.org/teiid
http://www.jboss.org/teiiddesigner
https://github.com/teiid/teiid-dashboard

Additional Tools
-SQuirrel SQL Client with Teiid JDBC jar installed
-SOAPUI
-Maven

Section 2 – Teiid Designer
Step 1 - Modify standalone/configuration/teiid-security-roles for admin role for dashboard for user

Step 2 - Add User for the Management Realm - admin/qwer1234@!#$

Step 3 - Add Server in JBDS

Step 4 - Browse to initial applications for verification

a. Access the Web Console - http://localhost:9990/console - with the admin user

b. Access the Teiid Dashboard - http://localhost:8080/dashbuilder/ - with the user user

Step 5 - Build the expenses VDB to enable changing the expenses csv in the dashboard to the expenses VDB

a. Action - Define Teiid Model Project - New, entering ExpensesModelProject and accepting defaults

b. Action - Create a Teiid Flat File Connection

c. Action - Create the source model from local flat file source - use expenseReports.csv

d. Action - Use expenseReports.csv and edit the delimeter as semicolon

e. Action - Preview Data

f.  Action - Define VDB

g. Action - Execute VDB

h. Action - Deploy VDB

Step 6 - Build the federated multisource VDB

Simple example of using a Union View for a federated vdb (on a XML File and a Flat File)

a. Create the data
b. Create the Project
c. Create the Flat File Connection
d. Create the Flat File Source Model
e. Create the XML File Connection
f. Create the XML File Source Model
g. Create a Union View Model
h. Create the Relational View Model
i. Create the View Table
j. Add Source Tables to View Transformation
k. Test the New Model
l. Add an additional column to one datasource

Step 7 - Simple example of MySQL Datasource

Step 8 - Simple example of Web Service Datasource

Step 9 - Test from a Java Program using the Teiid JDBC driver

Section 3 – Web Console

Step 1 - View the Teiid Virtual Databases and datasources in the web console

Section 4 – Teiid Dashboard

Step 1 - Change the expenses sample dashboard to us the Expenses VDB

a. Create the external connection with Teiid

b. Create the Data Source with SQL Query - SELECT office, amount FROM expenseReportsViewTable

c. From the Expense Reports Sample Dashboard, Edit Content on the expenses by Office USD and change the Data provider.  The data should be the same.  The virtual database has abstracted the data.

d. Amount is String error.  Change the data type for the amount field in the VDB and redeploy.  The total amount should have the correct amount.

Step 2 - Create a new Workspace

a. Create a External Connection and Datasource in the Teiid Dashboard

b. Create a Workspace, page and panel with KPIs using the VDBs from above

References:

https://community.jboss.org/wiki/ExampleOfFederatingTwoDataSources

https://docs.jboss.org/author/display/teiid82final/Installation+Guide