Wednesday, December 24, 2008

WebFOCUS Server Symbolic Variables

Within the WebFOCUS Reporting Server's profile logic, you can set symbolic variables that user programs can interrogate. For example, you could set a flag so that WebFOCUS programs know where they are running. This "where am I?" feature is useful when your test, development, production QA, and production environments may be slightly different and the programs must take into consideration some nuances.

Let's image putting the following statement in the WebFOCUS Reporting Server's profile (the edasprof.prf file):


Any program running on the server can now check this global variable to see what value the server has put there. In the example above, the programs could look at &&SYS_SERVER and know that they are on the development server.

Here is an example -- common code that programs could call to find the names and locations of company logo images:


- 'http://localhost/approot/baseapp/co_logo.jpg' ELSE

- 'http://localhost/approot/baseapp/co_logo_small.jpg' ELSE

- '/reports/images/co_logo.jpg' ELSE

- 'co_logo_small.jpg' ELSE
- IF &WFFMT IS 'HTML' THEN '/prod/images/co_logo.jpg' ELSE
- IF &WFFMT IS 'PDF' THEN 'co_logo_small.jpg' ELSE ' ';

By default, this common logic assumes reports run on the production server. If this is not the case, the server lets the program know that is on (in this example, anyway) the development server or on an developer's machine (using the default LOCALHOST name).

Here is another good idea for symbolics on the server.  Developers like to include debugging comments in their programs and often forget to remove them when promoting to production.  Using a WebFOCUS server symbolic, we can dynamically turn on and off debug commands depending upon which server is being used.  When running on the test server, turn on debugging.  When running production, however, turn those commands off.

On the production server, put this command in its profile:


On the test server, change it slightly to be this:


Inside all of your WebFOCUS programs, you can always have this command:


When running production, debug echos are turned off; in the test environment, they are turned on.

If you have any questions about WebFOCUS symbolic variables, let me know.

Tuesday, December 23, 2008

Tracing BI Dashboard Activity

Turning on traces for the WebFOCUS BI Dashboard involves tinkering with an XML configuration file on the Java application server. Like the WebFOCUS server traces, use caution -- play with a test server before trying this in production.

On a Windows box running WebFOCUS 7, you will find this BID configuration file at drive:\ibi\webfocus7x\worp\conf\bid-config.xml. It contains a parameter telling WebFOCUS whether or not to trace dashboard activity.

Within < > brackets, you will see:

trace-option trace-flag="false" trace-level="DEBUG"/

To start tracing, change the "false" to "true" and restart the Java app server. WebFOCUS will then create trace files for each person logging into the dashboard. On a Windows box, WebFOCUS 7 stores the logs in a folder called: drive:\ibi\webfocus7x\logs.

WebFOCUS names the output log files with a prefix of a unique number and "DashboardTracing." It also adds a suffix of the userid and date-time stamp. The naming format is: #####_DashboardTracing_userid_yymmdd_hhmmss.trace.

When you are done tracing BID activity, change the configuration setting back to "false" and restart the app server.

Below is an example of a Windows WebFOCUS BI Dashboard log-in failure:

Gen Date: Mon Apr 2 16:31:57 EDT 2007
Gen Number: 4
Gen Label: branch761
Server Info: Apache Tomcat/5.5.17
2 11/26/2007 11:29:44:937 INFO WORP_RM.logDefaultTraceInfo
2 11/26/2007 11:29:44:937 INFO WORP_RM.logDefaultTraceInfo ###########################################
2 11/26/2007 11:29:44:937 INFO WORP_RM.logDefaultTraceInfo Request type: WORP_LOGIN
2 11/26/2007 11:29:44:937 INFO WORP_RM.logDefaultTraceInfo Is valid session: false
2 11/26/2007 11:29:44:937 INFO WORP_RM.logDefaultTraceInfo ************************************
2 11/26/2007 11:29:44:937 DEBUG WORP_RM.logDefaultTraceInfo Request Parameters:
2 11/26/2007 11:29:44:937 DEBUG WORP_RM.logDefaultTraceInfo WORP_REQUEST_TYPE=WORP_LOGIN
2 11/26/2007 11:29:44:937 DEBUG WORP_RM.logDefaultTraceInfo IBIWF_language=en
2 11/26/2007 11:29:44:937 DEBUG WORP_RM.logDefaultTraceInfo WORP_USER=doug
2 11/26/2007 11:29:44:937 DEBUG WORP_RM.logDefaultTraceInfo WORP_PASS=******
2 11/26/2007 11:29:44:937 DEBUG WORP_RM.logDefaultTraceInfo ************************************
2 11/26/2007 11:29:44:937 DEBUG WORP_RM.logDefaultTraceInfo Request Attributes:
2 11/26/2007 11:29:44:937 DEBUG WORP_RM.logDefaultTraceInfo ************************************
2 11/26/2007 11:29:44:937 DEBUG SignonAdapter.createWFMRSession Created WFMRSession
2 11/26/2007 11:29:44:937 DEBUG WORP_SignonManager.singon() Signing on to Dashboard
2 11/26/2007 11:29:44:937 DEBUG WORP_SignonManager.singon() Doing form authentication: doug
2 11/26/2007 11:29:44:937 DEBUG WORP_SignonManager.singon() Doing single signon
2 11/26/2007 11:29:44:937 ERROR WORP_RM.doPost Exception
ibi.worp.util.WORP_Exception: (19105) Invalid user ID or password. Please try again.
at ibi.worp.mrapi.WORP_SignonAdapter.signon(
at ibi.worp.WORP_SignonManager.signon(
at ibi.worp.WORP_RM.doPost(
at javax.servlet.http.HttpServlet.service(
at javax.servlet.http.HttpServlet.service(
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
at org.apache.catalina.core.ApplicationFilterChain.doFilter(
at org.apache.catalina.core.StandardWrapperValve.invoke(
at org.apache.catalina.core.StandardContextValve.invoke(
at org.apache.catalina.core.StandardHostValve.invoke(
at org.apache.catalina.valves.ErrorReportValve.invoke(
at org.apache.catalina.core.StandardEngineValve.invoke(
at org.apache.catalina.connector.CoyoteAdapter.service(
at org.apache.coyote.http11.Http11Processor.process(
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(
at org.apache.tomcat.util.threads.ThreadPool$
at Source)
Caused by: ibi.webfoc.wfmre.mrutil.WFMRError: The User ID or Password you have entered is incorrect.
at ibi.webfoc.wfmre.mrutil.WFMRUtil.throwMRError(
at ibi.webfoc.wfmre.repos.htm_bd.WFMRFileUserHandler.getAuthenticatedUser(
at ibi.webfoc.wfmre.repos.core_bd.WFMRRouterUserHandler.getAuthenticatedUser(
at ibi.webfoc.wfmre.repos.WFMRUserHandlerProxy.getAuthenticatedUser(
at ibi.webfoc.wfmre.mrobj.WFMRRepository.getAuthenticatedUser(
at ibi.webfoc.wfmre.WFMRSession.getAuthenticatedUser(
at ibi.webfoc.wfmre.WFMRSession.authenticateUser(
at ibi.worp.mrapi.WORP_SignonAdapter.signon(
... 18 more
2 11/26/2007 11:29:44:937 DEBUG WORP_SessionUtil.doDispatch forwarding to: /bid-login
2 11/26/2007 11:29:44:937 DEBUG Opening file for reading=C:/ibi/WebFOCUS76\config\basedir-config.xml

Monday, December 22, 2008

Summit 2009 (June 7-11)

Mark your calendar for Information Builders' 2009 users’ group conference held on June 7th through the 11th. Like last year, we are going to the Gaylord Opryland Resort in Nashville, Tennessee.

Take advantage of this opportunity to network with WebFOCUS professionals from other companies and learn about their BI projects. If you are interested, I can coordinate breakfast, lunch, or dinner get-togethers to help you connect with the right people.

Summit is the place to increase your WebFOCUS knowledge -- listen to excellent speakers, attend workshops, and talk one-on-one with product managers and technical gurus. If you have a difficult BI problem at work, visit the Expert Room where top-quality vendor representatives (field technical managers, corporate technical support, and product managers) will dig in to figure out and resolve your issues.

To learn more about the Summit, visit IBI's website. I hope to see you there!

WebFOCUS on zLinux for Enterprise BI

On my general BI blog, I wrote about the State of Oklahoma Department of Human Services (OKDHS) moving their legacy FOCUS applications to WebFOCUS on zLinux. For more information, click here.

Sunday, December 21, 2008

Dude! Where's my Trace?

While an administrator can turn on traces, it's also possible for a developer to put some tracing commands in his or her WebFOCUS code.

When WebFOCUS traces are turned on, you can display them as comments within your output's HTML -- something called "client-side tracing." If you pick an output that does not generate an HTML page (e.g., PDF) then you will not be able to see the trace.

While most WebFOCUS environmental settings are basically toggle switches with ON and OFF settings, traces are different. There is a TRACEON setting as well as one for TRACEOFF. For each, you identify the trace component that you want to turn on/off. For example, I may want to see the SQL statements being generated from the 4GL commands; I need to TRACEON that particular SQL component (called STMTRACE -- comparable to the FSTRACE4 for FOCUS).

If you want to query the current environmental settings, you can use "?" for the trace component value. There is one setting to see the trace options turned on (SET TRACEON = ?) and another for those turned off (SET TRACEOFF = ?). With a syntax inconsistent with other settings, the WebFOCUS tracing feature is less than intuitive.

See the WebFOCUS Server Console diagnostics custom-filtering page for a full list of the trace components. Relational data adapter traces include:
  • SQLDI (like FSTRACE) - SQL physical layer (BX, BY messages)
  • SQLAGGR (like FSTRACE3) - optimization information (BR messages)
  • STMTRACE (like FSTRACE4) - SQL statements (AE, AF messages)
  • SQLCALL - exchange between physical and logical layers of the data adapter (BW messages)
For non-relational interfaces, you have:
  • ADBSIN (Adabas) - has 4 trace levels
  • IDMS - has 2 levels
  • IMS - has 4 levels
  • M204
  • Others (Nomad, Millenium, Supra/Total, CA-Datacom)
  • Proprietary traces for IBI developers (IBITROUT)

Some components do not seem to be made for HTML tracing -- their messages are not formatted properly for HTML standards. For example, NWH, NWH2, and CEH components may corrupt your HTML output. The R1H communications component may have a glitch -- it starts two WebFOCUS agents which never end. So, do not tinker with traces on the production server.

Also, instead of identifying a specific trace component, you can say "ALL" -- that is fine for turning OFF all traces, but you probably should not turn ON all traces.

In addition to the trace component, there is an optional parameter (separated by a forward slash) for the "Trace Level" as some components can display different levels of details. If you omit the trace level, you get all traces for that component. Setting the trace level to 1 will give you the top level of the details; 0 will turn it off. For the STMTRACE, you can specify the RDBMS adapter as the trace level, e.g., "STMTRACE/DB2/CLIENT"

Another optional parameter (again separated by a forward slash) is the location of the trace output. Frankly, I question the quality of this feature -- traces are okay going into the HTML comments, but do not seem to go to physical file locations properly (there is an additional setting of TRACEUSER to identify the filename). To send the trace output to the HTML in your browser, use "CLIENT" for this parameter.

Here is the syntax:
SET TRACEON = component [/tracelevel [ /filename]]
SET TRACEOFF = component [/tracelevel [ /filename]]

Here are some examples of the trace settings:
SET TRACEOFF = ALL(turn all the traces off)
SET TRACEOFF = ?(show the inactive trace settings)

SET TRACEON = MFP//CLIENT(MFD parsing; displays FG messages)
SET TRACEON = PRH//CLIENT(FEX parsing; displays AG messages)
SET TRACEON = STMTRACE//CLIENT(SQL calls; displays AE, AF messages)
SET TRACEON = SQLDI//CLIENT(SQL physical layer; displays BX, BY messages)
SET TRACEON = SQLCALL//CLIENT(exchange between SQL physical & logical layer; displays BW messages)
SET TRACEON = SQLAGGR//CLIENT(SQL optimization messages; displays BR messages)
SET TRACEON = ESSBASE//CLIENT(Essbase calls; displays CE, CF, CG, CH messages)
SET TRACEUSER = ON(turn on the user tracing)

In FOCUS, to see your generated SQL and optimization messages, you would have allocated some FSTRACE files and looked at the contents afterwards. To do something comparable in WebFOCUS, you use these commands and look in your HTML output:

Note: The XRETRIEVAL setting turns off the actual execution of the procedure, so you can see the SQL without actually running it and waiting for the answer set.

So while this WebFOCUS tracing feature may test your nerve and skill, it can be very useful to see what is going on under the covers.

Saturday, December 6, 2008

Free BI for Higher Ed

For over five years, Microstrategy has been part of an educational service from Teradata allowing universities free access to an online learning portal focused on business intelligence and data warehousing technology. In November of 2008, Microstrategy expanded this offering and announced a new program to give BI software and courseware, free of charge, for use in university curriculum.

MicroStrategy has provided its software for teaching BI courses to numerous universities in the U.S. and Europe, including University of Colorado Denver Business School, RWTH Aachen, University of Bamberg, and HTWK Leipzig.

Universities can build or augment their business intelligence course offerings with MicroStrategy and teach students about BI in a tangible way, providing hands-on experience that students can take directly into the workplace. By incorporating MicroStrategy software into a BI curriculum, students can learn valuable skills, such as designing business intelligence reports, performing data analyses, and monitoring business metrics.

Barbara Wixom, Associate Professor and Director MSMIT at the University of Virginia commented, “Companies want to hire students who can hit the ground running – they want students with an understanding of both foundational skills and contemporary tools that exist in the workplace. Having the ability to teach my students business intelligence using software like MicroStrategy is incredible. It means students experience business intelligence in a relevant, real-world way. And, it means that my students can be productive as soon as their careers begin.”

Microstrategy is making a smart move. Companies that have already implemented a BI product often struggle to find knowledgeable resources; being able to employ college students with formal BI training reduces the hiring challenge. That is good for Microstrategy as well - they are planting seeds in organizations that may not have their products yet. Plus, it is difficult to sell products that nobody knows how to use.

An Indiana company selected WebFOCUS as their BI product. However, before buying and implementing it, they wanted to hire a BI developer. Unfortunately for Information Builders, the software vendor, the would-be customer searched unsuccessfully for months trying to find a local professional with the right skills.

The two parties found themselves in an old-fashioned barter stand-off: "I'd give you that wagon wheel if you had a cow to trade fer it." Except it was: "I'll give you money for your BI software if you can introduce me to an experienced WebFOCUS developer." Yikes -- there were no available BI Hoosiers to be found.

Eventually, the organization settled on a quality person experienced with a different web-based BI tool and invested time and money training him in WebFOCUS. While Information Builders was able to make the sale, they should have also recognized the red flag being waved in their face. It is worth repeating -- it is difficult to sell products that nobody knows how to use.

If you are attending higher-education courses, ask your university if they offer business intelligence topics -- important skills needed by today's information-intensive firms. If you work for a software vendor, make sure that you are developing talent pools for your client base (sorry, offering $250-per-hour vendor consultants does not count). If you are a customer in the market for BI products, make sure one of your selection criteria is being able to easily hire technical resources.

Monday, November 10, 2008

No 'No Wake Zone' in BI

In my general blog on the BI software industry, I wrote how the mega-vendors (IBM, Microsoft, Oracle, and SAP) have consolidated the business intelligence marketspace and put serious pressure on smaller vendors such as Information Builders.

Click here to read that article.

Wednesday, October 29, 2008

Leave a Legacy (follow-up)

See my general BI blog for an article on Information Builder's legacy 4GL product, FOCUS.

Click here to read the blog.

Sunday, October 12, 2008

Leave a Legacy

If your organization uses a legacy 4GL (FOCUS, RAMIS, or NOMAD), read my general industry blog on the topic.

Click here to read the article.

Monday, September 29, 2008

Summit 2008

Have you ever wondered how other WebFOCUS clients are using their BI software?

Now you can see BI applications that were recently showcased at Information Builders' 2008 Summit annual users group in Nashville, Tennessee, by Greater Cincinnati organizations such as Henny Penny, Nationwide Insurance, TriHealth, and The Hillman Group, and Omnicare. Just click on the links in the session titles below to download the associated Powerpoint presentation.

If you would like to discuss how your company can utilize WebFOCUS to its fullest, just let me know.

Henny Penny Provides Enterprise BI to a Global Operation
Doug Lautzenheiser, Partner Intelligence
Don Garland, Henny Penny Corporation

The next time you eat french fries at the quick-service restaurant or get rotisserie chicken from the grocery store, think of Henny Penny, the Ohio-based manufacturer that probably supplied the equipment for your meal. See how this 50-year old privately-held company uses Information Builders products to support their global operations across the Americas, Europe, Middle East, and Asia, and communicate with distributors in over a hundred countries. Henny Penny will describe how WebFOCUS enables online access and automated information delivery, as well as how iWay Software automatically processes e-mailed orders. You will hear how a WebFOCUS dashboard provides employees and external partners around the world with real-time access to ERP data. Henny Penny's environment consists of Windows and iSeries platforms, with JDEdwards ERP.

This Time It's Personal: BI's Influence on 50,000 Students
William Cooper, Nationwide

Learn how WebFOCUS is influencing 50,000 students in the Columbus City School District. WebFOCUS business intelligence helped develop a series of cascading structures as well as a dashboard with drill-downs to the building, grade, subject, student, and curriculum levels, which assists with formative intervention to enhance academic achievement.
Enterprise Reporting Solution With MRE Dashboard, Metadata-Driven SROs and ReportCaster
Mufaddal Frosh, TriHealth
Deborah Heile, TriHealth

The TriHealth Data Warehouse team has developed a platform to deliver parameter-driven standard reports, custom reports for specific users, ad hoc functionality for analysts, and event-driven reports using BID, metadata-driven reporting objects and ReportCaster.
BI in the Real World
Kirk Townsley, The Hillman Group
Al Kolb, The Hillman Group

Hillman Group will demonstrate how they manage operational and strategic objectives using WebFOCUS.
Dynamically Loop Through Database Connections With DataMigrator
Lori Pieper, Information Builders
Kathy Getz, Omnicare

Omnicare has the same set of tables in many different databases. The table names and column names are the same, but the database is different. Learn how we wrote one data flow and looped through 70 databases, versus writing a data flow for each database. We'll also touch on how to send parameters to the data flow so the selection criteria is also specific to the database to which we are connecting.

Monday, September 15, 2008

Online TechSupport

As a WebFOCUS customer, you have the entire Information Builders library at your fingertips for free. You need only register on their TechSupport website.

If you have not yet done that, go to the New Users page and click on their “Sign Up!” button at the top left corner. To validate yourself as a customer, you need to enter your IB site code (an assigned value like xxxx.01). Fill out the rest of the form and give yourself a unique userid --you are ready to go!

Once logged in, check out the “Publications” link at the top middle of the screen. Two important options on that menu are “Search for Documentation” and “Search for Topics”. From the documentation option, you can find all of the WebFOCUS manuals in PDF format, available for free downloading. It you prefer, you can purchase hardcopy versions. If you do not know what manual you need, you can use the “Search for Topics” screen to do keyword searches for the right material.

While at the TechSupport site, you may also want to look over some of the other material, such as IB’s education offerings or information on how other clients are using WebFOCUS (be sure to read In the News on a regular basis, as well).

It is important to update your Site Profile so that IB has details on your WebFOCUS architecture. The IB Help Desk can then review this information when researching any open cases that you submit, which will hopefully speed up the process.

If you have any questions, just let me know.

Tuesday, September 9, 2008

Managed Reporting Environment's Repository

By default, the WebFOCUS Managed Reporting Environment (MRE) uses a file-based repository system to keep track of users, security groups, and reporting domains. WebFOCUS stores this information in simple HTML control files in the "basedir" of your WebFOCUS Client environment (typically stored on the web tier):
  • MRREPOS.HTM --Points to the user, the role, and individual domain control files
  • USER.HTM -- Contains user information and the groups to which they belong
  • ROLE.HTM -- Contains users and the roles they can perform within MRE
  • Username.HTM -- MRE users have their own control file, with pointers to the user directory, reports, deferred reports and output, and report domains

Under the basedir directory are the individual domain folders, with each folder containing its own domain control file:
  • Domainname.HTM -- Points to individual domain folders (standard report groups, reporting objects, other files, help files, and run links) and provides a listing of each folder's contents.
While adequate for many uses, there are some problems with a repository infrastructure that utilizes simple text files.

First, the files are prone to corruption should the system encounter an issue such as a full hard drive or a power outage. For example, if an individual domain control file is corrupted, it will appear to the MRE user that all of the contents of that domain have disappeared. In reality, WebFOCUS is just unable to present that information to the user as it has lost the association between the domain and its contents. Likewise, if the main repository control file is damaged, the entire MRE environment can become unavailable as the various programs are now unable to access necessary information. The Java App Server might begin to throw null pointer exceptions and fail as it tries to access the files. When using the file-based repository, therefore, it is important to establish a good backup and restore process.

A second problem with the file-based repository is that it can become slow when a large number of MRE users are in the system. Because each MRE user performs file I/Os on the same three files in the basedir and shares the domain file with other domain users, this file access can become a bottleneck for large user environments.

Also, special characters stored in the files can violate the HTML rules and make the file unreadable. For example, if you add a userid with special characters (such as a dash), this can corrupt the file and make it so that no users can log into MRE.

Understanding these issues, Information Builders offers an alternative. Instead of using the file system for storing the information, MRE can be configured to access a relational database such as DB2, SQL Server, Oracle, Informix, Sybase, or MySQL (a good practice would be to use the same database as that for the Report Caster repository). The MRE user administration interface is used regardless of your choice of internal files or external database repositories.

An external RDBMS repository has several advantages in that it provides protection against file corruption, should perform faster and scale better than a file-based system, and is probably already part of a formal backup and recovery process. The change to the underlying repository is transparent to the MRE administrator(s).

Of course, you have security options other than just picking between internal file-based and external database repositories. These are simply two out-of-the-box features available for easily managing the user authentication (userids and passwords) and entitlement (their roles and security groups) should you not have other security packages in place or choose not to take advantage of them.

While the MRE file-based repository might meet your needs today, you should consider the alternatives as you roll out more WebFOCUS applications.

2013 note: Everything changes with WebFOCUS 8. With that new architecture, Information Builders has completely revamped the MRE repository into a robust relational content repository. If you need information about this new structure, contact me. 

Thursday, August 21, 2008


This is my personal blog to share my thoughts on, knowledge of, and experience with WebFOCUS, one of the leading business intelligence products in the market. Information Builders, the maker of WebFOCUS, is one of the industry's oldest and largest privately-held software vendors. Almost all Fortune 500 firms and US government agencies use IB's products.

On a Friday the 13th after 13 years as an employee, I left IB to lead a professional services organization specializing in business intelligence software. It has been over a year now, and I am still extremely happy about my career decision.

I would love to talk to you about the industry and WebFOCUS in particular. Feel free to connect up with me on LinkedIn.

Be sure to also see my more general industry blog on Business Intelligence Software.

About Me

My photo

I am a project-based software consultant, specializing in automating transitions from legacy reporting applications into modern BI/Analytics to leverage Social, Cloud, Mobile, Big Data, Visualizations, and Predictive Analytics using Information Builders' WebFOCUS. Based on scores of successful engagements, I have assembled proven Best Practice methodologies, software tools, and templates.

I have been blessed to work with innovators from firms such as: Ford, FedEx, Procter & Gamble, Nationwide, The Wendy's Company, The Kroger Co., JPMorgan Chase, MasterCard, Bank of America Merrill Lynch, Siemens, American Express, and others.

I was educated at Valparaiso University and the University of Cincinnati, where I graduated summa cum laude. In 1990, I joined Information Builders and for over a dozen years served in regional pre- and post-sales technical leadership roles. Also, for several years I led the US technical services teams within Cincom Systems' ERP software product group and the Midwest custom software services arm of Xerox.

Since 2007, I have provided enterprise BI services such as: strategic advice; architecture, design, and software application development of intelligence systems (interactive dashboards and mobile); data warehousing; and automated modernization of legacy reporting. My experience with BI products include WebFOCUS (vendor certified expert), R, SAP Business Objects (WebI, Crystal Reports), Tableau, and others.