DATA WAREHOUSING AND DATA MININGPurpose of data warehouseBenefits of Data warehousePowerPoint PresentationSlide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17CS 8630 Database Administration, Dr. GuimaraesDATA WAREHOUSING AND DATA MINING MARK VROOMAN AND AMINAT ONIPROJECT REPORT KENNESAW STATE UNIVERSITYDEPARTMENT OF COMPUTER SCIENCECS8630- DATABASE ADMINISTRATIONCS 8630 Database Administration, Dr. GuimaraesPurpose of data warehouse–Beazerhomes is a builder that has properties all over the United States. They have been building homes and apartments since 1995 and they feel the need to have a data warehouse to help record and store what the selling and renting trends and patterns for their buildings are and have been. This will help top management make decisions such as where to build more properties, which properties to sell and which to rent out, what incentives make their buildings high sellers, and what prices to put on the buildings to maximize profit and at the same time, encourage buyers. –The data warehouse will have top management access that will include all departments, middle management access that will cover areas they manage, and staff access for their individual sections of work. It will also allow for simple queries on past and present data.CS 8630 Database Administration, Dr. GuimaraesBenefits of Data warehouse•All the data from all the OLTP systems can be integrated and made consistent for easier query.•Data present in the data warehouse cannot be modified, but can only be updated and deleted. This ensures that the data gotten straight from the OLTP systems are the same ones used in business analysis.•Data stored in a data warehouse contains more detailed information on a more historical basis. This makes for a better analysis of home-buying and selling trends and patterns.CS 8630 Database Administration, Dr. Guimaraes - Dimensional modeling is a technique used in describing the database component of a data warehouse. It organizes data in a form that corresponds to the way users query data. A dimensional model includes one more fact tables and a series of smaller tables called dimension tables. Different schemas can be used for dimensional modeling. These are star schema, snowflake schema, and starflake schema. In a star schema, the fact table is surrounded by dimension tables containing reference data that can be denormalized. It can also be described as each dimension table being linked to the primary key fact table. - The data warehouse design chosen by Beazerhomes uses the star schema for its dimensional modeling. The fact tables; Lease, PropertySale, PropertyMaintenance, PropertyViewing, and Advert contain the primary keys for the dimension tablesData warehouse designCS 8630 Database Administration, Dr. GuimaraesDimension and fact tablesCS 8630 Database Administration, Dr. GuimaraesOLTP System- The OLTP (Online Transaction processing) system is the daily processing database that contains data used daily, on a short term basis. The data from the OLTP is what is stored in the data warehouse. The “DreamHome” contains tables from the OLTP for BeazerHomes and shows information on how each property is sold or rented. The diagram below contains the fact and dimension tables for the OLTP system, “DreamHome”:CS 8630 Database Administration, Dr. GuimaraesCS 8630 Database Administration, Dr. GuimaraesData warehouse-The data warehouse contains historical and long-term information from the OLTP system. -The data will be loaded via an operational datastore. The data will be extracted from OLTP database by the operational datastore after the end of business hours on Friday (assuming a standard business week). The data will then be cleansed and its integrity will be verified. The data will then be loaded into the data warehouse by the operational datastore via the warehouse manager. Prior to the loading of the data, an incremental backup of the data warehouse will be performed. This will ensure a good rollback point if issues occur with the data loading. By carrying these activities out during non-business hours, there will be increased availability of resources and will not slow down operations during normal hours. This also allows time for a recovery if necessary.CS 8630 Database Administration, Dr. GuimaraesCS 8630 Database Administration, Dr. GuimaraesE-R DiagramCS 8630 Database Administration, Dr. GuimaraesE-R DescriptionThe above diagram is the entity-relationship diagram for the data warehouse, that shows how each dimension or fact table interacts with the other tables. These are described as follows:-Promotion uses Advert displayed in the Newspaper, which recommends PropertyForRent and promotes PropertyForSale. - Advert describes PropertyForSale. - ClientRenter holds Lease and seeks PropertyViewing.- Branch registers ClientRenter and offers PropertyForRent, sells PropertyForSale, has Staff, and contacts ClientBuyer.- ClientBuyer requests PropertyViewing and agrees to PropertySale. - PropertySale is for PropertyForSale.- PropertyForRent is placed in Advert and takes PropertyViewing. It is also leased via Lease and requires PropertyMaintenance.- Staff oversees PropertyForRent, manages PropertyForSale, and attends to PropertyMaintenance.CS 8630 Database Administration, Dr. Guimaraes- Owner owns provides (rent to) PropertyForRent or owns PropertyForSale - PropertyViewing views PropertyForSale.CS 8630 Database Administration, Dr. GuimaraesSAMPLE QUERIESSELECT COUNT (timeID)FROM PropertySaleWhere timeID = (SELECT (timeID) FROM Time WHERE year BETWEEN ‘2001’ AND ‘2003’); This query will provide a count of the number of properties that were sold between the beginning of 2001 and the end of 2003. The BETWEEN command includes the endpoints of the range.CS 8630 Database Administration, Dr. GuimaraesSELECT SUM (sellingPrice) AS TotalRevenueFROM PropertySaleWhere timeID = (SELECT (timeID) FROM Time WHERE year BETWEEN ‘2001’ AND ‘2003’); This query will give the total of sales (TotalRevenue) for the dates from the beginning of 2001 to the end of 2003.SAMPLE QUERIES, CONTD.CS 8630 Database Administration, Dr. GuimaraesData DictionaryThe data dictionary is a catalog of data files in the data warehouse. It also contains the characteristics of each file and is only used for the management of the files. An excerpt from Beazerhomes’ data warehouse can be seen below. Although a data