Oracle Essbase - Not Just For Financial Analytics

BI Consulting Group White PaperOracle Essbase- Not just for Financial AnalyticsExecutive SummaryThe goal is to increase the value of Essbase in yourOracle Essbase is not just for financial analyticsorganization by using it in ways you might not haveanymore. By expanding its use beyond the Financepreviously considered. Move it beyond the Financedepartment, you will begin to see the true value of thisDepartment and make yourself an analytics superstar!flexible and functional tool.Here are a few areas where savvy users of OracleThis white paper will show how you can use an existingEssbase can extend it horizontally across multiplesales analysis cube as a foundation for an inventorybusiness functions:analysis cube. Operations: inventory control, forecast accuracy analysisReusing existing meta-data such as the product Sales & Marketing: product profitability, spend planninghierarchy between cubes can really speed up the HR: compensation trend analysis, timesheet trackingdevelopment process and is a best practice of Manufacturing: build plans, bill of material analysisEssbase design. Additional examples featured in this Finance: days sales outstanding (DSO) analysiswhite paper include using this new inventory cube toanswer some real-world business questions.This white paper will describe how Oracle Essbasecan be used for detail level inventory analysis for aThe goal is to make Essbase relevant to your entirefictional Operations department. It utilizes a variationorganization so it can improve your daily operations.of the “Sample Basic” cube that ships with OracleEssbase.Along the way, we’ll review someNot Just for Financial Analytics Anymorebest-practice design tips. The intent is to show howPeople sometimes think of Oracle Essbase as onlyyou can leverage an existing, known cube structuresuited for the Finance department. While it’s true thatand build a new application using this knowledge.Essbase “excels” at this, the tool can help almost anybusiness function that uses numeric data. Its ability toDetail Level Inventory Analysisanalyze in a multidimensional fashion allows users toBackground: TBC (The Beverage Company) makes“slice and dice” numbers to gain new insight into what’sand distributes beverages. A large part of their capitalhappening in their business.is tied up in the bottles of various drinks that are sittingin their warehouse and on vendor’s shelves.Once you see how to do this in one area, why not usethe same techniques somewhere else?860 Blue Gentian Road Suite 290 Eagan, MN 55121-1-Tel 866.953.4757Email info@biconsultinggroup.comWeb www.BIConsultingGroup.com Copyright 2009, BI Consulting Group, LLC. All rights reserved. No part of this material may be reproduced, stored in a retrieval system or transmitted by anymeans, electronic, mechanical, photocopy, recording or otherwise, without written permission from the authors

BI Consulting Group White PaperOracle Essbase - Not just for Financial AnalyticsThey want to limit the amount of money spent on It’s evident that the existing Sample Basic cube would needmanufacturing their products and not spend moneysome changes to meet the requirements.making beverages that customers are not buying. Thiswill free up cash to be used for other business The “Year” must go to the day levelpurposes such as advertising, product research and The “Market” dimension does not applyinvesting. TBC thinks one way to achieve these goals A “Warehouse” dimension is neededis to better manage their warehouse inventories.Scenario: TBC has been using their Essbase cube“Sample Basic” to track product profitability (Profit,Sales, COGS and Operating Expenses) and someimportant inventory data (Opening and EndingInventory Balances). This allows users to answerquestions such as “What are the ending inventorybalances by Product by Month?” However, it does notgo down to a low enough level of detail for TBC’srequired reports (ie daily inventory analysis bywarehouse).Question: How can TBC build another Essbaseapplication that will provide more detailed analysis oftheir daily inventory warehouse operations?Figure 1Let’s start by reviewing the existing Sample Basic cubestructure: (Figure 1) The lowest level of data currently available for the “Year”Solution: This requirement can be addressed bydimension is “Month” (ie Jan, Feb, Mar, etc). However, TBCcreating a new “Inventory” cube based on the existingneeds it at the day level.“Sample Basic” cube. It will share dimensions and Many of the existing dimensions can still be used (Year,attributes where possible and have new dimensionsMeasures, Product, Scenario) but “Market” is not needed.and attributes where necessary.-2-860 Blue Gentian Road Suite 290 Eagan, MN 55121Tel 866.953.4757Email info@biconsultinggroup.comWeb www.BIConsultingGroup.com Copyright 2009, BI Consulting Group, LLC. All rights reserved. No part of this material may be reproduced, stored in a retrieval system or transmitted by anymeans, electronic, mechanical, photocopy, recording or otherwise, without written permission from the authors

BI Consulting Group White PaperOracle Essbase - Not just for Financial AnalyticsStep 1 - Make a copy of the “Sample Basic” applicationProduct, Market, Scenario” will become “Years,within Essbase Administration Services (EAS). To doMeasures, Product, Warehouse, Scenario”.this, right-click on application name “sample” andselect “Copy.” Name this new application “Inventory”The only dimension name change will be renaming(we’re limited to 8 characters in naming) and pressand updating “Market” to “Warehouse”.“OK”. (Figure 2)dimensions use identical names between cubes alsohelps with user acceptance training.HavingLearn theThis will create a new application/database “Inventoryfunction of one cube and the other cubes are similar.Basic” which contains all components of the “Sample(Figure 3)Basic” application.Figure 2Figure 3The advantage of copying an existing cube (ifavailable) instead of starting from scratch is thatseveral of the dimensions (such as “Product”) arecommon between both applications. Reusing all thehard work previously done saves time, money and cutsdown on errors. You can also use the same dimensionbuild rules in many cases.After our changes, dimensions “Years, Measures,-3-860 Blue Gentian Road Suite 290 Eagan, MN 55121Tel 866.953.4757Email info@biconsultinggroup.comWeb www.BIConsultingGroup.com Copyright 2009, BI Consulting Group, LLC. All rights reserved. No part of this material may be reproduced, stored in a retrieval system or transmitted by anymeans, electronic, mechanical, photocopy, recording or otherwise, without written permission from the authors

BI Consulting Group White PaperOracle Essbase - Not just for Financial AnalyticsStep 2 - Update “Year” dimension to include specificTo keep things simple, just change the member namesdate/time members. The “Year” dimension in “Sampleto reflect your needs.Basic” only goes to the month level. Our requirementunder East, West, South, and Central (such as Newis to go to the day level. No problem. simply addYork, Massachusetts, etc) and then add the prefixdates as children of the month member. (Figure 4)“Warehouse” to East, West, South and Central.Our example will use the first seven days of calendarYou do not want to use the same member names East,year 2009 to demonstrate the idea. I recommend youWest, South, and Central in different cubes if they haveadd a text prefix of “d” (for “date”) to the member namedifferent meanings.Delete the member namesso that Excel interprets this as a text value and not anumeric value. For example, list “20090101” (Jan 1,Figure 4Figure 52009) with the “d” prefix to desinate it as a date. Thisis not a requirement of Essbase, just a helpfulsuggestion.Correct choices while creating theEssbase outline can help users later on.Step 3 - Change “Market” to “Warehouse” and updatecontents. As previously stated, TBC does not want the“Market” dimension in this cube and needs a“Warehouse” dimension instead. (Figure 5)-4-860 Blue Gentian Road Suite 290 Eagan, MN 55121Tel 866.953.4757Email info@biconsultinggroup.comWeb www.BIConsultingGroup.com Copyright 2009, BI Consulting Group, LLC. All rights reserved. No part of this material may be reproduced, stored in a retrieval system or transmitted by anymeans, electronic, mechanical, photocopy, recording or otherwise, without written permission from the authors

BI Consulting Group White PaperOracle Essbase - Not just for Financial AnalyticsKeeping unique member names across your cubes isLikewise, keep identical names in dimensions if theya best-practice of Oracle Essbase outline design. Thisreference identical things. Notice how the “Product”reduces confusion for users over the meaning ofdimension is the same in both outlines. (Figure 7)member names and helps your company graduallydefine a common data dictionary of business terms.For instance, don’t use the term “East” in one cube todefine a “Market” and “Warehouse” in another cube.It confuses users and leads to incorrect data retrievalsor misinterpretation. (Figure 6)Figure 6-5-860 Blue Gentian Road Suite 290 Eagan, MN 55121Tel 866.953.4757Email info@biconsultinggroup.comFigure 7Web www.BIConsultingGroup.com Copyright 2009, BI Consulting Group, LLC. All rights reserved. No part of this material may be reproduced, stored in a retrieval system or transmitted by anymeans, electronic, mechanical, photocopy, recording or otherwise, without written permission from the authors

BI Consulting Group White PaperOracle Essbase - Not just for Financial AnalyticsStep 4 - Update the “Measures” dimension. Let’sAsk IT to extract a tab delimited text file containingdelete the “Profit” and “Ratios” members and replacedata with the following fields: (Figure 9)the “Inventory” members as shown. We want to lookat “Units” and the valuation of those units, called Date“Value”. (Figure 8) Product WarehouseAs a side note, inventory is valued at the cost to the Actualcompany to produce the products. So if you only have Inventory Unitsinventory “cost” available in your data source, it should Inventorybe the same thing as the inventory “valuation” in ourYou will need to create a load rule to get the data intoexample application.Figure 8Figure 9Step 5 - Load data. You may be asking yourself “JustEssbase. A best practice is to have your data “clean”where am I going to get data to support this cube?”before it gets to the load rule. This means includingGood question.the correct member names so no further manipulationI suggest asking your friendlyAll(such as fixing member names) is needed within thecompanies using Oracle Essbase have some sort ofload rule. This is not a requirement, just a good idea.neighborhood IT department for this file.computer based inventory management system.-6-860 Blue Gentian Road Suite 290 Eagan, MN 55121Tel 866.953.4757Email info@biconsultinggroup.comWeb www.BIConsultingGroup.com Copyright 2009, BI Consulting Group, LLC. All rights reserved. No part of this material may be reproduced, stored in a retrieval system or transmitted by anymeans, electronic, mechanical, photocopy, recording or otherwise, without written permission from the authors