Prologue Overview How to implement a DSS ? Tools and Database selection Data Warehouse Conclusion

DataWarehousing The competitive advantage within reach !

PROLOGUE

Research has shown that those who buy books related to their business would rather read them at one sitting, maybe during a plane trip o during their spare time. On the other hand these people feel that the time invested in reading is only worth if they can get ideas or techniques to improve their professional approach or solve a current problem they may be facing at the workplace.

The present White Paper deals with both concepts. It’s the result of a serious investigation, training, experience and thourough debate which allows us to offer information without taking up much of your valuable time. -

Top.-

OVERVIEW

You may have already faced one of the following situations.... An office worker is searching for information in old listings, a system operator is processing historical backup information late at night, a system analyst is under pressure programming unforeseen processes and a system manager is giving explanations about the need for time when databases are not ready and daily processes cannot be discontinued.

Business executives need information to make business decisions, however they don’t need it for tomorrow or for the following afternoon, they need it right now!!!

This scene can be experienced daily at any highly competitive company where quick and accurate information is missing and this situation may simply lead to either business failure or success.

The solution to support decision processes bears a name: DataWarehouse (DW) and Decision Support System (DSS)

Top.-

HOW TO IMPLEMENT A DSS ?

In our opinion the success of DSS is imposssible unless an adequate, serious methodology is adopted. The Work Team should get strong political support as well as enjoy the commitment of at least one end user having a deep vision of the business and a constant interaction with top corporate executives.-

The adoption of such a methodology leading to a successful implementation also applies to any other implementation, however, in this particular case, methodology becomes a critical issue since only decision makers have the knowledge and necessary certainty to be able to clearly define the reason of the business, the indicators to be monitored, the technical difficulties to be evaluated considering the several data sources to be integrated, the identification of the same, the quick adaptation of DSS to strategic changes, etc.-

Because implementation stages are very short, the company will quickly benefit from this methodology and final results will be soon afterwards observed after a long period of uncertainty and investments. The business evolution and constant changes make any information project longer than 6/9 months virtually impossible. Consequently, the strict control on the methodology adopted and the work schedule is extremely important If all the precautions are taken we may belong to the exiguous 16% of those who have reached success

Short time ago, investments in information systems were basically evaluated on the basis of the cost reduction involved. From now on, systems should be evaluated on the basis of the benefits they may bring. ADSS is the clearest example of this new economic/financial trend for System evaluation.-

Top.-

TOOLS AND DATABASES SELECTION

The selection of open tools when implementing a DSS and a proven database engine for the DataWarehouse represent a very important step which will allow you to:

• Provide a prompt response to Executive decision makers.
• Observe the technical evolution and market trends at a reasonable expense.-

Presently, there are some marketing driven "solutions" that conceive that the only effective way lies on the adoption of new technology.

Worldwide, the market of Decision Support is and will keep on growing at a very quick pace during the next 5 years. This situation generates a fierce competition among Software producers. As a consequence, Consulting companies unconsciously push customers to adopt inadequate latest generation technology. .-

When choosing a non-proprietary solution you will surely capitalize (whatever the trend may be: RDBMS or MDBMS, MOLAP or ROLAP) your experience and most of the products opportunely chosen.-

Top.-

DATAWAREHOUSE

A DW is a data architecture and as such it should not be associated only with specialized data repositories such as MDBMS (Multidimensional DataBase Management System). On the contrary this kind of architecture can be perfectly designed on RDBMS (Relational DataBase Management System).

The problems coming from the use of Transactional DataBases OLTP (On Line Transactional Processing) in decision support processes are mainly due to the fact that the same are oriented to a massive loading of daily transactions and not to the access, retrieval and subsequent analysis of the information.

In a DW, the data linked to a certain application (like data designed to process invoicing and daily collections) are transformed into decision support data which capture the basic nature of the business. The design of a DW should accept summarized, elaborated and descriptive information as an input and it should be subordinated so as to match more closely the way people visualize the strategy of the business involved.-

Data should be integrated due to its heterogeneous nature, i.e. it is generated by a variety of inherited systems or unprocessed information (internal or external) and variable in time.-

Another characteristic of DW is that it constantly incorporates information which should be assimilated to the already existing information. At the same time a DW should not interfere or collide with the systems currently in operation.-

It should also consolidate data inconsistencies among the multiple inherited corporate systems into a single database consistently oriented to decision making.-

When a DW is designed, the current data normalization concepts need to be discarded since serious inconveniences may come up when accessing to the information (OLTP normalization).-

By denormalizing the set of required tables through a methodology fit to DW, the resulting data models can be queried using SQL Standards. These would be very complex in a traditional normalization.-

The design can be OLAP (On Line Analytical Processing) oriented.-

A DW should include "at least" the following components:

• Source DataBase in a host such as OLTP, batch operations DataBase, external data, etc.
• Extraction and transformation tools to retrieve the information from the source, reorganize, summarize and store the same in the repository (Target).
• A Data repository or Target on a server, which could be relational or multidimensional. We should point out that the term "multidimensional analysis" is not exclusively related to MDBMS. If properly designed multidimensional access to a DW assembled on RDBMS is possible.
• From-End tools used by business professionals to have access and analyze data.

Before building up a DW, a "Target" DataBase should be selected (RDBMS or MDBMS) The selection of some conventional RDBMS’ and specialized extraction and transformation tools constitutes a highly effective mechanism to handle, retrieve and distribute data through a corporate DW environment.

Compatibility of conventional RDBMS’s should also be analyzed on the basis of industry standards including SQL, ODBC and OLE.

We should also include compatibility with frequently used front-end tools.

MDBMS technology and proprietary DW tool suppliers report that conventional RDBMS are not capable of providing a high performance in DW environments because they have been designed for OLTP applications. They promote their respective products as multiplatform engines that provide quick responses to on-line queries, programmable DW functions and High performance Standard SQL extensions. Most of these proprietary tools have multidimensional architecture for OLAP environments. In contrast MDBMS are quite limited (excessive increase of stored volumes, powerful hardware requirements, limited dimensions, etc.)

At the same time, we should consider the fact that large RDBMS suppliers are already announcing their new versions with DataWarehousing and OLAP capacity, some of which have already been launched into the market.

Depending on the type of DataBase engine, the end user will have different access and analysis tools such as "Query tools", specific DSS/EIS applications or one of the most popular spreadsheets. It should be taken into account that the selection of proprietary engines will fully restrict the freedom to choose such tools.

The basic requirements for a DataWarehouse consulting tool are the following:

 
• Extended facilities to access, handle and present data
  • Dimension pivoting
  • Drill Dow capacity (details, in case of multidimensional analysis)
  • Roll up capacity (Total, in case of multidimensional analysis)
  • Graphics
  • Management reports
  • etc.
• Certain characteristics so as to minimize MIS support
• Industry standards support such as DDE, OLE and ODBC
• Friendly environment support to end user

Top.-

CONCLUSION

Contrary to popular belief, DataWarehouse is just a step forward into the world of corporate information. Most technology suppliers do not offer a complete solution to this strategy. As a result many DW are built up using products provided by different suppliers. We feel there’s no need to make big efforts to use proprietary tools because all the necessary technology to efficiently generate, maintain, access and use a DW with an open tool architecture is available in this market (the principal consulting companies worldwide strongly agree on this issue) Higher costs should also be taken into account to implement a system that requires, training, development, hardware, etc., specially if current systems satisfy the basic needs.

Undoubtedly, the design of a DW was, is and will be essential. A design that does not consider the fundamental aspects of a DW makes the debate RDBMS or MDBMS meaningless. Products that were not adequately designed can not solve the decision making issue.

The work methodology for a DSS basically consists of a sequential implementation and a gradual knowledge transferal to the target group. As soon as the first module has been implemented, the group will be wholly trained in DSS and DW. Since all DSS are progressively implemented and constantly improved, OSC recommends caution in view of the prevailing euphoria. Adopting open and proven technologies to quickly preserve and capitalize the initial investment is a wise choice.-

Top.-

Carlos A. Arabito (DataWarehousing/DSS Consultant)

e-mail: carlos.arabito@gmail.com