|
Creating an Analysis Services Cube with Visual Studio 2008 - Part 1 |
Cart |
Your Account |
Trade |
Sitemap |
||
      |
|
 |
|
 |
Want to know more about Packt's Article Network? Interested in contributing your article ideas? Please visit our FAQ for more information. See More BROWSE
All Titles WordPress Web Services SOA BPEL Web Graphics & Video Web Development RAW Portugues, Espanol, Italiano, French PHP/MySQL Oracle Open Source Networking & Telephony Moodle Microsoft & .NET Linux Servers jQuery Joomla! JBoss Java e-Learning e-Commerce Dynamics Drupal CRM Cookbook Content Management Beginner Guides Architecture and Analysis AJAX Future Titles Recently Published Titles Report Services, Analysis Services, and Integration Services are the three pillars of Business Intelligence in Microsoft's vision that continues to evolve. Reporting is a basic activity, albeit one of the most important activities of an organization because it provides a specialized and customized view of the data of various forms (relational, text, xml etc) that live in data stores. The report is useful in making business decisions, scheduling business campaigns, or assessing the competition. The report itself may be required in hard copy in several document formats such as DOC, HTML, PDF, etc. Many times it is also required to be retrieved in an interactive form from the data store and viewed on a suitable interface, including a web browser. The Microsoft SQL Server 2005 Reporting Services, popularly known by its acronym SSRS, provides all that is necessary to create and manage reports and deploy them on a report server with output available in several document formats. The reader will greatly benefit from reading the several articles detailed in the author's Hodentek Blog. The content for the articles were developed using VS 2003, VS 2005, SQL 2000 and SQL 2005. See More |
Creating an Analysis Services Cube with Visual Studio 2008 - Part 1
Reviewing Jayaram's other OLAP related articles may greatly help in understanding this article. Installing SQL Server Analysis ServicesSQL 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 SSASThe 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 StudioFile | 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 2008File | 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 SourceRight 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.
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005
Creating a Data Source ViewThe 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. Solution Explorer
Tables and Diagram Organizer
Northwind.dsv [Design]
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.
SummaryThe 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. Beginners Guide to SQL Server Integration Services Using Visual Studio 2005
About the AuthorDr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his web site or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.netBooks from Packt |
TOP TITLES 
In this article by Alexandru Serban, let's take a more realistic software development scenario. What I am going to build is a room-reservation system for the newly launched Orbital Hotel. As you well know, this is the very first space building, after the International Space Station, used for tourism, allowing people to enjoy a view of our blue planet and stars from their private rooms. OK, OK, the Orbital Hotel doesn't yet exist, but when it does, it must have a room reservation system anyway. Who knows, it might be this one. See More |
| ||||||||
