IT & Programming Information

Basic steps to develop Data Warehouse Architecture 1. Extracting the transactional data from the data sources into a staging area 2. Transforming the transactional data 3. Loading the transformed data into a dimensional database 4. Building pre-calculated summary values to speed up report generation 5. Building (or purchasing) a front-end reporting tool Extracting Transactional Data A large part of building a DW is pulling data from various data sources and placing it in a central storage area . In fact, this can be the most difficult step to accomplish due to the reasons mentioned earlier. Fortunately for many small to mid-size companies, Microsoft has come up with an excellent tool for data extraction. Data Transformation Services (DTS), which is part of Microsoft SQL Server 7.0 and 2000, allows you to import and export data from any OLE DB or ODBC-compliant database as long as you have an appropriate provider. Transforming Transactional Data Most co

What Is a Data Warehouse? Data warehouse is a relational database that is designed for query and analysis. Data warehouse ( DW or DWH ), also known as an enterprise data warehouse ( EDW ), is a system used for reporting and data analysis. DWs are central repositories of integrated data from one or more disparate sources. They store current and historical data and are used for creating trending reports for senior management reporting such as annual and quarterly comparisons. The data stored in the warehouse is uploaded from the operational systems (such as marketing, sales, etc., shown in the figure to the right). The data may pass through an operational data store for additional operations before it is used in the DW for reporting. This helps in: ·         Maintaining historical records ·        Analyzing the data to gain a better understanding of the business and to improve the business Operational and Informational systems Operational Systems: An oper