MSSQL教程之怎样创立一个乐成的数据堆栈(data ware...

用一个库#bak_database存放这些历史数据。创立|数据
怎样创立一个乐成的数据堆栈(datawarehose),上面的故事将告知你！



Thecompanysfirstdatawarehouseprojectbeganwithacasualconversationbetweenseveralexecutivesontheirwaytolunch.ThepeopleinvolvedweretheITmanagerfordecisionsupportaswellasseveralmembersofadepartmentthathadjustdecidedtoinstalladatawarehouse.TheyhadplannedtoinstalltheirdatawarehousewithoutanyinvolvementfromtheITdepartment;nonetheless,thefollowingconversationensued:
"Weurgentlyneedadatawarehousetoanalyzeourdata!""Inthatcase,whydontyoutakeanOLAPtoolwithamultidimensionaldatabase?""Isitpossibletomakethesalesfiguresavailabletooursalespeople?""Yes,ofcourse,thatsnoproblembecauseofitsWebcapacity.""Weneedouranswersveryfast.""Performanceisntanissue,thedatarequestedcanbemadeavailableonalocalserver.""Great,whencanwestartouranalyses?""Installingsuchasystemshouldnttakemorethanafewweeks."
Encouragedbythesecasualtipsfromanexpert,thedepartmentdecidedtobuildadatawarehousethatcorrespondedexactlytoitsspecificneeds.Somemonthslater,thedatawarehousewasinstalledaccordingtotheoriginalspecifications.Afterthefirstsuccesseshadbecomepublicknowledgewithinthecompany,otherdepartmentsbegantoshowinterestinthedatawarehouse.Proudly,thesystemwasdisplayed,andenthusiasmwasspreading.Suddenly,eachdepartmentwantedtheirowndatawarehouse,andrequestsbegantopileuponthedesksoftheITmanagers.However,apartfromthecasualconversationpreviously,theITdepartmenthadnotbeeninvolvedinthedevelopmentofthisfirstdatawarehouse.Theprojectitselfhadbeenimplementedbythedepartmentwiththehelpofanexternalsystemintegrator.Nobodyhadplannedonintegratingadditionalusergroups.Ithadbecomeimperativetofurtherdevelopthissuccessfuldatawarehouse.Atthispointitbecameclearthatthedepartmenthadlockeditselfintoadatamartwithonlylimitedscalability,insteadofbuildingadatawarehousewithunlimitedcapacityforexpansion.Thisdifferencebetweendatamartsanddatawarehousesisabasicissuethatthewholecompanynowhadtoface.
WhattodoNext?
Basedonthesituationmentioned,somequestionsarise,suchas:Canadatawarehouseoriginallyconceivedonlyforonedepartmentbeusedforthewholecompany,orshouldnewdatamartsbebuiltforeachdepartment?Ifthelattersolutionispreferred,howwouldonedepartmentaccessdatafromanotherdepartment?Whowillguaranteethatalluserswillreceiveexactlythesameinformation?Thequestionaboutwhethertostartwithdatamartsoradatawarehousehasbeenwidelydiscussed.1,2,3Itcanonlybeansweredbyclearlydefiningtheprojectsgoals:doesithavetocovertheinformationneedsforcertaindepartmentsorisitseenasthefirststeptowardasharedenterpriseinformationpool.Ifonlydepartmentalneedsaretheissue,itwillsufficetoinstallsomeisolateddatamarts.However,ifacompanyregardsaccesstoanintegrated,company-widedatabaseascriticalforitsfuturesurvivalinthemarket,thenanenterprisedatawarehouseisthesolutiontoimplement.

Mythoughts,sofar,mayhavecreatedtheimpressionthatthereareonlytwooptions:eitherquicklyinstallafewdatamartstocoverafewdepartmentscurrentneedsforinformationorembarkontheexpensiveadventureofinstallinganenterprisedatawarehouse.Thereisathirdoptionthatcombinesthebestofbothworldsandcanbeimplementedquicklywithoutsacrificingfuturegrowthoptions.Thisthirdoptionistolaydownthefoundationofanenterprisedatawarehousebystartingwithascalabledatawarehouseframeworkinapilotproject.
HowtoProceed
Theproceduresthatleadtothisscalabledatawarehousepilotprojectarespecificallydesignedtosatisfytwoseeminglycontradictoryrequirements?fastdeliveryandexpandability.Assumingtheprojectiswellprepared,itshouldnottakemorethanthreeorfourmonthstoimplementafullyoperationalpilotforanenterprisedatawarehouse.Afteritisfinished,acompany-widedatawarehouseplatformwillbeavailableallowinguserstoexecuteconcreteanalysesanddevelopabetterunderstandingoftheirrealandsharedneeds.

Whatisthedifferencebetweenadatawarehousepilotandadepartmentaldatamart?Infact,thesetwoapproachesdiffermoreintheirstrategicgoalsthanintotalexpenditurerequiredforconceptualizationandimplementation.TorunaprojectwithinadepartmentmeansthatyoudonothavetonegotiatewithotherdepartmentsandITmanagers?somethingthatcanprovetime-consuming.Bycontrast,ifyouwanttoestablishacompany-wideproject,youmustcoordinatethiseffortwithotherdepartments,ITmanagementandtopexecutives.

Figure1:Theprocessofcreatingapilotforanenterprise.

Figure1showsapreparatoryphasetostartthedatawarehousepilotproject.Thoroughpreparationswillensurethatthepilotprojectwillnotexceedathreetofourmonthtimeframe.Amongotherthings,theprojectteamwillhavetoclarifytechnicalissuesregardingthesystemandthecontractswiththesystempartnersselected,issuesmostlyarisingoutofthechosendatawarehousearchitecture.Theselectioncriteriaforthecentraldatabasecomputerwilldependupontheamountofdataanticipatednow(andinthepossiblyknownfuture),thenumberandtypesofusersandthecomplexityofthequeries.Fromtheuserspointofview,theselectionoftheanalysistoolisthemostcriticalissue;however,thankstostandardizedinterfaceslikeODBC,itisnotmandatorytostaywithachosentoolforever.Inthebeginning,itissufficienttohaveasuitableOLAPtoolformultidimensionalanalysisandsoftwareprogramsforaccessingthedatawarehousedatabasedirectly.
ProjectDesign
Duringthedesignphase,alltheinformationnecessaryforimplementingthedatawarehousemustbegathered,suchas:
Requirementsofthedepartmentsregardingthepotentialinformationuses;Descriptionofsourcedataused;Definitionofbusinessterms,datadefinitionsandtransformationrules;Datamodelsforthecentraldatawarehouseandthelocaldatamarts.
Simultaneously,thenecessaryhardwareandsoftwaremustbeinstalled.Basically,thedesignphasecanbebrokendownintofoursteps:

Businessquestionsfromdepartments.Inordertoincreasethepilotprojectschanceforsuccess,theselectedbusinessquestionsneedtobeofthegreatestpotentialusefulness.Businessquestionsdonotnecessarilyhavetobestatedasquestions.Existingreportsthatcontainkeyfiguresorconcretesuggestionsastoanalysesnotpossiblebeforecanalsobeused.

Datasourcesavailable.Aftertheusersrequestshavebeenroughlyanalyzed,theITdepartmentmustinvestigatethesourcesystemsandinterfacesavailablewithinthecompany.Duetothetimeconstraintsthepilotprojectfaces,onlydatacanbeconsideredthatisavailableandmeetscertainqualitystandards,suchascompletenessandcorrectcontents.Forasuccessfulpilotinstallation,itisimportanttofocusonthemostimportantrequirements.Therefore,thebusinessquestionsmustbecorrelatedtotheavailabledata.

Businessdatamodel.Thebusinessdatamodelreflectstherealobjectscustomer,order,product,etc.andtheirrelationshipstoeachother.Inordertorepresentthemcorrectlyinthebusinessdatamodel,businessruleshavetobeapplied,suchas"eachorderrelatestoonecustomer,"or"eachcustomercanbelongtovariouscategories."4

Logicaldatamodel.Thelogicaldatamodelinitsnormalizedformisbasedonthebusinessdatamodel,andallobjectsarerepresentedwiththeirattributes.Usually,notallattributesavailablefromthesourcesystemsareneededforansweringthebusinessquestionssubmitted.However,potentiallyusefuldataelementswillbeintegratedsoitwillnotlaterbenecessarytorepeatalltheanalysesperformedforthepilotproject.


Figure2:Allthedataisavailablethroughtheaccesslayer.

Forreasonsofperformanceorbecausethequerytoolrequiresit,denormalizeddatamodelsareneededalongsideanormalizeddatamodel.5Onepossibilityistocomplementthenormalizeddatamodelwithsummarytables.Inanotherapproach,so-called"starschema"or"starmodels"arecreatedinadditiontothenormalizeddatamodel(seeFigure2).Togetherwiththenormalizeddata,theyareavailabletousesthroughviewsonthedatabase.Eachtime,thedatawarehouseisaccessedthroughthesecuritylayer,inwhichalltheaccessauthorizationsarestores.Thenormalizedapproachprovidesamagnitudeofmuchgreatercapabilityandscalabilityinallowingforanyquestiontobeaskedofthedataandalsotoeasilyaddmoredatainthefuturetothedatawarehousedatabase.

Figure2representsthedatamartsaslogicalstructures,i.e.,thenumbersarerecalculatedeachtimetheyarecalledup.Whenstartingthepilotproject,thefirststepshouldbetocreatethedatamartslogically.Onlyifperformanceisreallylacking,willtheybephysicallyimplementedbyusingfacttables,sinceoptimizingperformanceisnothepilotprojectstoppriority.Iftheperformanceisacceptable,itissufficienttobeginanalyzingthedataselected.Morefine-tuningofboththedatabaseandthetoolsutilizedshouldbeaccomplishedAFTERtheusers/managershavebeguntoreporttheirexperiencesandnewvalue.

Anotewithregardtothesetwodifferentdatamodels:thenormalizeddatamodelrepresentsallthebusinessrelationshipsand,therefore,shouldnotbechangedwithoutverygoodreasonsfordoingso.Bycontrast,datamartsareforthemostpartbasedonstarschemamodelsandcontaindataforspecificsubjectareas.Ifanorganizationutilizedstarmodelsandtherearechangingbusinessrequirements,thedatamartshavetoberedesignedand/orre-adjustedtomeetanynewbusinessrequirements.Thiscanbeveryexpensiveandalsolimitthefuturescalabilityandgrowthofyourdatawarehouse.
CheckingtheResults
Thelaststepbeforeadoptingthelogicaldatamodelistocheckitbyusingselectedbusinessqueries.Atypicalbusinessquerymaybe:"Givemeallsalesintheaspecificmonth,brokendownintoindustries(i.e.,hotelsandrestaurantsonly);numberoftransactions;typesofcustomers;andmodeofpayment."

Usingthisquery,systemintegratoranduserscheckthemodeltablebytabletofindpossibleinterpretativeerrorsofthedatamodeler.Experienceshowsthatendusersareverywellabletounderstandalogicaldatamodel,eveniftheyhaveneverseenonebefore.Particularlyfordirectqueriestothedatabase,aprofoundunderstandingofthedatawarehousedatamodelisanecessity.
ImplementationoftheDesign
Afterfinishingthedesignphasewiththesystemcheckoutlined,thedesignwillbeimplementedonthetargetsystemwiththefirstuserscreatinganalysisandreports.Theimplementationphaseconsistsofallstepsnecessarytotransferdatafromtheoperationalsystemsintothedatawarehouse.

Corestepsforsuccessinusingthismethodare:
Thetransformationofthelogicaldatamodelintoaphysicaldatastructureonthetargetsystem;Thecreationofextractionandtransformationprograms;Theimplementationoftherequiredcontrolprocedurestoperiodicallyupdatethedatainthedatawarehouse;Usersdefiningandtestingtheirnewanalysisandreporting.
Apilotprojectforanenterprisedatawarehousewillusuallycontainonlyafewgigabytesofdata,involveoneortwodepartmentsandtwotofoursourcesystems.

Itmayrequiremorecoordinationbetweendepartments,ITmanagersandcompanyexecutivesthandoesaquicklyinstalled,isolateddatamart,buttheseeffortswillreallypayoffoncethepilotdatawarehouseisupandrunning.Yourcompanywillbeusingthisplatformbothforitscurrentinformationalneedsandwithaneyetothefutureasyourbusinessandyourrequirementsexpand.




既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中。

备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。

无法深入到数据库系统层面去了解和探究

总感觉自己还是不会SQL

然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

比如日志传送、比如集群。。。

如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。