Setting up and Managing a Data Warehouse

Setting up and Managing a Data Warehouse © Shutterstock.com | Andrey VPData warehousing is the use of relational database to maintain historical records and analyze data to understand better and improve business. In this article, we will look at 1) what is a data warehouse? 2) Data warehouse integration process, 3) setting up a data warehouse, 4) data warehouse components, 5) data warehouse backup, storage and tools, and 6) management tool providers for data warehousing.WHAT IS A DATA WAREHOUSE?Data warehouse databases are designed for query and analysis, not transactions. The data that is collected from various sources is separated into analytic and transaction workloads while enabling extraction, reporting, data mining and a number of different capabilities that transform the information into actionable, useful applications.The main data warehouse structures as listed in Docs.oracle.com are the basic architecture, which is a simple set up that allows end-users to directly access the data from numerous sources through the warehouse, a second architecture is a warehouse with a staging area that simplifies warehouse management and helps with cleaning and processing the data before it is loaded into the warehouse. And finally there is the architecture with both a staging area and a data mart. Data marts are used to create and maintain custom categories in organizations with specialized database designed for specific businesses, so for example if an organization had a data warehouse for sales, separate from advertising, then the data mart setup would best serve their needs.To further understand a data warehouse, it is important to look at its characteristics, which are subject orientation, integration, non-volatility, and time variance.Subject Oriented: This refers to when data is giving information on a particular subject. For example, a company could use data to analyze their company’s marketing data, and it’s effectiveness. The devotion of a data warehouse to a specific matter is the key componen t of a subject-oriented warehouse.Integrated: This is when data gathered from a number of disparaging sources, and then all gathered into a coherent whole. By organizing differing data into consistent formats, companies can resolve problems and inconsistencies among units of measurement and promote better results.Nonvolatile: This refers to data that remains stable and unaffected by new developments. Once entered into the system, this data should not change, ensuring comparison analysis over a long period of time.Time Variant: This refers to data gathered is identified with a particular time period and focuses on change over time. By using a large amount of data, spread over a long time period, analysts can decipher patterns, and business relationships that would have otherwise been overlooked. DATA WAREHOUSE INTEGRATION PROCESSThe whole purpose of data mining is to facilitate business analysis. And to accomplish this, raw data must be arranged and consolidated into an information b ase usable by the firm. This process is referred to as ETL (Extract, Transform, Load), which though it may seem like specified steps, is in opposition referring to a broader concept.ExtractionThis step in the process refers to removing the data from its source and making it accessible for further processing. All the needed data is retrieved without affecting the source system’s performance, response time or locking in a negative manner. This first step in the ETL process usually involves a cleaning phase in which data quality is ensured through data unification. The rules of unification should include things such as making identifiers unique such as gender categories, phone number, and zip code conversions into standard form and validation of address fields converted into the proper format.TransformationThis step applies a set of rules to change source data into similar dimensions so the same units of measurement can be used. This transformation step also joins data from a variet y of sources, generates aggregates, surrogate keys and applies validation and new values.LoadingThe loading phase is a two-part process of disabling constraints and indexes before the load process starts and then enables them once the load is completed. In this step, the target of the load process is often a database.SETTING UP A DATA WAREHOUSEThe main purpose of a data warehouse is to organize large amounts of stable data to be easily retrieved and analyzed. So when setting up, care must be taken to ensure the data is rapidly accessible and easily analyzed. One way of designing this system is with the use of dimensional modeling, which allows large volumes of data to be efficiently queried and examined. Since much of the data in warehouses is stable, that is, unchanging, there is hardly a need for repetitive backup methods. Also, once new data is loaded it can be updated and backed up right away by way of, in some cases, the data preparation database, so it becomes available for ea sy access.  According to Wisegeek.org, there are four categories of data warehousing tools; these are extraction, table management, query management and data integrity tools.  All these tools can be used in the setup and maintenance of the best technology to manage and store the huge amounts of data a company collects, analyzes and reviews.Company AnalysisThe first step, in setting up the company’s data warehouse, is to evaluate the firm’s objectives, For example, a growing company might set the objective to engage customers in building rapport. By examining what the company needs to do to achieve these tasks, what will need to be tracked, the key performance indicators to be noted and a numeric evaluation of the company’s activities the company can note and evaluate where they need to get started.Existing System AnalysisBy asking customers and various stakeholders pointed questions, Business Intelligence leaders can gather the performance information they currently have in place that is or isn’t effective. Reports can be collected from various departments in the company, and they may even be able to collect analytical and summary reports from analysts and supervisors.Information modeling of core business processesAn information model is conceptual, and allows for one to form ideas of what business processes need to be interrelating and how to get them linked. Since the data warehouse is a collection of correlating structures, creating a concept of what indicators need to be linked together to create top performance levels is a vital step in the information modeling stage. A simple way to design this model is to gather key performance indicators into fact tables and relate them to dimensions such as customers, salespeople, products and such.Design and TrackOnce all those concepts are set in place, the next critical step is to move data into the warehouse structure and track where it comes from and what it relates to. In this phase of design, it is cr ucial to plan how to link data in the separate databases so that the information can be connected as it is loaded into the data warehouse tables. The ETL process can be pretty complex and require specialized programs with sophisticated algorithms, so the right tools have to be chosen at the right, and most cost effective price for the job. Because the data is to be tracked over time, the data will need to be available for a very long period. However the grain (atoms or make up) of the data will defer over time, but the system should be set that the differing granularity is still consistent throughout the singular data structure.Implementation of the planOnce the plan is developed, there is a viable basis for scheduling the project. Because the project is grand, there should be phases of completion scheduled and then fit together upon completion. With careful planning, the system can provide much-needed information on how factors work together to help the organizations activities.Upd atesSince the data warehouse is set to retain data for long stretches at many levels of granularity and has already been set to be consistent throughout the system, in the design phase of the warehouse setup, there can be various storage plans that tie into the non-repetitive update. As an example, an IT manager could set up a week and monthly grain storage systems. In the day grain, data is stored in its original format for 2-3 years, after which it is summarized and moved to the weekly grain structure where it could remain for another 3-5 years and then finally to a monthly grain structure. Of course, this can all be set at the design phase to work with the different grains based on data age and be done automatically.DATA WAREHOUSE COMPONENTSSo as was the case in the design and set up phase of the warehouse, data was merged from varying sources into a single related database. And so far we have seen that the point of creating this warehouse structure is to retrieve information fas ter and more easily so a firm can market faster, create more revenue, improve service standards and manage industry changes. www.enicn.com reports that there is usually a mixture of current, instant, and unfiltered data streamed alongside more structured data, so there needs to be implemented a system of management of the various components.Load managementLoad management refers to the collection of information from varying internal and external sources and summarizing, manipulating and changing the data into a format that allows for analysis. To manage the load, raw data must be kept along with the changed versions to enable construction of different representations as needed.Warehouse managementWarehouse management is the day-by-day management of a data warehouse that ensures the information is available and effectively backed up and secure.Query ManagementQuery management allows access to the warehouse contents and may even include the tasks of separating information into various categories to be presented to different users. Users may access information through query tools or custom built applications.  DATA WAREHOUSE BACKUP, STORAGE TOOLSLike any other program, data warehouses can be tedious to design create and implement, so special measures should be in place to ensure the information is not lost.Backup and restorationAn automatic system should be put in place to ensure the information is secure and that if needed data can be restored quickly and with little or no negative alterations. The first and most vital step is to ensure the original information source is backed up and then following that a weekly backup of the entire warehouse as it could prove costly to have to recreate the entire system from scratch. The use of cold and multiplexing backup systems will ensure less need for restoration. However, a disaster recovery site should be made available for copies of all key system components. The simplest way to achieve this is using a system that auto matically creates copies and sends them to the disaster site. But there are systems that can copy hybrid database systems and create the backup if that is needed as well.Online and Offline storageData storage should be done both online and offline to avoid overwhelming the system or having “disk full” issues. With the system setup to store data in different granularity settings, one could stash older, raw data and unused or rarely used reports and multimedia offline. The implementation of hierarchical storage management (storing files automatically to a secondary source while allowing users access,) is a smart move after implementing the data warehouse setup.Storage ToolsThere are a few tools being created to rectify the storage issues that occur with data warehouses.Storage Manager takes care of all the storage objects such as file systems, database, network intelligence devices and disk and tape arrays. This system also collects data about data, performs administrative duties and among other things let you see the health of your data warehouse.Storage Optimizer is another product that can be used for recommendations of actions that will remove hot spots and improve online performance and reliability. It will also include actions to take for offline storage based on historical patterns.Storage Planner enables planning for large online and offline database capacity. This program focuses on large, international databases and warehouses.MANAGEMENT TOOL PROVIDERS FOR DATA WAREHOUSINGBusiness Objects Data IntegrationPreviously known as Actaworks, the data integration and ETL tool, is mainly used for data warehouse creation and building data mart architectures. A component of the Business Objects Business Intelligence suite, called Data Integrator, provides a user with opportunity of extraction and integration of data for analysis and reporting, as well as transforming and loading batch data (processing program that takes input and produces output files) from flat-file (database that when not in use is stored as an ordinary, nonindexed file). According to Help.sap.com, this program has three components Designer, Repositories and Job Savers.IBM WebSphere DataStage ETL ServicesThe highlights of this product boast that it allows for delivery of information from multiple sources, in multiple formats, no matter what volume, or time frame is required for processing. Another integration and transformation choice for IT specialist, this product claims to enable consistent rule application across various data structures.Ab Initio Software Corporation analysis, manipulation and processingThis software is for data processing applications provided through a user-friendly platform that takes care of data analysis, batch processing, data manipulation, extraction, transformation and loading processes. This kind of software is good for credit card networks, stock exchange businesses, banks, government agencies, insurance companies and Internet adverti sing organizations.SQL Server Integration Services warehouse building and managementAnother data integration platform is SQL Server Integration Service. It allows users to build enterprise-level data integration and transformation solutions. Integration services offer problem solving through email files, data warehouse updates, data mining, cleaning, and management. This program has automatic built-ins that perform tasks and manage the various packages embedded in the system and can be run without prior coding knowledge or expertise to create advanced business solutions. The other brilliant benefit of the SQL Server Integration Services program is that it covers a wide range of Business Intelligence needs without the need for expensive add-ons or learning new skills, because the skills needed to work with the product on the cloud and on the company’s premises are the same.