L-8 Version 11 - Essbase Studio

1Lab8:Version11–EssbaseStudioLab8:Version11–EssbaseStudio1OverviewTheEssbaseStudioisthebiggestadditioninthe11.xrelease.TheStudioisdesignedtoprovideasinglemodelinginterfaceforthecreationofanalyticapplications.InsteadofcreatingsomemodelsinAdministrationServicesandothersinIntegrationServices,EssbaseStudiosimplifiescubeconstructionbydeliveringasingleenvironmentforperformingtasksrelatedtodatamodeling,cubedesigning,andanalyticapplicationconstruction.Withawizard-drivenuserinterface,EssbaseStudiosupportsmodelingofthevariousdatasourcetypesfromwhichEssbaseapplicationsaretypicallybuiltaswellasexposinganumberofnewdatasources.Asinglecommonmetadatarepository,orcatalog,capturesallmetadatarelatedtoallEssbaseapplicationsbuiltintheenterpriseandallowsthereuseofmetadataatthelowestlevelofgranularity.ThecatalogmakesEssbaseStudioinherentlyawareofthecommonmetadatathatissharedacrossthevariousapplicationsenterprisewide.EssbaseStudiosupportsseveraldrill-throughoptions:relationaldatabases,OBIEE,BI+reports,URLs,Essbaseapplications(drillacross),customSQL,andJavamethods.EssbaseStudioalsosupportslineagetrackingthrougharichgraphicalviewofmetadatarelationships,allowinguserstofollowapplicationlineagestotheirmetadatacomponentsandthroughtothedatasourcesfromwhichtheyweresourced.Beforeyoucanbeginthislab,ensurethefollowingservershavestarted:•LinuxVM•SharedServices•Essbase•EASServer•EssbaseStudioServer•AnalyticProviderServicesRefertoLab2forinstructionsonstartingtheaboveservers.2AddingDataSourcesInthecurrentreleasethefollowingcanbesourcesforStudio(aJDBCdriverisrequiredforeachsource):•Oracledatabase•OracleBusinessIntelligenceEnterpriseEdition•EnterprisePerformanceManagementArchitect•SQLServer•DB2•Teradata•DelimitedtextfilesNote:Excelcannotbeuseddirectlyasasource,itmustfirstbesavedasaCSVfile.1.OpentheEssbaseStudioConsolebyselectingthefollowingshortcut:Start|Programs|OracleEPMSystem|Essbase|EssbaseStudio|EssbaseStudioConsole.2Lab8:Version11–EssbaseStudio2.Attheloginscreen,selectortype“ptsessbase”fortheServer,“admin”fortheUserand“password”forthePassword.Click“LogIn”3.TheleftpanelinStudiohastheMetadataNavigator,whichprovidesatreeviewofyourmetadatarepository.Folderscanbecreatedundertherootdirectorytomanageandorganizemetadataelements.Therightpanelcontainsthedatasourcesandminischemasthatyouwillneedforbuildingyourcubes.Tobegin,makesurethattheDataSourcestabisselectedintherightpanel.Right-clickon“DataSources”andselect“New|DataSource…”.3Lab8:Version11–EssbaseStudio4.WewillfirstaddanOracleschemaasadatasource.ProvidethefollowinginformationintheDefineConnectionscreen:Clickon“TestConnection”.Click“OK”.Iftheconnectionwassuccessful,click“Next”intheDefineConnectionscreen.4Lab8:Version11–EssbaseStudioIntheDefineTablesscreen,selectalltables.Click“Finish”.Wewillbecreatingtheminischemasandhierarchieslater.Click“OK”whensuccessful.ExpandonTBCandononeofthetablestoviewthefields.Right-clickonatableandselect“ViewSampleData”.5Lab8:Version11–EssbaseStudioThefirst200rowsofthetablewillappearinthemiddlepanel.Clickonthe“X”toclosethetable.5.Repeataddinganewdatasourcebyselecting“File|New|DataSource…”.Type“Text”astheConnectionNameandselect“TextFile”fortheDataSourceType.Ensurethat“Comma”isselectedastheDelimiter.Click“Browse”nexttoLocation.6Lab8:Version11–EssbaseStudioSelect“tbc_samples”andclick“OK”.TherootdirectoryissetasHYPERION_HOME\products\Essbase\EssbaseStudio\Server\datafilesbydefault.Thetbc_samplesdirectorywasplacedinthereduringtheinstallation.Youcanchangethelocationoftherootdirectorybyaddingthefollowinglinetotheserver.propertiesfilefoundintheHYPERION_HOME\products\Essbase\EssbaseStudio\Serverdirectory:server.datafile.dir=directorypathClick“Next”totheModelFilesscreen.Clickonthe“Selectall”buttonandclick“Finish”.Click“OK”whensuccessful.7Lab8:Version11–EssbaseStudioExpandTextandright-clickononeofthefilestoviewitscontent.3ModelingDataSources-Minischemas8Lab8:Version11–EssbaseStudioTheprocessofmodelingdatasourcesinStudiovariesdependingonthetypeofsourceyouaremodeling.Themodelingprocessforflatfilesistakencareofinthewizardusedtomapthedatasource.TheprocessformodelingarelationalsourceisverysimilartothatfollowedinIntegrationServices.Theminischema(collectionofinterrelateddataelements)canbelookedatverymuchlikethedatamodelthatexistsinIntegrationServices.LiketheIntegrationServicesmodeltheminischemascontainthejoinrelationshipsbetweentables.UnliketheIntegrationServicesmodel,theminischemasdonotcontainhierarchyinformation.ItsimplyprovidesthemappingssotheStudiocantraversethedatasource.Otherimportantpointsregardingminischemasareasfollows:•Textfilescanbeaddedtominischemasforthepurposeoflogicalgroupingonly.Theycannotbejoinedtoothertextfilesorrelationaltables.•Studiosupportstheuseofmultiplefacttablesintheschema;bothcanprovidefactdatatothemodel.•ThereisnorequirementtohaveafacttableforhierarchycreationforEssbasedeployment.Tothatend,youcanleveragecolumnsoutofarelationalsourceforbuildingthemodelwithoutaminischemaanduseaflatfiletoloadthedata.Minischemasarefirst-classobjectsusedtocreatelogicalgroupingso