Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 — Save 50%
An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.
A cube in the context of Business Intelligence is a multidimensional representation of business facts that can be accessed quickly to provide specific information. (This can be accomplished by properly written queries in a relational database, but the overhead involved in processing the query, which may involve a large number of 'joins', is simply not efficient). While a relational model is more suited to OLTP, a different model is necessary for OLAP. Whereas highly normalized tables are a norm for OLTP, the model for OLAP does not require normalization. The SQL Server Analysis Services that supersedes the earlier versions is specifically meant for analyzing data in the archives or in OLTP databases to be retrieved and burnished to provide the 'Intelligence' by mining for facts hidden in the data. This two part article by Dr. Jayaram Krishnaswamy describes how a Cube is designed using Visual Studio 2008 and how it may be browsed on the Analysis Server. In Part 1, the necessary items for creating the Cube, namely the Data Source and Data Source Views are described.
Reviewing Jayaram's other OLAP related articles may greatly help in understanding this article.
Installing SQL Server Analysis Services
SQL Server Analysis Services (SSAS) gets installed during the SQL Server 2008 (February 2008 CTP) installation. At that time the Service Accounts for the various components are also set up as shown in this screen shot taken during a typical installation. For full details review the following article on SQL Server 2008 installation.
The Analysis Services is also configured during the same installation as shown displaying the default directories for Data, log file, backup etc.
Starting and Stopping the SSAS
The Analysis Services can be started and stopped from the Services in the local machine which can be accessed from All Programs | Control Panel | Administrative Tools | Services.
Connecting to SSAS from SQL Server Management Studio
File | Connect Object Explorer... in SQL Server Management Studio allows you to access the UI shown below where you can provide login information and get connected to the Analysis Services as shown.
Out of the box, the SSAS comes up with a simple folder structure as shown. It does not come with databases. You may create a new database (this is not to be confused with databases on the Database Engine of SQL Server 2008). The database you create here will have the same folder structure as the Analysis Services Project folder structure when you start with VS 2008.
Creating an Analysis Services Project in VS 2008
File | New | Project... opens the New Project window. Choose Business Intelligence Projects and from the Visual Studio installed templates choose Analysis Services Project. Change the default name of project to Nwind2008. This creates an empty project with a number of folders as shown. Although this is an empty structure you can deploy it on the server. To deploy right click with Nwind2008 highlighted and choose to deploy. After the processing is successfully completed you will see that Nwind 2008 is now available on the localhost.
Adding a Data Source
Right click on Data Sources folder and this will display the menu item New Data Source... as shown.
Click on New Data Source... to open the Data Source Wizard as shown in the next figure. Read carefully the instructions on this page. In particular note that in order to change features of the data source you need to call up the next wizard - The Data Source View Wizard.
Click on the Next button. This opens the page "Select how to define the connection" of the wizard as shown in the next figure. Here you can either create a data source based on an existing connection or use a new connection, or create a data source based on another object. The window also displays the existing connections under Data Connections and for each highlighted connection displays the properties on the right side. The New... button allows you to create a new connection by guiding you with more interactive windows.
Click on Hodentek2.Northwind (<Server Name>.<database name>) (this will be different in your case) choose this data source and click on the Next button. This opens the Impersonation Page of the Data Source Wizard as shown with all text boxes empty, but with the Windows user name and password as default choice. Northwind database is not installed when you install SQL Server 2008. You may follow the procedure described in these articles to bring the Northwind database to SQL server 2008. Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard and Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps.
Change it to Use the service account and click on the Next button. This displays the Completing the Wizard page as shown where a Data source name and its connection information are displayed.
Click on the Finish button. This adds the chosen data source to the Data Sources folder as shown. Northwind.ds is the file that is added and any changes to the source can be made by double clicking this file and applying the changes.
eBook Price: $23.99
Book Price: $39.99
Creating a Data Source View
The Data Source View in SSAS is a logical model of related tables, views, and queries from one or more data sources. The OLAP and data mining projects in SSAS are based on this model. The Data Source View contains the Meta data from the selected data source(s) and any relationships that are defined for them. This cached Meta data allows working with SSAS projects without a continuous active connection to the data source. The Data Source View is created by calling up the Data Source View Wizard that helps in creating the Data Source View.
Right Click Data Source Views folder and click on New Data Source View... menu item. This opens the Data Source View Wizard window as shown. The data source view is a more specialized view of data coming from the relational tables and views.
Click on the Next button. The Select Data Source page of the Data Source Wizard shows up. Here you use an existing data source, or create a new data source. Since in the earlier step you chose Northwind it shows up here as an existing data source. Here the Northwind will be the only source used in this tutorial.
Click on the Next button. This brings up the Select Tables and Views page of the Data Source View Wizard displaying all the existing Tables and Views in the Northwind data base. Using the > and >> arrows you can transfer objects from the Available Objects area in the left to the Included Objects area in the right. The objects transfered to right will be used in the projects. You may also use the < and << arrows to remove them as well. You need to highlight the table, or view in the left and use the > arrow to transfer. The Orders table has been transferred as shown in the next figure. You also have the option to filter the objects although no filtering is used for this article.
Highlight the chosen table in the right, the Add Related Tables button gets enabled if it is not. Click on the Add Related Tables button. A number of other tables related to the Orders table gets added to the project as shown.
Click on the Next button. This takes you to the final step in creating a Data Source View of the Northwind as shown in the next figure.
Click on the Finish button. This adds the Northwind.dsv file to the Data Source Views folder. New windows Tables and Diagram Organizer window gets displayed as shown. The various relationships between the related tables gets displayed in the Northwind.dsv[Design] pane as shown in the following figures.
Tables and Diagram Organizer
The designer is very flexible in that you can add and remove tables from this diagram retaining what you want for your use as well as carry out many other tasks. It will be instructive to try them out. If you make a right click in an empty area in the above figure in the designer you will display the following drop-down menu from which you can choose the required item.
For example to delete the Employee table after making a right click on the table you can display a choice to remove the table as shown. This removes the Employee table from the DSV (Data Source View) file.
You get a chance to delete it or not as shown in the next figure. If you choose to delete by clicking on Yes, it will be removed from the DSV.
In this manner only the following structure for the Data Source View was retained for Part 2.
The article described features of the SQL Server Analysis Services that can be installed from Microsoft SQL Server 2008. Steps involved in creating a data source and a data source view were also described. These will be needed in Cube design. The VS 2008 IDE is very flexible and allows you to go and make changes in several places quite easily. The wizards delineate steps which are easy to follow and unambiguous.
eBook Price: $23.99
Book Price: $39.99
About the Author :
Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.
He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.
He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.
Books From Packt