Reporting & Analytics for EBS with Oracle OLAP

1. Reporting & Analytics for EBS with Oracle OLAP Kailash Pareek

2. Agenda • Author Introduction • Background Information • Oracle OLAP • Designing the Cube • Reporting with Excel

3. Author Introduction • B.E (ECE), I.I.Sc., 1980 • 16 years Heading IT departments • 11 Years in IT Industry • > 15 Years Oracle Tech Experience • >10 years Oracle EBS Experience • Independent IT Consultant

4. Introduction to DWH, BI, OLAP & DM Data Warehouse The term Data Warehouse was coined by Bill Inmon in 1990, which he defined in the following way: "A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process". He defined the terms in the sentence as follows: (Source: "What is a Data Warehouse?" W.H. Inmon, Prism, Volume 1, Number 1, 1995). Business Intelligence In a 1958 article, IBM researcher Hans Peter Luhn used the term business intelligence. He defined intelligence as:"the ability to apprehend the interrelationships of presented facts in such a way as to guide action towards a desired goal.“ OLAP was a term coined by E F Codd (1993) and was defined by him as: “the dynamic synthesis, analysis and consolidation of large volumes of multidimensional data”

5. Introduction to DWH, BI, OLAP & DM Data Mining Data mining involves the use of sophisticated data analysis tools to discover previously unknown, valid patterns and relationships in large data sets. These tools can include statistical models, mathematical algorithms, and machine learning methods (algorithms that improve their performance automatically through experience, such as neural networks or decision trees). Consequently, data mining consists of more than collecting and managing data, it also includes analysis and prediction.

6. Introduction to DWH, BI, OLAP & DM Integration

7. Understanding Oracle OLAP • OLAP option to database 10g • Multi Dimensional Data type (Analytical Workspace) • OLAP Engine • OLAP Server side APIs • Analytical Workspace Manager (AWM) client tool • Oracle Warehouse Builder • BI Beans • Oracle discoverer for OLAP • BI Spreadsheet Add-In • Oracle Planning & Budgeting

8. Installing / verifying OLAP in database OLAP option is automatically installed with EE of database. To verify run the SQL select * from v$option where upper(parameter) ='OLAP‘ and select comp_name,status from dba_registry where upper(comp_name) like '%OLAP%' If the OLAP option is installed, the value will be TRUE in first query and status VALID in second. If the OLAP is installed but not configured for, use Oracle Database Configuration assistant (DBCA) to configure the database and add the OLAP option. If OLAP is not installed, use OUI to add the component and then configure to use with DBCA.

9. Installing AWM Analytical Workspace Manager (AWM) is bundled with Oracle client. Use the custom installed and select the existing Oracle 10g Home. Select product OLAP Analytic Workspace Manager and Worksheet from the list of components. Analytical Workspace Manager (AWM) is also standalone java client application at http://www.oracle.com/technology/products/bi/olap/index.html. The installation instructions are provided in README.TXT and it can be installed by unzipping the contents in a suitable folder. To verify the version, either AWM from Programs->10g home or run bin\awm.exe. Help->About.

10. Installing BI Spread Add-In • Download BI Spread Add-In from http://www.oracle.com/technology/products/bi/spreadsheet_addin/index.html • Excel should not be running during installation. • Run the installed executable and choose a folder to install. • To run the spreadsheet Add-In, just run Excel. The new menus will be available. To remove the cells populated with BI SS Menus, use the SS menus to delete. Do not delete with standard Excel operations. • To un-install SS Add-in either run uninstall.exe from the installation folder or use control panel->add / remove programs.

11. Views Used in Example In this example, following views are created on Order Management tables to load Dimensions and Measures. • SO_Time_V: Based on Custom table to store time dimension data. • SO_Customer_V: Customer/Geography dimension view • SO_Product_V: Product dimension view. • SO_Sales_persion_V: Sales person dimension view. • SO_Measures_V: Measures

12. Analytic Workspace Oracle Database stores the dimensional model in an analytic workspace. An analytic workspace can be considered as a collection of multidimensional data types and the physical implementation of the logical dimensional model. An analytic workspace is owned by a particular user ID, and other users can be granted access to it. Within a single database, many analytic workspaces can be created and shared among users. The AW is implemented as relational table AW$<AW name> within the database and individual components are stores as BLOB.

13. Using AWM11 Interface AWM is a client java tool to create & maintain AW in the database and all other components of multi dimensional model. AWM provides one or more view of data to DBA, Application developer or end user. • Model View – Used often for development • Object view – Used for OLAP DML by experts for directly manipulating the OLAP. There are three general steps in AW creation • Creation of Logical Model (Dimension, Cubes & measures) • Mapping of logical model to physical tables and columns • Loading of data Click AMW.EXE to start the AWM and give the connection information.

14. Using AWM11 Interface

15. Create an AW Login to AWM. Right click to Analytical workspace under logged in schema. The AW can be created by entering details or from existing template. The workspace is created with necessary structure as under.

16. Dimensional Data Model The dimensional data model is composed of cubes, measures, dimensions, hierarchies, levels, and attributes.

17. Cube & Measures • Cubes provide a means of organizing measures that have the same shape; that is, they have the exact same dimensions. The edges of the cube contain dimension members and the body of the cube contains data values. Cubes are the parents of measures and calculated measures. • Measures are used to store fact data within a cube. Common examples include Unit Sales and Dollar Sales. Measures are organized by dimensions, which typically include a Time dimension. • Calculated Measures are created by performing calculations on the base measures stored in an analytic workspace. These derived facts are not stored; the calculations are performed in response to individual queries.

18. Dimensions, Levels & Hierarchies • Dimensions provide context and structure to the factual data. They form the edges of a logical cube, and the measures within the cube. Dimensions are the parents of levels, hierarchies, and attributes in the logical model. Users define these supporting objects, in addition to the dimension itself. • Levels represent positions within the hierarchy. For business analysis, data is typically summarized at various levels. For example, a data warehouse may contain monthly snapshots of a transactional database. If months are at the base level, summarization would occur at the quarterly and yearly levels.

19. Dimensions, Levels & Hierarchies • Hierarchies organize data at different levels of aggregation. For example, in the Time dimension, a hierarchy is used to aggregate data from the month level to the quarter level to the year level. Hierarchical structures enable analysts to detect trends at the higher levels and, by drilling down to the lower levels to identify the factors that contributed to a trend. • Attributes provide information about the individual members of a dimension. They are used for selecting data and organizing dimension members. • Analytic Workspace Manager supports all common styles of dimensions, including list dimensions, level-based dimensions and value-based (also known as ‘parent-child’) dimensions.

20. Types of Hierarchies • List or flat dimension has no hierarchies • Level Based Hierarchy Create a level-based hierarchy when the dimension has parent-child relationships that define levels, such as Month and Year, or City and Region. You must define the levels before you can finish defining the hierarchy. • Value Based Hierarchy Create a value-based hierarchy when parent-child relationships exist, but you cannot group them into meaningful levels. For example, an employee dimension may have parent-child relationships defined in the data that identify each employee's supervisor, but these relationships may not form meaningful levels across the organization. You can define a value-based hierarchy only when the dimension members are unique in the data source;

21. Types of Hierarchies • A level based Hierarchy can be • Normal hierarchies consist of one or more levels of aggregation. Members roll up into the next higher level in a many-to-one relationship, and these members roll up into the next higher level, and so forth to the top level. • Ragged hierarchies contain at least one member with a different base, creating a "ragged" base level for the hierarchy. • Skip-level hierarchies contain at least one member whose parents are more than one level above it, creating a hole in the hierarchy. An example of a skip-level hierarchy is City-State-Country, where at least one city has a country as its parent (for example, Washington D.C. in the United States).

22. Data Warehouse V/s OLAP If your source data is already in a star or snowflake schema, then you already have the elements of a dimensional model: • Fact tables correspond to cubes. • Data columns in the fact tables correspond to measures. • Foreign key constraints in the fact tables identify the dimension tables. • Dimension tables identify the dimensions. • Primary keys in the dimension tables identify the base-level dimension members. • Parent columns in the dimension tables identify the higher level dimension members. • Columns in the dimension tables containing descriptions and characteristics of the dimension members identify the attributes.

23. Creating a Dimension • Every member of dimension must have a unique key across all levels. • Natural Keys are read from relational sources without modification. • If a dimension is flat or value-based, then it must use natural keys because no levels are defined as metadata. You must take whatever steps you need to assure that the dimension members are unique. • Surrogate keys are system generated andensure uniqueness by adding a level prefix to the members while loading them into the analytic workspace • Time dimension table must have period end date and time span. This is required for time series analysis such as comparison with previous periods. Time dimension should have at least one level to support time based analysis.

24. Creating a Dimension • Right click Dimensions->Create dimension.

25. Creating Level/Hierarchy • Right click the Levels (PRODUCT)->Create Level. Enter the levels from top to bottom. Total_Product, Class, Family, Item.

26. Mapping Dimensions • Tabular view. Drag-and-drop the names of individual columns from the schema navigation tree to the rows for the logical objects. • Graphical view. Drag-and-drop icons, which represent tables and views, from the schema navigation tree onto the mapping canvas. Then you draw lines from the columns to the logical objects.

27. Viewing Data in Dimensions After data has been loaded in the dimension, it can be viewed by right click->View data.

28. Creating Cube/Measures & Mappings • Right click Cubes->Create cube. Enter name Sales_Cube and select all dimensions.

29. Loading Cubes/Dimensions • Loading Cubes in similar to loading dimensions. OLAPSYS.XML_LOAD_LOG Stores the load log.

30. Viewing the Data • Query Builder

31. Viewing the Data • Right click the cube SALES_CUBE and select View Data.

32. Calculated Measures • Calculated measures return values computed at run time from the data stored in one or more measures. • They are stored as queries just like views on relational data. • Since they do not occupy storage, there is no storage overhead • They can be used in queries as well defining more calculated measures giving the depth to type of calculations that can be made.

33. Functions for calculated Measures • Basic Arithmetic • Addition, subtraction, multiplication, and division, using two measures or a measure and a number • Advanced Arithmetic • Cumulative total, index, percent markup, percent variance, rank, share, variance • Prior/Future Comparison • Prior value, difference from prior period, percent difference from prior period, future value • Time Frame • Moving average, moving maximum, moving minimum, moving total, year to date

34. Calculated Measures – Examples% variance Calculates % variance between two measures. (Target unit – Base Unit) / Target Unit e.g. Target Unit = Price , Base Unit = Cost

35. Calculated Measures – ExamplesIndex Index calculates % difference between a measure and selected value that serves as base number.

36. Calculated Measures – ExamplesRank Ranks the dimensions based on value of a measure. The ranking can be for all (Total), parent (within a parent) or Level (within same level).

37. Calculated Measures – ExamplesShare Share calculates the ratio of measure’s value for current dimension members to base line that can be • Total (total value of all values at same level at current member), • Parent (total value of members at same level as parent of current member), • Level (total values of all members at specific level), • Member (Value of specified member).

38. Calculated Measures – ExamplesCumulative Total Cumulative totals start with the first time period within a particular rank and calculate a running total up to the current member. The range can be all members of the level or just members with the same parent.

39. Calculated Measures – ExamplesPrior Period Gives the value at previous period which can be An year Ago, Period Ago or Number of years, quarters etc.

40. Calculated Measures – ExamplesTime frame – Period to Date Period to Date calculates the running total within the time frame.

41. Spread Sheet Add-In The Resultant spread sheet after the changes in Query.