WIXLIB

Faster and Smarter Data Warehouses with Oracle 11gSETUP INSTRUCTIONS BEGIN HERENote: Please follow all six of the following setup instructions prior to beginning the demo. The startup of the services in Setup Instruction #4 cantake several minutes. In addition, the Windows OS in the Virtual Machine may need to increase the size of its virtual memory paging file, which cantake some additional time during the startup of the services.Start the VMWare BIC2G image, and double-click the Process Control icon on its desktop.Setup Instruction #1Note: allocate as much memory as possible – preferably at least 1800KB – to running theVMWare image.Click the Service View link atthe top of the screen. SelectVlamis11g.rpd from theRepository dropdown, andVlamis11g from the WebCatalog dropdown, then clickthe Set button.Setup Instruction #2When the Repository and WebCatalog have been changed,you should see the selectionschanged as indicated with thegreen arrows.Note: You can either set theRefresh mode (top right of thescreen) to Continuous, or youcan leave it set to Manual andtoclick the refresh iconupdate the Process Controlscreen manually.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docPage 1 of 36

Open Windows Explorer in theVM, and navigate toc:\DEMOS\Vlamis.Double-click to execute the filenamed OBIEE Vlamis.cmd.Setup Instruction #3 – VERY IMPORTANT!This copies the file namedVlamis NQSConfig.ini intoNQSConfig.ini to disablecaching in OBIEE. (By default,the OBIEE demos have cachingenabled.)Setup Instruction #4Vlamis Global Demo Script version 11gClick the Demo View link atthe top of the screen. Select theBIEE radio button, then clickthe Start button. When allappropriate services havestarted, the top row of threeflags will all be green as shownhere.Vlamis Demo Script BIC2G 11g.docPage 2 of 36

From theWindows Startmenu, selectPrograms Oracle BusinessIntelligence PresentationServices tobring up thelogin screen.Setup Instruction #5Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docPage 3 of 36

Start Oracle SQL Developer,(shortcut in the top section ofthe Start menu) and connect tothe Oracle database using theDM connection.Setup Instruction #6Type this command into theSQL Statement area, and pressthe F9 button (or mouse-clickthe green arrow) to execute it:Alter materialized viewCB SALES CUBE disablequery rewrite.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docPage 4 of 36

DEMO BEGINS HEREScreenshotScriptThe purpose of this presentation is todemonstrate the use of Oracle BusinessIntelligence Enterprise Edition (OBIEE) inconjunction with the multi-dimensionalOLAP functionality in the Oracle database.We will purposefully concentrate on thevalue of OBIEE and Oracle OLAP, not howto actually map the data and build thereports.This presentation uses the “DM” sampleschema downloadable from OracleTechnology Network. “DM” is a fictionalcompany that sells PCs and consumerelectronics.IT SellingMessageOBIEE is a webbased deliverytool. There’snothing to installon the end users’PCs.Do ThisEnter the user idvlamis withpassword vlamisand click Log in.Our first step is to log in to the OBIEEPresentation Services environment and openour dashboard.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docPage 5 of 36

ScreenshotScriptIT SellingMessageDo ThisIn the first part of this demo, we’ll begenerating reports such as this one, usingdata coming from a standard relationaldatabase. And then later in the demo, we’llallow the Oracle database to switch overand execute some of those same reportsagainst our Oracle OLAP cubes to improveperformance.We’ve already created all of the reportdefinitions that we’ll be showing today. Inthis presentation we’ll be calling them upone by one in their finished form. But eventhough we’re opening saved reports, thedata being shown is live. These aren’t justscreenshots of something we did yesterday.We’re actually running OBIEE, and we’rehitting a live data source.On this report, we're looking our company'sperformance in each of our three mainproduct categories, Computers, PortableMusic and Video, and Cameras andCamcorders, for each of the years 2005through 2007. You can see that ourcomputer sales have been growing verynicely, and that our other two categories arealso growing, but perhaps not as much asthe sales of Computers.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docNothingNothingPage 6 of 36

ScreenshotVlamis Global Demo Script version 11gScriptIT SellingMessageDo ThisOne of the features of OBIEE is the abilityto drill into any amount on the screen,whether it's shown as a number in a table, oras a point on a line, or as a segment of a piechart, or, in this case, a particular bar in abar chart, If I were to click on, for example,this tall gold bar, I would be drilling into theresults for Computers for 2007. You canalso see that when I rest my mouse pointeron a particular bar, the actual valueassociated with that bar pops up.Point to, but donot click on, thetall gold cylinderfor Computers.But right now, I'm interested in seeing all ofthe subcategories under my Computersdivision for each of the past 3 years, and Ican do that by just clicking the 'Computers'title just below my chart.Click on theComputerslabel below thechart.Vlamis Demo Script BIC2G 11g.docPage 7 of 36

ScreenshotScriptOn this screen, I see that Total PersonalComputers is the biggest component of ourComputer sales, and I want to see itscomponents, so I'll click on the 'TotalPersonal Computers' title under the chart.Interestingly, we see that our PC Soundcomponents, things like sounds cards,speakers, headphones and so forth, areactually our biggest sellers, and arecontributing the greatest dollar amount ofsales from our Personal Computerscategory.But is PC Sound one of our biggest growthproducts? Here at the top of the chart, wecan choose to display different types ofdata. Right now, we're looking at dollarsales. But perhaps we're also interested inseeing a metric like Sales Pct Chg. Vs. PriorYear. Let’s select Pct Chg. Vs. Prior Yearfrom the dropdown and see the results.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docIT SellingMessageDo ThisClick on theTotal PersonalComputerslabel below thechart.Select Sales %Chg PrYr fromthe view selectordropdown.Page 8 of 36

ScreenshotScriptIT SellingMessageHere we can see that sales of PersonalComputers are growing at a much fasterrate than are our PC Sound products, andComputer Displays are growing pretty welltoo.Do ThisScroll down tothe AnalysisView of the data.We can also switch between different typesof views of the same data. If we scrolldown a bit we see a tabular view of our data, what’sknown in OBIEE as a Pivot Table, with ourDepartment, Category, and Product Typegoing down the page, and each of ourselected years going across. On this report,we’re choosing to display our sales figures.We can use one OBIEE’s features known asa View Selector to switch to a differentview of our data. We’ve set up a view thatwe're calling our Data Table View.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docMultiple metricsand view typesin the userinterface. Userscan create theirown content,independent ofIT involvement.Select the DataTable Viewfrom the viewselectordropdown, thenscroll down sothat it fills thescreen.Page 9 of 36

ScreenshotScriptIn this view, we can see that we've colorcoded our data so that for any product typeswhere Sales Pct Chg Vs Prior Yr is greaterthan 25%, the sales increase percentage isshown in green, and any that are between 0and 10% growth are shown in yellow.And if we scroll all the way down and clickto show us all of theon this buttonrecords IT SellingMessageDo ThisScroll to thebottom, andclick theAll Recordsbutton.When all recordsare displayed,scroll down tosee the 2007results. we can scroll down further and see thatwe've color-coded, in red, any productgroups that had declining sales from oneyear to another.So here we see that our PC Sound salesgrew by 9.84% in 2007, but our sales ofPersonal Computers grew by 37.65%, andso forth, and we've used color highlightingon our points of interest.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docNo ActionPage 10 of 36

ScreenshotScriptSo far, we've seen reporting of ACTUALresults, and those can generally be producedusing the features of the Oracle RelationalDatabase. But one thing that the RelationalEngine doesn't have, and one of the featuresof Oracle OLAP, is the ability to generateforecasts based on historical trends.IT SellingMessageDo ThisScroll up andclick on theSales Forecastdashboard tab.So next, we're going to switch over toanother dashboard page, which shows usnot only our actual sales, but also our salesprojections for the next 12 months.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docPage 11 of 36

ScreenshotVlamis Global Demo Script version 11gScriptIT SellingMessageDo ThisOn this line chart, the light blue linerepresents sales of PC Sound products in2005, 2006, and 2007, and the gold linerepresents sales of Personal Computers forthat same time.Point to the lightblue line and thegold line, andtheir labels.The darker lines out here to the right, thatare labeled 'Cross Over Best Fit Forecast',represent the forecasted sales for both of theproduct lines, using the "Holt-Winters"forecasting method, which takes intoaccount not only the general trend of thedata, but also its seasonality, its variabilitywithin the year.Point to thedarker lineextensions on theright, and theirlabels.Notice that that the PC Sound line and thePersonal Computers line are converging,and we should probably expect to see thesales of Personal Computers begin tosurpass the sales of PC Sound productssometime in 2009.Point to theconvergence ofthe two lines.Vlamis Demo Script BIC2G 11g.docPage 12 of 36

ScreenshotScriptThat's one of the powerful features of theOracle OLAP engine, and it's built-in aspart of the Oracle database. These forecastsare being generated by the OLAP engineINSIDE the Oracle database. You don'thave to extract your data from the database,move it into another tool like Excel,generate your forecast, then extract it out ofExcel and somehow get it back into yourOracle Database. This entire forecastingprocess can be set up once, completelyinside of the Oracle Database using theOLAP option, and the data can bereforecasted every month, automatically.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docIT SellingMessageCan combinecomplexfunctions intooneenvironment.Do ThisSwitch to thePowerPoint, anddisplay the firstE.F. Codd quote.Page 13 of 36

ScreenshotScriptIT SellingMessageDo ThisSo, why can't features like forecasting andtime series analysis just be imbeddeddirectly into the relational engine?E.F. Codd, who is one of the foundingfathers of data warehousing, gives us histake on why we haven’t seen, and indeedwon’t see, this type of functionality from arelational engine:Codd says that “Relational DBMS productshave boundaries with respect to providingfunction to support user views of data. Theability to consolidate, to view, and toanalyze data according to multipledimensions, in ways that make sense to oneor more specific enterprise analysts at anygiven point in time, has been lacking.”Move forward tothe next E.F.Codd slide.In Codd’s view, purely relational databasesjust aren't capable of performing this type ofmulti-dimensional analysis because thereare technological boundaries that prevent it.Vlamis Global Demo Script version 11gVlamis Demo Script BIC2G 11g.docPage 14 of 36