white paper db2 web query as a replacement for query/400

White Paper DB2 Web Query as a replacement for Query/400

The new IBM DB2 Web Query product is being hailed as a brave new world of business intelligence reporting on System i. But is it enough?

by Alan Jordan Chief Technology Officer Coglin Mill June 2008

2

DB2 Web Query as a Replacement for Query/400 © Copyright Coglin Mill, 2008 All rights reserved.

Introduction IBM™ DB2 Web Query for System i was announced in April 2007 as a replacement for the Query/400 product. It was released in September 2007, and is now available to all System i customers at V5R4 level and above. Its announcement has generated an enormous amount of interest in the System i community. In fact, the level of interest significantly exceeds IBM’s expectations. Why? It is undoubtedly true the ageing Query/400 product was overdue for replacement, but in truth, several other products from 3rd party vendors have been available for many years. Maybe it is the IBM name on the product, or maybe the expectation that it is ‘free’ or available at a very low cost. Regardless, it is apparent that many System i shops are planning to implement DB2 Web Query as a replacement for Query/400 in the very near future. It is also becoming apparent, from direct and anecdotal reports by early adopters, that DB2 Web Query by itself is not the universal panacea to existing problems in a Query/400 based reporting environment. This suggests there is some level of misconception regarding DB2 Web Query’s capabilities, or more likely that there is a general misconception regarding the required components of a successful business intelligence architecture. Informed decision making in any organization is dependent on access to reliable, accurate and timely information. Front-end tools, such as DB2 Web Query are only the delivery vehicle for this information, in much the same way that faucets deliver water to your kitchen sink and bathroom. Replacing a worn out faucet with a shiny new one will look great, but does nothing to address water quality, leaky pipes or a broken hot water system. Of course you wouldn’t expect a new faucet to fix your plumbing problems – but many organizations fail to recognize that their reporting problems are mostly associated with “plumbing” issues, and the fix needs to be applied before the data (water) gets to the reporting tool (faucet). This white paper explores:

Reporting challenges facing IT Managers Typical reporting problems How does DB2 Web Query address these issues Solving these problems - the unstructured approach Attributes of a successful business intelligence architecture The RODIN Solution Using RODIN & DB2 Web Query together

3


Reporting challenges facing IT managers (in System i organizations) In today’s modern business environment, IT managers in System i centric organizations are facing a significant number of reporting challenges compared to the situation they faced ten or more years ago. Let’s explore the past to fully understand the challenges this past decade has brought with it… In 1997 a typical organization using an AS/400 to run its business had fairly simple and easy to manage reporting requirements. All or most of its business applications might run on the same AS/400. In many cases all critical data came from the same ERP application - such as JDE or BPCS. Management reporting was fairly straightforward with fixed, paper based reports being the norm, whether provided by the application itself, or developed using Query/400. PC based graphical reporting tools were available, but not widely adopted in the AS/400 community. Since then, we have seen explosive growth of the internet and ecommerce, new technologies have emerged and new paradigms have come along (and in some cases, quietly disappeared a few years later). Additionally, we have seen unprecedented merger and acquisition activity in the marketplace. The mobility of the IT workforce brings new people into our organizations, bringing with them new skills and ideas. As a result, in many System i data centers, PCs, Unix and Linux boxes running Oracle, Microsoft SQL Server, MySQL and other databases sit alongside System i boxes. Times have changed. If we compare the System i marketplace to organizations using other platforms and databases, we see an interesting, potentially disturbing trend. The System i community as a whole, has not been an aggressive adopter of business intelligence technologies, and as such could be seen to lag behind their competitors on other platforms. Certainly there are many larger System i centric organizations that do have BI implementations – but clearly a significant number of medium sized organizations do not, as well as a majority of smaller businesses. When analyzing the reasons for this slow uptake, we see the very success of the platform is (at least in part,) responsible. Our integrated database in conjunction with the readily available Query/400 tool made it very easy for us to design our own reports and queries. RPG programmers were always available to write the more difficult reports. We have been spoiled because we have the very best available report programming language ever created! (Just in case you didn’t know: RPG stands for Report Program Generator). Contrast this to our colleagues struggling to provide the same capabilities on other platforms. Until the advent of the SQL language and modern BI reporting tools, these companies had fewer options. They struggled with data in text files or data in multiple databases, and with programming languages that were not particularly well suited to generating printed reports. Is it any wonder these organizations embraced BI tools and technologies when they became available? Now let us fast-forward to today’s business environment. While there are still many System i shops that are relatively insulated, with all of their data coming from System i applications, this is no longer the norm. Many IT managers are facing an enormous challenge in being able to provide the information that their business users are demanding. Data is spread across multiple systems, multiple applications and different databases. Added to this, many C-level execs are asking for dashboards to provide a high level view of the business with pictures, charts, color coded indicators and drill-down capabilities etc.

4


We have more data than ever before. Our business community has much higher expectations than ever before. But IT departments are seeing budget cuts and are expected to produce more with fewer resources. While some challenges are unrelated to reporting, a significant number of organizations cite access to data and reporting as one of their major issues. Increasingly, we are becoming data rich and information poor Good old Query/400 is no longer getting the job done. Now IBM is promising to solve our reporting issues with a new, modern web-based reporting tool – DB2 Web Query. Is it up to the task?

5


Typical reporting problems Let us explore some of the common issues plaguing our reporting initiatives. Some of these are found in almost every organization, regardless of size. Other issues may not apply to your specific environment, but are nevertheless common, and you could well encounter them in the future. Data Quality This is arguably the biggest issue of them all – however data quality is given little attention in many shops. While there are many reasons for this, there is probably one major underlying factor to this lack of attention: “I don’t have any data quality issues”. While it is possible this is true, based on the experiences of every business intelligence consultant and data analyst it is highly unlikely. Just because you don’t see significant data quality issues, it doesn’t mean there aren’t any. You just haven’t found them yet. Operational applications (e.g. ERP Systems) generate enormous amounts of data in many tables and columns. When bugs (associated with data issues) are encountered in the daily use of the application, the problem gets attention, and fixed. However, there are many instances when data is generated by a process, and not touched again by the application, or if touched, the error is such that it does not result in a recognizable problem. These data errors do not get discovered and corrected. So when you hear someone in your organization say “we don’t have any data problems”, be very skeptical! You have a problem if you are using that suspect data for business intelligence reporting. If you have done nothing to validate critical pieces of data (information) how confident can you be in the reports you are delivering or consuming? Whose neck are you putting at risk? Data Complexity The data contained in an operational database can be quite difficult to interpret. There are a number of reasons for this:

Data (information) in an operational database is designed to be accessed by applications – by the software that it was created to support. Therefore, the design of the database is based on principles and best practices that support this. Unfortunately, these design principles are often quite unfriendly when we try to access the data for business intelligence purposes:

o The principle of 3rd Normal Form calls for avoidance of redundancy, resulting in a

complex database with many more tables than you would think necessary. From your perspective, this means having to join many tables for just a simple report.

o Minimize disk usage. Disk storage used to be quite costly; therefore every effort was made to reduce the number of bytes used. Instead of meaningful values, single character codes are commonly used. The software understands them, and translates them to recognizable information – but do you know what they all mean?

There is usually no user-manual describing the structure and meaning of the database. Again,

this is because the database was not designed for human access. The software using it does

6


not need a user manual. Not only is it complex, but it is not documented!

In all but the very best database designs, you will encounter all sorts of inconsistencies. This is usually because the application has grown over time and been developed by many different programmers:

o The same piece of data may have a quite different name and description in different

tables. o Redundant columns may have been re-used for a different purpose, and therefore

have totally incorrect names and descriptions. o Meaningful information may be embedded inside another column (e.g. the first

character of the sales representative code identifies the sales region). Disparate Data Data coming from just one application may be difficult enough to understand and use – but that pales in comparison to the task of joining data across applications. If those applications reside on different systems and use different database types, it gets even harder. What are the odds that similar, related pieces of information are stored in the same format? Examining some common issues related to multiple applications:

If we are dealing with Customers (as most of us do), and we have two or more different applications, with overlapping function, it’s also likely we also have more than one customer database. Some customers will only exist in one place, but it is probable some will be in both databases (or all 3, or 4 of them). Unfortunately, it is highly unlikely that they’ll have the same customer number (or even that the customer name will be identical).

Example: Customer tables in different applications/databases What about products, suppliers, stores and sales reps…..

Assuming the data allows me to join tables across applications, how do I achieve this if the

applications are on different servers? And in different database types?

Each server will likely have different security; database availability may be different and many other issues will make this a nightmare.

In the Query/400 world, there is no simple resolution to these issues.

CUSTID CUSTNAMAA234 Julie JohnsonAA235 Fred HunterAB670 John SmithBD309 Alan Johnson

Customer File - CanadaCUSTNO CUSTNAME

1001 John Smith1002 Mary Jones1003 Chris Anderson1004 David Perry

Customer File - US

7


Dates Dates can be so much of an issue that we’ve given them their own section. In almost all legacy System i (e.g. AS/400 era) databases, dates are stored in numeric columns. Unfortunately there are many different potential formats: yyyymmdd, mmddyyyy, cyymmdd etc. If you are lucky, all dates in an application will be in the same format – but that may not be the case. At least today, your dates are probably in a Y2K compliant format! Dates are important since they comprise probably the single most important dimension as far as business intelligence reporting. We almost always want to know when something happened, or maybe to group and summarize information based on a particular timeframe. As such, we need to be easily able to determine the year or month from a date – or the week, or even the quarter. Or maybe we want to calculate the number of days (or months) between two dates. These requirements are not easily accomplished when your dates are just a number in a numeric column! Plus, many applications allow dates of zero, or set them to all 9s to indicate some unknown future date. These are not valid dates and must be specially handled. Performance When reporting directly from operational data, we must use whatever source of data is available to us containing the information we need. For transaction data that usually means the most detailed level stored in the database (e.g. invoice line item level). If you need a summary report by Division and Brand, and then another one by Customer Group and Region, your only option is to process all line items (matching the selection criteria) and perform the aggregation – and then do it again for the second report. As we’ve recognized above, we will almost certainly need to join this detail level data to several other tables. This often leads to significant performance problems. If these reports are run on an ad-hoc basis during the day, they not only take a while to run, but can also affect everyone else using the system.

8


How does DB2 Web Query address these issues? We’ve identified some common issues you may have encountered when using Query/400 for business intelligence reporting. Now we’ll examine how (if at all) DB2 Web Query can solve these problems. Data Quality Not addressed at all. This is the case with virtually all query and reporting tools. DB2 Web Query, Showcase, NGS, Cognos, Brio Query etc. have no coherent mechanisms to manage data quality. The old adage applies: “garbage in, garbage out”. Data Complexity The nature of this issue – the fact that the data is complex, does not change simply because you are using a new query tool. The effort required to investigate, discover and understand the data and all of its associate rules and idiosyncrasies remains the same. However, DB2 Web Query does give you the capability to add descriptive comments at the table and column level. Disparate Data The base version of DB2 Web Query has no support for access to data on other platforms. Dates DB2 Web Query does have limited support for numeric dates. However it requires manual handling of every date column and custom programming within DB2 Web Query - a very labor intensive process, and possibly outside the capabilities of the average user. Performance Reports written using DB2 Web Query may perform slightly better than an equivalent Query/400 report. This is because Query/400 always uses the ‘old’ Classic Query Engine (CQE), whereas DB2 Web Query will, in most cases take advantage of the newer SQL Query Engine (SQE), which can perform better for many types of queries. Certain types of query however, may still use the CQE, in which case performance will be comparable. However, the underlying reason for most performance issues is not the query engine – but the structure of the data being accessed, and the fact that you are reporting directly against your operational database. DB2 Web Query cannot address this. Of course, this is not a definitive list of the issues that can be encountered. However, the vast majority of issues fall broadly into one of the above categories, and you can expect that very few data related issues will be resolved by DB2 Web Query. The truth is that you should NOT expect a query/reporting tool to address them at all.

9


Solving these problems – the unstructured approach We’ve explored some of the common problems associated with business intelligence reporting. Organizations are unlikely to live with these restrictions and limitations, and instead will develop solutions. Let us look at poorly designed and implemented solutions to these reporting problems, and ensure we understand the pitfalls to avoid. You’ll probably recognize some or all of these symptoms within your own organization: Ad-hoc solutions Each time an issue is encountered in a specific report or query, it is addressed as a stand-alone issue and resolved using an approach chosen by the developer (or business user) assigned to the task. The individual challenges faced and the chosen solutions are quite varied, but we can look at some examples:

Create an “extract” table or “summary” table to stage and format the data required for the query and use an RPG program to load it.

Use several queries, each outputting to a work file, and then create a final query over those work files to get to the required result.

Use Client access to download data to Excel. Use Excel to further manipulate and merge various sets of data to get to the required end result.

Multiple versions of the truth How often have you seen related reports disagree with each other? Have you ever needed to spend hours or days every month ‘working the numbers’ until you get them to balance? It’s a very common scenario, and has several potential causes:

Data errors can lead to incorrect report output. Failure to understand the source data, resulting in incorrect calculations or processing logic. Different definitions of the (supposedly) same value. Net sales is a classic example of this.

Because of the nature of this piece of information, it usually needs to be calculated – and different people have conflicting ideas of what that calculation should be.

Because of the ad-hoc approaches used to solve individual reporting requirements, each can have any or all of these underlying reasons for producing incorrect results. Duplicated effort In many cases you will need different views of the same information. While the specific requirements may be somewhat different in each case, the underlying data values are very similar -just grouped, sorted, filtered and presented in different ways. When these individual issues are addressed independently, the same processing steps (extract data, perform calculations etc) are repeated each time. The developer tasked with each new project probably needs to re-discover the data sources and business rules. Not only is this totally inefficient, but is also a major contributor to the multiple versions of the truth phenomenon. Lack of Documentation In a reporting environment relying on printed reports or static on-line reports, your business users simply need to know what is available. This is not too hard to communicate. However, when business users are given access to query tools or OLAP tools allowing them to ‘explore’ data through the use of the tool, you immediately have a problem. You have turned business users into pseudo-IT people,

10


who are now empowered to create their own views of the data. But unless you also provide them with a road-map so that they know what data (ie tables) is available to them, and what it means they are going to give it up as too hard, or struggle with their incomplete knowledge and make mistakes. We are again faced with a multiple versions of the truth scenarios, and also potentially the ad-hoc approach issue. This scenario is found in many organizations, but quite often it is not recognized as an issue. The various elements (extracts, summary files, reports etc) grow over many years, with new ad-hoc components being added as needs dictate. The following diagram shows the end result in a typical organization.

PURCHASING SALES FINANCIALS

PurchasingExtract

BrandSales

Summary

RegionSales

Summary

ProfitabilityExtract

GLSummary

(Excel)

~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~

0

20

40

60

80

100

1st Qtr 2nd Qtr 3rd Qtr 4th Qtr

No one truststhis report

Mary wrote thisextract. She left last

year and no-oneknows how it works

John spends 5 daysevery month generatingthis and massaging thenumbers until he thinks

it is correct

These reports don’tbalance with each other.

This report is used for FinancialReporting.. but no one has yet realizedthat it is incorrect. The auditors will be

the first to discover the problem

The Net Salescalculation in this

extract is different toMary’s

~~~~~~~~~~~~~~~~~~~~~~~~~~~

No one everuses this one

Joe downloads thismanually via Client Access

every Monday.. except when he’s on

vacation or out with the flu!

Clearly, when we look at the overall situation, there is a problem. The question is: what is the solution?

11


Attributes of a successful Business Intelligence architecture By now you probably recognize that a data warehouse may be the answer. But many organizations, for one reason or another, shy away from this approach.

“We don’t have a problem”. In most cases, this really means, I don’t want to have to face up to it. (The ostrich syndrome).

“We’re too small to get into Data Warehousing. We don’t have the resources or the skills to do that”.

“I’ve heard Data Warehouse projects often fail – I’m not going to risk that”. It is true that to build an Enterprise Data Warehouse that is everything-to-everybody can be a significant task. It is also true several early high-profile Data Warehouse projects failed. These two facts are very much related: If you set out to build the be-all and end-all of data warehouses in one hit, you will probably never get it completed, and therefore will fail. The project will lose support as time drags on, and by the time you have something to deliver, it may no longer be relevant to your growing and changing business. What we have learned over the past decade is a set of best practices that, if followed, will eliminate the causes of failure. More importantly, these best practices can be implemented in a project of any scale. In other words – you can (and should) build and deliver small pieces of an overall ‘grand plan’, rather than trying to build it all at once. Furthermore, there are tools available today that will vastly reduce the overall effort involved. The key is to plan, build and deliver around an architected business intelligence framework. So what does that mean? Let’s list some key attributes of the overall project:

A standardized approach. Guidelines, procedures and principles are developed to ensure consistency. For example, develop naming conventions for tables and columns.

Think big, develop small. Implement different subject areas, or departmental requirements

one at a time, delivering each completed area out to your business community as completed. Ensure each part builds on the grand plan, using the same standardized approach.

Provide documentation. End-user documentation is critical to success. You must provide a

road-map so that your business users can understand the tables and reports you deliver.

Manage data quality. Poor data quality WILL sink the project.

A full discussion on architecture and design of a data warehouse is beyond the scope of this White Paper – but there are two well known, mature concepts that are commonly followed: The Corporate Information Factory, developed by Bill Inmon and Dr. Claudia Imhoff and the concept of Dimensional Modeling, developed by Ralph Kimball. There are many similarities between these two design philosophies, and both are well proven.

12


Depending on your needs, you may not need an extensive data warehouse. Maybe several data marts will suffice. It does not need to be hugely complex. The important thing is to have a structured approach and an overall plan. Whatever design approach you embrace you will need to:

Create the tables and indexes you have designed

Develop ETL (Extract, Transform and Load) processes to load these tables from your sources of data. Data quality management is a key aspect of ETL

Deliver documentation in the form of metadata

Manage all of this to ensure consistency

This is starting to sound like a lot of work!

13


The RODIN solution

With RODIN it is NOT a lot of work! RODIN is the most powerful ETL solution available for IBM System i and iSeries business systems. Comprehensive data integration, cleansing and transformation capabilities enable you to easily design and build complex applications in a minimum amount of time. Integrate data from virtually any source including local and remote System i tables, as well as relational databases such as DB2 UDB, Oracle, and SQL server. Features include:

Visual Development Environment RODIN developers enjoy a highly functional, visual IDE for building and managing tables and ETL processes. The powerful visual development capabilities dramatically increase productivity and enable programmers and non-programmers to develop complex applications quickly and easily.

Extensive Metadata Repository Metadata is one of the keys to success in a data warehouse or data mart environment. It provides end-users with a road map to the data while empowering people. RODIN stores extensive technical, business and administrative metadata for every object, at many levels. Free-form text entry provides unlimited descriptive text. Publish the metadata as HTML for end user access, or export it to other tools (including DB2 Web Query).

Simplified Database Management RODIN takes the hard work out of creating and managing tables and indexes. Build your database to any design: relational or star-schema, normalized or de-normalized. Integrated tools support bulk changes, reverse engineering of existing databases, as well as performing impact analysis and many other database tasks.

Comprehensive Error Management RODIN provides extensive functionality to trap, report, correct and re-process bad data, whether detected automatically, or based on your own business rules. Error reports are generated automatically, providing full details of the errors. The bad data is staged in an error suspense table, which, after correction can be easily re-processed.

14


Unsurpassed Performance & Flexibility With RODIN you can easily load multiple target tables in a single ETL process, both inserts and updates. Parallel processing can be used to reduce load time for large data sets. Command-driven processes are easily scheduled or included with other processes. Published benchmarks prove RODIN’s outstanding load performance and scalability.

Complete development/deployment environment You can install multiple RODIN environments on your system to support Development, Testing and Production. Definitions can be easily migrated from one environment to another. Change management, versioning and powerful comparison tools combine to make this a highly functional and productive environment.

Security It is extremely important to secure the tables in a data warehouse – both from unauthorized access to the data as well as unauthorized changes to the definitions. RODIN is fully integrated with System i security and allows complete customization to suit your own particular needs.

Auditing Sarbanes-Oxley requires public companies to provide comprehensive audit trails to show the origin and lineage of any information used for financial reporting – and this often comes from a data warehouse. However even for private organizations, there are tremendous benefits in being able to provide this same information. RODIN has fully automatic auditing of every ETL process, and the comprehensive metadata provides complete source to target data lineage, including all business rules and transformations.

Ease of Use This is where RODIN provides enormous benefit – especially to smaller organizations with limited resources. You do not need to be a programmer or DBA to use RODIN. It is designed from the outset to be very powerful, yet extremely easy to use. You can be up and running within a day of installation and delivering real value within a very short timeframe. RODIN lays the foundation for success. It enforces consistency and provides (often totally automatically) most of the factors necessary for a successful business intelligence implementation.

15


RODIN & DB2 Web Query – Perfect Partners If you are already using DB2 Web Query, or have kicked the tires, you will know that to be able to use a table for a DB2 Web Query report, you need to create some metadata in the form of a Synonym. The steps involved in creating a Synonym are not that difficult, however the advanced features that Synonyms support can be very time consuming to implement and manage. Let us take a look at some of the requirements and issues surrounding Synonyms: Multiple instances of the same table System i easily supports many instances of the same table name (in different libraries). When creating a Synonym, you point to a specific library/table, but at run time do you want DB2 Web Query to use the qualified table name, or the library list to determine the instance of the table to use? You need to remember to specify this every time you create a Synonym. The Synonym name is normally the same as the table name. However, if you need to access several versions of the same table, you need to apply a prefix, or some other means to create a unique Synonym name. You need to remember to do this every time you create a Synonym. Handling File changes Whenever a file change occurs, you need to remember to go back into the Developer Workbench and ‘refresh’ the Synonym. Missing information When using the CLI Adapter to create Synonyms (IBM’s recommended approach), two important pieces of information are not included:

If the column has an SQL name, the 10 character system name of the column is not included in the Synonym. This means that the end-user MUST be familiar with the SQL column names, which in some cases may not be the case.

The column headings are included (if present) but the 50 character text description is not. Enhancing Synonyms with text descriptions When using the Synonym Editor (part of the Developer Workbench, a chargeable feature), you can add up to 2,000 bytes of descriptive text at the table level and at the column level. This text is then available as pop-up text in reports based on this table. Unfortunately, the Synonym Editor is not very user friendly in this area. The text entry field is a single line entry box that can, at the most display only about 100 characters. Entering and maintaining any text longer than that will be very difficult. Also be careful trying to copy and paste text into this field: any line feeds in the copied text will cause corruption, and the text will be broken across multiple unrelated entry fields in the Editor.

Enabling support for legacy dates To allow DB2 Web Query to recognize a legacy date column as a real date, you need to use the Synonym Editor to create a new virtual column, and then use the Expression Editor to specify the from and to formats. These several manual steps are required for every date column, which will become a tedious chore.

16


Default joins for related tables Often a set of related tables need to be joined to produce a report. If several different reports need to be produced from the same information you will repeat these joins many times. Through the Developer Workbench, you can define a Synonym based on these related tables. The report editor then sees this as one table, with multiple segments. A change to any of the tables again involves a refresh requiring you to remember to refresh not only the individual table Synonym, but also any join Synonyms based on the table. So DB2 Web Query has some great features to make our reporting easier – but there are a few shortcomings and the manual management of Synonyms can be burdensome. RODIN’s integration with DB2 Web Query significantly simplifies this, and in most cases totally removes the need to manage Synonyms at all:

Synonyms for RODIN tables are created automatically. Parameters allow you to specify whether to include a prefix for all tables from this RODIN environment, and whether to qualify the table with the library name, or to use the library list at run time.

Whenever a RODIN table is modified, the DB2 Web Query Synonym is automatically

refreshed. You do not need to remember to do this.

The 10 character system name and 50 character text descriptions are included.

RODIN supports unlimited free format text at both table and column level. The RODIN text editor is significantly more user friendly, but the main advantage here is the data warehouse developer is likely more familiar with the table and columns and can enter more complete, meaningful information than possibly the person designing a report in DB2 Web Query.

RODIN fully supports true date, time and timestamp columns. When mapping legacy date

columns into RODIN tables, date (or time) conversion occurs automatically. Therefore your legacy date problem completely goes away, and there is no need to use the Synonym Editor to create virtual columns and create date conversion routines. Furthermore, RODIN automatically takes advantage of a feature of DB2 Web Query to ‘decompose’ dates. This feature defines Year, Month, Day and even Quarter virtual columns for each date, significantly simplifying and enhancing reporting by allowing you to sort and select by any of these components of a date.

17


RODIN contains modeling information in the form of Subject Areas. A Subject Area is a set of

related tables – most commonly representing a Star Schema. Each Subject Area has all of the logical table joins defined, and also allows unlimited descriptive text describing purpose and use. These subject areas are automatically created as Synonyms in DB2 Web Query, eliminating the need to define the joins a second time. Once again, if any table in the Subject Area changes, the Synonym is automatically refreshed.

RODIN Subject Area

DB2 Web Query Synonym

18


Summary and Conclusion The new DB2 Web Query product is poised to change the face of reporting on the System i platform. However, the evidence is clear – it will not by itself solve any of the issues limiting your reporting capabilities and frustrating your business community. Yes, you can deliver reports via the web. Yes, you can display information as charts. Yes, you can create dashboards etc. But if you migrate to DB2 Web Query without addressing the issues outlined in this paper, you will simply put a pretty new face on your problems. The phrase “lipstick on a pig” is appropriate here. This is a huge opportunity for organizations planning to implement DB2 Web Query. You have excited your business community by promising a brave new world of reporting – now you need to deliver. If the term business intelligence was not commonly used in your organization before, it certainly should be now. You have an opportunity to revolutionize both operational and management reporting in your organization. But you probably only have one chance to get it right. If you fail to implement the framework for success, the lipstick will wear off very quickly. The reports still will not balance with each other. You still won’t be able to combine data from disparate systems on the same report. The numbers could still be wrong. What chance will you have to take a second shot at it? Will you be able to go back to senior management, admit your errors and ask for the time and resources to fix it? Maybe. RODIN is a proven solution, used for many years by large organizations, such as HSBC Bank, Office Depot (Europe), Discovery Channel Stores, Fiserv CBS, Wells Fargo and other well known companies. They, like many other organizations have recognized that successful business intelligence requires an architected approach to data quality management, ETL, metadata etc, and the right tool is critical to success. RODIN and DB2 Web Query together, equal that success. It is very likely your success will exceed your expectations. Data warehouse or data mart implementations commonly provide a huge ROI. Various studies have shown the typical ROI (after 3 years) averages over 400%, with extreme examples in excess of 1,000%. Your mileage may vary – but with the right approach and the right tools (RODIN & DB2 Web Query) you can experience the same results, regardless of your organization’s size.

19


About the Author Alan Jordan is a Senior Vice President and CTO of Coglin Mill. He joined the Company in 1988 and has been involved in the development of RODIN since 1995. He is the senior Product Architect, oversees customer support and education and also undertakes short-term consulting engagements with RODIN customers. A native of Australia, he has been living and working in Rochester, MN since 1998. He is a regular speaker on the role of ETL in business intelligence at COMMON and other venues. Alan may be contacted via email at [email protected] About Coglin Mill Coglin Mill is a privately held Australian software company that has been developing software for IBM midrange systems since 1985. Early software products included a major Distribution and Financials package designed specifically for organizations with complex requirements, and an advanced set of utilities to help manage the development and production environments in mainly large System/38 installations. Today, the company focuses solely on its very successful RODIN Data Asset Management software suite, which is the leading solution for building and managing data warehouse and data mart environments on System i. Coglin Mill is an Advanced member of IBM’s PartnerWorld program. For more information Visit us on the web at www.ThinkRodin.com or For all inquiries, worldwide:

Call: +1 507 282 4151

Email [email protected]

white paper db2 web query as a replacement for query/400

Documents