搜索
平常心1Whatisthedifferencebetweenadatawarehouseandadatamart?Datawarehouse:Itisacollectionofdatamarts.Representshistoricaldata.adatawarehouseisarelationaldatabasewhichisspeciallydesignedforanalysispurposeratherthenfortransactionalpurpose.Datamart:Itisasubsetofdatawarehousing.Itcanprovidethedatatoanalyzequeryreporting&analysis.adatmartissubjectorienteddatabasewhichgivesthedataabouteachandeveryindividualdepartmentinanorganisation.2howcanoneconnecttwofacttables?isitpossible?how?Thisconfirmdimenstionmethodology.IfadimensiontableisconnectedtomorethenoneFacttableiscalledconfirmdimension.FactTablesareconnectedbyconfirmeddimensions,Facttablescannotbeconnecteddirectly,someansofdimensionwecanconnect3supposedataarecomingfromdifferentlocationsandthosedatawillnotchange.isthereanyneedtousesurrogatekey?Yes,Weshouldusethesurrogatekey,herewearegettingdatafromdifferentlocationsmeanseveryonehaveoneprimarykey,whiletransformingthedataintotargetthattimemorethantwokeynotinusesoifyouusesurrogatekeyitwillidentifiedtheduplicatefieldsindimensionaltable.4whatisthedifferencebetweenaggregatetableandfacttable?howdoyouloadthesetwotables?Afacttabletypicallyhastwotypesofcolumns:thosethatcontainnumericfacts(oftencalledmeasurements),andthosethatareforeignkeystodimensiontables.Afacttablecontainseitherdetail-levelfactsorfactsthathavebeenaggregated.Facttablesthatcontainaggregatedfactsareoftencalledsummarytablesoraggregatedfact.Afacttableusuallycontainsfactswiththesamelevelofaggregation.Thoughmostfactsareadditive,theycanalsobesemi-additiveornon-additive.Additivefactscanbeaggregatedbysimplearithmeticaladdition.Acommonexampleofthisissales.Non-additivefactscannotbeaddedatall.Anexampleofthisisaverages.Semi-additivefactscanbeaggregatedalongsomeofthedimensionsandnotalongothers.Anexampleofthisisinventorylevels,whereyoucannottellwhatalevelmeanssimplybylookingatit.5WhatOraclefeaturescanbeusedtooptimizemyWarehousesystem?Partitiontable,bitmapindex,sequence,tablefunction,sqlloader,functionlikecube,roll_upetc.6WhenshouldyouuseaSTARandwhenaSNOW-FLAKEschema?Thesnowflakeandstarschemaaremethodsofstoringdatawhicharemultidimensionalinnature(i.e.whichcanbeanalysedbyanyorallofanumberofindependentfactors)inarelationaldatabase.Thesnowflakeschema(sometimescalledsnowflakejoinschema)isamorecomplexschemathanthestarschemabecausethetableswhichdescribethedimensionsarenormalized.Snowflakeschemaisnothingbutonedimensiontablewillbeconnectedtoanotherdimensiontableandsoon.平常心Snowflake?Ifadimensionisverysparse(i.e.mostofthepossiblevaluesforthedimensionhavenodata)and/oradimensionhasaverylonglistofattributeswhichmaybeusedinaquery,thedimensiontablemayoccupyasignificantproportionofthedatabaseandsnowflakingmaybeappropriate.?Amultidimensionalviewissometimesaddedtoanexistingtransactionaldatabasetoaidreporting.Inthiscase,thetableswhichdescribethedimensionswillalreadyexistandwilltypicallybenormalized.Asnowflakeschemawillhencebeeasiertoimplement.?Asnowflakeschemacansometimesreflectthewayinwhichusersthinkaboutdata.Usersmayprefertogeneratequeriesusingastarschemainsomecases,althoughthismayormaynotbereflectedintheunderlyingorganizationofthedatabase.?Someusersmaywishtosubmitqueriestothedatabasewhich,usingconventionalmultidimensionalreportingtools,cannotbeexpressedwithinasimplestarschema.Thisisparticularlycommonindataminingofcustomerdatabases,whereacommonrequirementistolocatecommonfactorsbetweencustomerswhoboughtproductsmeetingcomplexcriteria.SomesnowflakingwouldtypicallyberequiredtopermitsimplequerytoolssuchasCognosPowerplaytoformsuchaquery,especiallyifprovisionfortheseformsofqueryweren'tanticipatedwhenthedatawarehousewasfirstdesigned.StarThestarschema(sometimesreferencedasstarjoinschema)isthesimplestdatawarehouseschema,consistingofasinglefacttablewithacompoundprimarykey,withonesegmentforeachdimensionandwithadditionalcolumnsofadditive,numericfacts.Thestarschemamakesmulti-dimensionaldatabase(MDDB)functionalitypossibleusingatraditionalrelationaldatabase.Becauserelationaldatabasesarethemostcommondatamanagementsysteminorganizationstoday,implementingmulti-dimensionalviewsofdatausingarelationaldatabaseisveryappealing.EvenifyouareusingaspecificMDDBsolution,itssourceslikelyarerelationaldatabases.Anotherreasonforusingstarschemaisitseaseofunderstanding.Facttablesinstarschemaaremostlyinthirdnormalform(3NF),butdimensionaltablesareinde-normalizedsecondnormalform(2NF).Ifyouwanttonormalizedimensionaltables,theylooklikesnowflakes(seesnowflakeschema)andthesameproblemsofrelationaldatabasesarise-youneedcomplexqueriesandbusinessuserscannoteasilyunderstandthemeaningofdata.AlthoughqueryperformancemaybeimprovedbyadvancedDBMStechnologyandhardware,highlynormalizedtablesmakereportingdifficultandapplicationscomplex.7WhenshouldoneuseanMD-database(multi-dimensionaldatabase)andnotarelationalone?1BecauseMorethanonedimensionscanbesharebleforOtherDepartment2ThePhysicalLoadwillbeless.3LessComplexityofFact8WhatisthedifferencebetweenanODSandaW/H?平常心AnODSisanenvironmentthatpullstogether,validates,cleansesandintegratesdatafromdisparatesou