Compress tables without performance tradeoff

  • Easy, comprehensive, accurate
  • Quick gain-estimation
  • SQL based optimization process

The product was specifically developed to reduce Teradata disk consumption, utilizing internal Teradata Multi-Value Compression (MVC) feature. PCW analyzes tables, produces the optimal MVC settings, and computes the possible space gains w/o modifying the tables.
In second phase, PCW generates the SQL scripts that implement the optimization and logs all important information for reporting achieved gains and resource (Time, CPU, I/O) expenditure. PCW supports ITIL compliant projects by generating revert-, revers engineering- and archiving scripts also.

  Key features

  • Process based program structure
  • Analyzing available in manual and also in batch mode
  • Export results to Excel document easily
  • 100% risk free value proposition and implementation
  • No database downtime required
  • Compression is 100% transparent for all users and applications
PRISE Compress Wizard - Teradata compression - Overview
  • The best available compress perfomance according to client feedbacks.
  • One click download and quick install. Easy to use, not a rocket science.
  • 100% safe transformation process without database downtime.
  • Java technology
  • Helps you to choose the right tables for compress
  • Virus busters find PCW safe.
  • Auto-checks your tables regularly and reports possible gains
  • No backdoor, no backtalks
  • Scripts can be reviewed before execution
  • Free trial, online support
  • Free version updates within the licence period
PRISE Compress Wizard - Teradata compression - Overview

I am a CIO/IT manager
10x ROI optimization project within 2 weeks

  • Dedicated tool to optimize existing Teradata hardware resources (disk, I/O and CPU) and boost ROI and TCO.
  • Automates manual work, eliminates human imperfections
  • Enterprise ready by design:
    • Gives 99+% accurate forecasts of achievable benefits
    • Provides full ITIL compliant risk, configuration and change management features.
  • Delivers 15% disk saving even in well managed Teradata environments.
  • Save on your DBA time, finish daily jobs faster and improve your hardware upgrade cycles!
  • The product is now available for free download and limited functional evaluation

I work in Procurement/Sourcing
Save at least 20% on Teradata expenses

  • Alternative to buy expensive hardware resources
  • Reduces frequent hardware upgrades.
  • Will bring substantial cost savings within weeks!
  • The product is now available for free download and limited functional evaluation

I am a technical person/DBA
Optimize Teradata storage, I/O and CPU easiest way ever

  • Teradata MVC feature reduces CPU and I/O consumption, even if sounds unbelivable
  • PCW will optimize your Teradata DB storage, using MVC feature optimization
  • PCW is a dedicated tool that automates manual work related to MVC settings
  • The product is now available for free download and limited functional evaluation
    • Analyzes all your database
    • Demonstrates full functionality on 3 tables of your choice
  • Be prepared to deliver big savings to your company

I am a Teradata developer
Raise your productivity and product quality

  • Deliver your new developments in production optimized for speed and storage by using PCW.
  • Typically 40% disk reduction and I/O improvement, reduced manual work and support for model update (reverse engineering).
  • Request the limited functional PCW now and evaluate it on 3 tables of your choice

I am a Data Warehouse User
Get resources for your refused request

IT keeps refusing to acccept development requests, because of not enough resources?
There is an alternative: PCW
  • Delivers free Teradata disk capacity within days
  • Optimizes Teradata internally, no additional components
  • No tradeoffs between space and performance
  • 100% transparent to the users and applications
  • Available in all Teradata environments
  • The product is now available for free download and limited functional evaluation

PRISE Compress Wizard (PCW)
Product Sheet

Main features

  • Full GUI Java application
    Easy to use graphical interface
  • No installation
    Portable software with configuration assistant
  • Process based structure
    Intuitively guides through the whole optimization process
  • Target table set selection
    DB browser or customizable SQL assisted
  • Analysis function
    Analyses table data and calculates gainable free PERM space before w/o modifying table structure.
    • Adjustable sample size
    • Limitable compress bits
    • Automatic version dependent feature usage (eg. VARCHAR compression)
    • Uncompressed size calculation
  • Multi-mode implementation
    • Online transformation through GUI. BTEQ and JDBC are both supported.
    • BTEQ SQL script export to fit into company patching process.
  • Batch analysis
    Schedule automatic analysis processes on custom SQL determined table set, and also receive the results via e-mail.

Screenshots (click to magnify)

Logon screen

Log into the target Teradata instance. PCW includes user/repository assistant for easy setup. Configures logon, permissions and repository.


Target selection

Select your candidate tables through DB tree browser or from a result of custom SQL select. Displays size and last compress info. Customize SQL by your taste. Add chosen tables to "Target list".


Optimizer parameters

Adjust Compress optimization algorithm parameters.


Analysis progress

Follow the Analysis process as it happens. You are kept informed on what is happening, and what amount of PERM space can be freed up. Note: nothing has changed yet!


Analysis result

Browse repository for previous Analysis' results. View summary of selected items or create Excel report including the consumed CPU and I/O. Transform tables immediately or generate BTEQ scripts for it to do later.


Implementation parameters

Set up the Script Generator and the Implementation process. Choose transformation method, locking, statistics handling, space management, channel, etc. You can set the DB user to be used for transforming the tables.


Implementation progress

Follow the Implementation process on the fly. You are kept informed what is happening, and what amount of PERM space has been already freed up. BTEQ and JDBC logs are displayed.


Implementation results

Browse repository for Implementation results, history included. View summary of selected items or create Excel report including the consumed CPU and I/O.


Batch configuration

Configure Analysis batch(es) for automatic check of compression on a regular basis. Customize preset SQL(s) to get the appropriate table list and adjust parameters. Schedule the batch(es) with Crontab or At to receive both the Analysis report via e-mail and data registered into the repository.


Online help

PCW contains context-aware online help. Click "help" icon, and point to the control you are interested in.

Safety considerations

PRISE Compress Wizard pays key attention for the safety to your data:

  • User level separation
    Analysis and Implementation runs with different DB user.
    Analysis user should not have write permission on target tables.
  • Locking and operation order
    Table transformation scripts are assembled on a way that other
    processes cannot harm data: no lost or duplicated modifications or inserts.
  • Rollback scripts
    Every time a transformation script is generated, a reverse ("rollback") script is also created. This can be used if a table transformation should ever be reverted for any reason.

Back to top

PRISE Compress Wizard - Teradata compression - Overview

PRISE Compress Wizard
Case Study

The Companies

PRISE - Vendor

PRISE enables its customers to realize the maximum value of their Teradata investments by decreasing storage space and hardware consumption using a proprietary self developed methodology.

The implemented PRISE Compress Wizard delivers quick win results for customers by immediately releasing resources like storage space and processing capabilities, and decreasing the specific operational costs.

PRISE's experience shows that even the best managed Teradata environments hide significant optimization opportunities. Unique enablers of the company are the over 10 years of Teradata Masters' industry experience and the proven solution for optimizing Teradata database internal compression.

PRISE Compress Wizard implementation projects demand minimal onsite activities, have a short implementation time, therefore offering quick wins. Most of the benefits manifest as immediately usable free database space and increased processing capacity, while others reduce the long-term costs. For more details please refer to Annex A.

Telenor Hungary - Recipient

Hungary's 2nd rank mobile network operator has been founded in 1994, and fully owned by the Telenor Group since 2002. The company's telecommunication services cover voice, text message (SMS) and mobile internet over 2G and 3G network. The 3.6 million subscribers' distribution is: 52% postpaid and 48% prepaid. The company achieved 650 million EUR revenue in 2009.


http://www.telenor.hu/en/telenor-hungary/facts-figures/

http://www.telenor.com/


In 1999 the firm decided to build a Teradata based Enterprise Data Warehouse (EDW) solution. The solution has since been continuously developed according to best practices to fulfill an ever expanding set of enterprise requirements. It has been deemed the best data warehouse solution in the Telenor Group in 2006, due to the complex analytical functionalities it provides, such as profitability calculation, social network modeling and churn prediction, and the one to one marketing capabilities established on top of it with the implementation of a campaign management solution. The data warehouse of Telenor Hungary stores a nearly 360° view of the customers and the company and stores all detail data for 13 months and aggregated level data from inception. During its lifetime the EDW has grown to 5 times its original size, and at least 3 times in complexity and the EDW has also been integrated into most core business processes, making it one of the most important IT systems within the company.

Methodology

Project lifecycle

Figure 1: PRISE Compress Wizard implementation lifecycle


Methodology and preparation

Non-disclosure agreement signed. Technology demonstrated on-site including ways of working, project milestones and timelines, contractual highlights and guarantees, Q&A. Technical environment initialized, including access permissions, resource allocation, timing.

Roles:

Vendor:On-site visit, presentation, requirements definition
Recipient:DBA and decision-maker get familiar with the product, resource planning, technical preparations

Analysis

Target areas identified. PRISE Compress Wizard assembled according to requirements and run. PRISE evaluates results within 3 working days.

Roles:

Vendor:Consults, prepares and supervises product configuration and scheduled run
Recipient:Participate in identifying focus areas, available for assistance

Forecast report

Findings and recommendations prepared and presented. Q&A session.

Roles:

Vendor:Calculates forecasted benefits, creates reports and perform presentation
Recipient:DBA and decision-maker get precise information about the achievable benefits

Decision

Recipient makes decision about implementing PRISE Compress Wizard optimization. No-go decision concludes the project with zero costs to Recipient and implementation will not take place.

Implementation

Optimization scripts are provided, deployed, scheduled and run.

Roles:

Vendor:Deploys optimization scripts and assists in running
Recipient:DBA fully involved in implementation, managing EDW processes and running optimization scripts

Result report and closure

Log data evaluated and the results of the overall project are assessed and presented. Project closure with benefits shared as previously agreed. Upon mutual satisfaction Recipient issues a reference certificate.

Roles:

Vendor:Assesses and presents results
Recipient:Evaluates results

Optimization process

PRISE Compress Wizard storage optimization is based on fine tuning Teradata Multi-Value Compression (MVC) functionality. MVC is a 100% transparent feature, using it does not influence the applications. MVC is usually manually configured and will not automatically adapt to the changing business and data environment.

From technical point of view the project has two separate tasks: analysis and implementation. During the analysis phase the vendor's expert identifies the set of tables in focus in cooperation with the Recipient's delegated DBA. The qualified tables will be analyzed by a proprietary software - PRISE Compress Wizard, which inspects the distribution of data values, calculates the optimal MVC settings and calculates the possible space gain for each involved table.

The Vendor then evaluates the result statistics and presents the suggested optimization prospects.

The second technical phase of the project covers the implementation, which enables the Recipient to apply the benefits in practice. The Vendor's and Recipient's DBAs create a deploy time plan to effectively execute the suggested modifications in the database. According to the plan the Vendor delivers SQL scripts that relocate data into the optimized table structure. The scripts are specifically designed to avoid all incompatibilities or system disturbances. For details please refer to Annex B.

Benefits of compression

The cost structure of a data warehouse consists of several different components:

·Storage and processing hardware costs, e.g. servers and disk arrays
·License and support fees in proportion to the system performance
·Operational expenses like backup, space and performance management, optimization
·Electric power consumption of the hardware components
·Server room reservation and cooling costs
·Personnel costs

Implementing PRISE Compress Wizard will decrease all cost components, saving significant expenses for the company, without noticeable disadvantages due to Teradata's unique technological solution utilized by PRISE Compress Wizard. Compression is recommended to be performed from time to time in order to exploit the maximum advantages of the product.

Benefits achieved can be classified into two categories. For further details please refer to Annex A.


Instant advantages

·Lower storage consumption, instantly freed disk space
·Less resource consumption -> faster queries to the users
·Shorter backup windows, less archive media
·Deferred hardware extension
·No side effects or application disturbances

Occasional advantages

·Shorter migration, reconfiguration times at upgrades
·Shorter disaster recovery time
·Slower growth of database size, less frequent capacity expansions
·More applications and reports can be served by the current infrastructure
·Significantly less DBA time is required for manual database optimization tasks

Achievements at Telenor Hungary

Storage space saving

On the optimized tables PRISE Compress Wizard managed to reach 17% storage space saving, which resulted in 4%..92% savings on individual tables. To visualize the achievement, the gained storage area is enough to cover 11 months of data volume increase. Detailed estimations and statistics are available in Annex C.

Reduction of resource consumption

Both the CPU and IO load of the system has been decreased by 15% and 13% respectively, at significantly increased user activity. The chart shows the system measurement results:

Figure 2: Overall Workload and CPU&IO usage and trends, including optimization phases


Telenor Hungary's Teradata RDBMS has the DBQL (Database Query Logging) feature switched on continuously, and stores ~1-2 months of historical data. The chart of Figure 2 is based on DBQL data and shows the total CPU and IO load of the system over the time.

·“Phases”:shows the optimization implementations in time (phase 4,5,7,8)
·System CPU time:7 days moving average of daily SUM() of CPU seconds
·System IOs:7 days moving average of daily SUM() of IO
·Workload:7 days moving average of number of SQL statements issued daily

Project details

The optimization project was initiated by PRISE in Q1 2011. It required noticeably low human resources from Telenor side, only one DBA, who prepared the working environment and then executed the implementation package. Telenor DBA time spent on the project did not exceed 5 working days. Decision maker time involvement was limited to a few hours in meetings.

The time frame of the project was quite long compared to the required human and machine resources. The reason was low resource availability, which lead to 8 implementation phases. The project did not have a drawback due to the lengthier timing, all benefits were delivered.


Figure 3: PRISE Compress Wizard implementation project timing



Annex

A. Aspects of benefits

Some of the achieved advantages will not be visible immediately, but on the long run they will undoubtedly apply: the company's growing business produces more and more data and rising market competition demands more complex algorithms, both of which require developments of hardware infrastructure sooner or later or periodically. Optimizing available resources will decrease investment into additional hardware resources along with their costs.

Teradata license and support fees' calculation model is based on the idea of TPERF, which is a weighted mixture of processing capacities. The more CPU and IO capacity a system has, the bigger the TPERF value of an installation, and raises the fees higher.

More server nodes and disk bundles consume more electric energy. Researches demonstrated that the power consumption expenses of IT infrastructure fall into the price range of license fees. The power energy consumption additionally doubles for server room cooling.

Other cost components are reduced immediately. Backup and archive times shorten proportional to freed disk capacity. Companies often suffer from the lack of free space. In this case difficult decisions must be made: investing into additional hardware or reducing either the scope of stored information or the depth of history. These will be painful to some division of the company – unlike compression, where there are no tradeoffs.

It is important to highlight, that PRISE Compress Wizard is 100% transparent for all kind of applications, therefore introduction or optimization of the product will not involve ETL, application or report development, which means there is no hidden cost.

B. Technical description of project

Prerequisites

For quality assurance purposes we prefer to use Teradata's DBQL feature. We use 3-4 weeks log data as basis of resource consumption calculation for analysis & implementation as well as final results.

Target selection

We used the Teradata data dictionary to make a pre-selection of the candidate tables. Based on a consultation with development, reporting and operation staff, we excluded the unnecessary databases and tables from the scope

Project decided to take the TOP 1000 tables by size to be examined, and after excluding the unnecessary and stale ones, 889 of them were candidate for analysis.

Calculation process

The analysis of the <target> is a 100% percent automatic process which has the following prerequisites:

  • 1 database in the EDW, where PRISE Compress Wizard can write, and have ~1000MB (depending on local situation), referenced <CCWDB>
  • 1 user in the EDW, which has read-only access to all EDW database tables, and data dictionary, and read-write access to <CCWDB>.
  • The given user and enough resources (priority, TASM limits) to be able to run the statistical processes. In Telenor Hungary PRISE used a standard „Developer Role” to run the analysis process with its default priority, spool space and access rights
  • Enough spool space for calculations, depending on the analysis sample size.
  • A Windows workstation with
    • JVM 1.8 installed
    • Teradata client installed
    • Network access to EDW system (through JDBC)

The process was run in multiple parts, during the nights, in order to minimize the disturbance of EDW users or daytime if the priority settings enable seamless EDW usage. The time the whole process takes depends on

  • Available processing resources
  • Number of tables in <target>
  • Number of columns in <target>'s tables
  • Storage size of <target>

The calculation process runs as an ordinary ad-hoc report, does not disturb loads (uses access locking) and other reporting and analysis tasks.

The calculation process in Telenor Hungary was run with the following details:

  • Used 1million record sample on each tables
  • Ran in 3 parts, took 10h, 16h, 1h respectively
  • We utilized a total of 94 CPU hours and 916 million IOs for the calculations
  • The calculation results (including detailed value distribution data) occupied ~700Mbytes

The result of the calculation process contains the original and the estimated optimized table sizes, to be able to estimate the amount of workspace that will be necessary for implementing the optimization and the storage gain which can be achieved – before any modifications are done on the database.

Analysis & report

After the completion of the calculation process we found that the following gains are available.

The table contains only the statistics for the tables that are currently not optimal.


Gain % rangeOrig. sum GBytesGainable GbytesNo. of tables
0..5%3856,85124,57222
05%..10%1269,25101,94134
10%..20%2388,77336,13213
20%..30%864,62227,6137
30%..40%624,15215,1464
40%..50%369,86175,5748
50%..60%126,3768,8630
60%..70%36,0122,8227
70%..80%21,6716,26
80%..90%11,359,357
90%..100%2,021,841
Total9570,921300,02889

Target selection

If we want to cut the time and resource demands of the implementation, it's reasonable to shorten the <target> table list. We've chosen to eliminate those tables that did not promise more than 20Mbytes of space gain. This way we had 488 tables left for implementation phase.

Implementation

Table compression parameters are creation-time defined; non modifiable in the available versions of Teradata, therefore the modification of compression is only applicable by recreating the tables, which will cause object and data absence for a while.

For this reason we had to choose low activity time windows to implement optimizations. The most appropriate time was the end of business time: 17:00

PRISE Compress Wizard generates all the necessary scripts that need to be run to achieve the possible storage gain by recompressing the tables, but they must be run by the local Teradata DBA. Before running we recommend that the DBA checks all the SQLs before they are applied.

The implementation was broken up into 8 phases since it was quite difficult to find continuous time frames with available resources of both DBA and database.

C. Result details

Space saving

The total results of the 8 implementation phases are shown in the table below:

PhaseNo of TablesOriginal GbytesGain GbytesGain%Runtime
13183,472,539,53~2h
28543,08158,2229,13~2h
35848,67169,9220,02~3.5h
420388,4699,8125,69~2h
54062304,38484,0321~8h
61120,6734,0728,23~0.5h
718705,37100,2614,21~3h
8272308,55140,16,07~6h
Total4887402,581258,9117.0%~27h

The implementation phase requires the optimized space amount of the target table as an additional free space during the technical run of the scripts, for one table at a time. This space will be immediately de-allocated after the table's optimization finishes.


The implementation consumed:

  • ~350 CPU hours
  • ~1450 million IO operations

Which are 80% and 25% respectively, of the regular daily system loads.

Load performance impact

The test measurements proved that the CPU consumption of an INSERT statement into a compressed table can be 50-200% more, than into its uncompressed equivalent. However, one can easily check that a suboptimal compression definition will demand the same, or more CPU.

Load procedures typically have two important time factors: longer preparation & transformation and significantly shorter final “INSERT”, however the more resource intensive first part is not affected at all.

After the implementation finished, we performed an investigation of the CPU and IO consumption of the INSERT statements issued against the optimized tables:

Figure 4: How did CPU usage of affected tables' INSERT statements change


Performance impact is displayed on the horizontal axis, 1x means no change, 0,5x half, 2x double amount of CPU for the similar INSERT operation. The number of tables where INSERT phase fell into a given performance impact range can be found on the vertical axis. One can read that the modal impact was around 0.95, therefore INSERT statements consumed a little less CPU than before optimization.

Fault tolerance during implementation

Production Data Warehouses are in continuous operation and will not easily go down for maintenance. For this reason PRISE developed a safe process to foreclose the possibility of data damage or loss and minimize the possibility of unavailability.

In Telenor Hungary's PRISE Compress Wizard implementation project we reached these goals 100%:

·No particle of data was lost or corrupted
·No database downtime was requested

Personal data security

PRISE undertakes to sign a very comprehensive non-disclosure agreement as a first step. PRISE Compress Wizard will not modify or keep any personal data. Additionally we only work with your data in your safe environment.

Change management

PRISE provides detailed documentation for ITIL change & configuration management process considerations. PRISE Compress Wizard can be tested initially in a test environment, then gradually implemented across the entire scope with maximum safety.



Open in PDF format: Telenor Hungary full reference case study

Back to top

PRISE Compress Wizard - Teradata compression - Overview