DataTransformationServiceWord文档格式.docx

DataTransformationServiceWord文档格式.docx

《DataTransformationServiceWord文档格式.docx》由会员分享，可在线阅读，更多相关《DataTransformationServiceWord文档格式.docx（27页珍藏版）》请在冰点文库上搜索。

Inthisexercise,youwilluseDataTransformationService（DTS）totransformdatafromtheFoodMartSalessampledatabasetotheDWStardatabasethatyouwillcreate.

Estimatedtimetocompletethislab:

45minutes

Exercise1

GettingStarted

Note

Additionalfilesareneededtocompletethisexercise;

thesefilescanbefoundinthelocationinwhichthisdocumentwasopened.

Requirements

1.ThisexerciseisdependenttheFoodMartSalesdatabasethatcanbefoundonHYPERLINK

2.DownloadFoodmart.exe

3.Double-clicktoexecutetheinstallprogram,followthewelcomescreens,andselecttoinstalltheFoodMartSalesOLPTdatabase.

CreatingtheDWStardatabase

1.StartQueryAnalyzerfromtheMicrosoft®

SQLServer™version7.0Group.

2.ConnecttoSQLServerbysupplyingtheSApassword（SQLServerauthentication）orbyusingMicrosoft®

WindowsNT®

Authentication.

3.OpenDWStar.sqlbyclickingFile,Openandselectingthefile.

4.

ExecutethefilebyclickingtheExecuteicon:

5.CloseQueryAnalyzeroncetheexecutionhascompleted.

Exercise2

CreatingaDTSPackage

EachDTSpackagedefinesoneormoretaskstobeexecutedinacoordinatedsequence.TheDTSpackagecanbecreatedmanuallybyusingalanguagethatsupportsOLEautomation,suchasMicrosoftVisualBasic®

developmentsystemorinteractivelybyusingtheDTSwizards.

Allstepsmustbecompetedinordertocompletethislab.

CreatingConnections

1.StartEnterpriseManagerbyselectingitfromtheSQL 

Server7.0Group.

2.Connecttoyourserverbyclickingthe+symboltotheleftofSQLServerGroup,thendouble-clickyourservername,whichwillexpandthetree.

3.Clickthe+symboltotheleftofDataTransformationPackages,andclickLocal.

4.RightclickLocalandselectNewPackage.

5.

TheDTSPackagetoolisdisplayed.DragtheMicrosoftSQLServer7.0Only（OLEDBProvider）icontotheworkareatocreateaconnection（theiconcanbefoundundertheDatataskbarontheleft-handsideofthescreenandlookslikeaserver）.

6.ThiswilllaunchtheConnectionPropertiesdialogbox.IntheNewConnectionfield,enterDWStar,selectUseWindowsNTauthentication.ClicktheRefreshbuttontorefreshthelistofdatabases.Next,clickthedrop-downlistfordatabasesandselectDWStar.（Note:

IfyouroperatingsystemisMicrosoftWindows®

95orWindows98,youwillhavetouseSQL 

ServerauthenticationandsupplytheSApassword）.

7.ClickOK.

8.Atthispointyourworkareawillhaveoneconnection,andyouwillneedtomakeaconnectionforeachdimensionthatyouwanttopopulate.

9.JustasyoucreatedtheDWStarconnection,youwillcreatethefirstoffiveconnectionsforthesourcedata.

10.DragtheMicrosoftSQL 

Server7.0Only（OLEDBProvider）icontotheworkareatocreatethenewconnection,asyoudidinstepfiveabove;

theConnectionPropertiesdialogboxisnowdisplayed.

11.IntheNewConnectionfieldenterFMS-Time,andselectUseWindowsNTauthentication.ClickRefreshtorefreshthelistofdatabases,andinthedatabasesdrop-downlist,selectFoodMartSales.

12.ClickOK.

13.Tocompletetheremainingconnections:

∙Repeatthestepsabove,enteringFMS-CustomerfortheNewConnectionfield,selectingWindowsNTauthentication,andselectingtheFoodMartSalesdatabaseintheDatabasedrop-downlist.

∙Repeatthestepsabove,enteringFMS-GeographyfortheNewConnectionField,selectingWindowsNTauthentication,andselectingtheFoodMartSalesdatabaseintheDatabasedrop-downlist.

∙Repeatthestepsabove,enteringFMS-ProductfortheNewConnectionfield,selectingWindowsNTauthentication,andselectingtheFoodMartSalesdatabaseintheDatabasedrop-downlist.

∙Repeatthestepsabove,enteringFMS-ProductfortheNewConnectionfield,selectingWindowsNTauthentication,andselectingtheFoodMartSalesdatabaseintheDatabasedrop-downbox.

14.YounowneedtocreateaduplicateconnectiontoDWStarandplaceittotherightofthedesignlayout.Thistime,dragthedatabaseiconasyoudidabove.IntheConnectionPropertiesdialogbox,selectExistingConnection,andinthedrop-downlistbox,selectDWStar.

15.ClickOK.

16.Oncecomplete,arrangetheiconssimilartothefollowingexample.

Exercise3

TransformData

Completeallstepsbeforemovingontothenextexercise.

Connectingconnectionsfordataflow.

1.ClicktheFMS-Timeconnectionthatisnearesttothetopofthescreen（thiswillselecttheicon）.

2.PresstheCTRLkeyandclicktheDWStarconnectioninthecenterofthescreen（bothiconsshouldnowbeselected）.

3.Right-clicktheDWStariconandselectTransformData.ThiswillcreatedatatransformationflowfromtheFMS-TimeconnectiontotheDWStarconnection.

4.Repeattheabovestepsfortheconnections,FMS-Customer,FMS-Geography,andFMS-Products.

5.CreateonemoredataflowconnectionbetweentheFMS-OrderFactconnectionandtheDWStarconnectionattherightsideofthescreen.

6.Nextyouneedtosettheprecedence,whichmeansthattheFactTablewillnotbepopulatedwithdatauntilalloftheDimensionTablesarecompletedfirst.Todothis,createtheconnectionbetweenDWStar（inthecenterofthescreen）andFMS-OrderFactusingtheCTRLkey,howeverthistimeselectPrecedencefollowedbyOnSuccess.

7.Notethegreenandwhitedashedarrow.

TransformData–TimeDimension

1.Double-clickthearrowbetweentheFMS-TimeandDWStaricons.

2.ThiswillbringuptheTransformDataPropertiesdialogbox.IntheDescriptionfield,enterLoadTimeDimension,andthenselectSQLQuery.

3.Inthisexercise,tosavetime,youwillusequeriesthatarealreadybuilt.UsingNotepad,openLoadDWStar.sql.HighlightthequeryfortheTimeDimensionasshowninthefollowingfigure.

4.CopyandPastethequeryintotheSQLQuerytextfield.

5.ClicktheDestinationtab,andintheTableNamelistboxselect[DWStar].[dbo].[Time].

6.ClicktheColumnMappingtab;

notethatthecolumnsarealreadymappedforyou.

TransformData–CustomerDimension

1.Movingcounterclockwise,double-clickthearrowbetweenthe

FMS-CustomerandDWStaricons.

2.ThiswillbringuptheTransformDataPropertiesdialogbox.IntheDescriptionfield,enterLoadCustomerDimension,andthenselectSQLQuery.

3.Tosavetime,inthisexerciseyouwillusequeriesthatarealreadybuilt.UsingtheNotepadopenLoadDWStar.sql.ScrolldowntoCustomerDimensionandhighlightthequeryasshowninthefollowingfigure.

5.ClicktheDestinationtab,andintheTableNamelistbox,select[DWStar].[dbo].[Customer].

notethatthecolumnsaremappedforyou.

TransformData–GeographyDimension

FMS-GeographyandDWStaricons.

2.ThiswillbringuptheTransformDataPropertiesdialogbox.IntheDescriptionfield,enterLoadGeographyDimension,andthenselectSQLQuery.

3.Inthisexercise,tosavetime,youwillusequeriesthatarealreadybuilt.UsingNotepadopenLoadDWStar.sql.ScrolldowntotheGeographyDimensionandhighlightthequery.

5.ClicktheDestinationtab,andintheTableNamelistbox,select[DWStar].[dbo].[Geography]fromthelist.

TransformData–ProductsDimension

FMS-ProductsandDWStaricons.

2.ThiswillbringuptheTransformDataPropertiesdialogbox.IntheDescriptionfield,enterLoadProductsDimension,andthenselectSQLQuery.

3.Tosavetime,inthisexerciseyouwillusequeriesthatarealreadybuilt.UsingNotepad,openLoadDWStar.sql.ScrolldowntoProductsDimensionandhighlightthequery.

5.ClicktheDestinationtab,andintheTableNamelistboxselect[DWStar].[dbo].[Products].

notethatthecolumnshavebeenmappedforyou.

TransformData–OrdersFact

1.Movingcounterclockwise,double-clickthearrowbetweentheFMS-OrderFactandDWStariconstothefarright.

2.ThiswillbringuptheTransformDataPropertiesdialogbox.IntheDescriptionfield,enterLoadOrdersFactTable,andthenselect

SQLQuery.

3.Inthisexercise,tosavetimeyouwillusequeriesthatarealreadybuilt.UsingNotepad,openLoadDWStar.sql.ScrolldowntoOrdersFactTableandhighlightthequeryasshowninthefollowingfigure.

5.ClicktheDestinationtab,andintheTableNamelistbox,select[DWStar].[dbo].[Orders].

SavingtheTransformationPackage.

1.ClicktheSaveicononthetoolbar.

2.TheSaveDTSPackagedialogboxisdisplayed.InthePackageNamefield,enterTransformFoodMartSales.Forthisexerciseyouwillsavethepackagetotheserver.

3.ClickOK.

4.Torunthenewlycreatedpackagemanually,clicktherunicononthetoolbar.SYMBOL52\f"

Webdings"

\s10.5

5.Duringtheexecutionyouwillseethestatusofexecutiondispla