Business Intelligence

business intelligence overview

Business Intelligence can be termed as Business specific Information that helps drive the business or make key business decisions, example an IT company may have 300 developers, 50 sales people, and 20 customers, they board has set a 10% growth target for current fiscal year, it would be required to add few more customers, sales, development and support teams. if a tool can help Chief Operating Officer to plan his next resource hiring and plan out sales operations and emerging markets business intelligence would be a tool / software that he/she might need.

Data Tier : Going further down , sometimes information in an organization can reside in LDAP Servers ( such as customer data ), Relational data bases ( such as products and solutions that they sell ), XML files ( example sales transactions and partner information)  , this discrete data is a part of Data tier.

Historical data such as city names stored in relational database, when we talk about historical data what it means is data is not updated but stored based on date, for example a city might be renamed from Madras to Chennai, the city name is not over written but its Historized. a query based on current city name might be Chennai, but a query that has date set to few years back would bring in Madras ( which is historized data ) same thing can be countries getting renamed, or people changing Jobs, or changing cities.

Information Extraction Tier: Information can come from more than one data source XML, DB, LDAP, Files, B2B Documents etc. Information tier tries to extract valid information from these data sources. it can use SQL Engine to Query various databases like Oracle, DB2, MySQL, It can use XMLQuery to load required data from XML Files, LDAP Servers to get data from LDAP systems such as open LDAP or Oracle Internet Directory.

Business Intelligence Layer : this is much filtered data reports that would be presented to C Level Executives or Upper Management. this is what that helps them to make decisions related to Business – this is Business Centric View – as defined by TOGAF and other architectural frameworks.

Transactional Data v/s Data Warehouse : Sometimes if the data is growing at a rapid pace its often stored as historical data in data warehousing systems, Transactional data is the current one whereas Historical data is older one – Business Intelligence sometimes may be required to read and build business reports from both these data sources. also see Business Architecture in TOGAF

oracle business intelligence home

A Snap shot of a Sample Oracle Business Intelligence Dashboard

also lets have a look at sample BAM Dashboard

Sample Hyperion Dashboard currently out of discussion scope within this Blog

BAM v/s OBIEE choice 

This Blog is related to my previous Blog covering Fusion Middleware Technologies such as SOA, BAM, OSB, Business Rules, BPM, WC, UCM, IDM and ADF Mobile technologies. Comprehensive Solutions with Oracle Fusion Middleware lets brainstorm about when is it right fit to use BAM (Business Activity Monitoring) and Oracle BI (Business Intelligence) ,

Similarities : Some of the common things both can do is generate complex reports with excellent Presentation to the Business or IT User including Options to Drill down. Both are capable of showing data changes in real time. BAM has an Advantage over BI in-terms of real time data refresh. Both allows invocation of Web Services designed at SOA layer. SQL based reports that can be Queried at run-time by passing various input parameters, Both allows Alerts and Notifications.

Differences: BAM heavily relies on Data Objected created from data sources, BI also allows creation of Reports from Data Sources, but BI has wider choice in its ability to handle multiple types of input data source such as flat file, xml file, various database, historical data, Ldap data and Multi Dimenstional data etc, not only that it also allows to Analyse the Data without writing a TON of Complex SQL , something similar to Excel Sheets, BI is more Business Centric and friendly towards Business User or Business Analyst who needs to take business decisions based on a Business Analysis where as BAM is more inclined towards IT user who needs to take IT related decisions towards a Business or IT problem.

Example : BI might be needed analyse data from various sources and to invoke a web service based on current Business trends to set up new Business Targets for next Financial Year, BAM might be an excellent candidate for trying to trigger a SOA Process automatically once alerts are raised on certain conditions example cutting off Partnership agreement during flooding of Documents in B2B Document Exchange Scenario. this is something IT would be concerned not really a concern of Business.

Working with BI

Usecase – let us extend our usecase where James Smith University , needs to do few things related to their line of business

  1. Once student join send a Formal Approval Document to the student (PDF)
  2. Allow Principal to view various reports such as student intake based on reservation category
  3. Allow the Business Analyst to decide on Cut off percentages or Stop Student Intake process for a current FY or next FY.

These are purely Business Decisions that really dont need dependency on IT folks to re-design their process or change and redeploy various applications.

creating report templates

All Reports need a template, these templates represents XSL style sheets which can be of RTF or XSL formats, BI comes with its own Auto Generate Template option based on Data Model, However if you plan to use customized templates with logos and texts placed at some precise locations with in template then you would need to install download and install OracleBIOffice.exe and BIPublisherDesktop.exe on Windows Machine, these would create a new tab to MS Word, Once you open MS Word, you would need load the XML Data file which could be generated from BI Datasource, once the XML Data File is loaded you can add various fields or use table wizard to create the template and save this as an RTF File. Template to send Admission Accepted Letter to Student

Template to View Student List under various categories

these RTF Templates are later uploaded while creating report in BI.

creating data modeL and reports

Before creating Report its important to create data model based on SQL or Web Service or LDAP or any other data sources. the below fig shows a SQL based data model which can also take various input parameters. Data Models needs to be selected it can be based on SQL, MDX, BI Analysis, View Objects, Web Services, LDAP Query, XML Files, Excel Files, XML Feeds based on http request.

let us select SQL Query based Data Model and select a table and its columns

SQL is displayed that can be edited

various input parameters can be set

Total Fees paid v/s reservation category : below report is using a BAR Chart Template , that lists student based on reservation category and total fees paid by them.

List of Students based on Reservation categories : this user Table Wizard Template to generate this report,

Offer or Invite Letter : this is based on offer letter table that we created earlier

it would be great process orchestration , that every time a new student is admitted and automated mail is sent to the student attaching the above BI generated PDF report.


creating dashboard

A new dashboard can be created by dragging and dropping various reports and action links and web page URLs all that needs to be in a great report. one dashboard can have multiple reports aligned next to each other or one above another.

creating action events – webservice invocation

BI can invoke a web service using Action Links, where WSDL URL is provided that can be reached by the host BI Machine. Let us create a simple database update web service for the sake of demonstration, this would do an Upsert Operation Update or Insert on a table , this takes 4 parameters, year, general merit cut off percentage, obc cut off percentage, and scst cut off percentage for various reservation categories, and based on input it will Update or Insert a record into DB table.

let us implement the above composite in the BPEL process, deploy and test run

In our case the URL will be http://ip:7001/soa-infra/services/default/CutoffPercentProj/cutoffprocess_client_ep?WSDL

once WSDL is selected it automatically shows the list of method invocation

it also displays list of input params that the web service takes, it can be hard coded or passed on run time.

Once an Action is defined , it can be invoked as a stand alone or from with in the BI Dashboard or can be set as links to various pieces of report data.

Tasking Decisions based on Business trends Business user sees the current Academic year student admissions, based on which he/she decides on the cut off percent for this or next year

Cut off percent are set based on web-service invocation through links at left side of the above report.

Once web service is successfully executed , it returns back a message. its also possible to show web service returned XML values as well.

soa BPM – bi integration point

Since Business user is now able to set off cut off percentage for admissions, we can use this service implementation in our original BPM process as service invocation

Student and Admin officer Roles Student Submits Application form online with his current academic details such as student name, percentage , reservation category, fees amount, parent income per year through his mobile device. first level of screening is done to identify current cut off percentage, if the cut off percentage is more than student percentage application is rejected here itself. Business Rules are applied to ensure seats are made available to people on not just cut off percentage but also depending up on students parents income group. Admin officer views the attached documents, approves to next level, student fees is collected through Net banking that basically transfers fees amount from student account to James Smith University account, if transaction gets declined the process stops else proceeds further.

once fees paid is confirmed its application is recheck and approved by finance officer, then sent to principal for his final approval. if principal approves, student id card are ordered through partner procurement. various reports are generated at BAM dashboard BI Finally helps in sending student his “Admission Certificate” to his email id. BI also helps Business Analyst to decide on cut off percentage or to even stop Admission Process for a given Academic year.


Interactive Layouts – new section added

Once you have a Data Model in our case a Student sql data model, we can create interactive reports, where user gets option to filter results, sort them, add conditions etc. In the Layout one needs to select “Blank Layout” to start with . Drag and drop the Data Table , add each columns in the Data Model into the Data Table , select interactive option, you can also select a field and do summation or averaging or apply any mathematical functions to render resultset.

while in the edit mode you can also drag n drop chart and gauge and set up relevant parameters from the data source

save and preview

Since interactive mode is selected during report design one can sort out the results on various conditions