Local Cube SSAS 2005 - CREATE GLOBAL CUBE

At the beginning of the project, the requirements was only to create local cube for a set of user. The first thing that we tried is the MDX-script "CREATE GLOBAL CUBE ". This article relates our experiences with the MDX-script "CREATE GLOBAL CUBE". The following aspects will be discussed:
1. Basic: create a local cube with CREATE GLOBAL CUBE
2. Slicing a cube

1. Basic
Before discussing the great stuffs, I just would like to explain the CREATE GLOBAL CUBE structure. The basic (no special selection, slicing) CREATE GLOBAL CUBE MDX script is structured as follow:

CREATE GLOBAL CUBE [Name of the cube]
Storage [full filename]
FROM [Server cube name]
(
MEASURE [Server Cube name].[Measure 1],
...
MEASURE [Server Cube name].[Measure n],

DIMENSION [Server Cube name].[Dimension name 1],
...
DIMENSION [Server Cube name].[Dimension name n],
)

Explanation
Name of the cube: specify here the name of the cube that will be stored in the cube file. A cube file can store several cubes. We don't use this functionality because it can be confusing for our end users: "which cube do I have to use?" Further, it can have a great impact on performance.

Full filename: This is the name of the local cube where the cube selection is going to be stored. The filename is fulle qualified: folder/directory + the name of the file (e.g. "C:\test.cub").

Server cube name: a local cube has to be created from a server cube. Specify here the name of the server cube that you will be used as data source for your local cube.

Measure 1-n: specify first the measure(s) you want to have in your local cube. Attention: Global cubes do not support distinct count measures!

Dimension name 1-n: specify first the measure(s) you want to have in your local cube.

2. Slicing the cube
The user which is going to use the local cube maybe need only a part of the total cube in order to realise its analysis. The total cube needs to be "filtered". In OLAP terminology, the term "slice" is used. To "slice" the data is to retrieve a piece (a slice) of the cube by specifying measures and values for some or all of the dimensions. In this paragraph, three slicing types will be discussed:
a. Slicing measure and dimensions
b. Slicing attributes in dimensions
c. Slicing members

a.Slicing measure and dimensions
Per definition, when using the CREATE GLOBAL CUBE MDX-script, you are slicing the server cube. If you specify all the measures and the dimensions of the server cube, then you have a full slice of the cube. If you specify just one measure and one dimension (assuming that the server cube has more than one measures and one dimensions), then you are obtain a partial slice of the server cube. The above mdx-script is enough to realise this kind of slice operation.

b. Slicing on attributes in dimensions
Next to the fact, that our user doesn't need all the measures and dimensions to realise his analysis, the user may also only be interested in specific attributes in a dimension. For example, he may only be interested in the customer name and customer region but not in detailed address fields.

[Example MDX script]

c. Slicing on members of attributes
Next to the fact, that our user doesn't need all the measures and dimensions to realise his analysis, the user may also only be interested in specific set of data (only the net sales of customers in region x) or maybe for security reason is he just allowed to see a part of the data.

CREATE GLOBAL CUBE [Name of the cube]
Storage [full filename]
FROM [Server cube name]
(
MEASURE [Server Cube name].[Measure 1]
, ...
, MEASURE [Server Cube name].[Measure n]

, DIMENSION [Server Cube name].[Dimension name 1]
, ...
, DIMENSION [Server Cube name].[Dimension name n]
( LEVEL [level name1]
, LEVEL [level name2]
, MEMBER [Dimension name n].[Hierarchy name].[level name1].&[member] ) )

References & Links
- Definition of slice and dice
- CREATE GLOBAL CUBE MDX Script structure