What is Data Warehouse and what are its advantages and disadvantages?

Question

What is Data Warehouse and what are the advantages and disadvantages of using it.

Answer 1

It is an abstract concept that indicates that you are consolidating data from various sources and applications of an organization into a single "location" facilitating your queries and eventually manipulation.

Usually this is done through a database and after fed data from external sources to it and these become independent of the sources.

The most common is to use tools OLAP to work with this data, as opposed to OLTP which is used in so-called operational databases of the organization. In general in DW the data is used more analytically, for help in decision making.

In the ideal world they should not be needed and all sources should be part of the main database and their access should be easy to use as is best. In practice this is rarely possible in large organizations.

It is important to note that data sources are not necessarily other databases, although they are the main data source for DW.

The subject is extensive and I think we can fit answers that complement each other here.

Perks

• Integrates diverse data sources in one place.
• The data is cleaned of things that only matter to the operation mechanism of the database and the application that manages it.
• He is a great data indexer.
• It’s easy to access all data in very different and complex ways in a simple, integrated and fast way, speeding up decision making.
• Historical data is usually easy to access.
• The data are standardized and canonized.
• Data sharing is facilitated, mainly through data Marts, each one accesses as best suits him, in theory.
• Data access control and permissions are facilitated.
• In theory reduces the need for IT personnel intervention in managerial spheres.
• Can provide relevant and difficult-to-obtain information, helping predict what will happen.
• Helps find defects in the overall organization of organization data and helps maintain consistency as the data needs to come in better shape and be more compatible with others.
• Does not affect operating systems (in a sense).

Disadvantages

• It is not easy to capture and transform data from diverse sources into something useful for DW. It is often said that the laborious DW is the transformation that can reduce the cost-benefit ratio. It is extremely complicated to make all data compatible.
• While controlling access on DW there may be security and privacy issues because the data comes out of your source that may have different policies.
• Implementing and maintaining to conform to all existing systems that are constantly changing and new systems is not simple and is expensive.
• Extra tools and infrastructure are often needed, not always offsetting the cost of acquisition, training and operation, often competing with the operating systems that are most important for everyday life.
• Information can quickly become obsolete.
• Works well with some scenarios but is a nuisance to others.
• Often it is too complex and it is common for the expected use expectations and results to be above reality, exposing a possible failure in the use option.
• It is common to generate conflicts within organizations because of their use.
• It is not easy to establish and maintain clear rules for all phases of operation of a DW.
• It is not a magic tool that solves any problem of the organization. One can abuse the information provided or use it improperly. Data alone does not say everything.
• It is difficult to foresee all the problems it will bring to the organization.
• It is common to give more importance than he actually has and people start working for him instead of working with him.
• Unstructured data remains difficult to work on DW.

Of course it is possible to prevent problems from happening but it is not an easy and guaranteed task.

This is a summary because it lists dozens of advantages and disadvantages. Some are variations of what has been said. It would be interesting a well done study that showed this in an "official" way and that was thought to avoid redundancies in the items without forgetting anything.

I have no experience with large companies but in small and medium-sized companies (which represent 99% of the world’s companies, 99.9% if you consider micro) it doesn’t usually work. In general it is easier to organize an application correctly so that DW is not needed, works better, meets the needs better and comes out cheaper. Often the adoption of DW is the confession that systems are in chaos. What would be the rule in large companies, mainly heterogeneous.