Need-for-Speed Performance Tuning

1. Need-for-SpeedPerformance Tuning Duncan Klett VP Analytics Research

2. Outline • Performance guidelines • 10.1 query improvements • Impact of guidelines • Custom data model design

3. Performance Guidelines (1) • Avoid “set” expressions • Use Lookup and Join • Use the “right” source worksheet(s) • Use common source component sheet if possible • Use single IN for all component sheet filters rather than individual filters • Use “expression-based” fields in data model rather than worksheet queries

4. Performance Guidelines (2) • Use “summarization” functions for group totals, fractions, etc • Collapse levels in composites • Defer bucketing • Use Reference to tables rather than strings • Avoid string concatenation if possible • Use Variables to identify “focus” items • Customized Data Model: Use “references” rather than strings

5. Choosing the table to report • Queries on Input data are fast (input tables, input fields) • Use Activity if “driver” and AvailableDate not needed • Use CTPActivity (or CTPPlannedOrder) if AvailableDate is needed but Driver is not needed • Full-level-peg tables are slower, but have driver information • FlatBillUp and FlatBillDown can be slow if multiple time-phased BOMs, substitute BOMs, and/or PartSources

6. Avoiding FlatBill* tables • 10.1 has new analytic variable:RR_Analytics_FlatBill_MergeIdentical • Reduces time and memory requirements by collapsing identical BOM and PartSource records when calculaing • To get a list of Parts that might be within the BOM (and within the same site), try (see data model guide): • Part In Components.Component OF ($SelectedFilter and Site[Eval('Site \'' + $SelectedSite + '\'')] )

7. Choosing the table to report • Use WhereConsumed rather than: • WhereConsumedForDemand or LateSupply unless: • reporting data from less than 10 driver parts and • filter is in context of independent demands • WhereConsumedForSupply • unless you need to peg to intermediate supply orders

8. Example Worksheet (for performance) • Crosstab to show demand, supply and balance, pegged back to selected part(s) by driver • Filter so only components with negative balance shown • Include Description and component make/buy sourcing

9. Worksheet Structure & Performance Composite Structure:

10. Original Query SRCWhereConsumedDemand: WhereConsumed[DriverPart[PRIVATE s'IndDmd parts' AND ABCCode='A' AND Name='CRUISER'] AND DriverPart.Site[PUBLIC Site 'All Sites']] {DriverPart=DriverPart.Name :By , DriverSite=DriverPart.Site :By , DriverDescription=DriverPart.Description :By , Level=Part.MultiSiteLowLevelCode :By , Part=Part.Name :By , Site=Part.Site :By , Description=Part.Description :By , MakeBuy=IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') :By , DemandDate=NeedDate :Bucket(CMPBalance!!Buckets!) , Quantity=NeedQuantity }; SRCWhereConsumedSupply … , AvailableDate=SplitSupplyAvailable :Bucket(CMPBalance!!Buckets!) , Quantity=NeedQuantity }; CMPBalance (SRCWhereConsumedDemand! OUTERJOIN SRCWhereConsumedSupply!) {DriverPart=SRCWhereConsumedDemand!DriverPart :By , DriverSite=SRCWhereConsumedDemand!DriverSite :By , DriverDescription=SRCWhereConsumedDemand!DriverDescription :By , Level=SRCWhereConsumedDemand!Level :By , Part=SRCWhereConsumedDemand!Part :By , Site=SRCWhereConsumedDemand!Site :By , Description=SRCWhereConsumedDemand!Description :By , MakeBuy=SRCWhereConsumedDemand!MakeBuy :By , Date=SRCWhereConsumedDemand!DemandDate :Bucket(CMPBalance!!Buckets!) , Demand=SRCWhereConsumedDemand!Quantity , Supply=SRCWhereConsumedSupply!Quantity , Balance=SRCWhereConsumedSupply!Quantity - SRCWhereConsumedDemand!Quantity: Subtotal(Running, Site) }; CMPBalanceFilter (SRCWhereConsumedDemand! OUTERJOIN SRCWhereConsumedSupply!) {DriverPart=SRCWhereConsumedDemand!DriverPart :By , DriverSite=SRCWhereConsumedDemand!DriverSite :By , DriverDescription=SRCWhereConsumedDemand!DriverDescription :By , Level=SRCWhereConsumedDemand!Level :By , Part=SRCWhereConsumedDemand!Part :By , Site=SRCWhereConsumedDemand!Site :By , Description=SRCWhereConsumedDemand!Description :By , Date=SRCWhereConsumedDemand!DemandDate :Bucket(CMPBalance!!Buckets!) , Balance=SRCWhereConsumedSupply!Quantity - SRCWhereConsumedDemand!Quantity: Subtotal(Running, Site) }[Date < d'20120305']; INBalanceFilter (CMPBalanceFilter!)[CMPBalanceFilter!Balance < 0] {ComponentPart=CMPBalanceFilter!Part :By , Site=CMPBalanceFilter!Site :By }; ComponentPlanning (CMPBalance!)[{ CMPBalance!Part, CMPBalance!Site } IN InBalanceFilter!] {DriverPart=CMPBalance!DriverPart :By :m n , DriverSite=CMPBalance!DriverSite :By :m n , DriverDescription=CMPBalance!DriverDescription :By :m n , Level=CMPBalance!Level :By :m n , Part=CMPBalance!Part :By :m n , Site=CMPBalance!Site :By :m n , Description=CMPBalance!Description :By :m n , MakeBuy=CMPBalance!MakeBuy :By :m n , Date=CMPBalance!Date :Bucket(ComponentPlanning!!Buckets!) :m n , Demand=CMPBalance!Demand :m n , Supply=CMPBalance!Supply :m n , Balance=CMPBalance!Supply - CMPBalance!Demand: Subtotal(Running, Site) :m n };

11. Worksheet Performance Tool Where did the time go? • 2 almost identical queries on WhereConsumed • 7,000+ records (after bucketing) eachIncluding a “set” expression for make/buy • Identical source sheets are RE-USED

12. Improvements • Merge the WhereConsumed queries into one • Pass Part as a reference • Removed Site, Description, Level fields • These can be calculated in the “top” sheet from the References • Defer bucketing • Use separate IN sheet to identify all POSSIBLE components • Avoid “Set” expression to determine Make, Buy, etc.

13. Identifying Possible Components • Component sheet based on Part • Use filter expression (rather than filter radio buttons) • Example Filter expression: In Components.Component OF ($SelectedFilter and Site[Eval('Site \'' + $SelectedSite + '\'')] ) • First column reports Self (as a Reference)

14. Avoiding a “set” operation • (Original) WhereConsumed expression: IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') • Move per-record structure in SRC WhereConsumed to per-part in Part query • Add column to InSelectedComponents worksheet • (Revised) Part expression: IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') +IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') +IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') • Use Lookup in final worksheet: MakeBuy=Lookup( CMPBalanceNewFilter!Part, InSelectedComponents!, 'NONE', Exact)

15. Setting a field as “Reference”

16. SRC sheet column comparison • Revised • DriverPart=DriverPart :Ref :By , • Part=Part :Ref :By , • AvailableDate=SplitSupplyAvailable :By , • DemandDate=NeedDate :By , • Quantity=NeedQuantity Original (Demand, repeated for Supply) • DriverPart=DriverPart.Name :By , • DriverSite=DriverPart.Site :By , • DriverDescription=DriverPart.Description :By , • Level=Part.MultiSiteLowLevelCode :By , • Part=Part.Name :By , Site=Part.Site :By , • Description=Part.Description :By , • MakeBuy=IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') + IF( has Part.PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') :By , • DemandDate=NeedDate :Bucket(CMPBalance!!Buckets!) , • Quantity=NeedQuantity

17. (Improved) Worksheet Structure & Performance Composite Structure (10.1) & Query Times: 9.5.1 Query times

18. Revised Query InSelectedComponents Part[((Components.Component OF $SelectedFilter and Site[Eval('Site \'' + $SelectedSite + '\'')] ))] {PartRef=Self :Ref :By , MakeBuy=IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Make'], 'Make ','') +IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Buy'], 'Buy ','') +IF( has PartSources [ LastEffectiveDate > d'mrpdate' and Type.PlannedOrderSupplyType.Source='Transfer'], 'XFER','') :Max }; SRCWhereConsumed WhereConsumed[DriverPart[PRIVATE s'IndDmd parts' AND ABCCode='A' AND Name='CRUISER'] and (Part IN INSelectedComponents!) AND DriverPart.Site[PUBLIC Site 'All Sites']] {DriverPart=DriverPart :Ref :By , Part=Part :Ref :By , AvailableDate=SplitSupplyAvailable :By , DemandDate=NeedDate :By , Quantity=NeedQuantity }; CMPSupply (SRCWhereConsumed!){DriverPart=SRCWhereConsumed!DriverPart :Ref :By , Part=SRCWhereConsumed!Part :Ref :By , AvailableDate=SRCWhereConsumed!AvailableDate :By , Quantity=SRCWhereConsumed!Quantity }; CMPDemand (SRCWhereConsumed!){DriverPart=SRCWhereConsumed!DriverPart :Ref :By , Part=SRCWhereConsumed!Part :Ref :By , DemandDate=SRCWhereConsumed!DemandDate :By , Quantity=SRCWhereConsumed!Quantity }; CMPBalanceNew (CMPSupply! OUTERJOIN CMPDemand!){DriverPart=CMPSupply!DriverPart :Ref :By , Part=CMPSupply!Part :Ref :By , Date=CMPSupply!AvailableDate :Bucket(CMPBalanceNewFilter!!Buckets!) , Supply=CMPSupply!Quantity , Demand=CMPDemand!Quantity , Balance=CMPSupply!Quantity - CMPDemand!Quantity: Subtotal(Running, Part) }; CMPBalanceNewFilter (CMPBalanceNew!){Part=CMPBalanceNew!Part :Ref :By , DriverPart=CMPBalanceNew!DriverPart :Ref :By , MinBalance=CMPBalanceNew!Balance :By: Subtotal(Min, Part) , Date=CMPBalanceNew!Date :Bucket(CMPBalanceNewFilter!!Buckets!) , Supply=CMPBalanceNew!Supply , Demand=CMPBalanceNew!Demand , Balance=CMPBalanceNew!Balance }; ComponentPlanningNew (CMPBalanceNewFilter!)[CMPBalanceNewFilter!MinBalance < 0] {DriverPart=CMPBalanceNewFilter!DriverPart.Name :By :m n , DriverSite=CMPBalanceNewFilter!DriverPart.Site :By :m n , DriverDescription=CMPBalanceNewFilter!DriverPart.Description :By :m n , Level=CMPBalanceNewFilter!Part.MultiSiteLowLevelCode :By :m n , Part=CMPBalanceNewFilter!Part.Name :By :m n , Site=CMPBalanceNewFilter!Part.Site :By :m n , Description=CMPBalanceNewFilter!Part.Description :By :m n , MakeBuy=Lookup( CMPBalanceNewFilter!Part, InSelectedComponents!, 'NONE', Exact) :By :m n , Date=CMPBalanceNewFilter!Date :Bucket(ComponentPlanningNew!!Buckets!) :m n , Demand=CMPBalanceNewFilter!Demand :m n , Supply=CMPBalanceNewFilter!Supply :m n , Balance=CMPBalanceNewFilter!Supply - CMPBalanceNewFilter!Demand: Subtotal(Running, Site) :m n }

19. Setting/Displaying “Focus Part” • Previously, needed a column expression, such asSelectedPart = IF ( Part = $FocusPart, ‘Y’, ‘’) • Then, use conditional formatting:Part = SelectedPart • Now, simply use Column Part value = $FocusPartin Conditional Formatting • Formatting is applied, no re-running of query is required

20. Define Workbook Variable

21. Define XML for Set Focus from Part column

22. Set Focus

23. Focus Display

24. Customized data model design • Avoid strings • Preferably only use for “key” fields and descriptions • Put the field on the “right” table • If the value is the same for a set of data, put the new field on a “header” record for the set • Original new fields on IndependentDemand: • (all fields were Strings) • U_CustomerLocation • U_CustomerCountry • U_CustomerRegion • Alternative 1 • IndependentDemand: • Order -> Customer • Customer: • U_Location • U_Location: • U_Country • U_Country • U_Region • Alternative 2 • IndependentDemand: • U_CustomerLocation • U_Location: • U_Country • U_Country • U_Region • Advantages of Alternatives: • Easier maintenance • Allows drop-lists for data entry • Ensures data consistency • Faster, simpler queries • Avoids long chains of nested IF statements

25. Performance Guidelines • Avoid “set” expressions • Use Lookup and Join • Use the “right” source worksheet(s) • Use common source component sheet if possible • Use single IN for all component sheet filters rather than individual filters • Use “expression-based” fields in data model rather than worksheet queries • Use “summarization” functions for group totals, fractions, etc • Collapse levels in composites • Defer bucketing • Use Reference to tables rather than strings • Avoid string concatenation if possible • Use Variables to identify “focus” items • Customized Data Model: Use “references” rather than strings

26. ThanksQuestions ?? Duncan Klett