BO Universe DesignBest Practices

White Paper

According to the book of Genesis, the universe was created in seven days.

The following day-by-day analogy provides a best practices guide for the creation of an SAP BusinessObjects Universe.

Seven days actually might just be enough.

Abstract

For basic usage, the following design choices should be considered:

• Lessismore:Thenumberofobjectsavailableintheuni-verseshouldbelimitedtothosethattheenduserreallyneeds.Inpracticehowever,thedeveloperisoftenfacedwith thecontradictory requirementsofbothbasic andadvancedusage.Agoodwaytohandlethissituationisusing security access levels: Advanced users will beabletouseallobjectsof theuniversewhileuserswithbasic needs just get displayed a subset of objects. Ifsecurityaccesslevelsarenooption,thenconsidercrea-tingalinkeduniverseonthefullscopemasteruniverse.The object hiding technique on that secondary linkeduniversecanthenbeusedinordertoreducethescopeandcomplexityofanadvanceduniverse.

• Onetruth:Auniverse intended forbasicusageshouldrefrainfromofferingseveral“solutions”tothesamebusi-nessrequest.Forexample,dimensionattributesshouldbeprovidedeitherincurrentorhistoricaltruth.Providingbothcurrentandhistoricaltruthforthesamedimensionattribute will only confuse a user with basic needs.Furthermore, for the sakeof simplicity, donotprovideseveraldefinitionsofagivenKPI.

• Self-explanatory: Focus on providing good and clearbusiness names anddescriptions. The namingwill bemosteasilyunderstoodbybusinessusersifitisadoptedfrom the source application front end. On the otherhand,ITusersmightprefertheoriginalcolumnnamestoavoidconfusionwhenmappingcolumnsfromthesourcetablestothereportingdatabase.

• Eliminatereport logic:Variables likeweightedaveragesarenormallycreatedonreportlevel,becausetheirvalueis weighted depending on the grouping context of atable/graphwithinthereport.However,creatingvariab-lesinareportrequiresskill,timeandaclearunderstan-ding of the exact definition. The universe measureproperty“databasedelegated”allowsforthecreationofsuchvariablesatuniverselevel.Thedrawbackof“data-base delegated” objects is that every change in thereportdesignwillrequireaqueryrefresh.However,thebetterreliabilitywillpayoffinthelongrun.ConsideralsousinganalyticalSQLfunctionsinyourqueriesinordertoprovidemeasuresthatwillbeindependentofthequeryandthereportgroupingcontext(e.g.teamaverage).

The First Day - “Let There Be Light”

Understandingthebusinessrequirementsisessentialforanysuccessfulimplementation.“Lettherebelight”standsforun-derstandingthesuccessfactorsandconstraintsinaproject:

-Whattypeofreportingwillbedone?-Whattypeofusagecanbeexpected?-Whatarethesecurityrequirements?-Whataretheprocessesinthisorganization?

What type of reporting will be done?

Eachtypeofreporting,likefinancialreporting,adhocanaly-sis, dashboards, data mining, etc., requires specific datamodelling.

Before starting with the universe design, it is advisable totransform the data into a model which suits the type ofreporting. Startingwith a good datamodelwill enable thedeveloper tocreatebetteruniverses.Forexample,multidi-mensional cubes are ideal for financial reporting becausethere isaneedforaggregationandnavigationalongunba-lancedhierarchies(e.g.accounthierarchies).

Ontheotherhand,adenormalizeddatamartmightbeprefer-red for producing standard KPI reporting. Then again, anormalized data model close to the model of the sourceapplication isbestsuited foranalyticalqueryingand reportprototyping.

What type of usage can be expected?

Willtheuniversebeusedinabasicratherthananadvancedway?Basicusageisaboutstandardreportsandkeyfigures.Themainconcernforbasicusageisthatthereportsaswellastheunderlyinguniversearecorrect,wellunderstood,andthatthedataareavailableassoonaspossible.Asaconse-quence the universe development should focus on thesegoals.

On the other hand, advanced usage is about newinsights that can be derived from the data. A universesupportingadvancedusageshouldenableprototypingandin depth analysis. The focus of development should lie onextending the data model in order to support as manyrequestsaspossible.

Frequently,anunjustifiedbiasinfavorofadvanceduniversedesigncanbeobserved.Thisisaneasytraptofallintobe-causethepersoninchargeofcreatingtheuniversenaturallyusesitlikeaprototypeatthestartoftheproject.

White Paper BO Universe Design

What are the security requirements?

Requirements regarding data access security can have adecisiveimpactonthedevelopmentcostofauniverse,andconsequentlyofthewholereportingsolution.Requirementscan range fromnosecurity restrictionsup toverycomplexsecurity models based on organizational charts. Considerthefollowingoptionswhendecidingonanimplementation:

• Rowlevelsecurity:TheBOusercredentialsarelinkedtodata on row level. For such an option towork, use abridgetablemanagingtherowlevelaccessondatabaselevel.Suchatablecanoftenbederivedfromthesourcedata itself, e.g. if it contains a table with the correctorganizational hierarchy. In other cases, it has to bemaintainedmanually.

• Foldersecurity:Thebuilt-inBOsecurityreliesonfolders,whichpassontheirrightstotheunderlyingobjectslikereportsanduniverses.Thismeansthataccessiseithergrantedtoalloftheunderlyingdataortononeofthem.Accesstocertainuniverseobjectslikedimensionsandmeasurescanbefurtherrestricted.

• Encryption:Sometimesthereisaneedforencryptionofdelicate&sensitiveobjects(e.g.customernames).

• Advanced Security: When security requirements arebasedonhierarchylevels,considerbuildingauniverseon top of the securitymechanism built into an OLAPcube. Most OLAP cubes can be accessed byBusinessObjects.ThecubewillmanagesecuritybasedontheBOuserid.

What are the processes in this organization ?

InmanyorganizationsthereisaninherentconflictbetweenIToperations and thebusiness triggeringnew report require-ments.Thegoalof IToperations istostabilizetheenviron-ment in order to guarantee service availability. Therefore,IToperationshasnointerestinquickanddirtysolutions,andITdepartmentstendtoenforceprocessesthatslowdownorevenpreventchanges.

Ontheotherhand,businesspeoplehavelittlepatiencewithcumbersome and slow processes. In many organizations,theBOuniverse is used towork around this conflict.BOuniverses are often owned by the business. IT operationsdoesnottakeanyresponsibility,andthereisnoriskthattheoperational processes will be affected by changes in theuniverse.

Thedrawbackisthatthingsthatshouldhavebeenbuiltintothedatabaseitselfendupassomewhatvolatileconstructsintheuniverse.Afavoritetoolinthiscontextisthe“derivedta-ble”functionalitywhichenablesthebusinessuserstocreate

something similar to database views, but – of course –withoutcreatingrealobjectsinthedatabase.Thisapproachcannotberecommendedasbestpracticeduetotooldepen-dencyandduetolackofreusability.Itisalwayspreferabletoimplement business logic on a database level, where thesamevaluesarevisibletoalltoolsandusers.

The Second Day - “The Separation of Waters”

Thetimehascometostructure&organizethedatachaos.Thedatahavetobeanalyzedandthenseparatedintodiffe-rent business areas. For each of these business areas aseparateuniverseshouldbecreated.Thequestionremains:howshouldthesebusinessareasbegrouped?

A“oneuniversefitsall”strategymightbecometoocomplexanddifficulttouse.Ontheotherhand,creatingauniverseforeach single business query leads to low reusability andentailstheriskofinconsistency.Thefollowingrulesofthumbapplywhendecidingonwhatdatashouldbegrouped:

• Business questions, which use the same or similarreportingdimensions,shouldbegroupedintothesamebusinessarea.

• Data that is strongly related to each other should begroupedintothesamebusinessarea.Trynottousethesamefacttableinmorethanoneuniverse.Howeveritisnoproblemtouseseveraldifferentfacttableswithinoneuniverse. Use contexts in order to control correctquerying.

• Aggregatedfacttablesandtheunderlyingrawfacttableshouldbemadeavailable inthesameuniverse.Imple-mentsuchauniversewithaggregateawarefunctionalityinordertogetthebestperformanceandconsistency.

• The number of different contexts should be limited toaboutseven.Combiningtoomanyfacttableswithinthesame universe leads to unmanageable universes withtoomanycontextstomaintain.

Certain dimensions are used repeatedly in several univer-ses(e.g.time,employee).Sometimesthesedimensionsarea common denominatorwhenmerging data fromdifferentuniverses in the same report. For reports combining datafromdifferentuniversesconsider,creatingasuper-universebylinkingtheunderlyinguniversestoit.

Unnecessarydimensions,whicharespecifictotheunderly-inguniverses,canbehidden inthesuper-universe inorderto reduce complexity and avoid inconsistent queries. TheuniverseshouldonlybeusedforKPI’sanddimensionsthatactuallymatch.Therefore,theuniversecontextscanbekeptsimplebecauseonlythelinkstothecommondenominatorshavetobemaintained.

White Paper BO Universe Design

The Third Day - “Solid Ground”

You are not on solid ground unless your results arereliable. It is important that any query on a universe willalways returncorrect results, at least in a technical sense.This is probably themost important guideline for universedesign.Beforegoing live,extensive testing isnecessary toensurecorrectconfigurationofallcontextsandtoweedoutanyincorrectjoins.Iftoomuchlogicisbuiltintotheuniverse,testingbecomesintractable.Itisalwaysagoodideatocon-siderwaysof reducinguniversecomplexity.Business logiccanbeimplementedanywherealongthedatachainfromthesourceapplicationuptothereportandquery.Thebestplacetoputitlargelydependsonbudgetrestrictions,aswellasonthelevelofcredibilityandreusabilitythatisrequired.

implementationlevel/needs &restrictions

SOURCE APPLICATION -- ++ -- ++DWH TABLE - + - ++DWH VIEW + ~ + +UNIVERSE + ~ + ~REPORT/QUERY ++ - - --

IMPLEMENTATION COSTS

CREDIBILITY

MAINTENANCE COSTS

REUSABILITY

Veryoften,logicisimplementedonaqueryandreportlevel.This is quite tempting because of the low initial cost, andbecauseofthefactthattheprojectstartsoutasaprototype.Yet keeping logicona report level for a longerperiodcanlead tohighmaintenancecostsanderror-pronereports. Inmost reporting environments there is the need to movebusinesslogicfurtherupthedatachainratherthandowntothereport.Forexample,ifweknowthatemployeeclassifica-tions are going to be reused in several universes, suchclassificationsshouldbe implementedasadatabaseview.Theemployeeinformationwillthenberolledouttoalluniver-sesautomatically.EspeciallypeoplewhopreferrunningSQLqueriesdirectlyon theDWH,withoutusingaBOuniverse,willbegrateful foranybusiness logic(e.g.grouping)that isprovidedforthemonaDWHlevel.

Therearetimeswhenitisbesttonotjustrelyonageneralpurpose reporting system. Logic implemented and/ordisplayedwithinthesourceapplicationisoftenperceivedasmorecrediblethandataonsomereport.Ifyouwanttomakesure your numbers are used, tested, and finally trusted, itmight be a good choice to display them in the sourcesystem.Forexample, therecouldbe informationdisplayedontheCRMapplicationscreenshowingwhetheracustomeriseligiblefordiscounts.Eventhoughthedataqualityofsuchinformationmightbebad,itisnormallymoreacceptableandcertainlymoreaccessiblethanthesameinformationwithinareport.Also,anyreportinformationwillbeperceivedasmorecredibleifthesameinformationisvisibleinthesourceappli-

cation.Consistency is sometimes valuedmore highly thancorrectdata interpretation. This is abusiness choiceoftenobservedandalsomightmakesenseforthesakeofcompa-rability. Implementations in thesourceapplicationwillavoiddiscussionsaboutdatainterpretationbygivingthebusinesstheresponsibilityanddataownershipforimportantKPI’sandclassifications.Forexample,itmightmakesensetohavetheproducthierarchyandcataloguemaintainedwithintheCRMsystem instead of maintaining a product hierarchy withinreporting.

Someinformationissogenerallyusefulthat itshouldneverbe fragmented across various source systems or multiplereports.

ThenaturalplaceforsuchdataistheDWHandthenprovi-dedasshareddimensionuniverse.Examplesinclude:

• Generic date table/time universe that contains allpossible date objects, hierarchies, time filters andgroupings(e.g.YearToDate)

• Organization(Employee)hierarchyview/universe

• Mappingtable(s) forgroupingofvalues,e.g.products,etc.Ifitisdifficulttocreatetablesforthebusiness,thenonemapping table that fits all dimensionsmight be agood choice, otherwise a mapping table per object/dimension type isbettersincesecuritycanbeapplieddirectlyandthepurposeisclear.

The Fourth Day - “Sun, Moon and Stars”

If the reporting systemwere tobe theEarth -whatwouldthe Sun be? The source application (e.g. CRM System)actuallyfitsquitewellintosuchananalogy.ThesunenableslifeonEarthjustlikethesourceapplicationprovidesthedatatobeanalyzedtoareportingsystem.Makinguseofthesour-ceapplicationduringdevelopmentisveryusefulforvariousreasons:

• It helps to better understand the processes and theinconsistenciesofasystem.

• Business terms are often linked to the wording usedin source applications. Adopting the business termsfromthesourceapplication isoftenagoodwaytogobecauseitisbestunderstoodbytheusers.

• Reviewingdataonadetailed level.Often it is faster toreview detailed data in the source application, sincesuchsystemsaredesignedforthedetailedcase.

Itisimportanttogetaccesstosourceapplicationsatanearlystageoftheproject.Gettingsometrainingoractuallyworkingwiththesesystemsforafewdayswouldalsobeagoodidea.

White Paper BO Universe Design

Moons & Stars: Additional tools and helpers from the world of SAP BOpartnersmaybrightenupthenightlyreportingsky.Needsforautomationcanbe implementedusing theBOSDK library.ManySAPBOpartnershaveimplementedspecificsolutionsto automation problems. The following tools could be ofinterest:

• A tool that provides an overview of dependenciesbetweenreportsanduniverses.Reference:D1SolutionsDBOXS

• Atoolthatautomatesreportdocumentationbylookingattheuniverseobjectdescriptions.Reference:D1SolutionsDBOXS

• A tool for maintenance of access levels, users andrights.Reference:D1SolutionsDBOXS

• Atoolformaintenanceofhierarchies&navigationtreestobeusedintheuniverse.

• BO Performance Monitoring & other helpers:http://www.apos.com

• Another important resource for all BO developers istheBOBforumathttp://www.forumtopics.com/busobjwhichwillprovideyouwithhelpersandadditional tipsandtricksasyougoaboutbuildingyouruniverse.

The Fifth Day - “Birds & Sea Creatures”

Averyusefultechniquewhendesigninguniversesforalargeraudienceisusingthehideobjectfunctionality.Wecandividetheobjectstobecreatedintobirdsandseacreatures.Seacreaturesarenotvisible;theyarebelowthesurfaceandwillonlybeavailabletotheuniversedeveloper.Youcancreateasmanyandasstrangeobjectsyouwant.Iftheyturnouttobedinosaurs, let themgoextinct.Followthe followingguideli-nes:

• Neverbuildclasshierarchieswithmorethantwolevels.• Alwayspre-formatyourdateandnumberobjects.• Alwaysnameyourobjectswiththeclassnameandthen

theobjectname.Thereshouldnotbetwoobjectswiththesamenameinoneuniverse.

• Order theobjectswithin theclass inaconsistentway.Thiscouldbeasfollows:1. Physicalprimarykey2. Logicalprimarykey3. ReferentialDimensions

(iflessthan5objectsusesameclass,elseaddtoclass)

4. DateObjects(TopDown)5. OtherDimensions(TopDown)–Alphabeticalorder6. Measureobjects–Alphabeticalorder7. Unusedobjectshiddenatthebottom

• Replacenull valuescausedby leftouter joinswith thestring ‘N/A’.DependingonyourDBsystem, thecom-mandtodothisisCOALESCE(),ISNULL(),orNVL().

The Sixth Day - “Land Animals”

Wearenowabout toturnthebirdsandseacreatures intolandanimals.Abitliketheturnfromfishtofrog,weturnthecombination of a dimension with a basic measure into apivotedmeasure(landanimal).

Letusassumeyouhavethefollowingemployeetablewhichgives you an overview about how many FTE’s wereemployedduringacertaintimeperiodperdepartmentandemployeetype:

Withtheabovedatainputitcanbeatedioustasktocalcu-late the share of internal FTE’s versus the total number ofFTE’swithinthereportitself.InthiscaseitisbettertoprovidepivotedobjectsbyconstrainingtheFTEaggregationtotherespectiveemployeetypevalue.

Thefollowingsyntaxwouldbeusedtocreatethenewmea-sureobject“InternalFTE”.

EMPLOYEE TABLE:

TIME DEPARTMENT EMPTYPE FTE2011/01 MARKETING INTERNAL 302011/01 MARKETING EXTERNAL 22011/01 SALES FORCE INTERNAL 202011/01 SALES FORCE EXTERNAL 902011/02 MARKETING INTERNAL 352011/02 SALES FORCE INTERNAL 202011/02 SALES FORCE EXTERNAL 902011/03 MARKET&SALES INTERNAL 452011/03 MARKET&SALES EXTERNAL 902011/03 COMMUNICATIONS INTERNAL 10

-- “INTERNAL FTE”

SUM(CASE

WHEN EMPTYPE=’INTERNAL’

THEN FTE

ELSE 0

END) -- AS “INTERNAL FTE”

1. Note that the “AS” part has to be commented

out or left away. Else the query will raise

an error when the new measure object is used

in a where clause.

2. Note that formatting and commenting the SQL

will be greatly appreciated by anyone using/

analyzing the SQL

3. Note that “ELSE 0” can be optionally used in

order to enforce a zero return value. In

most cases this behavior is preferred to

NULL value returns. An advantage of the

zero value return is that no special func-

tions have to be applied when summing up the

pivoted measures (remember that NULL + 234

returns NULL and not 234)

White Paper BO Universe Design

©D1 Solutions AGZypressenstrasse71,Postfach,8040Zürich,Switzerlandwww.d1-solutions.com,info@d1-solutions.comT+41444351010,F+41444351015

Pivotedmeasuresareoftenthebetterchoicewithregardtousability.Theuserwillnotneedtobuildsuchobjectswithinthe report. Also, such pivotedmeasuresmight actually berequiredasan inputwhenworkingwithotherBItools (e.g.Xcelsius).

Apositivesideeffectofcreatingpivotedvalues isthattheycanbenamedinawaythatismoreunderstandabletobusi-ness users because it is based on something real theyalready know. In our example, EMPTYPE is more or lessmeaninglesswhile“InternalFTE”isveryclear.

However, each additional pivoted object in the universeentails somemaintenanceoverhead, because its definitionwillhave tochange if thepossiblevaluesof theunderlyingobjectchange.

Pivotedmeasuresshouldbecreatedforobjectsthatwillbeusedconstantlyandwhichareexpectedtohavealonglife.IntheEMPLOYEEexamplewewouldavoidcreatingapivo-ted measure for each of the departments since they arenumerousaswellassubjecttochangeeveryonceinawhile.

Anothertypeofpivotedobject istheuseofMINandMAX.Oftenthebusinessuserdoesnottrustaggregateddata.Thisisespeciallytruewhenitlookserroneousorillogical.Thebestway of dispelling distrust in data quality (stemming fromwrongqueries)istoprovideallthedetailrecordsusedfortheaggregationandKPIcalculation.

Theuserwillthenbeabletodrilldowntotheserecordsandunderstandwhythedataisthewayitis.Ifthereistoomucheffort involved in providing such functionality, it is a veryconvenientshortcuttoprovideMAXandMINobjectsoftheunderlyingdetailrecord.Thequerywillstillbeperformingwellandreturnareducedsetofdata.

Nevertheless, it will also provide the end user with twoexamples for eachaggregation.Evenbetter examples canbeprovidedbyusinganalyticalfunctionslikefirst_value()withawell chosenargument. In the employee example suchamin or max object would be the maximum and minimumemployeenumber.OryoucouldchoosesomeaverageguylikeDavidasanexample.

The Seventh Day - “Rest & Enjoy”

• Rest&Enjoy

AuthorCandidgraduatedwithadegreeinecono-micsfromUniversityofLausanne(HEC)in2000.Hisareasofexpertisearecustomercare,finance,controlling,andprocess-performancemanagement.Projectsinma-nufacturing,financialservices,andtelecom.Candidhasbeenintheteamsince2007.

Your contact for further informationSimonHefti,Chairmansimon.hefti@d1-solutions.com,P+41444351010

White Paper BO Universe Design