Assignment

Ghanpnb P
timer Asked: May 2nd, 2016

Question Description

During Week Seven we continue our ETL development. This week will take our Mapping Document and develop our ETL process to load our 3NF Inmon tables.

The following is a high level scenario of what you will be developing this week:

You're a data architect at a large pharmacy chain (XVS pharmacy). XVS Pharmacy incorporates an Inmon 3NF or close to 3NF Physical data model, and Kimball star Schema Semantic/Reporting layer. Last week we loaded our staging tables. This week we will load our 3NF Inmon tables. You will use stored procedures to extract data from the staging tables, transform the extract data and then load the appropriate relational tables. Next week we will load the Star Schema from your 3NF Innmon tables.

Assignments for this week includesstored procedure development and discussion forum post.

REMINDER: If you use a reference as a source for your work products, you must use the APA in-text citation format to credit the author.


Unformatted Attachment Preview

1. Using the BCP utility, create a batch script to test your staging bulk data loads. Ans:- For Drug:- >bcp XVSPharmacy.dbo.Drug out c:\XvsPharmacy\drug.txt For Patient:- > bcp XVSPharmacy.dbo.Patient out c:\XvsPharmacy\Patient.txt For Store :- > bcp XVSPharmacy.dbo.Store out c:\XvsPharmacy\Store.txt For Prescription >bcp XVSPharmacy.dbo.Prescription out c:\XvsPharmacy\Prescription.txt 2. The standards at XVS Pharmacy are to bulk data load data to the staging tables with no data transformations. The staging tables are a one to one correlations to the four file layout. Ans:- Testing 4 Txt files are created or not 3. Once the batch script has been tested, create a SSIS Package, using the Execute Process Task to embed the batch script to load the data. ANS: go to sql server business intelligence development studio-> file-> new-> project-> select integration project-> name as XVSPharmacy. Click ok. Window like this In the left pane drag and drop the Execute Process task- right click  select edit one window is open Go to process-- executable Give the Path where the source file is there.. After that Arguments--- Give the path to destination(in this you right what type of file you want either ,txt or csv or copy the existing file) Copy Command is:- /c Path.. Here we have to copy the file in same location Then enter ok button. Then right click execute process task-- select Execute task… Task run Successfully to load the data to that destination path.. 4. Add a step before the Execute Process task to truncate the staging table. Ans:- we have to Execute sql task to truncate the staging table…. Drag and drop the Execute Sql task--right click select edit one window open like this Go to connection - select new connection Select New coonection give which database you have to use… Above Execute sql task editor go to Sql statement- write this truncate command Truncate table drug; Truncate table Prescription; Truncate table store; Truncate table Patient; Then click the ok button. Then you have to connection the execute sql task to bulk insert data… like this… Than execute the Task. First if that table already exists then truncate table after that insert in to that table….
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors