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 May 17, 2004

Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes - Page 2

By William Pearson

An Introduction to Local Cubes

The number of mobile information consumers in today's business environment is growing at exponential rates. More consumers are using portable computers than ever before, with the trends indicating a significant preference for portables over stationary desktops because they can be transported easily. Many of my clients have built, or are in the process of building, large sales organizations with mobile computing platforms, and even those that are not as "sales intensive" have witnessed a huge increase in the use of mobile PCs by internal staff and management alike.

The rise in the general population of "disconnected users" is placing large demands on the OLAP technologies that currently exist, and support of these mobile information consumers requires the capability to grant them access to organizational data without active connections. Local (or "offline") cubes offer opportunities for analysts, be they salespeople, distributed management or the host of other interested parties that cannot always be "wired in" to the central corporate data stores, to be able to carry and work with business intelligence applications and perform "disconnected analysis" from virtually any location.

Understanding Local Cubes

Local cubes provide a means of answering many of the requirements of the mobile user, as they are appropriate in situations in which an Analysis Server is unavailable. A local cube consists of a single, highly portable file that can live as easily on a laptop computer as it can on a server. Information consumers with local cubes on their PCs can browse and report from the cubes without the need for a connection to an Analysis Server, or to the cube's original data source. Of the many general types of cubes available via Analysis Services, local cubes are the sole data sources that are truly portable.

MS Office is self-contained in its capacity to support our needs to create local cubes, as well as to enable us to use these portable OLAP data sources for analytical purposes. Like the cubes we typically encounter within Analysis Services, local cubes have dimensions with members, as well as measures. Local cubes are, as one would expect, smaller than server-based cubes, and are not designed with a few of the capabilities that we find in their server cousins. Nevertheless, local cubes still retain many robust features that make them ideal for the "road warrior" analyst, as well as for other, less obvious applications, including the provision of capabilities to work when a network is down or inordinately slow, and so forth.

Local cubes do not offer the following features / capabilities that are typically found in server cubes:

  • aggregations
  • partitions
  • member properties
  • shared dimensions
  • virtual dimensions
  • permanent write-back storage capacity and structures
  • capability to create parent-child dimensions.

The storage mode for a local cube can be either multidimensional OLAP (MOLAP) or relational OLAP (ROLAP). Using the ROLAP mode, the cube structure is created, and the dimensions populated, but measure data is not saved locally within the cube. Once created, we still need to be connected to the cube's data source (and not simply Analysis Services) to analyze the data it contains. Since ROLAP-mode local cubes store no measure data, queries against them require more time to execute in comparison to the same queries based upon MOLAP-mode local cubes.

As one might have guessed from our discussion so far, the MOLAP variety will be the focus of our lesson with local cubes. The MOLAP storage option does mean that the cube takes longer to create, and that more disk space will be required to store it compared to its ROLAP counterpart. However, the tradeoff is most likely acceptable when we consider that our cubes have the data we need, locally accessible and immediately ready for efficient and straightforward analysis; indeed, this is the largest driver for the presence of the cubes in the first place. To minimize creation time, as well as the space requirements that result from our local cube design, we will need to plan judiciously and be selective in our choices of what data needs to be included in our proposed cubes.

Creating a Local Cube

In this and the subsequent article, we will explore and practice the two primary means of creating a local cube within Office 2003. Both approaches will rely upon the PivotTable Service to create the cubes, but we will follow two significantly different paths to reach the same destination. We will undertake cube creation following each path, and discuss the differences and nuances as we go.

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