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
