1 / 28

OLAP applications

OLAP applications. Application areas. OLAP most commonly used in the financial and marketing areas Data rich industries have been the most typical users consumer goods, retail, financial services and transport. General vs. Pre-built OLAP. In increasing number of cases

tadeo
Télécharger la présentation

OLAP applications

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. OLAP applications

  2. Application areas • OLAP most commonly used in the financial and marketing areas • Data rich industries have been the most typical users • consumer goods, • retail, • financial services and • transport

  3. General vs. Pre-built OLAP • In increasing number of cases • specialist OLAP applications (pre-built) • In most others • general-purpose OLAP tool • versatile enough for many applications

  4. General vs. Pre-built OLAP • Better to have a general-purpose product for multiple applications • Some applications (financial reporting) are complex • better to use a pre-built application

  5. Marketing and sales analysis • Most commercial companies require this application • However, large-scale versions of this application occur in three industries: • Consumer goods industries • Retailers • Financial services

  6. Consumer goods industries • Large numbers of products • Analyze data monthly, but sometimes it may go down to weekly • There are usually a number of dimensions, none especially large

  7. Retailers • Due to POS data, huge amounts of data • Large retailers could have over 100,000 products (SKUs) and hundreds of branches • Down to weekly or daily level • Even track sales by time of day

  8. Financial services(insurance, banks etc) • A relatively new user of OLAP technology for sales analysis • Analyzing data down to individual customer level • which means that the largest dimension may have millions of members • Because of the need to monitor a wide variety of risk factors, there may be large numbers of attributes and dimensions

  9. Questions that are answered in marketing and sales analysis • Are we on target to achieve the month-end goals, by product and by region? • Have some new products failed to achieve their expected penetration, and should they be withdrawn? • Is our advertising budget properly allocated? Do we see a rise in sales for products and in areas where we run campaigns? • Is there a correlation between promotions and sales growth? • Are all areas achieving the expected product mix, or are some groups failing to sell some otherwise popular products?

  10. A marketing example • In response to a sudden management panic near a quarter end, a marketing analyst is given a few minutes to analyze the market acceptance of new products • She decides to group 20 products that were introduced between six and nine months ago and compare their sales with a comparable group of 50 products introduced between two and three years ago

  11. A marketing example (cont.) • She simply defines two new, on-the-fly, product groupings and creates a ratio of the new group to the older group • She can then track this ratio of sales revenue or volume by any level of location, over time, by customer sector or by sales group • Defining the new groupings and the ratio takes a couple of minutes, and any of the analyses take a matter of a few seconds to generate, even though the database has tens of thousands of products and hundreds of locations • It doesn’t take more than a total of 15 minutes to spot that some regions have not accepted the new products as fast as others.

  12. A marketing example (cont.) • Then, she investigates whether this was because of inadequate promotion, unsuitability of the new products, or if some areas always accept new products more slowly • She looks at other new product introductions by creating new groupings of products of different ages, and finds that the same areas are always conservative when introducing expensive new products • Management can now decide if there really is a problem, what it is and what to do about solving it

  13. Exercise 1 Inventory • Value chain for retailers: • The retailer issues an order to a product manufacturer • Products are delivered to retailer’s warehouse (actual) and held in inventory • Delivery is made to a store, where again the product sits in inventory until it is purchased

  14. Exercise 1 Inventory models • Periodic snapshot: • Every day we measure the inventory levels of each product and place them as separate rows in a fact table • Transactions: • Record every transaction that has an impact on inventory levels as products move through the warehouse Each model tells a different story

  15. Exercise 1 Periodic snapshot • Make sure that the right product is in the right store at the right time (minimize out-of-stock) • 4 steps • Business process: analyze inventory at individual stores • Granularity: daily inventory by product at each store • Dimensions: ? • Facts: ?

  16. Exercise 1 Periodic snapshot • Aggregation over date dimension: For a given item we have • Mon: 50 items • Tue: 50 items (unchanged) • Wed: 100 items (+50 items) • Thu: 100 items (unchanged) • Fri: 100 items (unchanged) • Week balance is not 400 (50+50+100+100+100) • Inventory levels are not additive over the date dimension • Propose a solution

  17. Exercise 1 Periodic snapshot • 60,000 products • 100 stores • 6,000,000 rows in fact table each day • 14 bytes per row • 84 MB per day • 30 GB per year • Propose solutions (hint: aggregate historical data)

  18. Exercise 1 Transactions • Record every transaction to answer questions like: • Which products were returned to the vendor due to inspection failure? • How many separate shipments did we receive from a given vendor? • 4 steps • Business process: analyze inventory transactions at each warehouse • Granularity: daily transactions by product at each warehouse and by each vendor • Dimensions: ? • Facts: ?

  19. Exercise 1 Transactions • Transaction types: • Receive product • Place product into inspection hold • Release product from inspection hold • Return product to vendor due to inspection failure • Authorize product for sale • Package product for shipment • Ship product to customer • Return product to inventory from customer return • Remove product from inventory

  20. Exercise 1 Comparison • Compare the Inventory model against the Transactions model • What are the differences? • Which kind of information gives the one but not the other? • How they complement each other?

  21. Exercise 2 Financial services • A bank offers products (called accounts): • checking accounts, savings accounts, mortgage loans, personal loans, credit cards, safe deposit boxes, etc. • Business process: effective market by offering additional products to households • Preferable to analyze bank’s relationship with an entire economic unit, like a household

  22. Exercise 2 Requirements • Business users want to see historical monthly snapshot data on every account • Every account has a primary balance • Each account has only one household, branch, and product associated with • A household may be comprised of several accounts and individual account holders. E.g., John and Mary Smith are a single household, but John has a checking account, Mary has a savings account, and together they have a credit card • All these accounts/products are considered to be part of the Smiths’ household • Additionally to household identification, we are interested in demographic information about customers and households (income, children, household, etc.)

  23. Exercise 2 Granularity • Record the primary balances of every account at the end of each month • Are they additive w.r.t. time? • Record other metrics as well: interest paid, interest charged, etc. • Dimensions? • Facts?

  24. Financial reporting • Every organization has responsibilities for producing financial reports for internal (management) consumption • Even the simplest financial consolidation consists of at least three dimensions • It must have a chart of facts (measures) • at least one organization structure plus time

  25. Exercise 3 General Ledger (G/L) • Core foundation of financial systems. Ties together information collected by: • Purchasing • Payables (what you owe to others) • Receivables (what others owe you) • At the end of each fiscal period (may not relate directly to a time period)

  26. Exercise 3 General Ledger (G/L) • Business process: perform G/L by recording balance amounts as periodic snapshots • Grain: one record per fiscal period • Main source of information: chart of accounts • Identify the account type. E.g., 1,000 – 1,999 asset accounts, 2,000-2,999 liabilities (intelligent keys, do we need them in the warehouse?) • Each account is associated with organizational cost centers (hierarchy: cost center, department, division) • Each account is associated with a set of books • For every organizational cost center in an account, there is a single book

  27. Exercise 3 General Ledger (G/L) • Dimensions? • Facts: are balance amounts additive?

  28. All exercises • Report in each design three questions that can be answered (scenarios)

More Related