Current State of the Hbc Brand

Document Sample
Current State of the Hbc Brand Powered By Docstoc
					       Workload Management
         HBC Case Study
       IRMAC, January 2008

      Enter Date in Title Master
Shelley Perrior -DBA team lead


Corporate and presenter background
WLM case study
      System profile
      Business goals
      WLM configuration
Other points
About HBC

About HBC:
Hbc – the Hudson’s Bay Company
     Canada’s oldest and largest diversified general
      merchandise retailer
     Incorporated on May 2, 1670
     More than 580 Stores in all 10 Provinces across
     5 Retail formats, along with an eRetail portal
         The Bay – Department
         Zellers – Mass
         Home Outfitters – Specialty home
         Designer Depot – Deals outlet
         Fields – Discount
     70,000 associates focused on exceptional customer
     Privately owned as of 2006
Database Group - HBC

Presenter Background:

•   Supervisor database administration for all databases

•   Teradata technical lead, Teradata Certified master

•   Liaison with business and application teams

•   Extensive experience in system and application
    performance tuning on Teradata
Configuration Of Data Warehouse:

• Database is Teradata V2R6.1.1
• BI reporting tool is Microstrategy 8.0
• Current production system is a 12 node 5380
  system with 7.5 TB of data
• Dev system is a 4 node 5350 system.
• Both Prod and Dev databases are at V2R6.1.1
• Current workload consists mainly of a Business
  Intelligence application using Microstrategy for
  reporting and our Fraud Control application. Also
  have limited adhocs running as well.
• We use TDWM and Priority scheduler to monitor
  and control workload.
Business goals to address with WLM

Business goals to address with WLM

Goals in priority order:
      Goal 1: Ensure a response time of less than 10
       seconds from the POS for Returns transaction

      Goal 2: Ensure 95% of all Microstrategy queries
       finish in under 10 minutes

      Goal 3: Ensure ETL processing completes within
       specified batch window

      Goal 4: Accommodate effectively, emergency
       requests and changes in workload (daily, monthly,
       weekly etc)
Problems if WLM not used:

     Heavy workload from Microstrategy can threaten the
      response times for the higher priority work.

     Potential runaway queries may monopolize system

     Changes in workload may not be handled effectively
      (i.e.. batch runs too long and impacts business
WLM Configuration:

Work   categories (with different WLM controls)
      Transaction load and verification from POS
      Microstrategy Reporting
      ETL
      Other

WLM controls: Resource allocation & priority controls
   CPU allocation: Ensure a percentage reserved for
     high priority work (expedited workload)
   Use milestones to push short queries through faster
   Spool space usage limits
   Allocations change by time of day (for ETL)
   controls set up by user account code
WLM Configuration cont’d:

Individual user request controls
      Max allowable spool
      priority of query by user account code
Concurrency controls
      Limit number of reports per individual, total number
       on system, no batch in certain windows.
Emergency Query privileges
      Allow for special situations when queries may need
       a higher priority.
Other points:
WLM implementation process:
     Work with application to identify business
     Identify query grouping (i.e.. application, type of
      query, etc)
     Collect and analyze data on resource usage
     implement WLM controls and monitor
     adjust as required until desired results are met
     Set up SLA
     set up monitoring and alerts
Lessons Learned:

- Workload management is an ongoing process

- Good communication is necessary between
  applications and database team

- SLA’s are necessary

- Monitoring critical

- Historical data important

Shared By: