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

Local Cube SSAS 2005 - Introduction

For a customer, local cubes (also called offline cube, cube file) needed to be created. The challenge was to create on a daily basis a set of local cubes with different structures. Lots of research have been done before being able to realise the solution. I would like to share with you all the activities that we executed in a set of articles. All these articles will be available at the end in a white paper over local cube SSAS 2005.

During our quest, our knowledge grow and to discuss local cube, I will follow our path:
1. MDX-script: CREATE GLOBAL CUBE
2. Local Cube task, Cube slice
3. ASSL (Analysis Services Scripting Language) batch
4. Qlickview

Enjoy the story!

SSIS - Script functions trim leading zeros

In some development case, you may have to trim leading zero's. In SQL, it is possible with the SQL function PATINDEX. But how to do it within a SSIS package? The Data Flow Transaformation task "Derived column" doesn't provide any function in order to realise this operation. That's whi I developed a little script that determine the position of the first character that doesn't match a give character; in this case a zero.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Declare variable
Dim StartPosition As Integer

StartPosition = DefineFirstPositionChar(Row.ColumnToBeSearched, "0")

Row.ColumnToBeSearched = Mid(Row.ColumnToBeSearched, StartPosition, Row.ColumnToBeSearched.Length)
End Sub

Function DefineFirstPositionChar(ByVal EntityID As String, ByVal SearchChar As String) As Integer
'Declare variable
Dim Length As Integer
Dim letter As String
Dim start As Integer = 1

'Store the length of the entity
Length = EntityID.Length

'Loop through the string of the entity
'When letter SearchChar is found return the start position through DefineFirstPositionChar
Do Until start = Length
letter = Mid(EntityID, start, 1)
If letter <> SearchChar Then
DefineFirstPositionChar = start
Exit Do
End If
start = start + 1
Loop

Exit Function
End Function