Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 21, 2004

Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation

By William Pearson

About the Series ...

This is the twenty-fourth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real-world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("MSAS") (and uses MS Office 2000 and above, in cases where MS Office components are presented in the article).


In our last article, Introduction to Local Cubes, we ventured beyond earlier topics surrounding the retrieval and reporting of data from a server-based MSAS cube, and transitioned into the realm of remote, independent OLAP data source design and creation. We explored approaches to creating local cubes within MS Office, discussing many of the foundational concepts behind the architecture of multidimensional data sources, and their creation from an integrated MS Office client application, Excel. As a part of a hands-on practice exercise, we then created a local cube from an existing Excel PivotTable report, sourced initially from an MSAS server-based cube.

We explored many practical aspects of putting the functionality to work immediately, discussing ways that local (or "offline") cubes can meet the business requirements of distributed information consumers, and add value to the organization in general. Throughout the hands-on practice exercise we performed, in creating a local cube from an existing server-based cube, we commented upon the results we obtained, to reinforce our understanding of the concepts involved.

In this article, we will explore a second approach to the creation of a local cube. While we will rely again upon the PivotTable report as our design and development tool, this time we will focus more on the use of Microsoft Query ("MS Query"), and begin with a relational database instead of an MSAS server-based cube. We will discuss advantages in taking this approach and situations for which it is especially useful. As with the prior article, Introduction to Local Cubes, the intent of this article is to offer options for more independence from the perspective of the information consumer, as well as to make the fruits of MSAS OLAP available to enterprise team members through the conduits of the applications that are pervasive in the desktop population we find in business today.

In this lesson, we will:

  • Discuss the creation of a local cube from a relational data source;
  • Discuss scenarios where starting with a relational source might be advantageous;
  • Discuss how the creation of a local cube from a relational data source can be used to complement an MSAS implementation;
  • Derive a subset of relational data as the basis of our local cube with the Query Wizard;
  • Introduce the OLAP Cube Wizard, and complete design of our local cube;
  • Discuss the results obtained through the various steps of the cube development process in our practice exercises.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM