1 / 40

Business Intelligence

Business Intelligence. INVENTORY CASE STUDY. Lecturer: Richard de Koning Students: Attila Gönczi Svetoslav Todorov Todor Yakimov Arya Nawing. Overview. Introduction Some Common Questions related to Inventory Value Chain BI Inventory models Periodic Snapshots

aleda
Download Presentation

Business Intelligence

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. Business Intelligence INVENTORY CASE STUDY Lecturer: Richard de Koning Students: Attila Gönczi Svetoslav Todorov Todor Yakimov Arya Nawing

  2. Overview • Introduction • Some Common Questions related to Inventory • Value Chain • BI Inventory models • Periodic Snapshots • Simple Periodic Snapshot • Enhanced Periodic Snapshot • Inventory Transactions • Inventory Accumulating Snapshot • Fact Table Type Comparison • Aggregate Function types in Analysis services 2008 • Value Chain Integration • Data Warehouse Bus Architecture • Kimball vs. Inmon • Bus Matrix • Conformed Dimensions • Cube demo • Recommended readings • Questions

  3. Introduction • Optimized inventory levels in stores can have a major impact on chain profitability: • minimize out-of-stocks • reduce overall inventory carrying costs

  4. Some Common Questions related to Inventory • How did the inventory level changed per product, per warehouse over time? • How is the profitability of products in our inventory? • How many times have we placed a product into an inventory bin on the same day we picked the product from the same bin at a different time? • How many separate shipments did we receive from a given vendor, and when did we get them? • On which products have we had more than one round of inspection failures that caused return of the product to the vendor? • … etc.  BI helps answering these questions

  5. Value chain example We will examine this in our Analysis Services project Value chain Products pass through the value chain in order, and gain value. • What is the primary objective of most analytic decision support systems ? •  monitor the performance results of key business processes • each business process produces unique metrics at unique time intervals with unique granularity and dimensionality • each process typically spawns one or more fact tables • value chain provides high-level insight into the overall enterprisedata warehouse

  6. BI Inventory Models • The three main models to be discussed: • Inventory Periodic Snapshot • Inventory Transactions • Inventory Accumulating Snapshot They are complementary models, and provide different information about the Inventory

  7. Periodic Snapshot The most common inventory scheme Example of Retail Store Chain Inventory: • The assumed atomic level of detail is: • Inventory • per product, • per day, • per store Fact: Inventory Basic dimensions: Product Day Store

  8. Simple Inventory Periodic Snapshot Usage: Provide information about inventory levels: Daily Inventory level Average Inventory level over a time period • Problems: • Inventory levels are semi-additive (i.e. NOT additive through each dimension) •  Through the Date dimension the quantity on hand is NOT additive • Historical Inventory data using daily granularity results in unreasonably huge amount of data over time •  Suggestion: define distinct atomic time period for short and long term measures

  9. Enhanced Inventory Periodic Snapshot Velocity of inventory movement becomes measurable Key concepts: • Number of Turns • Number of days’ supply • Growth Margin Return on Inventory (GMROI) Extra recorded facts

  10. Enhanced Inventory Periodic Snapshot Extra recorded facts

  11. Enhanced Inventory Periodic Snapshot GMROI - Growth Margin Return on Inventory • GMROI is a standard metric used by inventory analysts to judge a company’s quality of investment in its inventory. • We do not store GMROI in the fact table because it is not additive!!!

  12. Inventory Transactions Record every transaction that affects inventory:

  13. Inventory Transactions • Use: Measure the frequency and timing of specific transaction types • Example: • How many times have we placed a product into an inventory bin on the same day we picked the product from the same bin at a different time? • How many separate shipments did we receive from a given vendor, and when did we get them? • On which products have we had more than one round of inspection failures that caused return of the product to the vendor?

  14. Inventory Accumulating Snapshot • IN • Record disposition of product since it arrive in the warehouse until it has left the warehouse • Single row fact table track many different kind of date such as: • Date received • Date inspected • Date placed in inventory • Date authorized to sell • Date picked • Date boxed • Date shipped • etc • Only possible if we can reliably distinguish products delivered in one shipment from those delivered at a later time • Appropriate to track disposition at very detailed level Received Inspected Placed in inventory Authorized to sell Inventory Picked Boxed Shipped • OUT

  15. Inventory Accumulating Snapshot • Philosophy : • To provide updated status of the product shipment as it moves through milestone such as receiving, inspection, bin placement, authorization to sell, picking, boxing, and shipping

  16. Fact Table Type Comparison

  17. Notes CH6Aggregate Function Types in Analysis Services 2008: • Additive • SUM • COUNT • Pseudo-additive • MIN • MAX • Non-additive • DistinctCount • None • Semi-additive • FirstChild • LastChild • FirstNonEmpty • LastNonEmpty • AverageOfChildren • ByAccount The functions can be used as a out-of-the-box functions in Analysis services in creating measures

  18. Value Chain Integration • Integrating business processes together benefits: • Intelligence aspects: • Better understand customer relationships from an end-to-end perspective • Observe information across business processes • Technological aspects: • Reusability • Less resources used • Question: How do we properly integrate all business processes in the enterprise? • Answer: Data Warehouse Architecture

  19. Data Warehouse Bus Architecture • Bus: • “Common structure to which everything can and is connected” • Data Warehouse Bus Architecture: • Defining a standard warehouse architecture (bus interface) to which different data marts can connect. • Standardizes dimensions and facts that have uniform interpretation across the enterprise. • Architectural framework for the overall design and separate data marts following the framework.

  20. Data Warehouse Architecture Kimball vs. Inmon • Bill Inmon and Ralph Kimball – the co-founders of the data warehouse concept and their views on data warehouse architecture • Dependent Data Mart Structure (Inmon) • Let everyone build what and when they want and we will integrate it if we need it. • Each data mart gets information from the operational data base and then data is loaded in the data warehouse • Data Warehouse Bus Structure (Kimball) • Design everything then build. • The data warehouse is responsible for loading data in the data marts from the operational database.

  21. Bus Matrix • The tool we use to document the Data Warehouse Bus Architecture • A part technical, part management, part communication tool • Business processes as ROWS • Common dimensions as Columns

  22. Bus Matrix (cont.) • Rows : • Business processes • A business process translates into a First-Level Data Mart • Each Data Mart spanning over multiple business processes translates into a Consolidated Data Mart (E.G. Profitability) • Columns: • Common Dimension used across the enterprise • Consequences of improper or non-existent bus matrix: • Isolated data marts blocking the coherent warehouse environment, narrowing down the scope of information to be viewed. • Expansion of the data warehouse is nearly impossible

  23. Conformed Dimensions • What are conformed dimensions: • The cornerstone of the Bus Architecture • A single, coherent view of data across the enterprise that can be reused across different Data Marts. • Conformed dimensions have: • Consistent dimension keys • Consistent attribute values • Consistent naming, attribute definitions.

  24. Conformed dimensions (cont.) • Some characteristics of conformed dimensions • Each conformed dimension has the same meaning in each Data Mart • They are defined at the most granular level possible

  25. Conformed dimensions (cont.) • Some considerations when defining conformed dimensions • Rolled-up dimensions • Rolled-up dimensions – having higher level of granularity • Rolled-up dimensions conform to the base-level atomic dimension if they are a strict subset of that dimension

  26. Conformed dimensions (cont.) - Considerations (cont.) • Dimension subsetting • Two dimensions with same level of detail but representing different subsets of rows or columns • Rolled-up dimensions are another example of dimension subsetting • Advised Solution – dimension authority • Has responsibility for defining, maintaining and publishing dimensions and their subsets to all Data Marts

  27. Conformed Facts • Conformed facts are: • Facts used living in more that one data mart. • Same rules and characteristics apply in designing and implementing them as with conformed dimensions • Few more considerations are: • Units of measure for the fact • Identical labeling • Underlying definitions and equations

  28. Cube demo • Data Source specifications • The Data Source wizard

  29. Cube demo • Data Source View specifications • The Data Source View wizard

  30. Cube demo • Implementing named calculations • Creating a user friendly date

  31. Cube demo • Implementing named queries

  32. Cube demo • Creating dimensions with the Dimension wizard • The dimensions wizard

  33. Cube demo • Dimensions design view

  34. Cube demo • Dimensions design view

  35. Cube demo • The cube wizard

  36. Cube demo • The cube wizard

  37. Cube demo • Cube design view

  38. Recommended readings • About.com | Investing for beginners | Investing Lesson 4: Income Statement Analysis Online guide for calculating different business measures • About.com | Inventory Turns / Inventory Turnover • Prenhall.com | Online glossary • Wikipedia definition of GMROII • Official website of the Kimball Group • W. H. Inmon - Building the Data Warehouse, Fourth Edition ISBN-13: 978-0-7645-9944-6 | ISBN-10: 0-7645-9944-5 • Ralph Kimball & Mary Ross - Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition) ISBN100471200247 | ISBN13978047120024 • Scott Cameron – Step by Step Microsoft SQL Server 2008 Analysis Services ISBN100735626200 | ISBN139780735626201 • Chris Webb, Alberto Ferrari, Marco Russo - Expert Cube Development with Microsoft SQL Server 2008 Analysis Services ISBN 978-1-847197-22-1

  39. Questions

  40. End of Presentation INVENTORY CASE STUDY

More Related