Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Exclusive offer: get 50% off this eBook here
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

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.

$23.99    $12.00
by Jayaram Krishnaswamy | July 2008 | .NET Microsoft

As noted in Part 1, OLAP presents Business Intelligence via what is known as a CUBE. A Cube has many dimensions and it provides a faster method to access the intelligence compared to the structured querying where the overhead of 'Joins' makes it inefficient. Taking the example of a 'Sales' Cube having everything related to sales we can identify the dimensions as the Sales Outlet, The Customers , the Products and the time period over which sales are being audited or looked into. We also notice that there are certain measures that we are interested related to these 'dimensions' like 'Store Sales', 'Cost of Products, 'number of units sold' etc. The Analysis Services analyzes these intricate relationship that exist in a cube. In this part we will see the process of creating a Cube in VS 2008.

Reviewing Jayaram's other OLAP related articles may greatly help in understanding this article.

Creating a New Cube

The folder structure for the project developed in Part 1 is shown in the next figure. The Northwind.ds data source and the Northwind.dsv data source view were configured in Part 1. There are no pre-existing cubes in Nwind2008.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Right click the Cubes folder and from the drop-down menu you can create a new Cube.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Click on New Cube... menu item. This opens the Cube Wizard welcome window as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Click on the Next button. This opens the Select Creation Method page of the wizard as shown. There are three options and the default is used for this article.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Click on the Next button. This opens the Select Measure Groups tables. At least one table must be chosen to continue. There is even the option of asking for a suggestion.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Click on the Suggest button at the top. The program goes through the motions and comes up with two tables as candidates for Measures group, the Products table and the Order Details table. You will see check marks appearing for these two tables. Accept the suggested tables for measures and click on the Next button.

This opens the Select Measures window where you can choose measures that you want to include in the Cube as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Uncheck the ID related items in the Products table and click on the Next button. This brings up the Select New Dimensions window as shown in the next figure. Here also one could choose the needed items. For this article the default is accepted.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Click on the Next button. This takes you to the Completing the Wizard window which shows your Cube contents in a tree view as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Now click on the Finish button. This creates the Cube as shown in the Solution Explorer.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Now you will see additional tabs open up for the Northwind.cube as shown. Using these tabs you can look at more details. These are outside the scope of this article.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Also separate windows gets displayed for Cube's Measures and Dimensions as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Also, the Data Source View of the Cube with the relationships between the Dimensions and Measures gets displayed as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

 


 

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 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.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

Cube Processing

At this point it may be interesting to know how all this might have affected the Analysis Server. As you can see from the following figure that every node is empty. The database is there because we created an empty database with the same name to start with.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Right click the Northwind.cube and from the drop-down menu choose Process... as shown in the next figure.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Since a database was pre-existing you may get the following message from the Microsoft Visual Studio interface.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

As the answer for the question posed, click on the Yes button. The Visual Studio begins the deployment and after a while you should see the Deployment Succeed message as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Now if you go back to the SQL Server Management Studio and refresh the NWind2008 database you should be seeing the database details as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

As soon as the deployment is completed the Visual Studio Interface displays the Process Cube Wizard window as shown. Although one can make many changes as one might see from the window, for this article, the defaults are accepted. Since this is the first time working with the Cube, the Process Option, namely Process Full is appropriate. Later it is possible to use other options shown by the pull-down menu.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Click on the button Run.... The Cube gets processed so that all the SQL queries that form the basis for setting up the Cube gets executed. The Process Progress window shows the details of the processing. You may reprocess if changes are made to the Cube.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Click the View Details button to see the following display.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100=
"http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Nwind2008</DatabaseID>
<CubeID>Northwind</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

Close the Process Progress and Process Cube windows.

Summary

This article shows the design and processing of the Cube using the Data Source and Data Source View designed in Part 1. The Visual Studio interface provides a very straight forward procedure to process the Cube and the process is unambiguous. The processing of the Cube essentially consists of deploying the database and then processing the various queries necessary for the details contained in the Cube.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 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.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

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.

Contact Jayaram Krishnaswamy

Books From Packt

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

ODP.NET Developer’s Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET
ODP.NET Developer’s Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET

Microsoft AJAX Library Essentials: Client-side ASP.NET AJAX 1.0 Explained
Microsoft AJAX Library Essentials: Client-side ASP.NET AJAX 1.0 Explained

ASP.NET Data Presentation Controls Essentials
ASP.NET Data Presentation Controls Essentials

Programming Windows Workflow Foundation: Practical WF Techniques and Examples using XAML and C#
Programming Windows Workflow Foundation: Practical WF Techniques and Examples using XAML and C#

Visual SourceSafe 2005 Software Configuration Management in Practice
Visual SourceSafe 2005 Software Configuration Management in Practice

LINQ Quickly
LINQ Quickly

BlackBerry Enterprise Server for Microsoft® Exchange
BlackBerry Enterprise Server for Microsoft® Exchange

 


Your rating: None Average: 3 (1 vote)

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
a
4
e
Y
6
g
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software