Data warehouse Layer

There is no widespread agreement on exactly what constitutes a data warehouse architecture. Though they may not be contradictory, views differ as to the relative importance of the possible components. One possible conceptualization of a data warehouse architecture consists of the following interconnected layers:

Operational database layer

The source data for the data warehouse

Informational access layer

The data accessed for reporting and analyzing and the tools for reporting and analyzing data

Data access layer

The interface between the operational and informational access layer

Metadata layer

The data directory (which is often much more detailed than an operational system data directory).

Benefits of data warehousing

• A data warehouse provides a common data model for data, regardless of the data's source. This makes it easier to report and analyze information than it would be if multiple data models from disparate sources were used to retrieve information such as sales invoices, order receipts, general ledger charges, etc.
• Prior to loading data into the data warehouse inconsistencies are identified and resolved. This greatly simplifies reporting and analysis.
• Information in the data warehouse is under the control of data warehouse users so that, even if the source system data is purged over time, the information in the warehouse can be stored safely for extended periods of time.
• Because they are separate from operational systems, data warehouses provide fast retrieval of data without slowing down operational systems.
• Data warehouses facilitate decision support system applications such as trend reports (e.g., the items with the most sales in a particular area within the last two years), exception reports, and reports that show actual performance versus goals.
• Data warehouses can work in conjunction with and, hence, enhance the value of operational business applications, notably customer relationship management (CRM) systems.

INTERVIEW QUESTION

1. What is source qualifier?
2. Difference between DSS & OLTP?
3. Explain grouped cross tab?
4. Hierarchy of DWH?
5. How many repositories can we create in Informatica?
6. What is surrogate key?
7. What is difference between Mapplet and reusable transformation?
8. What is aggregate awareness?
9. Explain reference cursor?
10. What are parallel querys and query hints?
11. DWH architecture?
12. What are cursors?
13. Advantages of de normalized data?
14. What is operational data source (ODS)?
15. What is meta data and system catalog?
16. What is factless fact schema?
17. What is confirmed dimension?
18. What is the capacity of power cube?
19. Difference between PowerPlay transformer and power play reports?
20. What is IQD file?
21. What is Cognos script editor?
22. What is difference macros and prompts?
23. What is power play plug in?
24. Which kind of index is preferred in DWH?
25. What is hash partition?
26. What is DTM session?
27. How can you define a transformation? What are different types of transformations in Informatica?
28. What is mapplet?
29. What is query panel?
30. What is a look up function? What is default transformation for the look up function?
31. What is difference between a connected look up and unconnected look up?
32. What is staging area?
33. What is data merging, data cleansing and sampling?
34. What is up date strategy and what are th options for update strategy?
35. OLAP architecture?
36. What is subject area?
37. Why do we use DSS database for OLAP tools?