|#5 Top Data warehousing Skills in the age of Big data:|
What is Data warehousing
If your company's data warehouse were advertised as a used car, for example, it may be described this way: "Contains late-model, previously owned data, all of which has undergone a 25-point quality check and is offered to you with a brand-new warranty to guarantee hassle-free ownership."
Most organizations build a data warehouse in a relatively straightforward manner:
- The data warehousing team selects a focus area, such as tracking and reporting the company's product sales activity against that of its competitors.
- The team in charge of building the data warehouse assigns a group of business users and other key individuals within the company to play the role of Subject-Matter Experts. Together, these people compile a list of different types of data that enable them to use the data warehouse to help track sales activity (or whatever the focus is for the project).
- The group then goes through the list of data, item by item, and figures out where it can obtain that particular piece of information. In most cases, the group can get it from at least one internal (within the company) database or file, such as the one the application uses to process orders by mail or the master database of all customers and their current addresses. In other cases, a piece of information is not available from within the company's computer applications but could be obtained by purchasing it from some other company. Although the credit ratings and total outstanding debt for all of a bank's customers, for example, aren't known internally, that information can be purchased from a credit bureau.
- After completing the details of where each piece of data comes from, the data warehousing team (usually computer analysts and programmers) create extraction programs. These programs collect data from various internal databases and files, copy certain data to a staging area (a work area outside the data warehouse), ensure that the data has no errors, and then copy it all into the data warehouse. Extraction programs are created either by hand (custom-coded) or by using specialized data warehousing products.
Different roles in Data warehousing projects:
ETL development: ETL refers to the extraction of data from source systems into staging, the transformations necessary to recast source data for analysis, and the loading of transformed data into the presentation repository. ETL includes the selection criteria to extract data from source systems, performing any necessary data transformations or derivations needed, data quality audits, and cleansing.
Data cleansing: Source data is typically not perfect. Furthermore, merging data from multiple sources can inject new data quality issues. Data hygiene is an important aspect of data warehouse that requires specific skills and techniques.
OLAP design: Typically data warehouses support some variety of online analytical processing (HOLAP, MOLAP, or ROLAP). Each OLAP technique is different but requires special design skills to balance the reporting requirements against performance constraints.
Application development: Users commonly require an application interface into the data warehouse that provides an easy-to-use front end combined with comprehensive analytical capabilities, and one that is tailored to the way the users work. This often requires some degree of custom programming or commercial application customization.
Production automation: Data warehouses are generally designed for periodic automated updates when new and modified data is slurped into the warehouse so that users can view the most recent data available. These automated update processes must have built-in fail-over strategies and must ensure data consistency and correctness.
General systems and database administration: Data warehouse developers must have many of the same skills held by the typical network administrator and database administrator. They must understand the implications of efficiently moving possibly large volumes of data across the network, and the issues of effectively storing changing data.