OLAP Theory-English version On-Line Analytical processing (Buisness Intelligence) Ing.Skorkovský,CSc Department of Corporate Economy Faculty of Economics and Administration Masaryk Uinverzity Brno Czech Republic Agenda l lThe Market lWhy OLAP lIntroduction to OLAP lOLAP Terms and Concepts l Summary OLAP market size OLAPmarketSize Projections are always subject to change, but this chart shows the growth in the OLAP market in 1994-99, and the expected trend for the next three years. Even with the predicted fall in the percentage revenue growth rate (mainly due to falling prices), the market is very likely to approach $4bn by 2002. OLAP demand is mushrooming as technology prices fall and all the major database vendors will be actively promoting what had been, until recently, a niche technology. Source : The OLAP Report Why OLAP lThe Right Information In The Right Place At The Right Time lWhy –More self-sufficient Business users –Keep the integrity of the data –Reduces the query drag(burden) and network traffic –Organization can respond more quickly to market demands In a business environment characterized by fierce competition and rapid change, you need to make decisions quickly and on the basis of reliable facts and figures. But what if that information is incomplete? Or simply unavailable? What happens if executives and knowledge workers are left stranded in an ivory tower, unable to obtain the answers to pressing questions on customers, products, profitability and potential new sources of revenue The information you need is somewhere within your organization, but it is often locked away in operational applications, and difficult to obtain. And when you do get hold of the statistics you need, it is often not in the form you require, and you have little scope to explore that information in more detail to identify the precise cause of a sudden surge in costs or the reasons for the unexpected popularity of a particular product in a particular region. The recognized technical concept for meeting this challenge is OLAP. OLAP is a separate environment with a dedicated database drawing on diverse data sources and designed to support queries and analysis. The first wave of data warehouses has encountered a number of difficulties, including technical integration and laborious and lengthy implementation. One major obstacle has been gearing the warehouse to the business processes of the user organization. Online analytical processing (OLAP) is an increasingly popular technology that can dramatically improve business analysis, but that has been characterized historically by expensive tools, difficult implementation, and inflexible deployment. Damgaard has tackled the OLAP problem and created a solution that makes multidimensional analysis accessible to a broader audience and potentially at a significantly lower cost of ownership. Introduction to OLAP l l“OLAP enables analysts, managers, and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information. OLAP transforms raw data so that it reflects the real dimensionality of the enterprise as understood by the user. “ l The OLAP Counsil defenition •The need of end users to analyze corporate data for the purpose of making better decisions is of paramount importance. •OLAP technologies are essential to delivering this end-user value and are a critical component of a broader information technology architecture. •Corporate data needs to be transformed into multidimensional aggregations supporting enterprise-scale data volumes and hierarchies. •Fast, consistent response to end-user requests is critical to interactive, ad-hoc exploration, comparison and analysis of data, regardless of database size and complexity. •End users must be able to manipulate and derive data for analysis purposes by applying analytical operations such as ratios, cumulative totals, trends and allocations across dimensions and across hierarchical levels. •An OLAP Server may either physically stage the processed multidimensional information or populate its data structures in real-time from relational, OLTP or other databases, as long as it delivers consistent and rapid response to end users. Introduction to OLAP lUsers –Analysts, managers and executive managers lAccess –Fast consistent, interactive –Wide variety of possible views lTransformation –Raw data –Real dimensionality of enterprise l Most information in a relational data warehouse is not easily browsed. Often the data structures are difficult for the end user to comprehend, or the questions (such as “Who are the top sales people in each region for the last year by month?”) are complex when expressed in SQL. Some challenges can be addressed with advanced query tools, which hide the database complexity from the end user. However, for a large class of applications where the end user is viewing multidimensional data, Microsoft believes the optimal solution is OLAP technology. All organizations have multidimensional data, and complexity is not necessarily a function of company size. Even the smallest organization would like to track sales by product, salesperson, geography, customer, and time. Organizations have long sought tools to access, navigate, and analyze multidimensional data in an easy, natural way. OLAP accelerates the delivery of information to end users viewing these multidimensional structures by preparing some computed values in the data in advance, rather than at execution time. The combination of easy navigation and fast performance allows end users to view and analyze data more quickly and efficiently than is possible with relational database technology only. The end result is more time spent analyzing data and less time analyzing databases. Introduction to OLAP lOrganizational functions –Finance lBudgeting lPerformance analysis –Sales lSales analysis and forecasting –Marketing lMarket research analysis lMarket/customer segmentation –Purchase lCost of materials –Production lCost of conversion –Distribution lCost of shipping –etc Relational database Multidimensional database OLAP Terms and Concepts lRelational database l lMultidimensional database Relational Schemas and Cubes The most common way of managing relational data for multidimensional use is with a star schema. A star schema consists of a single fact table that is joined to a number of dimension tables. The fact table contains the numeric data that corresponds to the measures of a cube. Dimension table columns, as their name implies, map to the hierarchical levels in a dimension. Note: A star schema is not required in order to create a cube. You can also use a snowflake schema, or even a single table schema. Olap statement lUnlike data warehouses, OLAP cube includes pre-processed data aggregations according to defined hierarchical dimension structures and their combinations. l l Dimensions and its values Analysis by dimensions Data pump Primary Transaction System (ERP,CRM,..) Data warehouse Data pump Data pump (critical application area) Primary Transaction system ERP,CRM Data warehouse ETL Item entries Value entries G/L entries Customer Ledger Entries ETL 2002 (4)->2007 (2) 2006 (1)->2006 (3) časový filtr-dimenze čas Částečná změna struktury dat a případně další aplikace dimenzí (oblast, typ zákazníka,.. ) Data test correct?) IF NOT OK THEN correction feedback=TRUE Data pump = Extraction Transformation and Loading = ETL MS Dynamics NAV Relationships Sales Line Order Location List Location card Bins Tab Bins List Bin Contents 11.11.2019 Relations among tables I TheArtOfNav_BigPictureOfNAV_Overview 11.11.2019 Relations among tables II TheArtOfNav_BigPictureOfNAV_Overview 11.11.2019 One table and its relations I 11.11.2019 One table (Sales Line) and its relations II ERP NAV MS Dynamics NAV Analysis by Dimensions OLAP Terms and Concepts In an OLAP data model, information is conceptually viewed as cubes, which consist of descriptive categories (dimensions) and quantitative values (measures). The multidimensional data model makes it simple for users to formulate complex queries, arrange data on a report, switch from summary to detail data, and filter or slice data into meaningful subsets. For example, typical dimensions in a cube containing sales information would include time, geography, product, channel, organization, and scenario (budget or actual). Typical measures would include dollar sales, unit sales, inventory, headcount, income, and expense. Within each dimension of an OLAP data model, data can be organized into a hierarchy that represents levels of detail on the data. For example, within the time dimension, you may have the levels years, months, and days; similarly, within the geography dimension, you may have the levels country, region, state/province, and city. A particular instance of the OLAP data model would have the specific values for each level in the hierarchy. A user viewing OLAP data will move up or down between levels to see more or less detailed information. OLAP Terms and Concepts lCube –Information Is conceptually viewed as cubes. OLAP Terms and Concepts lCube –Information Is conceptually viewed as cubes. lDimension –Distinct categories for business data. OLAP Terms and Concepts lCube –Information Is conceptually viewed as cubes. lDimension –Distinct categories for business data. lHierarchy –Levels of details on the data. OLAP Terms and Concepts lCube –Information Is conceptually viewed as cubes. lDimension –Distinct categories for business data. lHierarchy –Levels of details on the data. OLAP Terms and Concepts I. lCube –Information Is conceptually viewed as cubes. lDimension –Distinct categories for business data. lHierarchy –Levels of details on the data. lMeasure –Quantitative values. OLAP Terms and Concepts II. Cube Information Is conceptually viewed as cubes. Dimension Distinct categories for business data. Hierarchy Levels of details on the data. Measure Quantitative values. OLAP Cube Reporting (NAV tools or JETs) lReporting 28 Main principles (source tables and their entries) lMain principles 29 Customer Vendor Item Account Dimension Control parameters (time, type of products, Costs, Revenue, Area,..) Sales analysis NAv 2016w1-part of CRM lSales analysis cycle- part of CRM lWorking Capital JETs * Working capital – Show of the results from JETs 31 Some chosen analysis asked by CFO of company X in Czech Republic Some chosen analysis asked by CFO of company X in Czech Republic lInventory Dashboard 32 Some chosen analysis examples (JETs) lAnalysis examples 33 On-line Transaction Processing and OLAP lOnly for information – will be not used for exams Terminoly - metadata * Meta data is the data defining warehouse objects. It has the following kinds * Description of the structure of the warehouse (location, dimension, used schema..) * The algorithms used for summarization * Business data ( business terms and definitions, ownership of data) * * lOnly for information – will be not used for exams Business Intelligence Architecture ERP lFor exams should be simplified (if used) Databaseà Data Warehouse->OLAP server->Reporting