Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
June 21, 2004
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: