DATA WAREHOUSING/COGNOS INFORMATICA/DATASTAGE/ETL5 out of 5 based on 3651 ratings. 5 user reviews.
Introduction to Data Warehousing Concepts
A data warehouse is a database designed to modify business intelligence activities: it exists to assist users perceive and enhance their organization's performance. it's designed for query and analysis instead of for transaction process, and typically contains historical knowledge derived from transaction information, however will include data from alternative sources. data warehouses separate analysis employment from transaction work and alter a company to consolidate knowledge from many sources. This helps in:
- Maintaining historical records
- Analyzing the data to gain a better understanding of the business and to improve the business
Why Data warehouses ?
Data warehouses are distinct from on-line transaction process (OLTP) systems. With a data warehouse you separate analysis work from transaction workload. so data warehouses area unit greatly read-oriented systems. they need a way higher quantity of information reading versus writing and change. this allows much better analytical performance and avoids impacting your transaction systems.warehouse system may be optimized to consolidate data from several sources to realize a key goal: it becomes your organization's "single source of truth". there's nice worth in having a homogenous source of information that each one users will look to, it prevents several disputes and enhances decision-making potency.
Who should learn Data warehouses ?
Users of the data warehouse perform data analyses that are often time-related. Examples include consolidation of last year's sales figures, inventory analysis, and profit by product and by customer. But time-focused or not, users want to "slice and dice" their data however they see fit and a well-designed data warehouse will be flexible enough to meet those demands. Users will sometimes need highly aggregated data, and other times they will need to drill down to details. More sophisticated analyses include trend analyses and data mining, which use existing data to forecast trends or predict futures. The data warehouse acts as the underlying engine used by middleware business intelligence environments that serve reports, dashboards and other interfaces to end users.
Scope of Data warehouses ?
A data mart serves the same role as a data warehouse, but it is intentionally limited in scope. It may serve one particular department or line of business. The advantage of a data mart versus a data warehouse is that it can be created much faster due to its limited coverage. However, data marts also create problems with inconsistency. It takes tight discipline to keep data and calculation definitions consistent across data marts. This problem has been widely recognized, so data marts exist in two styles. Independent data marts are those which are fed directly from source data. They can turn into islands of inconsistent information. Dependent data marts are fed from an existing data warehouse. Dependent data marts can avoid the problems of inconsistency, but they require that an enterprise-level data warehouse already exist.
Key Characteristics of a Data Warehouse
The key characteristics of a data warehouse are as follows:
- Data is structured for simplicity of access and high-speed query performance.
- End users are time-sensitive and desire speed-of-thought response times.
- Large amounts of historical data are used.
- Queries often retrieve large amounts of data, perhaps many thousands of rows.
- Both predefined and ad hoc queries are common.
- The data load involves multiple sources and transformations.
What is data warehouse?A data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis, and is considered as a core component of Business Intelligence environment. DWs are central repositories of integrated data from one or more disparate sources.
What is domain key normal form in DBMS?Domain-key normal form (DK/NF) is a normal form used in database normalization which requires that the database contains no constraints other than domain constraints and key constraints.
What is linked server in SQL?Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.
What is primary key in SQL?A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields.
What is the scope in Data Warehouse?Data warehousing lets people look at corporate data as never before. Patterns, trends, seeing the forest and the trees -- all became a possibility with data warehousing. Business people were able to look at their enterprises as no one had ever been able to previously and entire industries and multi-billion dollar companies grew out of this love of knowing more.