OLAP Theory-English version On-Line Analytical processing (Business Intelligence) [Ing.J.Skorkovský,CSc.] Department of corporate economy Agenda l lThe Market lWhy OLAP (On-Line-Analytic-Processing 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 2004-2010, 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 2014. 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 (CEO,..) and executive managers (CFO,..) lAccess –Fast consistent, interactive –Wide variety of possible views lTransformation of –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 (components to final product) –Distribution lCost of shipping –etc 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. 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 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 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 Cube Information Is conceptually viewed as cubes. Dimension Distinct categories for business data. Hierarchy Levels of details on the data. Measure Quantitative values. OLAP Terms and Concepts lCube –Information Is conceptually viewed as cubes. lDimension –Distinct categories for business data. lHierarchy –Levels of details on the data. lMeasure –Quantitative values. lData slice –A subset of the data in a partition. OLAP Cube 3wwl2ijc[1] 3wwl2ijc[1] Data Warehouse->Data Marts (Technology) 3wwl2ijc[1] Data Warehouse Data Mart Data Mart Data Mart Decision Support Information Decision Support Information Decision Support Information j0233536 OLAP architecture OLAP (Technology) METADATA Data pump (Technology) Prime transaction oriented system (ERP,CRM,..) Data warehouse Data pump Reporting (NAV tools or JETs) lReporting 21 http://www.jetreports.com/jet-essentials/reporting-solutions.php Open hyper text link (annotation) Main principles (source tables and their entries) lMain principles 22 Customer Vendor Item Account Dimension Control parameters (time, type of products, Costs, Revenue, Area,..) NAV-data generation (entries)->Document->F11->Entries->Reports Some chosen analysis asked by not named important customer (prospect) in the Czech Republic lAnalysis * Working capital – setup of the accounting schedule from NAV l 23 lAnalysis * Working capital – Show of the results from NAV 24 Some chosen analysis asked by not named important customer (prospect) in the Czech Republic lWorking Capital JETs * Working capital – Show of the results from JETs 25 Some chosen analysis asked by not named important customer (prospect) in the Czech Republic lInventory Dashboard 26 Some chosen analysis asked by not named important customer (prospect) in the Czech Republic Some chosen analysis examples (JETs) lAnalysis examples 27