Business Intelligence:
Report Creation and Automation Using Business Objects
Dylan Black
Computer Science and Software Engineering Department
University of Wisconsin – Platteville
Platteville, WI 53818
Abstract
Business Intelligence assists organizations by helping increase their competitive advantage by allowing them to make educated decisions based on data warehoused in databases, partitioned datasets and many other types of data stores. Business Objects is rapidly becoming one of the top solutions for extracting this data and transforming it into user-friendly reports. This information is available to all users and is delivered in a secure, accurate and timely manner.
Business Intelligence
The use of Business Intelligence (BI) software in the workplace has become a standard for most organizations today. BI can be used to extract data and help decision makers track costs associated with certain product lines, evaluate employee performance, forecast negative situations or basically organize data any way that the user sees fit. [2]
BI Software Solutions
In the last few years, the Business Intelligence software market has exploded with many different software solutions. Some examples are Business Objects, CyberScience, OutlookSoft, Oracle PeopleSoft Enterprise One and many, many others. For our purposes, I will concentrate on the use of Business Objects as an enterprise-level reporting application. [1]
Business Objects Overview
Core Layer
Business Objects is a multi-layered Business Intelligence system. The core layer is the actual application itself. This layer allows the user to take the raw data and create reports that may include many types of graphs, charts and plots.
Second Layer
The second layer takes the reports created in the core layer and distributes them to other users so that the data can be analyzed. There are two different means of accomplishing this. Business Objects Web Intelligence (WEBI) is a web based application used for report creation and viewing. Simple reports can be created and distributed to any user very quickly and efficiently.
The Full-Client version of Business Objects allows Information Technology Professionals to create custom reports that can have Visual Basic macros embedded in them. These reports can then be scheduled by Business Object Broadcast Agent (BCA) to run in monthly, weekly, or even daily intervals and be distributed to all necessary users.
Third Layer
In the third layer, the reports that were created in the core layer and analyzed in the second layer are now used to make decisions that are in the best interest of the company. These decisions can be used to help users plan for the future.
Figure 1: Layers of Business Objects
Data Access
Universe Level
Business Objects Universe is included in the BO Business Intelligence Platform. It is an application that allows experienced BO users the ability to create groups of variables that can be accessed at the report level by all users. These variables are usually created with SQL queries. These queries allow IT professionals the opportunity to access data stored in DB2 tables, partitioned datasets, excel spreadsheets etc. using existing business logic while insulating the end user from the actual data itself. The user accesses these variables from the main Business Objects application. They are viewed as objects that you can drag-and-drop onto a report where they automatically inserted into the report as a table. These tables can then be used to create more complex graphs and charts.
Figure 2: Business Objects Report Example
Report Level
Sometimes users need to create customized variables that will only be used once for that particular report that they are working on. In this situation, it wouldn’t be necessary to create a variable at the Universe Level. At the Report Level, the user can create new variables to be used in the report by creating formulas based on Universe Level reports. Figure 2 shows how this is done. The formulas used in this report are listed on the left side of the screenshot under the Formulas folder.
Report creators that have an understanding of SQL (Structured Query Language) are able to put these skills to work. Freehand SQL (FHSQL) is a way to bypass the Universe to get data. The report creator is able to connect to DB2 tables and access the needed data by writing their own SQL query and embedding it in the report.
Report Creation
Business Objects Broadcast Agent
Business Objects Broadcast Agent (BCA) is a report scheduling application that can be used as an enterprise wide scheduler. This application resides on a server that all Business Objects users can gain access to if needed. Report are scheduled at certain intervals and output to numerous end users. BCA has capabilities to output reports as Adobe PDF files and either store them in some type of data store or email them to the users.
BCA also has functionality to store reports in a report repository. Users can send reports from this repository to other users without having to gain security clearance from the BCA administrator. Reports can be executed and created anytime that the user deems necessary. Figure 3 shows the scheduling part of BCA.
One way to schedule reports more dynamically is to use the File Watcher functionality. Broadcast Agent will watch for whatever file that is specified here to be created. When this file is created it will refresh the report data and execute any bundled Visual Basic macro. One of the main uses of this is to tie older technology like batch data processing with Business Objects. A very good example of this is a report that access DB2 tables for its data. Each month these DB2 tables have COBOL or SAS programs create/update them. When these batch programs are completed they output a text file via FTP to some directory that BCA has access to. When BCA sees this file it refreshes and creates the report. This process requires no user interaction once it is scheduled.
Figure 3: Screenshot of the report scheduling portion of BCA.
Visual Basic Macros
One of the most versatile features of the Business Objects Reporting Suite is the ability to execute Visual Basic macros. Any type of functionality contained in Visual Basic for Applications (VBA) can be used within Business Objects. This includes the use of popular functionality like file manipulation using files system objects and email creation using Microsoft’s Collaboration Data Objects [4]. See appendix for an example of a visual basic macro that implementsBusiness Object’s built in Visual Basic functions to create a PDF version of the report and store twelve different versions of the reports in a directory.
Another implementation of the Visual Basic Macro is to use them to run command line scripts such as batch or even PERL. Since PERL is known for its ability to search through and process directories and files very quickly, it can be executed very efficiently from within the VB macro. After the data is processed, it is returned back to the calling VB macro so that the Business Objects report is able to use it.
Figure 5: A business objects Visual Basic Macro viewed in the standard VB Macro editor you would see in Microsoft Excel or Access.
Business Objects Live Office
Business objects Live Office is a new feature added in the current version of Business Object, Business Objects XI Release 2 that gives users the ability to include Business Objects report in Microsoft PowerPoint, Excel, and Word files. This adds the ability distribute reports over the internet in a more dynamic real-time environment.
Business Object Live Office Reports can actually be “live” reports. The reports can be tied to their Universe variables or Freehand SQL queries. This gives the user the ability to change the way the report works whenever needed. With this added functionality, business men and women are now able to create and present much more dynamic presentations.
Figure 6: This screenshot shows a Business Objects Report embedded in a PowerPoint Presentation.
Conclusion
Business Objects has found ways to implement new technologies while tying them together with existing technologies. This is a vital attribute that Business Intelligence software must possess in order for it to be effective. The cost of converting older, existing technologies to newer technologies is very, very high. Today, many organizations try to do this over time by adding newer technology in small steps, while using the existing technology at the same time.
Business Intelligence has become a vital part of any organization. Managers and other decision makers are given the ability to have data presented to them in many different ways in a safe, secure manner. Business Objects has become one of the leading applications to implement Business Objects. Surely Business Intelligence and Business Objects will be around for years to come.
References
[1] TechTarget, Retrieved October 2006
[2] Business Intelligence, Retrieved October 2006
[3] Business Objects XI, Redefining How Information Empowers Users, Retrieved October 2006
[4] Microsoft Collaboration Data Object, Retrieved October 2006
[5] Business Objects Live Office, Retrieved October 2006