Data Warehousing

 Data Warehouse is open to an almost limitless range of definitions. Simply put, data warehouses store and aggregation of a company's data.

Data warehouses are an important asset for organisations to maintain efficiency, profitability and competitive advantages, organisations collect data through many sources- online, call centre, sales needs, inventory management. The data collected have degrees values and business relevance.

Figure shown below shows the architecture of a typical data warehouse and illustrate the gathering of data, the storage of data, and the quaring and data analysis support.

Different steps involved in getting data into a warehouse are called as extract, transform and lode or ELT tasks; extraction refers to getting data from the sources, while loaders reference to loading the data into data warehouse.

Characteristics of data warehouse:

• Multidimensional conceptual view
• Generic dimensionality
• Unlimited dimensions and aggregation levels
• Unrestricted cross dimensional operations
• Dynamic sparse Matrix handling
• Client/server architecture
• Multi user support
• Accessibility
• Transparency
• Intuitiive data manipulation
• Consistent reporting performance
• Flexible reporting

Pre- Data Warehouse:

The pre- data warehouse zone provides the data for data warehousing. Data warehouse designers determine which data contains business value for insertion.

OLTP databases are where operational data are stored. OLTP databases can reside in transactional software applications such as enterprise resource management, supply chain, point of sale, customer serving software. OLTP are design for Transaction speed and

accuracy.

Meta data ensure the sanctity and accuracy of data entering into the data lifecycle process. Metadata in shows that that has the right format and relevancy.

Data Cleansing:

Before data and data warehouse, the extraction, transformation and cleaning process ensure that the data passes the data quality threshold.

Data Repository:

The data warehouse repository is the database that stores active data of business value for an organisation. The data warehouse modelling design is optimised for data analysis.

 There are variants of data warehouses- Data Marts and ODS.data warehouses collects data and is the repository for historical data. Hence it is not always efficient for providing up to date analysis. This is where ODS, operational data stores, come in.

Front End Analysis:

The last and most critical portion of the data warehouse over you are the front and applications that business users will use to interact with data stored in the repositories.

Typical functionality of a Data Warehouse: 

Data warehouse exist to facilitate complex, data intensive, and frequent ad hoc queries.Accordingly,data warehouses must provide for greater and more efficient query support then is demanded of transactional databases. In particular enhanced spreadsheet functionality includes support for state -of-the art spreadsheet applications, for example :MS-Excel, as well as for OLAP applications programs. These offer pre-programmed functionalities such as the following:

• Roll up: with increasing generalisation (for example weekly to quarterly to annually).
• Drill down: increasing levels of details are revealed (the complement of roll-up)
• Pivot: cross tabulation (also referred as rotation) is performed
• Slice and dice: projection operations are performed on the dimensions
• Sorting: data is sorted by ordinal value
• Selection: data is available by value or range
• Derived attributes: attributes are computed by operations on stored and derived values

Data warehouse problems:

• Data that is required is not collected or not accessible
• Not enough time saw spent prototyping or understanding the real business needs in depth
• initial database scope was too broad trying to contain too much information to soon
• Besides the initial project approval, senior management did not provide much direction in terms of priorities, resulting in a disconnection between the data needed and the data gathered