Data Warehousing
...traints. Metadata contains all the information in the data warehouse environment that is not the actual data itself. The metadata is used to map data sources to a common view of the data within the warehouse by extraction and loading processes, to automate the production of Summary tables by warehouse management process, to direct a query to the most appropriate data source by query management process. End user access tool: The principle purpose of data warehouse is to provide information to business users for decision making. These users interact with the warehouse using end user access tools. These tools include reporting and query tools, OLAP (online analytical processing) tools, EIS (executive information systems) tools, and data mining. Reporting and query tools: Reporting tools allow users to produce reports based on the warehouse data. There are two main classification of reporting tools are report writes and production reporting tools. Report writers produces report as-needed basis. These require some initial programming to create the report template but once the template has been defined, generating report can be easy. On the other hand, production reporting tools are used to generate regular basis reports. The examples of reporting tools are IQ Software (IQ/smartserver), Seagate Software (Crystal Reports). Query tools that invites user to form their own queries by manipulating tables and their joins. Query tools for relational data warehouses are designed to accept SQL, or to generate SQL statements to query data stored in a data warehouse. OLAP tools: OLAP tools allow users to make ad hoc queries or generate canned queries against the warehouse database. An OLAP tool has divided into the multidimensional OLAP and relational OLAP. Multidimensional OLAP tools run against a multidimensional database and are better suited to power users in the enterprise. Relational OLAP tools run against warehouses directly in relational databases. The examples of OLAP tools include Essbase OLAP, Powerplay and R/olapXL. EIS tools: EIS systems are applications that run against warehouse data. These provide different executive reports that support for the enterprise budgeting process. There are EIS and DSS (decision support system) development tools available that enable the rapid development and maintenance of custom-made decision systems. The examples of EIS tools include Comshare Decision, Oracle Financial Analyzer, and Oracle Express Objects. Data mining tools: Data mining is a set of automated techniques used to extract previously unknown, actionable pieces of information from large database and using it to make crucial business decisions. The focus of data mining is to reveal the information that is hidden, unexpected and previously unknown. Data mining tools predict future trends and behaviors, allowing businesses to make proactive, knowledge-driven decisions. Data mining applications can be implemented rapidly on existing software and hardware platforms to enhance the value of existing system. The examples of data mining products are IBM Intelligent Miner, Syllogic Data Mining Tool, and NeoVista software. Standards (steps) to successful data warehousing: There are some standards to be followed in implementing data warehousing. These are: • Recognize that the job is probably harder that you expect. • Understand the data in your existing systems. • Use metadata to support data quality. • Select the right data transformation tools. • Take advantage of external sources. • Use new information distribution methods. • Focus on high-payback marketing applications. • Don’t underestimate hardware requirements. • Consider outsourcing your data warehouse development and maintenance. Advantages and Disadvantages of a data warehouse: Advantages: • Improved decision support: The data warehouse collects information from transactional systems without disrupting transactions or corrupting valuable data. The data is gleaned, cleaned up and stored in a central repository that is easy to access and using standard reporting tools, the data can be used to answer important questions about your business and provide instant reporting. The end user access tools produces hidden and unexpected reports and that reports are very useful to decision support system. • Improve operations: Operations data is vitally important to organizations but the data is difficult to collect. The data warehouse collects operational information and executives can monitor current operational data and compare it to historical data. In addition, managers can make operational changes in a more timely and effective manner. • High returns on investment: Data warehouse supports a variety of decision support application that can lead to benefits for organizations. By having a data warehouse, you can get good return on your investment. • More and better information: With data warehouse, we can always have more, accurate and better information. The information that will be useful to make good decision. • Better decision: Data warehouse helps managers and executives notice problems and opportunities sooner, perhaps increases the extent of their analysis and leads to better decision. • Improvement of business processes: Data warehouse have the potential to support significant changes in how an organization is structured and carries out its business. Knowingly or not, organizational design and the design of work processes are shaped by the amount and type of information required in given environment and the organization’s information processing capability. Because a data warehouse can provide more detailed, integrated, accessible, and historically complete information, it should be possible for an organization to operate very differently. Disadvantages: • Complex development: A data warehouse can not just be bought as a product. In fact it is designed for an organization needs. Choice of hardware, software and structure requires careful consideration and how they will progressively work together in future. • Time to build: The major disadvantage is very hard to figure out how long it will take to build data warehouse. • Expensive: To build a data warehouse is really expensive. • Training: Require a new skill-set for warehouse developers and end users. • Security: Data held in one place highlights data integrity problems and vulnerability from the public domain. Relevance of security problems: Data warehousing presents an ideal target for those unauthorized users who hope to penetrate the system. Data warehouses offer an enticing target because their data is orderly, integrated, centrally-located and easily accessible. The kinds of data that are of interest to an unauthorized user include financial, personal medical and human resource data. When the internet is coupled with a data warehouse, the temptation for wrongdoing and damage is very strong and internet creates a need for special security measures. With the internet, unauthorized individuals have easier access into the organization. Solutions available for security problem: In above figure we can see the hierarchy of security techniques and technologies. Encryption/decryption is at the top of the hierarchy. If a corporation has a real concern as to the security of data, encryption provides easily the most secure technology. Security technologies such as firewall security, database VIEW security and system-based logon/logoff security have been in place for years. The limitation with this king of security is that, in many cases, it is easily violated (penetrated). Methods for breaking into this kind of security include trying many password combinations, deploying ‘Trojan Horses’ or ‘spoofers’ to gain access to data, deploying available programs to crack passwords. Application-based security: In application-based security, the loading of data into the data warehouse is done by means of an application, and the access of data is done through and with the help (support) of an application. The application can enforce security. This security solution is not popular because application code requires resources for writing and maintenance and application protected data can be accessed only through the particular application. Encryption/decryption security: In encryption/decryption security, encryption of data begins where initial transaction made and that encrypted data is loaded into the data warehouse. Once that data arrives at the data warehouse, it is stored in an encrypted manner. When the end users access that data, that data is sent to the receiving work station. That data is decrypted at the receiving station. Data is safe as it is transported into the warehouse. Even if the data is siphoned off, it will mean nothing to the unauthorized because that data is in encryption/decryption format. This level of security makes sensitive data safe for access from the internet. You can see in above figure how it works and the protection of data. Comparison of data warehouse with data mart: Data warehouses contain large quantities of data from key operational systems in an enterprise. In contrast, data marts contain only a subset of the data that would have been stored in an enterprise data warehouse. Data mart data are selected to meet the specific needs of a subset of the organization. The data mart can be standalone or linked centrally to the corporate data warehouse. The popularity of data marts is developed as a result of the fact that data warehouses are proving difficult to build and use. The data mart is build for a specific group of people (e.g. department) and contains information relevant only to that group. A data mart takes less time to build due to its reduced scope. It also costs less and is less complex than a data warehouse. A company can have many data marts, each focused on a subset of the entire firm. The characteristics that differentiate data marts and data warehouses include: • Data warehouses deals with multiple subject areas where as data marts deal with single subject areas. • Data warehouses tend to be controlled by the centre of the organization where as a data mart can be controlled by a department of an organization. • A data warehouse assembles multiple types of data from multiple sources. Data marts assemble single types of data from far less sources. • A data mart focuses on only the requirements of users associated with one department or business function. Historically development in data warehouse: In past 20 years back, system development had primarily emphasized on operational systems that support the day-to-day mission-critical operations of the enterprise. Decision support systems and executive information systems may be viewed as the closest precursors to data warehousing systems. Many factors have influenced the fast evolution of data warehousing. Enormous advances in hardware and software has affected the evolution in data warehousing. The explosion of intranets and web based applications has greatly impacted data warehousing as well. Some business and organizational have forced, in late eighties, to re-evaluate their business practices and to identify and focus on their core competency areas. These phenomena have increased the need for continuous analysis and management of data. All these factors influenced to create a data warehousing. The first data warehouse model was the information warehouse framework announced by IBM in September 1991. By mid 1994, numerous data warehouse products came on the market. Practical application: There are many choices of hardware and software for data warehouses. Because data warehouses grow, the hardware and software chosen should be highly scalable. Oracle 9i: Oracle is currently the market leader in database systems, the addition of this essential functionality has an added benefit to current and potential organizations. Operation of a data warehouses demand...