iLab 6, Part a: Create a BIDS Analysis Services Project
You have been asked to analyze the Redwood database by using multidimensional techniques. One way to do this is to create a data
cube that represents data in various dimensions (attributes) along with various measures over a period of time.
Upon completing this lab, you will be able to:
1. Create a BIDS Analysis Services project
2. Define a data source and view
3. Define dimensions and attributes
4. Define a cube
Submit the YourName_Lab6A_Questions.docx to the Week 6 iLab Dropbox.
Section Deliverable Points
Part A Define a BIDS Analysis Services project 5
Part B Define a data source 5
Part C Define a data source view 5
Part D Define dimension(s) and add attribute(s) to dimension(s) 5
Part E Define a cube using measure(s), dimension(s), and time 10
Download the Week6_iLab6_Part_A_Questions.docx from Doc Sharing. You will answer the questions and provide screen prints
as required for each part of the lab.
1.
2. Create a folder on your C:\ drive named Transfer.
3. Download the Week6_iLab6_CustomerData.txt file from Doc Sharing to your folder named Transfer.
4. Log in to the Citrix remote lab.
Print
Submit your assignment to the Dropbox located on the silver tab at the top of this page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)
i L A B O V E R V I E W
Scenario and Summary
Deliverables
i L A B S T E P S
Preparation Back to Top
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
1 of 69 10/7/2014 11:49 PM
1. Follow the login instructions located in the iLab tab in Course Home.
2. Upload your txt file to your Citrix drive using the instructions provided in the iLab tab in Course Home.
Part A: Define a BIDS Analysis Services project
Create a new SQL 2008 Server Business Intelligence Development Studio, BIDS for short, Analysis Services project.
a. Go to the iLab and open BIDS. Then go to File > New > Project.
In the New Project dialog, select Analysis Services Project. Enter an appropriate filename and finally, specify a Location on your
F:\ drive. You should get Fig 1 below. Notice the highlighted project types and templates. Then look at the Name and Location.
The project will be saved on the F:\ drive in a subfolder.
b.
After you click OK, review the Solutions Explorer to note the new project. This confirms that a BIDS Analysis Services project
was built and that you are now ready to proceed with the other steps to build a cube. SAVE YOUR WORK OFTEN. Take a
screenshot of the New Project in the BIDS application.
c.
STEP 1: Problem Description Back to Top
STEP 2: Use an Analysis Services Template Back to Top
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
2 of 69 10/7/2014 11:49 PM
Using the Lab6A_Questions.docx, paste your screen shots in the appropriate areas.
Part B: Define a data source
We now need to connect our project to the database which will serve as our source of data for the cube.
a. In Solution Explorer, right click on Data Sources and then click on New Data Source.
STEP 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: To Define a new data source Back to Top
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
3 of 69 10/7/2014 11:49 PM
b. After you click on New Data Source you should get Fig 4 below.
c. Click Next and make sure that Create a data source based on an existing or new connection is selected.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
4 of 69 10/7/2014 11:49 PM
d. Click New to open the Connection Manager.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
5 of 69 10/7/2014 11:49 PM
In the Connection Manager, enter BIS445SQL2008 for the Server name and then choose BIS445_Redwood for Select, or enter
a database name as shown below.
e.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
6 of 69 10/7/2014 11:49 PM
f. Click the Test Connection button. You should receive a dialog stating that the Test connection succeeded.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
7 of 69 10/7/2014 11:49 PM
g. Click OK to return to the Data Source Wizard.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
8 of 69 10/7/2014 11:49 PM
BEFORE YOU GO ON, MAKE SURE THAT THE DATA CONNECTION IS CORRECT. NEXT, MAKE SURE THAT THE INITIAL
CATALOG SAYS BIS445_REDWOOD. This is actually the name of the database that will be your data source. You could have
also picked one of the other databases in SQL 2008 SERVER and everything would still look ok. So, please be careful! Click
Next.
h.
i. On the Impersonation Information dialog select Use the service account. Click Next.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
9 of 69 10/7/2014 11:49 PM
Perform a screenshot of the Completed Wizard dialog as shown below in Fig 11. Please make sure that your data source name
is BIS445_Redwood.
j.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
10 of 69 10/7/2014 11:49 PM
k. Click Finish to complete the Data Source Wizard.
In the Solution Explorer, double click the newly created .ds file under Data Sources. Capture a screenshot of the Data Source
Designer dialog and save your work.
l.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
11 of 69 10/7/2014 11:49 PM
m. Click OK.
Using the Lab6A_Questions.docx, answer the questions for Part B.
Part C: Define a data source view
In Part B you created a data source. Now you need to create a visual representation of that data.
a. In Solution Explorer, right click on Data Source View and select New Data Source View.
STEP 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: To Define a new data source view Back to Top
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
12 of 69 10/7/2014 11:49 PM
b. Click on New Data Source View and you will see the following window.
Click Next and make sure the Relational Data Sources item is correct; it should be the same data source that you created in Part
B above, namely BIS445_Redwood.
c.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
13 of 69 10/7/2014 11:49 PM
d. Click Next. The Select Tables and Views dialog shows the tables (objects) in the BIS445_Redwood data source/database.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
14 of 69 10/7/2014 11:49 PM
When working with a database you are intimately familiar with, you might be more selective as to the tables and/or views you
want in your data source view. In this case, you will select all of the tables. Click on the third button down in the middle of the
window, the one with the >> symbol. The Available objects now become the included objects as shown in the figure below.
e.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
15 of 69 10/7/2014 11:49 PM
Click Next and then click Finish to complete the Data Source View Wizard. Notice that the data source view name is called –
BIS445_Redwood Data Source View. This process will create a visual representation of the database as shown below.
f.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
16 of 69 10/7/2014 11:49 PM
g. Click Finish and you will get the figure below.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
17 of 69 10/7/2014 11:49 PM
Go to the View menu > Zoom and select 50%. The table columns will disappear and you should only see tables. Click on any
tables that have … and expand them to show the full name of the table. Capture a screenshot of the completed diagram.
h.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
18 of 69 10/7/2014 11:49 PM
Note: This next item is something you might want to do at some point. Select a table and look at the Properties panel in the
lower right hand corner of the application. Notice that there is a property called Friendly Name. If you did not like the name of the
table you could change it here.
Using the Lab6A_Questions.docx, answer the questions for Part C.
Part D: Define dimension(s) and add attribute(s) to dimension(s)
Microsoft states: “A database dimension is a collection of related objects, called attributes, which can be used to provide information
about fact data in one or more cubes. For example, typical attributes in a product dimension might be product name, product category,
product line, product size, and product price. These objects are bound to one or more columns in one or more tables in a data source
view. By default, these attributes are visible as attribute hierarchies and can be used to understand the fact data in a cube. Attributes
can be organized into user-defined hierarchies that provide navigational paths to assist users when browsing the data in a cube.”
a. Right click on Dimensions in Solution Explorer and then click on New Dimension.
STEP 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: Define Dimension(s) Back to Top
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
19 of 69 10/7/2014 11:49 PM
b. You should get a Welcome to Dimension Wizard Window. Click Next twice.
In the figure above, make sure that Use an existing table is selected and click Next. In the Specify Source Information dialog
make the You should get a figure similar to the one below where you are using the BIS445_Redwood Data Source View along
with the Listings table and the BeginListDate Dimension.
Data source view: BIS445 Redwood
Main table: Listings
Key columns: ListingID
Name column: BeginListDate
c.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
20 of 69 10/7/2014 11:49 PM
Click Next in the figure above. You should get a figure similar to the one below. Here you should see the related tables to the
Main Dimension table – Listings.
d.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
21 of 69 10/7/2014 11:49 PM
e. In the figure above, click Next and you should get a figure similar to the one below.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
22 of 69 10/7/2014 11:49 PM
f. Leave the Window as is. Click Next and you should get a figure similar to the one below.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
23 of 69 10/7/2014 11:49 PM
g. Capture a screenshot of your application.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
24 of 69 10/7/2014 11:49 PM
Using the Lab6A_Questions.docx, answer the questions for Part D.
Part E: : Define a Cube
A cube has three things of interest:
Dimension(s), which are tied to attributes in the database
Measures, which are quantities that we can use to look at how good or bad our dimensions or attributes are doing
Dimension/attributes and measures over time
In the Solutions Explorer, right click on Cube and select New Cube…
After Clicking on New Cube above you should get a figure similar to the one below.
a.
STEP 3: Deliverable Back to Top
Step 1: Problem Description Back to Top
STEP 2: Define a Cube Back to Top
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
25 of 69 10/7/2014 11:49 PM
b. Click Next and you will get the figure below. Make sure that Use existing tables is checked.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
26 of 69 10/7/2014 11:49 PM
Click Next and you will get the figure below after clicking the Suggest button. Also make sure that the Data source view is
BIS445_Redwood Data Source View.
c.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
27 of 69 10/7/2014 11:49 PM
Click Next in the figure above and you will get the figure below. A Note From Microsoft: “By default, the wizard selects as
measures all numeric columns in the fact table that are not linked to dimensions.” “Measure group tables, also named fact
tables, contain the measures that you are interested in….”
d.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
28 of 69 10/7/2014 11:49 PM
At this point, we will go with the default. Click Next on the figure above and you will get a figure similar to the one below with the
Listings Dimension.
e.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
29 of 69 10/7/2014 11:49 PM
f. Click Next in the figure above and you will get the figure below. We now have another Dimension – Contact Reason.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
30 of 69 10/7/2014 11:49 PM
g. Click Next until you are on the Completing the Wizard dialog. Capture a screenshot of this dialog.
h. Click Finish and then capture a screenshot of your workspace.
iLab https://devry.equella.ecollege.com/file/2a48cf2f-9ae4-431f-b4ba-8e1cad…
31 of 69 10/7/2014 11:49 PM
Submit your completed YourName_Lab6A_Questions.docx to the Week 6 iLab Dropbox.
Submit your assignment to the Dropbox located on the silver tab at the top of this page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)
iLab 6, Part b: Build an ETL package for data warehousing
Submit your assignment to the Dropbox located on the silver tab at the top of this page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)
You have been asked to extract data from a flat file into a query found in the Redwood database, transform the data for loading, and
then load the data from the flat file into the query. Upon completing this lab you will be able to:
Create a BIDS Integration Services project
Create an ETL package
Add and configure a Flat File and an OLE DB Connection Manager
Add a data flow task to the package
Add and configure the Flat File source and an OLE DB destination
Clean up the package and test it
Submit the YourName_Lab6B_Questions.docx to the Week 6 iLab Dropbox.
Section Deliverable Points
Part A Define a BIDS Integration Services project and ETL package 4
Part B Create a new View and a flat file 3
Part C Add and Configure a Flat File Connection Manager 4
Part D Add and Configure an OLE DB Connection Manager 4
Part E Add a data flow task to the package 3
Part F Add and configure the Flat File source 3
Part G Add and configure the OLE DB destination 3
Part H Tidy up the package 3
Part I Test the package 3
Download the Week6_iLab6_Part_B_Questions.docx from Doc Sharing. You will answer the questions and provide screen prints
as required for each part of the lab.
1.
Final Deliverable Back to Top
i L A B O V E R V I E W
Scenario and Summary
Deliverables
i L A B S T E P S
Preparation Back to Top
2. Create a folder on your C:\ drive named Transfer.
3. Download the Week6_iLab6_CustomerData.txt file from Doc Sharing to your folder named Transfer.
a. Log in to the Citrix remote lab.
b. Follow the login instructions located in the iLab tab in Course Home.
c. Upload your txt file to your Citrix drive using the instructions provided in the iLab tab in Course Home.
4.
Part A: Define a BIDS Integration Services project
Create a new Integration Services project and ETL package.
a. Go to iLab and open up BIDS. Then go to File > New > Project.
In the New Project dialog select Integration Services Project. Enter an appropriate filename and finally specify a Location on your
F:\ drive. Complete the dialog by pressing OK.
b.
After you click OK in the figure above, you should get a figure similar to the one below. This confirms that a BIDS Integration
Services project was built and that you are now ready to proceed with the other steps to build an ETL package. SAVE YOUR
WORK OFTEN.
c.
In the Solution Explorer above, right click on Package.dtsx and rename it to PackLab6B.dtsx. Look at the figure below. You will
receive a dialog asking you to rename the package object, select Yes.
d.
STEP 1: Problem Description Back to Top
STEP 2: Use an Analysis Services Template Back to Top
e. After you rename the package you should get the figure below.
f. Click Ok and you should get a figure similar to the one below.
g. Perform a screen capture of your newly created project as shown below.
Using the Lab6A_Questions.docx, paste your screenshots in the appropriate areas.
Part B: Create a new View and a flat file.
Create a flat file that will be used to extract, transform, and load data into a table of the Redwood database.
Before you go any further in the project/package, you need to create a flat file that you will use to add data to a database. We are
using the Redwood database, and you should open up SQL 2008 Server Management Studio to review the database.
a.
Right click on the dbo.LiscenseStatus table and choose Select Top 1000 Rows. The query should produce 16 rows and two
columns of data.
b.
STEP 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Create Package Back to Top
In order to add the data to the table, you will need to create a query that will allow you to perform the add and also stand out as
the query to add data to the table.
c.
d. In the BIS445_Redwood database right click on Views and select New View.
In the Add Table dialog select LicenseStatus (dbo) and then click the Add button. Complete the dialog by pressing the Close
button.
e.
Next you will complete the query by entering an * in the Select clause as shown below. Click the red exclamation point button to
execute the query; you should receive the same output as you did in Step 2.b.
f.
g. Now save the view and enter a view name of vwLicStatLab6B as shown below.
Take a screenshot of your current progress. Click OK in the figure above and then you should get the figure below after
expanding the Views folder in the Object Explorer on the left of the Window.
h.
i. You can close SQL 2008 Server Management Studio if you so desire, but you will need it open again at the end of the lab.
Now we need to create the flat file that will be used by the above view to load the data. So, you will need to open up iLab
Windows Explorer and find the subdirectory on the F:\ drive where you are storing your BIDS projects.
j.
k. Inside that subdirectory create a new text file with Notepad named MyLicenseStatus.txt. The data in that file should exactly
match the image below. Use tabs to separate the headings and fields. Take another screenshot of your completed .TXT file.
Using the Lab6B_Questions.docx, answer the questions for Part B.
Part C: Add and Configure a Flat File Connection Manager
From Microsoft: “A Flat File connection manager enables a package to extract data from a flat file. Using the Flat File connection
manager, you can specify the filename and location, the locale and code page, and the file format, including column delimiters, to apply
when the package extracts data from the flat file. In addition, you can manually specify the data type for the individual columns, or use
the Suggest Column Types dialog box to automatically map the columns of extracted data to Integration Services data types.”
a. In BIDS, right click in the Connection Managers panel and select New Flat File Connection.
STEP 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: To Add and Configure a Flat File Connection Manager Back to Top
In the Flat File Connection Manager Editor dialog, make the following entries:
Connection manager name: Flat File Conn Mgr Source Data.
File name: Select the MyLiscenseStatus.txt file created previously.
Column names in the first data row: Checked.
b.
Next, click Advanced in the left dialog box. You should get the figure below. From Microsoft: “By default, all columns are initially
set to a string data type [DT_STR] with an OUTPUTCOLUMNWIDTH of 50.”
c.
d. Click Suggest Types. Set the Number of rows to 5 and make sure only Suggest the smallest integer data type is checked.
e. Click OK and take a screenshot of the resulting dialog as shown below.
f. Click on the Status Column > Suggest Types and enter 5 for the Number of rows and uncheck all checkboxes.
Click OK. However, there is a problem with the output. The Data Type for Status will give us a problem later on. Here is some
info from Microsoft: “When working with flat file source data, it is important to understand how the Flat File connection manager
interprets the flat file data. If the flat file source is Unicode, the Flat File connection manager defines all columns as [DT_WSTR]
with a default column width of 50. If the flat file source is ANSI-encoded, the columns are defined as [DT_STR] with a column
width of 50. You will probably have to change these defaults to make the string column types more appropriate for your data. ”
g.

To correct this issue, in the figure above, change the Data Type to Unicode string [DT_WSTR]. Your result should look the figure
below. Take a screenshot of the completed dialog.
h.

i. In the left column, click Preview and take a screenshot of the dialog.

j. Click OK. Take a screenshot of the application window.
Using the Lab6B_Questions.docx, answer the questions for Part C.
Part D: Add and Configure an OLE DB Connection Manager
From Microsoft: “After you have added a Flat File connection manager to connect to the data source, the next task is to add an OLE
DB connection manager to connect to the destination. An OLE DB connection manager enables a package to extract data from or load
data into any OLE DB–compliant data source. Using the OLE DB Connection manager, you can specify the server, the authentication
method, and the default database for the connection.”
a. Right click in the Connection Managers panel area and select New OLE DB Connection.
STEP 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
Step 2: Add and Configure an OLE DB Connection Manager Back to Top
b. Click on New OLE DB Connection and you will get a figure similar to the one below.
c. Ordinarily, you would click OK at this point. But, if you recall, the Data connection shown was created in Lab 6A, and if you had
problems there you will have problems here. So, to mitigate errors, delete the connection. Click New.
Enter the following values
Server name: BIS445SQL2008
Select or enter a database name: BIS445_Redwood
Click Test Connection
Click OK twice
d. Take a screenshot of the Configure OLE DB Connection Manager dialog.
e. Click OK. Take a screenshot of the application window.
Using the Lab6B_Questions.docx, answer the questions for Part D.
Part E: Add a data flow task to the package
From Microsoft: “The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and
provides the functionality for transforming, cleaning, and modifying data as it is moved. The Data Flow task is where most of the work
of an extract, transform, and load (ETL) process occurs.”
Make sure that you have your completed Part D in front of you. The Control Flow tab should be selected. Click to show the
Toolbox on the left and then have the Control Flow Items selected.
a.
b. Drag the Data Flow Task to the Control Flow Area as shown below.
STEP 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Add a data flow task to the package Back to Top
Rename the Data Flow Task by right clicking on it and select Rename. Enter a name of Data Flow Task Extract License Status
Data. Look at the figure below. Take a screenshot of the resulting application window.
c.
Using the Lab6B_Questions.docx, answer the questions for Part E.
Part F: Add and configure the Flat File source
From Microsoft: “A Flat File source is a data flow component that uses metadata defined by a Flat File connection manager to specify
the format and structure of the data to be extracted from the flat file by a transform process. The Flat File source can be configured to
extract data from a single flat file by using the file format definition provided by the Flat File connection manager.”
a. Click the Data Flow Tab or double click the Data Flow Task Extract License Status Data.
b. Click on the Toolbox on the left of the application window and you should see Data Flow Sources. Select a Flat File Source.
STEP 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Add and configure the Flat File source Back to Top
c. Drag the Flat File Source to the Data Flow Area and rename it to Flat File Source License Status Data.
Double click on the Flat File Source Extract License Status Data. Notice that the name of the Flat File Connection Manager
should come up in the new dialog.
d.
Click the Preview button. The resulting dialog shows the Data View Window on top of the Flat File Source Editor. Take a
screenshot of the dialog.
e.
f. Close the Data View Window and then click Columns in the Data Source Editor. Take a screenshot of the resulting dialog.
g. Click OK.
Using the Lab6B_Questions.docx, answer the questions for Part F.
Part G: Add and configure the OLE DB destination
From Microsoft: “Your package now can extract data from the flat file source and transform that data into a format that is compatible
with the destination. The next task is to actually load the transformed data into the destination. To load the data, you must add an OLE
DB destination to the data flow. The OLE DB destination can use a database table, view, or an SQL command to load data into a
variety of OLE DB-compliant databases.”
a. Go to the Toolbox and expand Data Flow Destinations. Select OLE DB Destination.
Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Add and configure the OLE DB destination Back to Top
Drag the OLE DB Destination below the Flat File Source Extract License Status Data on the Data Flow Tab. And Select the Flat
File Source Extract License Status Data and you should see a green and red arrow on its bottom portion. Drag the green arrow
and connect it with the OLE DB Destination.
b.
c. Change the name of OLE DB Destination to OLE DB Dest License Status.
Double click OLE DB Dest License Status. In the Name of the table or the view: pull-down select the View that we created
earlier.
d.
Click Mappings and change the Input Column to match the Destination Column. For the first row select ID and for the second
row select Status.
e.
f. Click Connection Manager again and then click Preview. Take a screenshot of the resulting dialog.
g. Click Close and then click OK.
Using the Lab6B_Questions.docx, answer the questions for Part G.
Part H: Tidy up the package
From Microsoft: “Business Intelligence Development Studio provides tools that make it easy and quick to format the package layout.
The formatting features include the ability to make shapes the same size, align shapes, and manipulate the horizontal and vertical
spacing between shapes.”
Make sure that you selected the Data Flow Tab. Drag your cursor around the Flat File Source and the OLE DB Dest to select
both of them.
a.
Then go to the Format Menu > Make Same Size > Both. And then do Format > Align > Lefts. Finally, right click on the
background of the Data Flow Design Area and click Add Annotations.
b.
In the annotations box type: The data flow will extract data from a file and then will import the data to a table via the View that
you created earlier. Look at the figure below for what to type in the Annotation. To get a new line in the Annotation, Press
Ctrl-Enter.
c.
Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Tidy up the package Back to Top
d. Take a screenshot of the resulting application window.
Using the Lab6B_Questions.docx, answer the questions for Part H.
Part I: Test the package
We now need to test our package and see if it will load our flat file into the dbo.LicenseStatus table using the View that we built,
dbo.vwLicStatLab6B.
Open iLab SQL 2008 Server Management Studio. Expand the BIS445_Redwood database and then expand Tables. Right click
on the dbo.LicenseStatus Table to Select the Top 1000 Rows; you should receive the following 16 row output.
a.
Step 3: Deliverable Back to Top
STEP 1: Problem Description Back to Top
STEP 2: Define a Cube Back to Top
67 of 69 10/7/2014 11:49 PM
Go back to BIDS. The Data Flow Tab should be active. Go to the Debug Menu and click Start Debugging. Notice that Package
Execution was completed. Don’t forget to Stop Debugging.
b.
Go Back to SQL 2008 Server Management Studio. Do a Select Top 1000 Rows query. You should see the additional rows, 17 –
21, added to the table. Take a screenshot of the resulting data output.
c.

Note: The techniques in this lab can be used to ETL files with millions of records and can even process many files at a time.
Using the Lab6B_Questions.docx, answer the questions for Part I.
Submit your completed YourName_Lab6B_Questions.docx to the Week 6 iLab Dropbox.
Step 3: Deliverable Back to Top
Final Deliverable Back to Top
Submit your assignment to the Dropbox located on the silver tab at the top of this page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)
Back to Top