Data Warehousing

:: SOLUTIONS

Overview Each transaction in a computer environment creates a record. When a record is created it is defined as either a transactional or a historical record. A transactional record is one that requires future user interface or modification until the record becomes closed. At the time the transactional record is closed, it becomes a historical record thus becoming the subject of a Data Warehouse. An index is required to store either a transactional or historical record. Initially, transactional and historical records and their corresponding indexes are stored on a dynamic data base. The dynamic database is the organization’s primary computing process; a mainframe, server, PC, etc. At some future point, historical data contained on the dynamic data base is migrated to a “low cost” storage media. “Low cost” storage media could be another mainframe, a server, optical or tape media. The “low cost” storage device’s primary function is to store the historical data for access by users. The “low cost” storage devices cost less. The sacrifice made by moving the data to the “low cost” media is an increase in the time it takes to retrieve the data. It must be stressed at this point that “low cost” storage devices cost less than mainframes on which the dynamic database resides. However, “low cost” storage devices can be quite expensive; generally they are less expensive than the dynamic data base devices. Recors Indexing Indexing records is a major issue in dynamic databases. As records grow, this growth generates indexes that usually are a similar size as the records they are indexing. A dynamic database, generally, uses a “tree type” index, in which each record is indexed and set on a balanced binary tree (see figure 1 below). click on graphic to view full size image. Every new record affects the balance, and the index required to be inserted with each record within each batch of records when user ends the input session. This insertion of nodes in the index file takes processing power, memory and speed. When a record search is performed, potentially hundreds of steps may be required to retrieve the information; consuming valuable memory, space and time for the user and for the system. A 50 million record dynamic database which occupies 5 gigabytes (“GB”) of storage space, generates an index that also uses another 2.5 GB’s. A search on 50 million records not only slows down the processing time of concurrent transactions, but also could take several minutes, depending on database type and operating system. In the figure above, by adding 2 records in step 2 the whole index was rebalanced. A search for check # 1000 would take 1 process in Step 1 while a search for the same check #1000 would take 3 processes in Step 2 adding one more record, 1) increases the number of steps required to index the records, 2) increases the time for inquiries search, and 3) reduces the system performance. Now multiply that for 100 million records and the quagmire takes unimaginable proportions. TMGR’s main feature is not only the flexibility of the compression algorithms used for each specific purpose, but its indexing technologies. TMGRs indexing technology does away with binary systems and utilizes indexing algorithms that can reduce the size of the index, from 50% of database size to approximately ~0.03% of compressed database size. Additionally, searching a minimum non-binary index takes fractions of seconds rather than minutes or hours. Combination of both features, compression and accessing speed make TMGR an unbeatable option for Data Warehouse applications. Most historical data collected by Data Warehouses is stored away from most users. All access to this historical data is prevented for two reasons: (1) avoid modifications to historical records, and (b) avoid host-overload (access to history while the server is busy handling real-time critic transactional operations). TMGR enables the storage of historical records in a fraction of the original space and provides easy access and querying capabilities. In a TMGR Data Warehouse environment, historical records are housed in a much smaller space, multi platform environment and retrieved at significantly high speeds. They are searchable and appendable. The historical data can be available for other purposes (actuarial, marketing, regulation compliance, auditing, fraud prevention, security etc.) for which they were not previously available, due to the overload of the transactional system and valid IT department security concerns that eliminated most access to the system where current business transactions are stored for fear of jeopardizing performance and risk of misuse or loss of data.