DWH Questions

What is an ODS?
An Operational Data store is a construct, which has been designed to overcome the need for Information (EIS) in the Operational area. It is similar to a data warehouse but differs in many respects. Sitting in between the operational, transaction processing environment and the enterprise data warehouse is this structure called the ODS. The ODS provides foundation to achieve tangible integrated operational results in a short time frame. While the data warehouse offers no relief to the organization struggling with nonintegrated operational systems, the ODS offers immediate relief.

What is a Federated Data Warehouse?
Most techniques used by organizations to build a data warehousing system employ either a top-down or bottom-up development approach. In the top-down approach, an

Most techniques used by organizations to build a data warehousing system employ either a top-down or bottom-up development approach. In the top-down approach, an enterprise data warehouse (EDW) is built in an iterative manner and underlying dependent data marts are created as required. In the bottom-up approach,  independent data marts are created with the view to integrating them into an enterprise data warehouse at some time in the future. There are many pros and cons of the two approaches but there is a steady trend toward the use of independent data marts, especially with the move toward the use of turnkey analytic application packages.

A solution must offer low cost and rapid ROI advantages of the independent data mart approach without problems of data integration in the future. Such a solution is called the federated data warehouse. Two components of a federated data warehouse are the common business model and shared information staging areas. What is a Virtual Data Warehouse? When end-users access the “system of record” (the OLTP system) directly and generate “summarized data” reports and thereby given the feel of a “data warehouse”, such a data warehouse is known as a “virtual data warehouse”. What is Top-Down architecture of Warehouse development? The interaction associated with the architecture begins with an Extraction, Transformation, Migration, and Loading (ETML) process working from legacy and/or external data sources. Extraction transformation, and migration, process data from these sources and output it to a centralized Data Staging Area. Following this, data and metadata are loaded into the Enterprise Data Warehouse and the centralized metadata repository. Once these are constituted, Data Marts are created from summarized data warehouse data and metadata. What is Bottom-up architecture of Warehouse development? The second data warehousing systems architecture, the "Bottom-up" architecture became popular because  the Top-down architecture took too long to implement, was often politically unacceptable,  and was too expensive. What is Normalization, First Normal Form, Second Normal Form , Third Normal Form? 1.Normalization is process for assigning attributes to entities–Reduces data redundancies– Helps eliminate data anomalies–Produces controlled redundancies to link tables  2.Normalization is the analysis of functional dependency between attributes /  data items of user views,It reduces a complex user view to a set of small and stable subgroups of fields / relations 1NF: Repeating groups must be eliminated, Dependencies can be identified,  All key attributes defined,No repeating groups in table 2NF: The Table is already in1NF, includes no partial dependencies– No attribute dependent on a portion of primary key, Still possible to exhibit transitive dependency,  Attributes may be functionally dependent on non-key attributes. 3NF: The Table is already in 2NF, Contains no transitive dependencies Why do we need a different schema for Data warehousing/Business Intelligence?  A data warehouse stores historical data that is collected from various sources and the data is time variant.  The data warehouse is updated in a scheduled and controlled manner. An online system usually does not  store historical data, however if history data is stored it would pose severe performance issues,  further being an Online environment 2 users accessing the same set of information are  likely to derive different results. An online system would provide data only from a single source,  whereas a data warehouse usually has data being put in from various sources. What are the various features of Enterprise Data Warehouse?  The various features of an EDW are:  An EDW may or may not interact with Data Mart. The most important aspect of any  Data Warehouse is the consistency of information. An EDW may have same level of granularity as a Data Mart.  This means that the data from the Data Mart will be dumped into an EDW. An EDW may have more detailed level of data than a Data Mart.  This means that the data requirement  is at a more granular level that may not be catered by the Data Mart.