Self Service Business Intelligence - how to bring Oracle and

Self Service Business Intelligence - how to bring Oracle and
DB2 z/OS data together
During my work as presales consultant I found in a lot of big companies this typical data
environment: legacy applications, which store their data in DB2 z/OS and client server applications
with Oracle databases. These companies use also a data warehouse, where these both data are
stored and come together for Business Intelligence analysis. Now the need of Self Service Business
Intelligence comes more and more to such a data environment mix of DB2 z/OS and Oracle. Toad
Business Intelligence Suite can handle such a need very quick and easy.
The Toad Business Intelligence Suite has three components:
Toad® Decision Point: Self-Service BI Tool for Business Users – Simplify Data Access, Analysis, and
Toad® Data Point: Data Query and Integration Tool for Data Analysts – Connect, Join, Transform,
and Provision Data with Ease
Toad® Intelligence Central: Agile Data Collaboration that Works with Your Existing IT and BI
Now follow me during the steps, you need to do with Toad Business Intelligence Suite to get Self
Service Business Intelligence with your DB2 z/OS and Oracle data:
Side 1
Starting with Toad® Data Point I connect to the Oracle and the DB2 z/OS databases. I can use here
the standard database connectors from the database vendors instead of only ODBC connection:
Side 2
Toad® Data Point can help me to have a look on the relationships from the tables I want to use and
a look on the data. Here for example the relationships on my Oracle tables I want to use:
Here the data view on one of my DB2 z/OS table:
Now I want to create a query, which join my Oracle and DB2 z/OS tables and give me the needed
data back. The Query Builder from Toad® Data Point will help me here. At first I bring via doubleclick or drag-and-drop the Oracle tables EMP and DEPT into the Query Builder. It automatically
shows the entity relationship between both tables, which is defined in the Oracle database. Then I
put the DB2 z/OS tables EMP_BI and DEP_BI into the Query Builder, which now change into Cross-
Side 3
Connection Query Mode:
With drap-and-drop on the columns I define the application relationship, which is not defined in the
databases, between the DB2 z/OS tables and between Oracle and DB2 z/OS tables:
Now I click and mark the columns from all tables I want in my query. At the bottom all selected
columns show up with column and table name at the Diagram tab:
Side 4
At the Query tab I can control the SQL, which is generated for me:
The Results tab shows me the data, when I run this SQL:
Side 5
Now I have the needed data from Oracle and DB2 z/OS for my Self Service Business Intelligence
project together. Via right-click on the data I can start the publishing feature, which will store this
data in Toad® Intelligence Central, the second part of the Toad Business Intelligence Suite:
I do this, because I want to share this data with other people, who can use this data for example for
creating a storyboard, which show analysis of this data. These people have in most cases not the
possibilities and knowledge to get this directly from the Oracle and DB2 z/OS databases or not allow
to do this.
So back to the publish feature from Toad® Data Point. I have the possibility to define the destination
folder, name, sharing and other helpful settings:
Side 6
The publish type is very important. I have the choice for snapshot, view and dataset. Here are more
details and difference of the publish types:
Now the data is stored on Toad® Intelligence Central and I can check it with the Admin Console:
Side 7
At this point I can start to use the third tool of the Toad Business Intelligence Suite - Toad®
Decision Point. This tool allows me to create reports and storyboards on my data. It is very easy to
use and also design to support people without deep IT knowledge.
At first I need to add the data, which I have stored in Toad® Intelligence Central. A wizard helps me
to do this:
In the next step I need to choose a format. In this case I take the Bar Chart. Toad® Decision Point
automatically show me the available columns of my data and for date field it delivers also additional
columns like year, quarter, month, day hours etc, which is also very helpful:
Side 8
Now I drop the columns/fields into the bar chart to fill with data:
Over additional settings I put a trend line into the bar chart graphic. So you can see my row data is
transformed into a nice graphic, which show for the analysis a lot of information easier.
At the end I want to transfer this bar chart into a storyboard, so I click on the right side on “Create
New Storyboard” and get this result:
Side 9
Here I can do additional things like drawing:
Also I can save it as an URL to share it or send it via Email:
Side 10
I hope you get an overview how Self Service Business Intelligence on your DB2 z/OS and Oracle
data can work and how easy and integrated this work can be supported with the Toad Business
Intelligence Suite.
Also it helps to cover the needs of your Business Users and Data Analysts and let them work
together in a secured way.
About the Author
Udo Brede is Senior Solutions Architect Databases at Dell Software. Before joining Quest/DELL in
2001, Udo worked as a presales consultant at Compuware, supporting the File-AID products.
Currently, Udo works primarily with Dell tools for BI, Oracle, DB2 z/OS and DB2 LUW. He supports
customers across Europe and speaks at IDUG Europe and regional DB2 and Oracle user groups
Side 11