WIXLIB

Avoiding CommonOBIEE MistakesLearning from Post-ImplementationAssessments

Agenda AgendaBI First ImpressionsBI DefinedTransformation ComponentsCase Studieso Data Storageo Metadata Definitionso Dashboards & Reportso User Analysis Summary QuestionsMANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 2

First Impressions of Business Intelligencebusiness intelligence isBetterBusinessDecisionsRawDatabrilliant!Lets install it right away!MANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 3

A Closer look at BIForrester’s Definitionbusiness intelligence is defined as:StrategicInsightsTransformationoccurs through: Methodologies Processes Architectures TechnologiesMANAGEMENT CONSULTING3/16/2011 OperationalInsightsTacticalInsights Best PracticesSYSTEMS IMPLEMENTATIONwww.archetypeconsulting.com BI SuccessMANAGED SERVICESArchetype Consulting, Inc – Confidential 4

BI Transformation ComponentsHow it all comes togetherDashboards& ReportsUser AnalysisMetadataDefinitionsData StorageMANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 5

Data Storage

Wayne EnterprisesAn OverviewApplicationsBusiness UnitSiebel Marketing, Call Center & AnalyticsMarketingThe ProblemThe CauseThe CatchWayne Enterprises’marketing departmentcould not execute their20 daily emailcampaigns within 24hoursFrequently requested,3rd party data wasbeing stored in aremote area of themarketing databasecausing slow runningmarketing programsThe 3rd party data wascorrectly being storedin a proven Oracle “outof the box” dataschema.MANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 7

Technical Terms often heard at Wayne EnterprisesTarget LevelSegment a list of fields thatcan be used tocreate a segment contains businesslogic thatdescribes therequired criteriaof a marketingcampaignQualified ListItem (QLI) the targetedentity of asegment Email address asQualified ListItemMarketingCache Data that satisfiessegment criteriawill be store theQLI value into atemporaryMarketing CachetableSegmentemail is not nullAND Request ‘Brand A’MC TARGET @example.comMANAGEMENT CONSULTING3/16/2011SYSTEMS oad FormatGUID2801WQS2801WQS2801WQS2801WQS The layout ofcampaign dataexported to thetransactionalapplicationsystemExample Fields First Name Last Name Email Address Request ID Package ID Consumer IDMANAGED SERVICESArchetype Consulting, Inc – Confidential 8

Uncovering the IssueWayne EnterprisesTracing the Campaign Execution1. Marketing Campaign Segment is scheduled in Siebel Marketing (OLTP) and a query isexecuted against OLAP database (Siebel Analytics)2. Query results are stored in MC Target Level table3. Attributes are added to the segment results in MC Target Level defined in thecampaign load so that it can be loaded into OLTP (Call Center, Siebel Marketing)MANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 9

Wayne EnterprisesCapturing the SQLCampaign Load FormatDISCOVERIESPoor SQLRED highlights show Outer joins to dimensionextension tables ( DX) Out of the box 3rd partyassigned columnsThis SQL statementexecutes for each ofthe 20 daily campaignsMANAGEMENT CONSULTING3/16/2011(select distinct T259560."ROW WID" as c1,T259555."LAST NAME" as c2,T259555."FST NAME" as c3,T259555."PRSP CON UID" as c4,T259551."INTEGRATION ID" as c5,T259555."INTEGRATION ID" as c6,T259574."ADDRESS KEY" as c7,T259574."PERSISTENT ID" as c8,T259555."ROW WID" as c9,concat(coalesce(cast(T259555."INTEGRATION ID" as VARCHAR ( 20 ) ) , ''), coalesce(' ' , '')) as c10,T259552."ADDRESS KEY" as c11,T259551."ROW WID" as c12,T259551."ST ADDRESS" as c13,T259552."ST ADDRESS2" as c14,T259551."CITY" as c15,T259551."STATE" as c16,T259551."ZIPCODE" as c17,T259551."COUNTRY" as c18,T259557."INTEGRATION ID" as c20,T259558."PROVIDED MODEL NUM" as c21,T259557."SERIAL NUM" as c22,T259557."ROW WID" as c23,concat(coalesce(cast(T259557."INTEGRATION ID" as VARCHAR ( 20 ) ) , ''), coalesce('0' , '')) as c24from"SIEBEL"."W PERSON D" T259555,"SIEBEL"."W PERSON DX" T259574,"SIEBEL"."W ORG DX" T259552,"SIEBEL"."W ORG D" T259551,"SIEBEL"."W ASSET D" T259557,"SIEBEL"."W ASSET F" T259560where (and "SIEBEL"."W PERSON D"."ROW WID""SIEBEL"."W PERSON DX"."ROW WID"and "SIEBEL"."W ASSET D"."ROW WID"and "SIEBEL"."W ORG D"."ROW WID"and "SIEBEL"."W ORG D"."ROW WID"and "SIEBEL"."W PERSON D"."ROW WID""SIEBEL"."W ASSET F"."CONTACT WID"and "SIEBEL"."W PERSON D". FST NAME is not nulland "SIEBEL"."W PERSON D"."PRSP CON UID" is not nulland "SIEBEL"."W PERSON D"."LAST NAME" is not nullSYSTEMS IMPLEMENTATIONwww.archetypeconsulting.com "SIEBEL"."W ASSET F"."ASSET WID""SIEBEL"."W ASSET F"."ACCNT WID""SIEBEL"."W ORG DX"."ROW WID"MANAGED SERVICESArchetype Consulting, Inc – Confidential 10

Technical SolutionWayne EnterprisesMoving the 3rd Party data All 3rd party data in the campaign load format was moved from the dimension extensiontables to the dimensions themselves. Wayne Enterprise’s marketing programs were completing in 1/3 of the originalexecution time, allowing for all daily campaigns to be completedMANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 11

Wayne EnterprisesThe Origin of the ProblemPreventative measuresBoth Business and IT failed to communicateThe Business failed tocommunicate workload ofmarketing campaigns.IT followed best practices bylimiting any customization to outof the box data mappingsThe Business also felt IT’s firstpriority was not to help theenable business activitiesIT failed to understand how 3rdparty was going to be usedOrganizational SolutionSME’s from IT and business created weekly check in meetings to moreclosely understand each other’s issues and discover areas for improvementMANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 12

Metadata Definitions

Vandelay IndustriesProblems with the MetadataThe ProblemThe CauseDashboard reports areshowing that customers arerequesting movies from onetrilogy but receiving moviesfrom another trilogyThe dashboard reports werecomparing consumerrequests (at the requestlevel) to consumerfulfillment (at the consumerlevel).MANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comThe CatchThe issue occurred whensegment results of twotarget levels with differentgranularity are stored in thesame history tableMANAGED SERVICESArchetype Consulting, Inc – Confidential 14

Vandelay Industries – Movies by Mail Division– A Recent PromotionMOVIE TRILOGY SPECIAL!FOR THEPRICE OFSTART NOW BY PICKING A MOVIE TRILOGYreceive your 1stmovie in a few daysMANAGEMENT CONSULTING3/16/2011your 2nd movie willarrive in a weekSYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comin two weeks, you’llreceive the 3rdMANAGED SERVICESArchetype Consulting, Inc – Confidential 15

Vandelay IndustriesKeanu Reeves entered ‘The Matrix’ TrilogyOLAP DATA WAREHOUSECUSTOMERORDERSData stored in customer order factORDER ID1000100110021003ORDER DATE1/12/20113/8/20112/20/20112/18/2011MANAGEMENT CONSULTING3/16/2011CUSTOMER IDCUSTOMER34SEAN ASTIN14KEANU REEVES27HARRISON FORD27HARRISON FORDSYSTEMS UESTTRILOGY PROMO LORD OF THE RINGSTRILOGY PROMOTHE MATRIXTRILOGY PROMOINDIANA JONESTRILOGY PROMOSTAR WARSMANAGED SERVICESArchetype Consulting, Inc – Confidential 16

Vandelay IndustriesThe Matrix Segment Chooses KeanuHow Keanu gets his first movieOLAP DATA WAREHOUSESEGMENT: Matrix 1Target Order ID’s whereCUSTOMERORDERSORDER ID1001SOURCE ‘TRILOGY PROMO’REQUEST ‘THE MATRIX’ORDER ID NOT IN MOVIES SHIPPEDCUSTOMERSOURCEREQUESTKEANU REEVES TRILOGY PROMO THE MATRIXOrder ID triggers thefirst movie to be sentMarketing Cache (segment results)QLIGUID1001MANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comSEGMENT: Matrix 1 20110308MANAGED SERVICESArchetype Consulting, Inc – Confidential 17

Vandelay IndustriesSaving the HistoryHow Keanu gets his first movieMarketing Cache (segment results)Campaign Load FormatFirst Name Last Name Source Order Id MovieQLIGUID1001SEGMENT: Matrix 1 20110308Using the Order ID,the Campaign LoadFormat adds NT DETAILSShippingVendorCampaign Load FileMANAGEMENT ID1021KEANUREEVESTHEMATRIX1 of 31001SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 18

Vandelay IndustriesA Different Matrix Segment Chooses Keanu One Week LaterOLAP DATA WAREHOUSESEGMENT: Matrix 2 (Reloaded)Target CUSTOMER IDs whereSHIPPINGHISTORYSHIP ID1021Customer ID inShipping Historytriggers the 2ndmovie to be sentITEM ‘THE MATRIX’SHIP DATE TODAY() – 7SHIP DATE CUSTOMER ID CUSTOMER ITEMKEANUTHE3/8/201114REEVESMATRIXSEQUENCEORDER ID1 of 31001Marketing Cache (segment results)QLI14MANAGEMENT CONSULTING3/16/2011GUIDSEGMENT: Matrix 2 (Reloaded) 20110315SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 19

Vandelay IndustriesSaving the HistoryMarketing Cache (segment results)Campaign Load FormatQLIGUIDFirst Name Last Name Source Order ID Movie27SEGMENT: Matrix 2 (Reloaded) 20110315Using the OrderID, the CampaignLoad Formatadds OrderAttributesSHIPPINGHISTORYSHIPMENT DETAILSShippingVendorCampaign Load FileMANAGEMENT CONSULTING3/16/2011SHIP IDCUSTOMERITEM1021KEANU REEVES1045KEANU REEVESTHE MATRIXTHE MATRIXRELOADEDSYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comSEQUENCE ORDER ID1 of 310012 of 31001MANAGED SERVICESArchetype Consulting, Inc – Confidential 20

Vandelay IndustriesThe Harrison Ford ProblemWhen a Customer Orders more than 1 TrilogyRequestsORDER ID ORDER DATE CUSTOMER ID2/16/20112710022/18/2011271003CUSTOMERHARRISON FORDHARRISON FORDSOURCETRILOGY PROMOTRILOGY PROMOREQUESTINDIANA JONESSTAR WARSShipping HistorySHIPID95796298999210071014SHIPPED CUSTOMERITEMSEQUENCE ORDER IDDATEID2/17/201127RAIDERS OF THE LOST ARK1 of 310022/19/201127STAR WARS1 of 310032/25/201127TEMPLE OF DOOM2 of 310032/27/201127THE EMPIRE STRIKES BACK2 of 310033/5/201127THE LAST CRUSADE3 of 310023/7/201127RETURN OF THE JEDI3 of 31002ORDER REQUESTINDIANA JONESSTAR WARSSTAR WARSSTAR WARSINDIANA JONESINDIANA JONES?!!!!MANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 21

Vandelay IndustriesUsing Customer ID to create Campaign Load FileThe Harrison Ford Problem explainedCampaign Load FormatMarketing Cache (segment results)First Name Last Name Source Order ID MovieQLI27SHIPPING HISTORYGUIDSEGMENT: IND. JONES 2 (Temple ofDoom) 20110315Customer ID cannot accurately select theOrder ID if there are multiple. The systemselects one, often times incorrectly.SHIP SHIPPED CUSTOMERITEMSEQUENCE ORDER ID ORDER REQUESTIDDATEID957 2/17/201127RAIDERS OF THE LOST ARK1 of 31002INDIANA JONES962 2/19/201127STAR WARS1 of 31003STAR WARSCUSTOMER IDITEM27TEMPLE OF DOOMORDER ID1003ORDER REQUESTSTAR WARSCampaign Load FileMANAGEMENT CONSULTING3/16/2011SYSTEMS IMPLEMENTATIONwww.archetypeconsulting.comMANAGED SERVICESArchetype Consulting, Inc – Confidential 22