More NextBlog
Home
DatastageRelated
DatastageTraining
CreateBlog SignIn
BigData
Unix
Database
InterviewRelated
Certifications
DiscussionForum
Feedback
ManythanksforvisitingmyBlog..!!PleasesharethisblogusingbelowsharebuttonsandleaveyourComments/Feedback/Appreciatio
SEARCHYOURPROBLEMSSOLUTIONINTHISBLOG
Search
Like
Tweet
Share
ShareThisBlog..!!
TRANSLATETHISBLOG
DatastageSlowlyChangingDimensions
VISITOR'SVIEWCOUNT
8 0 6 2 5 9
SelectLanguage
LogIn
DatastageImplementationsSlowlyChangingDimensions
ABOUTME:CLICKONG+BUTTONTOFOLLOWME
Devendra Kumar Yadav
BasicsofSCD
SlowlyChangingDimensions(SCDs)aredimensionsthathavedatathatchangesslowly,ratherthanchangingonatimebased,regularschedule.
Follow
181 followers
Type1
TheType1methodologyoverwritesolddatawithnewdata,andthereforedoesnottrackhistoricaldataatall.
Hereisanexampleofadatabasetablethatkeepssupplierinformation:
Supplier_Key Supplier_Code Supplier_Name
123
ABC
Supplier_State
AcmeSupplyCo CA
Inthisexample,Supplier_CodeisthenaturalkeyandSupplier_Keyisasurrogatekey.Technically,thesurrogatekeyisnotnecessary,sincethe
tablewillbeuniquebythenaturalkey(Supplier_Code).However,thejoinswillperformbetteronanintegerthanonacharacterstring.
[Link]:
Supplier_Key Supplier_Code Supplier_Name
123
ABC
Supplier_State
AcmeSupplyCo IL
OTHERDATASTAGEQUESTIONSSOLUTIONS
2014(34)
2013(48)
Dec(8)
Nov(15)
Type2
Oct(12)
TheType2methodtrackshistoricaldatabycreatingmultiplerecordsforagivennaturalkeyinthedimensionaltableswithseparatesurrogatekeys
and/ordifferentversionnumbers.WithType2,wehaveunlimitedhistorypreservationasanewrecordisinsertedeachtimeachangeismade.
Inthesameexample,ifthesuppliermovestoIllinois,thetablecouldlooklikethis,withincrementedversionnumberstoindicatethesequenceof
Jan(13)
IBMDataStage8.5NewFeatures
changes:
UseofStagesinDatastage8.5or8xSeries
NewDebugfeatureinDataStage8.5
Supplier_Key Supplier_Code Supplier_Name Supplier_State Version
DSParallelProcessing&PartitionTechniques
123
ABC
AcmeSupplyCo CA
DatastageTransformerStageLoopingconcept
124
ABC
AcmeSupplyCo IL
DataModelingConceptinDatawarehouse
DW&BIConceptsInterviewQuestionsandAnswers
Anotherpopularmethodfortupleversioningistoaddeffectivedatecolumns.
Datastage8xEngine/TiersStartandStopProcess
RT_SCTEMPError:NotabletorunDatastageJobs
Supplier_Key Supplier_Code Supplier_Name Supplier_State Start_Date
ParametersUsingParameter/ValueSet/ValueFile
End_Date
123
ABC
AcmeSupplyCo CA
01Jan2000 21Dec2004
124
ABC
AcmeSupplyCo IL
22Dec2004
ThenullEnd_Dateinrowtwoindicatesthecurrenttupleversion.Insomecases,astandardizedsurrogatehighdate(e.g.99991231)maybeused
DeleteDataStagejobsfromthecommandline
MYMOSTPOPULARFREQUENTLYACCESSEDPOSTS
DatastageSlowlyChangingDimensions
Datastage8.5,8.7and9.1Differences
DiskSpaceEstimation
asanenddate,sothatthefieldcanbeincludedinanindex,andsothatnullvaluesubstitutionisnotrequiredwhenquerying.
DatastageTransformerStageLoopingconcept
2012(4)
HowtoImplementSCDusingDataStage8.1SCDstage?
Datapartitioning&collectingmethodsExamples
Step1:Createadatastagejobwiththebelowstructure
1.
SourcefilethatcomesfromtheOLTPsources
2.
Olddimesionreferncetablelink
3.
TheSCDstage
4.
TargetFactTable
5.
DimesionUpdate/Insertlink
IBMDatastage9.1NewlyAddedfeatures
DATASTAGEPerformanceTuningTipsV1.1
SurrogateKeyGeneratorImplementation
ParametersUsingParameter/ValueSet/ValueFile
UseofStagesinDatastage8.5or8xSeries
DatastageInterviewQuestionsandAnswersV1.4
DatastageScenarioBasedQuestion/Answer:1
LISTOFVISITOR'SCOUNTRIES
RECENTLYVISITEDUSER'SLOCATION
LiveTrafficFeed
Figure1
Step2:TosetuptheSCDpropertiesintheSCDstage,openthestageandaccesstheFastPath
Figure2
Step3:Thetab2ofSCDstageisusedspecifythepurposeofeachofthepulledkeysfromthereferenceddimensiontables.
AvisitorfromMontral,Quebecviewed
"DEV'SDATASTAGE
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:SurrogateKey
AvisitorfromMumbai,Maharashtraleft
GeneratorImplementation"4minsago
"DEV'SDATASTAGE
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:DatastageRelated
AvisitorfromHyderabad,AndhraPradesh
ProblemsandSolutions"viadatastageinfoguid
viewed"DEV'SDATASTAGE
[Link].com11minsago
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:DatastageJobsBest
AvisitorfromHyderabad,AndhraPradeshleft
PracticesforTuning"12minsago
"DEV'SDATASTAGE
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:Searchresultsfor
AvisitorfromBangalore,Karnatakaviewed
funnelstage"viadatastageinfoguid
"DEV'SDATASTAGE
[Link].au12minsago
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:SequentialFileBest
AvisitorfromMumbai,Maharashtraleft
PerformanceTips/Settings"17minsago
"DEV'SDATASTAGE
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:DatastageRelated
AvisitorfromMumbai,Maharashtraviewed
ProblemsandSolutions"viadatastageinfoguid
"DEV'SDATASTAGE
[Link].com24minsago
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:Conductor
AvisitorfromHyderabad,AndhraPradesh
Node,SectionLeadersandPlayers"35minsago
viewed"DEV'SDATASTAGE
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:DatastageJobsBest
AvisitorfromGrandRapids,Michiganviewed
PracticesforTuning"37minsago
"DEV'SDATASTAGE
PracticesforTuning"37minsago
"DEV'SDATASTAGE
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RELATEDSOLUTIONS:IBMDatastage9.1
AvisitorfromIndiaviewed"DEV'S
NewlyAddedfeatures"40minsago
DATASTAGE
TUTORIAL,GUIDES,TRAININGAND
[Link],ETL,DATABASE
RealtimeviewMenu
DAILYHOROSCOPES
DailyHoroscopes
Aries
Libra
3/214/19
9/2310/22
Taurus
Scorpio
4/205/20
10/2311/21
Figure3
Step4:Tab3isusedtoprovidetheseqencegeneratorfile/tablenamewhichisusedtogeneratethenewsurrogatekeysfortheneworlatest
[Link].
Gemini
5/216/21
Sagittarius
11/2212/21
Cancer
Capricorn
6/227/22
12/221/19
Leo
Aquarius
7/238/22
1/202/18
Virgo
8/239/22
Pisces
2/193/20
[Link]
MYBLOGPOSTS
DEV'SDATAWAREHOUSINGHELPGUIDE
DataStageParalleljobsfailwithfailurecode11
Figure4
Step5:[Link]
thatwecanconfigureasapartofthistabare:
1.
GenerationthenewSurrogatekeyvaluestobepassedtothedimensionandfacttable
2.
Mappingthesourcecolumnswiththesourcecolumn
3.
Settingupoftheexpiredvaluesfortheoldrows
4.
Definingthevaluestomarkthecurrentactiverowsoutofmultipletyperows
Figure5
Step6:Setthederivationlogicforthefactasapartofthelasttab.
Figure6
Step7:Completetheremainingsetup,runthejob
Figure7
+1 Recommend this on Google
Reactions:
Like (0)
Useful (0)
Dislike (0)
WHAT'STHIS?
AROUNDTHEWEB
PBHNetwork
IncrediblePicture
GalleriesOfLifeAround
TheWorld
Lifespan
Stack
14FoodsThatWillMake
YouHappier
'XForce'WillBetheNext
XMenMovie
[Link]
TheTop5CoolestKit
Cars
[Link]
DEV'SDATASTAGETUTORIAL,GUIDES,TRAINING
[Link],ETL,DATABASE
RELATEDSOLUTIONS:ReadersDiscussionQuestion
DEV'SDATASTAGETUTORIAL,GUIDES,TRAINING
AnswersSection51comments
[Link],ETL,DATABASE
RELATEDSOLUTIONS:DataModelingConceptin
Datawarehouse1comment
1Comment
DEV'SDATASTAGETUTORIAL,GUIDES,TRAINING
[Link],ETL,DATABASE
RELATEDSOLUTIONS:NewDebugfeaturein
DEV'SDATASTAGETUTORIAL,GUIDES,TRAINING
DataStage8.52comments
[Link],ETL,DATABASE
RELATEDSOLUTIONS:DatastagePerformance
TuningTipsV1.23comments
[Link]
SortbyNewest
Jointhediscussion
maheshtanpure 2yearsago
Articlegivesgoodunderstandingofimplementation..goodjob
Login
Share Favorite
NewerPost
Home
OlderPost
Subscribeto:PostComments(Atom)
DISCLAIMER
Allcontentprovidedonthis[Link]
[Link][Link]
[Link],injuries,ordamagesfromthedisplayoruseofthisinformation.
FINDYOURPROBLEMSSOLUTIONHERE..!
Search
