Integrating Microsoft SQL Server 2000 OLAP and Microsoft Office: Creating a Local Cube with Office 2000
Introducing 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 analystsbe they salespeople, distributed management, or the host of other interested parties who cannot always be "wired in" to the central corporate data storesto be able to carry and work with business intelligence applications and perform "disconnected analysis" in virtually any location.
Understanding Local Cubes
Local cubes provide a way to answer many of the requirements of the mobile user because 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 source data. Of the many general types of cubes available via Analysis Services, local cubes are the sole data sources that are truly portable.
Office 2000 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 MSSQL Server 2000 Analysis Services, local cubes have dimensions with members as well as measures. As one would expect, local cubes are smaller than server cubes, and are not designed with the number of capabilities that we find in their server cousins. But 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.
Local cubes do not offer the following features/capabilities that are typically found in server cubes:
- Member properties
- Shared dimensions
- Virtual dimensions
- Permanent write-back storage capacity/tables
- 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 into 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 guess from the 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 more disk space will be required to store it compared to its ROLAP counterpart, but 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 need to plan judiciously and be selective in our choices of which data needs to be included in our proposed cubes.