Data warehousing guide for managers

Data warehousing tools: Definition, scope, and significance 

Organizations generate two types of data:

• Operational data: Generated for carrying out day-to-day operations.
• Historical data: Over time, data generated regularly in the past needs to be retained for reporting and analysis without impacting the operational data systems. Such data is, therefore, held separately in a data warehouse. Data warehousing tools help organizations build such warehouses.

A data warehouse can be useful for applications such as forecasting, trend analysis, analysis of compliance issues, inventory management, competition analysis, etc.

A data warehouse comprises  three layers:

• Staging layer is used to store raw data.
• Integration layer integrates data.
• Access layer makes data available to end-users.

It typically receives operational data by one of the following means:

• By using multiple tools developed in-house or purchased off the shelf: This approach creates the possibility of ‘key’ business rule being lost. For example, if data transformation occurs at multiple locations, developer attempting to modify existing transformation routine might overlook one or more location(s).
• By using ETL (extract, transform, and load) tools: These tools ensure that data with equivalent interpretation from different operational systems has a uniform representation before being stored in the data warehouse. For instance, “Full-Time” and “Part-Time” might be used by one operational system to represent status of an employee while another may use “FT” or “PT”. ETL tools remove such inconsistencies.

Back to Index

Trends in the data warehousing tools market 

• Data warehousing appliances: Vendors are increasingly offering appliances to help organizations leverage their existing hardware instead of procuring proprietary hardware. This may result in the reduction of deployment time and cost.
• Fusion of structured and unstructured data: DW tool vendors have been improving their text tagging and annotation mechanisms that integrate unstructured and structured data. This trend must be viewed in the backdrop of the regulatory compliance pressure on the organizations with respect to dealing with and accountability of unstructured data. Unstructured data typically comprises the data found in e-mails, email attachments, and loose files.
• Cloud-based data warehousing tools have been gaining popularity, specially in the SMB segment.

Back to Index

Benefits of deploying data warehousing tools 

A DW tool helps create a data warehouse, which once built, offers the following benefits:

• Central repository with ‘consistent’ data: Data from multiple data sources is pooled into a central repository to create a standard data representation. Inconsistencies are flagged and resolved before it goes into the data warehouse. As a result, reporting and analysis become robust.
• Faster data retrieval: Since data warehouses and operational systems are different entities, faster data retrieval from the former is doable without impacting performance of the latter.
• Lower TCO in case of a cloud-based DW tool:

Back to Index

Challenges of deploying data warehousing tools 

Data warehousing projects can be ridden with the following issues:

• Initial ‘go live’ time is long.
• Hardware costs are high. They may be marginally lower if the chosen vendor also offers a DW appliance.
• Software licensing costs are high.
• In case of cloud-based DW tools, subscription fee is incurred but corresponding benefits are not seen if the DW tool in question is not designed properly to handle analysis-heavy requirements.

Back to Index

DW tools: Key evaluation considerations 

Before commencing on a DW tool evaluation, ensure that there’s a top-level agreement on the need for a data warehouse and accompanying costs. Further, there needs to be a reasonable degree of standardization amongst business units with respect to data and associated policies and procedures. This will ease the overall process of choosing the right tool for your data warehousing needs. Once that’s in place, consider the following ‘key’ aspects while evaluating a tool.

• Hardware requirements –

• 1. In case you are building a data warehouse tool in-house, buy a storage server with sufficient space for current and future storage needs; also pay attention to aspects such as high processing speed and memory.
  2. Assess if the data warehousing tool vendor offers an appliance that allows you to use your existing hardware. This can be a significant cost buster.
• Following data warehousing architecture options may be evaluated based on what you expect the tool to deliver:
  1. Central data warehouse which handles the analytical workload as well.
  2. Central data warehouse but analytical workloads can be performed in small, logical data marts.
  3. Operational data store which receives information from operational systems and passes it to the actual data warehouse which stores the data in a normalized way to reduce data redundancy. Data marts may be created for analytics.

A few other important aspects to evaluate include:

• Can the data warehousing tool obtain data from multiple operational sources seamlessly in an ongoing manner instead of starting from ground zero?
• Evaluate the ETL (extract, transform and load) abilities of the data warehousing tool. Set ETL rules in such a way that only the good quality and consistent data goes into the data warehouse.
• Does the data warehousing tool allow easy extendibility?
• How easy is it to customize the DW tool for your domain?
• Are there built-in data auditing capabilities offered?

Back to Index

Data warehousing vendors and the tools they offer 

Back to Index

Further Reading 

Definition from Whatis.com: Data warehouse or information warehouse

NEWS: Mega-vendors dominate Gartner’s Magic quadrants

News: Data warehousing technologies to evolve rapidly, says Gartner

Tip: Data warehousing best practices: Part I

Tip: Avoid data warehouse project failures with these tips

Tip: Data warehousing best practices: Part II

Tip: Making the business case for an enterprise data warehouse

Tip: 6 data warehouse design mistakes to avoid

News: 2010 data warehouse appliance guide

Back to Index