data warehouse n.
Skip this Video
Loading SlideShow in 5 Seconds..
Data Warehouse PowerPoint Presentation
Download Presentation
Data Warehouse

Data Warehouse

18 Vues Download Presentation
Télécharger la présentation

Data Warehouse

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Data Warehouse

  2. Design DataWarehouse

  3. Key Design Considerations • it is important to consider the intended purpose of the data warehouse or business intelligence solution in order to ensure the design aligns to business requirements.

  4. Business Scenario • An electronics manufacturer has recently acquired a competitor to extend its existing product line. • The acquisition has provided them with an established customer base in a new geographic region that was considered for development. • The manufacturer plans future acquisitions to strengthen the current portfolio and enable growth. • While the acquisition presents new opportunities for expansion and growth, competition across existing product lines and higher raw material costs have limited their ability to expand. • In addition, the manufacturer has encountered several internal challenges that have limited growth.

  5. The Challenges • Latency and inaccuracies in receiving sales information. • Inability to determine profitability by product and product line. • Poor quality of information. • Difficulty in forecasting sales leading to higher inventory carrying costs. • Inaccuracies in calculating sales commissions. • Needed information regarding sales, inventory levels, and manufacturing commitments to raw materials for product managers. • Use of spreadsheets in analysis of sales, manufacturing, and finance data extracted from ERP systems.

  6. The Challenges • Difficulty in identifying root cause of manufacturing issues due to the inability to access supporting detailed data. • Inability to accurately predict the most marketable bundles of products that maximize profits, utilize excess manufacturing capacity, and reduce inventory levels. • Difficulty in satisfying the reporting needs of diverse user communities due to multiple tools, inconsistent definitions, and complexities encountered in finding and understanding data.

  7. The Proposed Solution • The proposed solution will be used by sales, finance, and manufacturing to answer the most significant queries • Sales analysis: • What are the sales by quarter, sales representative, and geography? • How are sales trending to industry forecasts? • How do sales compare in the Northeast to Southwest?

  8. The Proposed Solution • Product profitability: • Which product lines are the highest revenue producers this year? • Which products and product lines are the most profitable this quarter? • Which product lines are above seasonal forecasts? • Sales representative analysis: • Who are the top five sales representatives by sales volume? • Who are the most productive sales representatives in divisions, regions, and territories? • Which sales divisions, regions, and territories generate the highest revenues and margins?

  9. The Proposed Solution • Customer analysis: • Who are the best customers? • Who are the most profitable customers? • What percentage of sales is generated from the top five customers? • Which customers purchase the most products by product line? • Which industry has experienced the fastest growth over last year?

  10. Normalized Design

  11. Dimensional Design • Dimensional design is a denormalization technique used in providing an intuitive view of historically correct information that corresponds to the needs of users. • Commonly referred to as star schema design, dimensional designs are subject-oriented structures comprised of fact tables and dimension tables.

  12. Dimensional Design • Fact tables represent processes, events, or activities that are used in measuring a business. • A fact table is comprised of two types of columns: foreign key references to dimension tables and to measures. • Foreign key columns are provided to join the fact table to dimension tables that enable the filtering and constraining of data.

  13. Dimensional Design • Dimension tables contain attributes and hierarchies that enable a logical grouping and constraining of data. • Dimension attributes are normally descriptive and represent details about the dimension. • Hierarchies within a dimension provide a natural summarization of information ranging from the lowest level of detail to the highest summary level. • The most common hierarchy in dimensional designs is the time hierarchy that provides multiple levels that typically range from day, week, and month to year.

  14. Dimensional Design • Measures or metrics represent the information that is to be measured about a business process or event. • Measures represent information that is specific about one or more dimensional values. • In this example, the measures captured are quantity, sales, profit, and commission.

  15. Dimensional Design

  16. Dimensional Design

  17. Dimensional Design

  18. Benefit from a Hybrid Design • Flexibility to extend • Ease of use • Subject-area centric • Quality initiatives

  19. Online Analytical Processing Design • The Oracle OLAP engine uses what is called an analytic workspace as a multidimensional data structure to support analytic processing. • When constructing our OLAP design, the analytic workspace is built by logically defining dimensions, levels, hierarchies, attributes, cubes, measures, and mappings prior to physically loading data from a relational source.

  20. Online Analytical Processing Design • The OLAP cube provides a logical multidimensional representation used to organize metrics that share the same relationships for analysis. • The edges of a cube represent the dimensions used for filtering and constraining while the body of the cube contains one or more metrics or measures used in measuring a business process or event. • In our example, we created a sales cube that is dimensioned by time, customer, product, and sales rep in a manner similar to the dimensional model.