MSSQL教程之计划数据堆栈的注重事项(Part II)

限制,如果WHERE子句的查询条件里有不等号（WHEREcoloum!=），MySQL将无法使用索引。类似地，如果WHERE子句的查询条件里使用了函数（WHEREDAY（column）=），MySQL也将无法使用索引。计划|数据
EndUsers

Theendusersarewhoyouaredesigningandbuildingthedatawarehousefor?yourcustomers.Theyrelyontheinformationtheyextractfromthewarehouseinordertoworkmoreeffectivelyandefficiently.Theyalsoultimatelydeterminewhetherornotyourdatawarehouseprojectmetitsstatedgoals.Forthesereasons,yourprojectmustbebuiltaroundprovidingtheenduserswiththeinformationtheyneedinauser-friendlyfashion.

Theprimaryendusersfordatawarehouseinformationarereportrecipientsandquerydesigners.Theyprovidetheresultsoftheirworktothedecisionmakerswhodeterminethecourseofyourorganization.

ReportRecipients

Reportrecipientsaretheendusersmostdependentupontheinformationextractedfromthedatawarehouse.Theyincludebusinessanalysts,financialanalysts,mid-levelmanagers,seniormanagersandexecutives.Althoughsomeofthereportrecipientswillhaveparticipatedinthedesignprocess,theywillhavedifferentperspectivesasendusers.Theywillhavethegreatestinterestsinreportcontentformatandrequirements,timeframeandupdatefrequency,adhocquerycapabilitiesanddrilldown/drillthroughrequirements.

Reportcontentandformatrequirementsdefinewhatinformationispresentedandarranged.Yourenduserswilleachhaveaspecialperspectiveonwhatinformationisimportantintheperiodicreportstheyrelyon.Manytimestheywillneedsimilarsourcedata,butthereportswillcontaindifferentcalculatedvaluesandextrapolations.Evenwhentheyagreeonwhatinformationmustbepresent,theyeachhaveadifferentsetofrequirementswhenitcomestohowtheinformationisarranged.Eachoftheseviewsmustbetakenintoconsiderationwhendefiningyourprojectrequirements.

QueryDesigners

Querydesignersaretechnicallycompetentpeoplewhoaretaskedwithbuildingthequeriesthatprovidethereports.Theyhavetopleasethereportrecipientswhileworkingunderthoselimitationsimposedbythestructureandcontentofthedatawarehouse.

Thequerydesignersareavaluableresourceinthesameareasasthoserepresentedbythereportrecipients,butfromadifferentperspective.Theyfrequentlyaskyoutoincludecalculatedcolumnsthereportrecipientsdontknowarenecessary.Theyalsoaskforsimplerstructureinmanyareas,soastomaketheirquerydesignsmoremanageableandalsodemandhighlydenormalizedtableswhereverpossible.

Management

Managementwilltypicallyprovidethemostdemandingandleastdetailedsetofrequirementsfromthedatawarehouseproject.Youcanexpectsuchrequestsas,"Ineedtoknowhowprofitableweareonaquarterlybasis."or"Whoareourtop100customers?"Youwillthenhavetoworktodefineprofitabilityandtopcustomerintermsthatcanbeappliedtothedatawarehouse.

Managementrequirementsusuallyfallintothreegeneralcategories:strategicreportingandprojections,tacticalreportingandenterprisestandardscompliance.

StrategicReportingandProjections

Strategicreportingandprojectionarelongtermprocessesaimedatpositioningtheorganizationtoremaincompetitiveinthemarketplace.Strategyisdefinedasaplandesignedtoplaceanorganizationinanadvantageouspositionpriortocompetition.Fromthiswecanseethattheinformationneededtosupportstrategicinitiativesconcernslong-termtrendandmarketpenetrationanalysis,long-termrawmaterialinventoryavailabilityandusage,changesinthetechnicalenvironment,workforceprojectionsandcustomerdemographicinformation.Eachmanagementteamwillhaveitsownlistofspecificinformationtheyneedinordertohelpthemformulateoradjustthecorporatestrategy.

TacticalReporting

Tacticalreportingdealswithunderstandinghowday-to-dayoperationsaremeetingstrategicobjectives.Theseshort-termreportsmayconsistofdaily,weekly,monthly,quarterlyorannualanalysesofthecurrentstateofaffairswithintheorganization.Thesereportswillcontainoperationalinformationonsalesstatistics,workforceutilization,inventorymanagementandmeasuresspecifictoeachindustry.Thetimelinessofthesereportsrequiresinformationbemadeavailableasrapidlyaspossibleafteritiscaptured.Itisnotunusualformanagementtorequirethatdailyreportsbeavailablewhentheyarriveforworkonthefollowingday,orthatweeklyreportsbeavailableby9:00AMonMondaymorning.Yourdatawarehousewillhavetobetunedtoprovidetherequiredlevelsofperformance.

EnterpriseStandardsCompliance

Enterprisestandardscomplianceconsistsofinsuringthatallpartsoftheorganizationareworkingintheacceptedmanner.Thereportsthathelpmanagementdeterminetheextenttowhichthisistruecanrangefromverysimple,withonlyafewrows,eachcontainingonlyfourorfivecolumns,tocomplex,multi-pagereports,supportedbypivottablesandcharts.Theextenttowhichyourmanagementteamwishestotrackstandardscompliancewilleffectthelevelofdetailthatyouwillneedtocapture.Forinstance,shouldthemanagementteamwanttoinsurethateachworkgroupiscomplyingwithcorporatestandardsregardingworkhoursandbreaktimes,yourwarehousewillhavetocontainallthepertinentdataoneachemployeesscheduleoveraspecifiedperiodoftime.Youwillalsohavetobuildsummarytablestosupportmoregeneralanalyses.

SystemsStaff

Yoursystemsstaffmemberswillhelpidentifythebesthardware,softwareandconnectivityconfigurationsforyourwarehousingoperation.Theyalsowillhelpyouidentifytherisksandlimitationspresentedbyeachpossibleconfiguration.

Hardware

Hardwareconsistsofalltheprocessing,storageandperipheralcomponentsuponwhichyourdatawarehouseoperationwillreside.Yoursystemsspecialistswillhelpyoumakeintelligentandinformedchoicesregardingmainframe,mid-range,mini-computerorpersonalcomputerprocessing;multipleprocessing,eitherparallelordistributed;distributedorconcentratedstorage;andallthedecisionsthatdeterminethephysicalstructureofyourwarehouseenvironment.

OperatingSystemandSoftware

Insomecases,theoperatingsystem(OS)andsoftwarechoicesavailabletoyouwillbedeterminedbyyourhardwareselections.Inothercases,thehardwarechoiceswillaffect,butnotdictate,theOSandsoftwareoptionsavailabletoyou.Yoursystemsspecialistswillworkwithyoutodeterminewhichcombinationsfityourprojectsneedswhilecomplyingwithcorporatestandards.

Connectivity

Connectivitydealswithallthecomponentsandassociatedcommunicationsthatallowonecomputertosendandreceiveinformationfromanother.Yoursystemteammemberswillprovideyouwithvaluableinformationregardingthechoicesavailable,recommendedconfigurations,currentlyimplementeddesignsandanyenterprisestandardsyourprojectmustcomplyto.

SecurityStaff

Yourorganizationssecurityspecialistsfallintotwomaincategories,information/systemssecurityandfacility/physicalsecurity.Ineachcase,thesespecialistswillprovideyourteamwithinformationvitaltothesuccessofyourproject.

Information/SystemSecurity

Informationandsystemsecurityissuescenteronthreeprimarycategories:authentication,authorizationandusecontrol.

Authenticationistheprocessofinsuringthataparticularindividualisaccuratelyidentified.Authorizationdealswiththosemechanismsthatpreventapersonfromgainingaccesstoapplicationsordatabasestheyshouldnothaveaccessto,whileinsuringaccesstoapplicationsordatabasestheyhasobtainedapprovalfor.Usecontrolisconcernedwithlimitingeachindividualtooperationsthathavebeenidentifiedasappropriatefortheirjobandoperationalfunctions.

Facility/PhysicalSecurity

Facilityandphysicalsecurityissuesareconcernedwithwhereandwhenparticularindividualsmaybeincorporateproperties,whattheycandowhilethereandmakingsurecorporatepropertyissecuredagainstmaliciousdamageandtheft.Threeofthecategoriesoffacilityandphysicalsecurityarethesameasthoseforinformationandsystemsecurity:authentication,authorizationandusecontrolaswellaslossprevention.

Asisthecasewithinformationandsystemsecurity,authenticationistheprocessofinsuringthatanindividualis,infact,whotheypurporttobe.Authorizationconcernstheprocessofallowingonlythosewiththeproperclearancestoenterspecificareaswithincorporatefacilities,whileprovidingcompleteandreadyaccessforthosewhoshouldbethere.Usecontroldealswithinsuringeachfacilityorphysicalstructureisusedinaccordancewithcorporatepolicy,andthosepresentcomplywithallsafetyandregulatoryrequirements.Losspreventioninvolvesprotectingbothcorporateandpersonalpropertyfromtheftorlossthroughothermeans(fire,vandalism,etc.),aswellasprovidingforthesafetyofthoseindividualswhoareworkingatorvisitingatthefacility.

PhysicalMaintenanceStaff

Thephysicalmaintenancestaff?janitors,plumbers,electriciansandfacilitiesplanners?canprovideadditionalinformationtoyourprojectteam.Althoughthisgroupisseldomincludedinprojectrequirementsplanningsessions,theirinputcanbeessential.

Facilities

Assessingthefacilitiesavailabletoyourprojectteamforthedesignanddevelopmentphases,aswellasidentifyingthephysicallocationsforhardwareandforthelong-termwarehouseoperationsteamarevitaltoinsuringtheprojectcanbecompleted.

Ifyouareunabletofindworkspaceandmeetingroomsforyourteammembers,theprojectwillbedelayedatbest.Ifyoufailtoidentifytheroominwhichyourdatabaseserverwillbelocated?andreservetherequiredspaceandutilitiesassoonaspossible?youmayfindyourselfwithacompleteprojectplanandexcellentdesigndocumentation,butwithnoplacetosetthehardware.

Youwillalsoneedtoverifythatteammemberscanaccess,bothphysicallyandthroughtelecommunications,thevariouslocationsassociatedwithyourproject.Theyalsoneedtoknowthedatesandtimesaccessisvalidforeachfacility,andwhat,ifany,priornotificationsandapprovalsarenecessary.

Foreachoftheinvolvedfacilities,youneedatleastsomeparticipationfrompersonnelassignedtothatfacility,primarilythefacilitiesmaintenancestaff.Thesearethepeoplewhocanbestadviseyouonthosefactorsthatwilllimityouroptionsinthisrespect.

MaintenanceSchedules

Thefacilitiesmaintenanceteamswillalsobeabletoadviseyourteamastoscheduledmaintenanceactivitiesthatwillaffectyourprojectschedule.Suchmaintenanceactivitiesinclude:newconstruction,repairs,telecommunicationsupdates,powergridmaintenanceandlargescalepersonnelrelocations.

Thefacilitiesteammemberswillalsobeabletoadviseyourprojectteamonestimateddowntimesforunscheduledmaintenanceactivities.Theseactivitiesaretypicallyassociatedwithexternalproblemscompletelyoutsidethecontrolofyourorganization.Yourorganizationwillmostlikelyhaveaplaninplacefortherestorationofservicesshouldanysuchproblemoccur.Thisistheinformationthatthefacilitiesteammemberscanprovideandthussaveyourteamtheeffortofreconstructingthesupportingestimatesanddisasterrecoveryplans.
由MySQL用来存储数据的文件格式以已经被广泛地测试过，但是总是有外部情况可以导致数据库表被破坏：

也可谈一下你是怎么优化存储过程的?