Exploring Business Intelligence Development Studio

SQL Server 2005 Business Intelligence Development Studio is a simplified edition of Microsoft Visual Studio 2005 that is installed with the SQL Server Client Tools. You may install BIDS and the other SQL Server 2005 client tools on any desktop computers with connectivity to your licensed SQL server. No other licensing is required to use this product. BIDS uses the same base application as Visual Studio, but the templates installed with BIDS are used specifically for designing business intelligence (BI) solutions. These templates include Analysis Services, Integration Services, and Reporting Services projects.

The BIDS interface is somewhat similar to SQL Server 2005 Server Management Studio, with docking and autohiding utility windows, but these are two different applications. SQL Server Management Studio (SSMS), with its integrated query windows and object browser, is designed for managing SQL Server and its related services. BIDS is designed to help you manage and develop BI projects and solutions.

You will use the BIDS environment for creating new projects and solutions. Later, you will build on this skill by adding components to existing projects and solutions. A solution is simply a container for managing related projects and really has no other specific functionality. A project consists of several files that define objects such as data connections and SSIS packages.

Different project types are created in BIDS, using specialized templates and designer components that integrate with the BIDS/Visual Studio shell, called the Integrated Development Environment (IDE). The project designers and templates installed with BIDS are used to create SQL Server 2005 Analysis Services, Integration Services, and Reporting Services projects.

Project files should be stored in your local file system or on a network share owned and managed by a specific user. These project files can be shared among multiple project team members by using source and version control software such as Microsoft Visual SourceSafe (VSS) or Visual Studio 2005 Team Foundation Server. These tools allow project files to be locked, checked in, checked out, and explicitly shared by team members from within the BIDS or Visual Studio environment. Third-party source code management software can also be used to perform the same tasks, but it might not integrate as easily into the BIDS environment.

SSIS packages and other object definition files are stored in a standard XML format. BIDS enables you to view and edit the raw XML for an object or to use a graphical designer, with related menus, toolbars, and dialog boxes. In general, it's best to use the graphical designer to make modifications. On occasion, it might be convenient to use a simple Find and Replace command to make changes to the XML file. Just make sure that you always make a backup copy of any file before making changes in this way.

Installing any edition of Visual Studio 2005 will simply add additional templates and designers to the development environment. These project templates will be available by using both the Business Intelligence Development Studio shortcut on the SQL Server program group and the Visual Studio program group. Visual Studio and BIDS use a standard set of menus, toolbars, and utility windows, regardless of the project type. Many of these options are customized and enhanced, depending on the specific project type.

The development environment, illustrated in Figure 2-1, consists of a designer window in the center with utility windows on either side. These windows, such as the Server Explorer, Toolbox, Solution Explorer, and Properties panes, can be set to dock or autohide. When a window is hidden, an icon is displayed on the side bar. When you pass the mouse pointer over this icon, the hidden window will slide over the design surface.

Figure 2-1: The SSIS development environment

Standard and specialized toolbars are available to gain access to many design, development, debugging, and deployment features. These options vary, depending on the project type and the specific object designer you have open. Later, you'll learn to use variables, debugging tools, and deployment configuration settings.

Solution Explorer

The Solution Explorer pane, shown in Figure 2-2, is located in the upper-right area by default. This is used to manage all of the objects and files related to a project.

Figure 2-2: Solution Explorer enables you to manage all of the objects and files in a project

The Properties pane shows a list of all properties for the selected object. Properties can be grouped categorically or sorted alphabetically. Most properties can be set and modified by using this pane. Additionally, many objects have a custom properties interface, accessible by double-clicking the object, that enables you to manage properties by using a dialog box more suited for the object type. The Properties pane contains an object selection drop-down list at the top of the pane, which allows easy selection of all objects, as shown in Figure 2-3.

Figure 2-3: The Properties pane describes the state of an item in BIDS, such as the status of all the properties for a selected object

Other utility panes and windows include SSIS package variables, debugging tools, and the errors window. You will learn to use these in later chapters, including Chapter 7, "Debugging Packages," and Chapter 9, "Detecting and Handling Processing Errors."

When you open BIDS, the Start Page is displayed. This contains a list of the most recently opened projects, getting-started links for new BI developers, and current information about WebCasts, articles, and MSDN educational events. This is a Web page that will be updated if you have Internet connectivity on your development computer.

Docking Utility Windows

The utility windows can be customized to move, hide, show, and dock in the BIDS shell environment. The standard utility windows can be docked to the edges of the BIDS shell window, and a window can be moved to various landing zones in the development environment. Use the mouse to drag a window to view the docking zones before you release it. Once docked, each window can be set to stay or to autohide. In this mode, the window will slide out of the way until you hover the mouse pointer over a corresponding icon. This behavior is controlled by using the pushpin icon in the utility window header bar. Windows can be grouped together to form tab groups. The best way to become acquainted with all of these options is to take some time to work in the development environment and experiment with these features.

Solution Explorer is the master view to a solution and project. When an item is selected in Solution Explorer or selected in a designer window, the properties for that item are available in the Properties pane. You can also use the object drop-down list at the top of the Properties pane to select a specific object or item. When a package is opened in the designer, the Toolbox, displayed on the left side of the BIDS application window, shows available components that can be dropped onto the designer surface.

Exploring an SSIS Project in BIDS

This demonstration introduces you to the SQL Server Business Intelligence Development Studio. You will open an existing package and review key components of the BIDS integrated development environment.

Open SSIS Sample Solution

1. Open Windows Explorer and navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap02 folder.

2. Double-click the  SSIS Sample Solution.sln file. This opens the project in BIDS.

3. Explore the project in Solution Explorer, noting that the Solution Explorer pane is displayed on the right side of the BIDS application window.

4. If only the Solution Explorer icon is visible, the window is probably hidden. In this case, hover the mouse over the icon to show the Solution Explorer pane, and then click the thumbtack icon to pin the window down.

   Note 

   The thumbtack icon in the title bar of the Solution Explorer pane indicates the autohide state of the window. Click the icon to toggle between the autohiding and pinned states. In the pinned position, the thumbtack is pointing down. This indicates that the window will not hide when the mouse pointer is moved away. When the thumbtack points to the side, the window will autohide after the mouse pointer is moved away from it.

5. In Solution Explorer, note the following project elements and their respective object types:

   Open table as spreadsheet

   Project Element	Object Type
   is2005SbS	Data source
   Employee	Data source view
   ScrapReason	Data source view
   ImportCustomers	Package
   CreateLists	Package

View the ScrapReason Data Source View in the Data Source View Designer

1. Double-click the ScrapReason data source view in Solution Explorer to open the data source view designer. Note that the diagram window contains tables with linked relationships and a named query called ScrappedProducts.

   This appears as a virtual table. You can easily navigate to the diagram by left-clicking the mouse over the compass points arrow in the lower-right corner of the designer. Holding the mouse button down allows you to reposition the diagram view in a small thumbnail diagram window. Your screen looks like this:

   

2. Right-click the named query, ScrappedProducts, and choose Edit Named Query to view the Transact-SQL query joining five tables in the Transact-SQL Graphical Query Designer.

   This window is similar to those used in other Microsoft query products such as Microsoft Office Access and Reporting Services.

3. When completed, close the Edit Named Query editor by clicking the Cancel button.

4. Right-click the named query and choose Explore Data to view the results of this query in a separate designer tab. Close this tab (titled Explore ScrappedProducts Table) by clicking the X button on the right of the designer title bar when completed.

   Often, a data source view will contain many tables and queries and can contain tables from multiple data sources.

5. To navigate to tables that are not currently in view, left-click and hold over the compass points icon in the lower-right intersection of the vertical and horizontal scroll bars. A thumbnail view of the diagram appears. Move the pointer over this viewer to navigate the diagram window. Close the data source view designer window when completed.

View the CreateLists Package in the Package Designer

1. Double-click the CreateLists package in Solution Explorer to open the designer. This opens the Control Flow tab showing two data flow tasks with a precedence constraint from the Employee List task to the Scrapped Products task.

2. Double-click the green line to view the precedence constraint properties. The package designer portion of your screen looks like this:

   

   Note that the Employee List task execution must complete with success before the Scrapped Products task will execute.

3. Click the Cancel button to close the Precedence Constraint Editor when completed.

Open the Data Flow Task - Employee List

A component can be edited by double-clicking its designer shape when it doesn't have focus or clicked on its edge when it does have focus.

1. Open the first task, labeled Data Flow Task - Employee List. This moves to the Data Flow tab and shows all of the components within this task.

   Note that you can switch between multiple data flow tasks by using the drop-down list in the Data Flow tab page. The Employee List data flow task consists of the following components:

   Open table as spreadsheet

   Component	Component Type
   Employee Query	OLE DB data source
   FullName	Derived column transformation
   Department Shift Employee	Sort transformation
   Employees CSV	Flat file destination

   Like the control flow components, each element is connected with a green line. On the Data Flow tab, this is a data flow path, which indicates the flow of data as it is read from sources, transformed, and then written to destinations. Your screen looks like this:

   

Explore the Connection Managers

Located at the bottom of the designer window is the Connection Managers pane. A connection manager stores shared data source and destination connection information at the package level. A connection manager can also be derived from a project-level data source.

1. Open each of the connection managers and view the connection properties. In turn, double-click each of the icons in the Connection Managers pane to open the corresponding editor, and then close the editor by clicking the Cancel button.

   The is2005sbs connection manager derives its connection properties from the  is2005sbs.ds data source for the project. The two flat file connection managers refer to specific text files located at C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap02\Data.

2. In the editor dialog box for the Employees connection manager, view properties in the General properties group. Note the format, header, and delimiter settings used to read the file.

3. The list box on the left side of the connection manager dialog box contains a list of property group icons. Click an item on this list to change the property group page.

   1. View the data and columns read from the text files in the Columns group of properties. Your screen looks like this:

      

   2. View the properties for each data flow component.

Open the OLE DB Source - Employee Query Task

1. Open the OLE DB Source editor for the Employee Query data source, the first component in the data flow designer.

   Note that this source uses the is2005sbs connection manager. This data source retrieves results by using a SQL command with the following Transact-SQL statement:

    SELECT   Shift.Name AS Shift , Department.Name AS Department , Employee.Title , Employee.LastName , Employee.FirstName FROM Shift INNER JOIN Employee ON Shift.ShiftID = Employee.ShiftID INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID 

2. Use the Columns page to view the data fields returned by this query.

3. On the Connection Manager page, click the Preview button to view the query results.

4. Close the editor dialog box when completed.

Open the Derived Column - FullName Task

1. Double-click the FullName derived column transformation.

   This allows you to define new columns added to the data flow, usually based on existing column values.

   Note that the Employee column is derived from the expression FirstName + " " + LastName, which concatenates the first and last column values separated by a space.

2. Close this editor by clicking the Cancel button.

Open the Sort - Department Shift Employee Task

1. Double-click the Department Shift Employee sort transformation to open the editor. This sorts the data flow on specified column values. Each column is listed with a sort order and sort type, either ascending or descending.

   Note that the Title column and the Employee derived column are set to pass through- rather than participate in-the sorting.

2. Close this editor by clicking the Cancel button.

Open the Flat File Destination - Employees CSV Task

1. Open the Employees CSV flat file destination.

   This is used to create or overwrite a text file named  Employees.csv.

2. Click the Mappings group to see how the columns from the data flow correspond to the columns defined for the destination file.

   Note that only the Department, Shift, and Employee columns are written to the file. Your screen looks like this:

   

3. Close the Flat File Destination Editor.

View the Final Output

1. On the Connection Manager page, click the Preview button to view the final output. Close this editor for the Employees CSV flat file destination when completed.

2. Review components of the Scrapped Products data flow task.

3. Using the same pattern as before, use the editors to view properties for each of the transformations in the Scrapped Products data flow task. Starting on the Control Flow tab, edit the Scrapped Products task.

   Note that the connection manager property for the ScrappedProducts data source refers to the data source view ScrapReason, using the reference is2005sbs\ScrapReason.

   By selecting the ScrappedProducts named query in the data source view in Solution Explorer, the original Transact-SQL query is reused.

4. View the properties for the Sum ScrappedQty aggregate transformation.

   This groups values on the ProductCategory, ProductSubCategory, Product, Product-Number, and ScrapReason column values and then applies the Sum aggregation on all detail rows for each grouping.

5. Close any open editor dialog boxes by clicking the Cancel button.

Execute the CreateLists Package

The package is complete. All data sources and destinations have been verified, and all tasks and transformations are set and configured. The next step is to execute the package to load and transform the data.

1. In Solution Explorer, right-click the CreateLists package and choose Execute Package. This runs the package in Debug mode, enabling several useful debugging features.

   As the packages execute, each task, connection, and transformation will be displayed in yellow while it executes and then green when completed. When all components are green, the package execution has completed. Your screen looks like this:

   

   Because it is running in Debug mode, execution is paused at the end of the operation and must be stopped before returning to the design environment.

2. Terminate execution debugging by clicking Stop Debugging on the Debug menu.

3. Review the destination text files.

View Employees, ScrappedProducts, and Customer Records Files in the Data Folder

1. In Windows Explorer, view the contents of the C:\Documents and Settings\<user-name>\My Documents\Microsoft Press\is2005sbs\Chap02\Data folder.

   The  Employees.csv and ScrappedProducts.csv files were created and populated by this package.

2. Use Notepad to view the contents of each file. (You can right-click a file, choose Open With, and then select Notepad.)

3. While viewing the contents of this folder, view the contents of the four files containing customer records. Each contains an alphabetic range of customer names and related demographic information.

Review the ImportCustomers Package

1. In SSIS, double-click ImportCustomers in Solution Explorer to open it in the package designer.

   This package contains a Foreach Loop task that can be used to iterate through the members of a variety of different collection types. It is configured to find all comma separated value (CSV) files with names beginning with the word Customer in the data folder. For each file, the data flow script tasks (visible in the control flow designer) are executed. You will learn about the mechanics of this process later. For now, just view the related data flow components and their properties.

   This package contains text annotations that explain some of the logic and might be helpful to understand better how the components interact.

2. View the data flow task components of the Data Flow tab.

   Note that the data flow path line has a viewer icon that indicates that it has been configured with a data viewer. This is a debugging tool that effectively works as a breakpoint on each iteration through the file collection loop and displays the records present at that stage in the data flow.

Excute the ImportCustomers Package

1. In Solution Explorer, right-click the ImportCustomers package and choose Execute Package.

   The components will display yellow and then green as each task and transformation is completed.

   Partway through the first loop iteration, a grid window will appear with the result from the first customer source file.

2. Resize this window if necessary to view the LastName column.

   Note 

   The data view window has a peculiarity where at times it can't be resized using the lower-right corner. To resize the window if this behavior occurs, resize it vertically and then horizontally.

   Your screen looks like this:

   

3. Click the green play button to step through the process and view the results of the next loop iteration. Again, note the LastName column values.

4. Repeat this step to display four sets of records.

5. When completed, close the data viewer window, and then click Stop Debugging on the Debug menu.

   Note 

   The script task has deleted each of the source files, so if you would like to repeat this exercise in the future, you can copy these files from the Data_Backup folder to the Data folder.