1 / 22

Data Warehousing

Data Warehousing. ISYS 650. What is a data warehouse?. A data warehouse is a subject-oriented, integrated, nonvolatile, time-variant collection of data in support of management’s decision.

samson
Download Presentation

Data Warehousing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Warehousing ISYS 650

  2. What is a data warehouse? • A data warehouse is a subject-oriented, integrated, nonvolatile, time-variant collection of data in support of management’s decision. • Subject-oriented: data is organized around major subjects of the enterprise, such as sales, rather than individual transactions, and is oriented to decision making. • Integrated: the same piece of information collected from various systems is referred to in only one way. • Example: Gender: M, F; Male, Female; Sex: 0, 1 • Nonvolatile: Data is loaded into a data warehouse on a scheduled basis. • Time-variant: Historical data to support time-series and trend analysis.

  3. What is a Data Warehouse? • A physical repository where relational data are specially organized to provide enterprise-wide, cleansed data in a standardized format • “The data warehouse is a collection of integrated, subject-oriented databases designed to support DSS functions, where each unit of data is non-volatile and relevant to some moment in time”

  4. Need for Data Warehousing • Separation of operational and informational systems and data for improved performance.

  5. Types of Data in a DW • Current detailed data: consistent at the time the data is extracted from the transaction system. • Old detailed data: need to be archived. • Summarized data • Metadata: • A directory of what is in the warehouse. • A guide to mapping data from transaction database to data warehouse

  6. Data Mart A departmental data warehouse that stores only relevant data • Dependent data mart A subset that is created directly from a data warehouse • Independent data mart A small data warehouse designed for a strategic business unit or a department

  7. DW Framework

  8. Data Integration and the Extraction, Transformation, and Load (ETL) Process Extraction, transformation, and load (ETL)

  9. Representation of Data in DW • Dimensional Modeling – a retrieval-based system that supports high-volume query access • Star schema – the most commonly used and the simplest style of dimensional modeling • Contain a fact table surrounded by and connected to several dimension tables • Fact table contains the descriptive attributes (numerical values) needed to perform decision analysis and query reporting • Dimension tables contain classification and aggregation information about the values in the fact table • Snowflakes schema – an extension of star schema where the diagram resembles a snowflake in shape

  10. Multidimensionality • Multidimensionality The ability to organize, present, and analyze data by several dimensions, such as sales by region, by product, by salesperson, and by time (four dimensions) • Multidimensional presentation • Dimensions: products, salespeople, market segments, business units, geographical locations, distribution channels, country, or industry • Measures: money, sales volume, head count, inventory profit, actual versus forecast • Time: daily, weekly, monthly, quarterly, or yearly

  11. Example: Northwind Database

  12. Examples of Sales Analysis • Total sales by Product • Sales related to Customer: • Location: Sales by City, Country • Sales related to Time: • Quarterly, monthly, yearly Sales • Sales related to Employee:

  13. Analyze Sales DataDetailed Business Data • Total sales: • Amount of each detail line: Quantity*UnitPrice*Discount • Sum (Quantity*UnitPrice*Discount) • Total quantity sold: • Sum(Quantity) • Detailed business data: • Quantity*UnitPrice*Discount • Quantity

  14. Dimensions for Data Analysis:Factors relevant to the detailed business data • Analyze sales by: • Product, product category • Location: City, State, Country • Time: • Quarterly, yearly sales • Employee: • And combinations of these dimensions: • Ex: Product and Location, Product and Time

  15. Data Warehouse Design- Star Schema - • Dimension tables • contain descriptions about the subjects of the business such ascustomers, employees, locations, products, time periods, etc. • Fact table • contain detailed business data with links to dimension tables.

  16. Define Product Dimension • Product Table: • ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued • Product dimension table: • ProductID, ProductName, CategoryID

  17. Define Employee Dimenstion • Employees Table: EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, etc. • Employee Dimension: • EmployeeID, FullName, Title, EmpCity

  18. Define Location Dimension • Customers table: • CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax • Location dimension: • LocationCode, City, Country • Define Location Code: This is an artificial code created to link detailed business data with the city and country. • In the Northwind database, I used the Make Table query to create a Location table from the Customers table with City and Country fields. Then I used the Customers table’s design view to add a LocationCode field with the Auto Number data type.

  19. Define Period Dimension • Period: • In the Orders table: OrderDate • In the data warehouse we define Period to be: PeriodCode, Year, Quarter • OrderDate: 04-Jul-1996 -> 1996, 3, 7 • OrderDate: 20-Dec-1996 -> 1996, 4, 12 • In Access: Create view based on Orders table • Year:Year(OrderDate); Month:Month(OrderDate) • Quarter: • Quarter: IIf([month]<=3,1,IIf([month]<=6,2,IIf([month]<=9,3,4))) • Define Period Code: • PeriodCode:Cstr(Year) + Cstr(Quarter) • 1996, 3, 7 -> 19963 • 1996, 4, 12 -> 19964

  20. Star Schema Employee Dimension EmployeeID FullName Title EmpCity Location Dimension LocationCode City Country FactTable LocationCode PeriodCode EmployeeID ProductID Qty Amount Period Dimension PeriodCode Year Quarter Product Dimension ProductID ProductName CategoryID

  21. A Query to retrieve data for Fact Table

  22. Transfer Data Between Access Databases • Create/Query/Design View • 1. Create the query with the data to transfer • 2. Click Make Table button • Make table in the same database • Make table in other database • 3. Click Run

More Related