BI Log

Implementing Analysis Services Process Add Functionality – Part 3

2011-07-12T08:44:00.000+01:00

Following on from my previous post, which can be found here, where I presented one solution for implementing process add functionality, I’ll try to cover off some functional code in order to implement this. Clearly this code can be enhanced and/or converted to your specific needs but hopefully this is a start.

First let’s setup a dummy set of tables to represent a customer dimension. (Apologies for the formatting, will fix once I can edit my templates again.)

Dummy Tables

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET NOCOUNT ON;

/**

* Delete objects.

IF EXISTS ( SELECT *

FROM sys.objects

WHERE [object_id] = OBJECT_ID(N'[dbo].[customer]') AND

[type] in (N'U') )

BEGIN

DROP TABLE [dbo].[customer];

END;

IF EXISTS ( SELECT *

FROM sys.objects

WHERE [object_id] = OBJECT_ID(N'[dbo].[address]') AND

[type] in (N'U') )

BEGIN

DROP TABLE [dbo].[address];

END;

IF EXISTS ( SELECT *

FROM sys.objects

WHERE [object_id] = OBJECT_ID(N'[dbo].[customer_group]') AND

[type] in (N'U') )

BEGIN

DROP TABLE [dbo].[customer_group];

END;

IF EXISTS ( SELECT *

FROM sys.objects

WHERE [object_id] = OBJECT_ID(N'[dbo].[customer_class]') AND

[type] in (N'U') )

BEGIN

DROP TABLE [dbo].[customer_class];

END;

/**

* CUSTOMER CLASS table.

CREATE TABLE [dbo].[customer_class]

(

[customer_class_id] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[customer_class_name] VARCHAR(50) NOT NULL

) ON [PRIMARY];

SET IDENTITY_INSERT [dbo].[customer_class] ON;

INSERT INTO [dbo].[customer_class] ([customer_class_id], [customer_class_name])

VALUES(1, 'Class1');

SET IDENTITY_INSERT [dbo].[customer_class] OFF;

/**

* CUSTOMER GROUP table.

CREATE TABLE [dbo].[customer_group]

(

[customer_group_id] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[customer_group_name] VARCHAR(50) NOT NULL,

[customer_class_id] INT NOT NULL

CONSTRAINT [fk_customer_group_customer_class]

REFERENCES [dbo].[customer_class] ([customer_class_id])

) ON [PRIMARY];

SET IDENTITY_INSERT [dbo].[customer_group] ON;

INSERT INTO [dbo].[customer_group] ( [customer_group_id], [customer_group_name],

[customer_class_id] )

VALUES(1, 'Group1', 1);

INSERT INTO [dbo].[customer_group] ( [customer_group_id], [customer_group_name],

[customer_class_id] )

VALUES(2, 'Group2', 1);

SET IDENTITY_INSERT [dbo].[customer_group] OFF;

/**

* ADDRESS table.

CREATE TABLE [dbo].[address]

(

[address_id] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[address_line_1] VARCHAR(50) NOT NULL,

[address_line_2] VARCHAR(50) NULL

) ON [PRIMARY];

SET IDENTITY_INSERT [dbo].[address] ON;

INSERT INTO [dbo].[address] ([address_id], [address_line_1], [address_line_2])

VALUES(-1, 'Unknown', NULL);

INSERT INTO [dbo].[address] ([address_id], [address_line_1], [address_line_2])

VALUES(1, 'House 1', 'Street 1');

INSERT INTO [dbo].[address] ([address_id], [address_line_1], [address_line_2])

VALUES(2, 'House 2', NULL);

SET IDENTITY_INSERT [dbo].[address] OFF;

/**

* CUSTOMER table.

CREATE TABLE [dbo].[customer]

(

[customer_id] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[customer_name] VARCHAR(50) NOT NULL,

[address_id] INT NOT NULL

CONSTRAINT [fk_customer_address]

REFERENCES [dbo].[address]([address_id]),

[customer_group_id] INT NOT NULL

CONSTRAINT [fk_customer_customer_group]

REFERENCES [dbo].[customer_group]([customer_group_id])

) ON [PRIMARY];

SET IDENTITY_INSERT [dbo].[customer] ON;

INSERT INTO [dbo].[customer] ([customer_id], [customer_name], [address_id], [customer_group_id])

VALUES(1, 'Customer 1', 1, 1);

INSERT INTO [dbo].[customer] ([customer_id], [customer_name], [address_id], [customer_group_id])

VALUES(2, 'Customer 2', -1, 1);

INSERT INTO [dbo].[customer] ([customer_id], [customer_name], [address_id], [customer_group_id])

VALUES(3, 'Customer 3', 2, 2);

SET IDENTITY_INSERT [dbo].[customer] OFF;

Now, taking each of the steps in turn…

The Seven-And-A-Half Steps to Process Add

Step 1

First we need to capture the list of dimensions we are interested in and the associated key table for that dimension. Note that by “key table”, I’m referring to the table that contains the attribute to be used as the key in the dimension. The following code will create a table that captures this information.

/**

* LIST OF DIMENSIONS

IF EXISTS ( SELECT *

FROM sys.objects

WHERE [object_id] = OBJECT_ID(N'[dbo].[dimension]') AND

[type] in (N'U') )

BEGIN

DROP TABLE [dbo].[dimension];

END;

CREATE TABLE [dbo].[dimension](

[dimension_id] INT IDENTITY(1,1) NOT NULL

CONSTRAINT [pk_dimension] PRIMARY KEY CLUSTERED,

[olap_dimension_id] SYSNAME NOT NULL

CONSTRAINT [uk_dimension] UNIQUE NONCLUSTERED ,

[dimension_name] SYSNAME NOT NULL

) ON [PRIMARY];

Step 1a

For performance reasons we will create a table in which we will capture the names of all the tables used by a given. (i.e. The key table and recursively all related tables.)

/**

* DIMENSION TABLE RELATIONSHIP

IF EXISTS ( SELECT *

FROM sys.objects

WHERE [object_id] = OBJECT_ID(N'[dbo].[dimension_table_relationship]') AND

[type] in (N'U') )

BEGIN

DROP TABLE [dbo].[dimension_table_relationship];

END;

CREATE TABLE [dbo].[dimension_table_relationship](

[dimension_table_relationship_id] INT IDENTITY(1,1) NOT NULL

CONSTRAINT [pk_dimension_table_relationship] PRIMARY KEY CLUSTERED,

[dimension_id] INT NOT NULL

CONSTRAINT [fk_dimension_table_relationship_dimension_dimension]

REFERENCES [dbo].[dimension]([dimension_id]),

[olap_dimension_id] SYSNAME NOT NULL

CONSTRAINT [fk_dimension_table_relationship_dimension_olap]

REFERENCES [dbo].[dimension]([olap_dimension_id]),

[dimension_key_table_schema] SYSNAME NOT NULL,

[dimension_key_table_name] SYSNAME NOT NULL,

[attribute_table_schema] SYSNAME NOT NULL,

[attribute_table_name] SYSNAME NOT NULL,

[first_referencing_table_schema] SYSNAME NOT NULL,

[first_referencing_table_name] SYSNAME NOT NULL

) ON [PRIMARY];

Step 2

Next we need to create a table in which we will capture the names of all the tables used by a given dimension on rows. (i.e. The key table and all related tables.) as well as the previous and current identity values for each table.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dimension_process_attribute_key_range]') AND type in (N'U'))

BEGIN

DROP TABLE [dbo].[dimension_process_attribute_key_range];

END;

CREATE TABLE [dbo].[dimension_process_attribute_key_range](

[dimension_process_attribute_key_range_key] INT IDENTITY(1,1) NOT NULL

CONSTRAINT [pk_dimension_process_attribute_key_range] PRIMARY KEY CLUSTERED ,

[olap_dimension_id] SYSNAME NOT NULL,

[dimension_key_table_schema] SYSNAME NOT NULL,

[dimension_key_table_name] SYSNAME NOT NULL,

[attribute_table_schema] SYSNAME NOT NULL,

[attribute_table_name] SYSNAME NOT NULL,

[min_key_value] BIGINT NOT NULL,

[max_key_value] BIGINT NULL,

[new_data_flag] BIT NOT NULL,

[process_flag] BIT NOT NULL,

[created_date] DATETIME NOT NULL,

[created_by] NVARCHAR(255) NOT NULL,

[updated_date] DATETIME NULL,

[updated_by] NVARCHAR(255) NULL

) ON [PRIMARY];

Step 3

Populate the tables created in point 1 and 1a above with the dimensions for which you wish to use process add and where the underlying relational schema has been configured as described in the previous posts.

SET IDENTITY_INSERT [dbo].[dimension] ON;

INSERT INTO [dbo].[dimension] ([dimension_id], [olap_dimension_id], [dimension_name] )

VALUES(1, 'Customer', 'Customer');

SET IDENTITY_INSERT [dbo].[dimension] OFF;

INSERT INTO [dbo].[dimension_table_relationship]

( [dimension_id], [olap_dimension_id],

[dimension_key_table_schema], [dimension_key_table_name],

[attribute_table_schema], [attribute_table_name],

[first_referencing_table_schema], [first_referencing_table_name] )

VALUES(1, 'Customer', 'dbo', 'customer', 'dbo', 'customer', 'dbo', 'customer');

Step 4

Create a stored procedure that accepts a given dimension name and key table name and, using the information schema views etc, identify all related tables by means of foreign key relationships. If you did not create foreign keys between all tables, you will need statically capture all tables used by a given dimension and maintain this over time as tables are added or dropped.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_reseed_dimension_table_relationship_by_dimension]') AND type in (N'P', N'PC'))

BEGIN

DROP PROCEDURE [dbo].[usp_reseed_dimension_table_relationship_by_dimension];

END;

CREATE PROCEDURE [dbo].[usp_reseed_dimension_table_relationship_by_dimension]

@i_DimensionID INT = NULL,

@i_DimensionKeyTableSchema SYSNAME = NULL,

@i_DimensionKeyTableName SYSNAME = NULL

/**

* Recursively captures all tables related to the table passed into the

* stored procedure. Essentially it recursively captures the attribute

* relationships from a given "dimension key" table.

* Input Parameters:

* ----------------

* @i_DimensionID The relational identifier for the dimension for

* which attribute relationships are required.

* @i_DimensionKeyTableSchema The schema in which the dimension key table

* resides.

* @i_DimensionKeyTableName The name of the table from which all related

* tables should be derived.

* Ver Author Date Comments

* --- ------------------- ----------- ------------------------------

* 1 Philip Stephenson 11-Mar-2011 Original version.

SET NOCOUNT ON;

DECLARE -- Define Error Codes.

@ERR_MISSING_PARAM INT,

@ERR_GENERAL INT,

-- Define user variables.

@ErrCode INT,

@ErrMsg NVARCHAR(MAX),

@StepNbr SMALLINT,

@FQProcedureName NVARCHAR(392),

@RetVal INT,

@NewLine NVARCHAR(MAX),

@OlapDimensionID SYSNAME,

@RowsAffected BIGINT;

BEGIN

/**

* STEP 1

* Set variables.

SET @StepNbr = @StepNbr + 1;

SET @ERR_MISSING_PARAM = 1;

SET @ERR_GENERAL = 128;

SET @ErrCode = 0;

SET @FQProcedureName = QUOTENAME(DB_NAME()) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID));

SET @ErrMsg = N'ERROR - Stored procedure ' + @FQProcedureName + N' ';

SET @NewLine = NCHAR(13) + NCHAR(10);

-- Capture the OLAP dimension ID for the dimension ID provided.

SELECT @OlapDimensionID = [olap_dimension_id]

FROM [dbo].[dimension]

WHERE [dimension_id] = @i_DimensionID;

-- Set the default rows affected count.

SET @RowsAffected = -1;

/**

* STEP 2

* Ensure that the dimension ID parameter is not NULL and that a non-null olap

* dimension ID was found.

SET @StepNbr = @StepNbr + 1;

IF (@i_DimensionID IS NULL) OR (@OlapDimensionID IS NULL)

BEGIN

SET @ErrMsg = @ErrMsg + N'identified that either the given @i_DimensionID parameter (i.e. ' + ISNULL(@i_DimensionID, '{null}') + N') was NULL or the associated [olap_dimension_id] was NULL. This is not allowed!';

SET @ErrCode = @ERR_MISSING_PARAM;

GOTO FAIL;

END;

/**

* STEP 3

* Ensure that the dimension key table schema parameter is not NULL.

SET @StepNbr = @StepNbr + 1;

IF (@i_DimensionKeyTableSchema IS NULL)

BEGIN

SET @ErrMsg = @ErrMsg + N'identified that the @i_DimensionKeyTableSchema parameter was NULL. This is not allowed!';

SET @ErrCode = @ERR_MISSING_PARAM;

GOTO FAIL;

END;

/**

* STEP 4

* Ensure that the dimension key table name parameter is not NULL.

SET @StepNbr = @StepNbr + 1;

IF (@i_DimensionKeyTableName IS NULL)

BEGIN

SET @ErrMsg = @ErrMsg + N'identified that the @i_DimensionKeyTableName parameter was NULL. This is not allowed!';

SET @ErrCode = @ERR_MISSING_PARAM;

GOTO FAIL;

END;

BEGIN TRY

/**

* STEP 5

* Create temporary table, else truncate it if it already exists.

SET @StepNbr = @StepNbr + 1;

IF (OBJECT_ID('[tempdb].[dbo].[#foreign_key_tree]') IS NULL)

BEGIN

CREATE TABLE [#foreign_key_tree]

(

[base_table_schema] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL,

[base_table_name] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL,

[referenced_table_schema] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL,

[referenced_table_name] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL,

[referencing_table_schema] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL,

[referencing_table_name] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL

)

END;

ELSE

BEGIN

TRUNCATE TABLE [#foreign_key_tree];

END;

/**

* STEP 6

* Capture the starting point from which to recursively derive all related

* tables.

SET @StepNbr = @StepNbr + 1;

INSERT INTO [#foreign_key_tree] ( [base_table_schema], [base_table_name],

[referenced_table_schema],

[referenced_table_name],

[referencing_table_schema],

[referencing_table_name] )

VALUES(@i_DimensionKeyTableSchema, @i_DimensionKeyTableName,

@i_DimensionKeyTableSchema, @i_DimensionKeyTableName,

@i_DimensionKeyTableSchema, @i_DimensionKeyTableName);

/**

* STEP 7

* Recursively capture all tables related to the set of tables already in

* the temporary table. The idea here is to obtain the set of all tables

* that could be used by a dimension in Analysis Services.

SET @StepNbr = @StepNbr + 1;

WHILE (@RowsAffected <> 0)

BEGIN

INSERT INTO [#foreign_key_tree] ( [base_table_schema], [base_table_name],

[referenced_table_schema],

[referenced_table_name],

[referencing_table_schema],

[referencing_table_name] )

SELECT inline.[base_table_schema] AS "base_table_schema",

inline.[base_table_name] AS "base_table_name",

inline.[TABLE_SCHEMA] AS "referenced_table_schema",

inline.[TABLE_NAME] AS "referenced_table_name",

inline.[referenced_table_schema] AS "referencing_table_schema",

inline.[referenced_table_name] AS "referencing_table_name"

FROM

(

SELECT DISTINCT tmp.[base_table_schema],

tmp.[base_table_name],

reftab.[TABLE_SCHEMA],

reftab.[TABLE_NAME],

tmp.[referenced_table_schema],

tmp.[referenced_table_name]

FROM INFORMATION_SCHEMA.TABLES tab

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS con

ON tab.[TABLE_SCHEMA] = con.[TABLE_SCHEMA] AND

tab.[TABLE_NAME] = con.[TABLE_NAME]

INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fctcol

ON tab.[TABLE_SCHEMA] = fctcol.[TABLE_SCHEMA] AND

tab.[TABLE_NAME] = fctcol.[TABLE_NAME] AND

con.[CONSTRAINT_NAME] = fctcol.[CONSTRAINT_NAME]

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rfc

ON con.[CONSTRAINT_SCHEMA] = rfc.[CONSTRAINT_SCHEMA] AND

con.[CONSTRAINT_NAME] = rfc.[CONSTRAINT_NAME]

INNER JOIN ( SELECT [CONSTRAINT_SCHEMA],

[CONSTRAINT_NAME],

[TABLE_SCHEMA],

[TABLE_NAME],

[COLUMN_NAME]

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

UNION

-- Include unique indexes

SELECT SCHEMA_NAME(obj.[schema_id]) AS "CONSTRAINT_SCHEMA",

idx.[name] AS "CONSTRAINT_NAME",

SCHEMA_NAME(obj.[schema_id]) AS "TABLE_SCHEMA",

obj.[name] AS "TABLE_NAME",

col.[name] AS "COLUMN_NAME"

FROM sys.index_columns icl

INNER JOIN sys.indexes idx

ON icl.[index_id] = idx.[index_id]

INNER JOIN sys.objects obj

ON idx.[object_id] = obj.[object_id] AND

icl.[object_id] = obj.[object_id]

INNER JOIN sys.columns col

ON obj.[object_id] = col.[object_id] AND

icl.[column_id] = col.[column_id]

WHERE idx.[is_unique] = 1 AND

idx.[is_primary_key] = 0 AND

obj.[is_ms_shipped] = 0 ) refcol

ON rfc.[UNIQUE_CONSTRAINT_SCHEMA] = refcol.[CONSTRAINT_SCHEMA] AND

rfc.[UNIQUE_CONSTRAINT_NAME] = refcol.[CONSTRAINT_NAME]

INNER JOIN INFORMATION_SCHEMA.TABLES reftab

ON refcol.[TABLE_SCHEMA] = reftab.[TABLE_SCHEMA] AND

refcol.[TABLE_NAME] = reftab.[TABLE_NAME]

INNER JOIN [#foreign_key_tree] tmp

ON tab.[TABLE_SCHEMA] = tmp.[referenced_table_schema] AND

tab.[TABLE_NAME] = tmp.[referenced_table_name]

WHERE tab.[TABLE_TYPE] = 'BASE TABLE' AND

con.[CONSTRAINT_TYPE] = 'FOREIGN KEY'

) inline LEFT OUTER JOIN [#foreign_key_tree] nxt

ON inline.[TABLE_SCHEMA] = nxt.[referenced_table_schema] AND

inline.[TABLE_NAME] = nxt.[referenced_table_name]

WHERE nxt.[base_table_name] IS NULL;

-- Capture the number of rows inserted.

SET @RowsAffected = @@ROWCOUNT;

END;

/**

* STEP 9

* Update current information in the static table.

SET @StepNbr = @StepNbr + 1;

UPDATE tgt

SET [first_referencing_table_schema] = src.[referencing_table_schema],

[first_referencing_table_name] = src.[referencing_table_name]

FROM [dbo].[dimension_table_relationship] tgt

INNER JOIN [#foreign_key_tree] src

ON tgt.[dimension_id] = @i_DimensionID AND

tgt.[olap_dimension_id] = @OlapDimensionID AND

tgt.[dimension_key_table_schema] = src.[base_table_schema] AND

tgt.[dimension_key_table_name] = src.[base_table_name] AND

tgt.[attribute_table_schema] = src.[referenced_table_schema] AND

tgt.[attribute_table_name] = src.[referenced_table_name]

WHERE tgt.[first_referencing_table_schema] <> src.[referencing_table_schema] OR

tgt.[first_referencing_table_name] <> src.[referencing_table_name];

/**

* STEP 9

* Insert new information into the static table.

SET @StepNbr = @StepNbr + 1;

INSERT INTO [dbo].[dimension_table_relationship]

( [dimension_id], [olap_dimension_id],

[dimension_key_table_schema], [dimension_key_table_name],

[attribute_table_schema], [attribute_table_name],

[first_referencing_table_schema], [first_referencing_table_name] )

SELECT @i_DimensionID AS "dimension_id",

@OlapDimensionID AS "olap_dimension_id",

[base_table_schema],

[base_table_name],

[referenced_table_schema],

[referenced_table_name],

[referencing_table_schema],

[referencing_table_name]

FROM [#foreign_key_tree] src

LEFT OUTER JOIN [dbo].[dimension_table_relationship] tgt

ON tgt.[dimension_id] = @i_DimensionID AND

tgt.[olap_dimension_id] = @OlapDimensionID AND

tgt.[dimension_key_table_schema] = src.[base_table_schema] AND

tgt.[dimension_key_table_name] = src.[base_table_name] AND

tgt.[attribute_table_schema] = src.[referenced_table_schema] AND

tgt.[attribute_table_name] = src.[referenced_table_name]

WHERE tgt.[dimension_table_relationship_id] IS NULL;

/**

* STEP 11

* Clean up.

SET @StepNbr = @StepNbr + 1;

IF (OBJECT_ID('[tempdb].[dbo].[#foreign_key_tree]') IS NOT NULL)

BEGIN

DROP TABLE [#foreign_key_tree];

END;

END TRY

BEGIN CATCH

SET @ErrCode = @ERR_GENERAL;

SET @ErrMsg = @ErrMsg + N'raised error (' + CAST(ERROR_NUMBER() AS NVARCHAR) + N') in step number (' + CAST(@StepNbr AS NVARCHAR) + N') at line number (' + CAST(ERROR_LINE() AS NVARCHAR) + N') with message: ' + ERROR_MESSAGE();

GOTO FAIL;

END CATCH;

GOTO SUCCESS;

FAIL:

-- Cater for max exception string length.

SET @ErrMsg = CASE WHEN (LEN(@ErrMsg) > 2044) THEN N'...' + RIGHT(@ErrMsg, 2041) ELSE RIGHT(@ErrMsg, 2044) END;

RAISERROR(@ErrMsg, 16, 1);

IF (OBJECT_ID('[tempdb].[dbo].[#foreign_key_tree]') IS NOT NULL)

BEGIN

DROP TABLE [#foreign_key_tree];

END;

RETURN @ErrCode;

SUCCESS:

RETURN 0;

END;

Step 5

Create a stored procedure that uses the information captured by the SP created in step 4 above, to now populate the table created in point 2 above with an arbitrary minimum value and, using the IDENT_CURRENT(‘’) function, populate the max value with the current identity value for the table. Note that you must ensure that you retrieve the values for the key table before any of the related tables. This is what provides protection against the race condition discussed in my previous post.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_derive_new_attribute_keys_for_processing]') AND type in (N'P', N'PC'))

BEGIN

DROP PROCEDURE [dbo].[usp_derive_new_attribute_keys_for_processing];

END;

CREATE PROCEDURE [dbo].[usp_derive_new_attribute_keys_for_processing]

/**

* For each dimension configured to be processed using "process add", capture the

* set of new attribute keys for every attribute in the dimension.

* Ver Author Date Comments

* --- ------------------- ----------- ------------------------------

* 1 Philip Stephenson 01-Jul-2011 Original version.

SET NOCOUNT ON;

DECLARE -- Define Error Codes.

@ERR_GENERAL INT,

@ERR_LOOKUP_FAIL INT,

-- Define user variables.

@ErrCode INT ,--= 0,

@ErrMsg NVARCHAR(MAX),

@StepNbr SMALLINT,-- = 0,

@FQProcedureName NVARCHAR(392),

@RetVal INT,

@AbsoluteMinKeyValue BIGINT,

@AbsoluteMaxKeyValue BIGINT,

@MinKeyValue BIGINT,

@RowsAffected BIGINT,

@ProcessAddFlag BIT;

BEGIN

/**

* STEP 1

* Set variables.

SET @StepNbr = @StepNbr + 1;

SET @ERR_GENERAL = 128

SET @ERR_LOOKUP_FAIL = 512;

SET @FQProcedureName = QUOTENAME(DB_NAME()) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID));

SET @ErrMsg = N'ERROR - Stored procedure ' + @FQProcedureName + N' ';

SET @AbsoluteMinKeyValue = -32768;

SET @AbsoluteMaxKeyValue = ((POWER(CAST(2 AS BIGINT), 62) - 1) * 2) + 1;

SET @MinKeyValue = @AbsoluteMinKeyValue;

SET @RowsAffected = -1;

BEGIN TRY

/**

* STEP 2

* Create temporary table, else truncate it if it already exists.

SET @StepNbr = @StepNbr + 1;

IF (OBJECT_ID('[tempdb].[dbo].[#foreign_key_tree]') IS NULL)

BEGIN

CREATE TABLE [#foreign_key_tree]

(

[olap_dimension_id] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL,

[base_table_schema] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL,

[base_table_name] SYSNAME COLLATE DATABASE_DEFAULT NOT NULL,

[referenced_table_schema] SYSNAME COLLATE DATABASE_DEFAULT NULL,

[referenced_table_name] SYSNAME COLLATE DATABASE_DEFAULT NULL,

[new_min_key_value] BIGINT NULL,

[min_key_value] BIGINT NULL,

[max_key_value] BIGINT NULL,

[process_add_flag] BIT NOT NULL

)

END

ELSE

BEGIN

TRUNCATE TABLE [#foreign_key_tree];

END;

/**

* STEP 3

* Capture all tables (and their current identity column value) for all

* dimensions set to be processed. The idea here is to obtain the set of

* all tables that could be used by a dimension in Analysis Services and

* to capture the available keys.

SET @StepNbr = @StepNbr + 1;

INSERT INTO [#foreign_key_tree] ( [olap_dimension_id], [base_table_schema],

[base_table_name], [referenced_table_schema],

[referenced_table_name], [new_min_key_value],

[min_key_value], [max_key_value],

[process_add_flag] )

SELECT rel.[olap_dimension_id],

rel.[dimension_key_table_schema] AS "base_table_schema",

rel.[dimension_key_table_name] AS "base_table_name",

rel.[attribute_table_schema] AS "referenced_table_schema",

rel.[attribute_table_name] AS "referenced_table_name",

atr.[max_key_value] + 1 AS "new_min_key_value",

@AbsoluteMinKeyValue AS "min_key_value",

IDENT_CURRENT(QUOTENAME(rel.[attribute_table_schema]) + N'.' + QUOTENAME(rel.[attribute_table_name])) AS "max_key_value",

1 AS "process_add_flag"

FROM [dbo].[dimension_table_relationship] rel

LEFT OUTER JOIN [dbo].[dimension_process_attribute_key_range] atr

ON rel.[olap_dimension_id] = atr.[olap_dimension_id] AND

rel.[dimension_key_table_schema] = atr.[dimension_key_table_schema] AND

rel.[dimension_key_table_name] = atr.[dimension_key_table_name] AND

rel.[attribute_table_schema] = atr.[attribute_table_schema] AND

rel.[attribute_table_name] = atr.[attribute_table_name] AND

atr.[process_flag] = 1;

/**

* STEP 4

* Invert current (process flag = 1) and non-current (process flag = 0) rows,

* updating related attributes appropriately.

SET @StepNbr = @StepNbr + 1;

UPDATE tgt

SET [process_flag] = CASE WHEN tgt.[process_flag] = 1 THEN

ELSE

END,

[min_key_value] = CASE

-- Update soon to be non-current record for ProcessAdd.

WHEN (tmp.[process_add_flag] = 1) AND (tgt.[process_flag] = 1) THEN tmp.[max_key_value] + 1

-- Update soon to be current record for ProcessAdd when flipped from "non process add".

WHEN (tmp.[process_add_flag] = 1) AND (tgt.[process_flag] = 0) AND (tgt.[min_key_value] = @AbsoluteMinKeyValue) THEN non.[max_key_value] + 1

-- Update soon to be current record for ProcessUpdate/ProcessFull

WHEN (tmp.[process_add_flag] = 0) AND (tgt.[process_flag] = 0) THEN @AbsoluteMinKeyValue

ELSE

tgt.[min_key_value]

END,

[max_key_value] = tmp.[max_key_value],

[new_data_flag] = CASE

WHEN ( (tmp.[process_add_flag] = 1) AND (tgt.[process_flag] = 1) AND ((tmp.[max_key_value] + 1) <= ISNULL(tmp.[max_key_value], @AbsoluteMaxKeyValue)) ) THEN 1

WHEN ( (tmp.[process_add_flag] = 0) AND (tgt.[process_flag] = 0) AND (@AbsoluteMinKeyValue <= ISNULL(tmp.[max_key_value], @AbsoluteMaxKeyValue)) ) THEN 1

WHEN ( (tmp.[process_add_flag] = 1) AND (tgt.[process_flag] = 0) AND (tgt.[min_key_value] = @AbsoluteMinKeyValue) AND ((non.[max_key_value] + 1) <= ISNULL(tmp.[max_key_value], @AbsoluteMaxKeyValue)) ) THEN 1

WHEN ( (tmp.[process_add_flag] = 1) AND (tgt.[process_flag] = 0) AND (tgt.[min_key_value] <> @AbsoluteMinKeyValue) AND (tgt.[min_key_value] <= ISNULL(tmp.[max_key_value], @AbsoluteMaxKeyValue)) ) THEN 1

WHEN ( (tmp.[process_add_flag] = 0) AND (tgt.[process_flag] = 1) AND (tgt.[min_key_value] <= ISNULL(tmp.[max_key_value], @AbsoluteMaxKeyValue)) ) THEN 1

ELSE

END,

[updated_date] = GETDATE(),

[updated_by] = ORIGINAL_LOGIN()

FROM [dbo].[dimension_process_attribute_key_range] tgt

LEFT OUTER JOIN [dbo].[dimension_process_attribute_key_range] non

ON tgt.[olap_dimension_id] = non.[olap_dimension_id] AND

tgt.[dimension_key_table_schema] = non.[dimension_key_table_schema] AND

tgt.[dimension_key_table_name] = non.[dimension_key_table_name] AND

tgt.[attribute_table_schema] = non.[attribute_table_schema] AND

tgt.[attribute_table_name] = non.[attribute_table_name] AND

tgt.[process_flag] <> non.[process_flag]

INNER JOIN [#foreign_key_tree] tmp

ON tgt.[dimension_key_table_schema] = tmp.[base_table_schema] AND

tgt.[dimension_key_table_name] = tmp.[base_table_name] AND

tgt.[attribute_table_schema] = tmp.[referenced_table_schema] AND

tgt.[attribute_table_name] = tmp.[referenced_table_name]

WHERE tgt.[olap_dimension_id] = tmp.[olap_dimension_id] AND

( (non.[process_flag] IS NULL) OR ((non.[process_flag] IS NOT NULL) AND

(tgt.[process_flag] <> non.[process_flag])) );

/**

* STEP 5

* Capture information for use by OLAP views during dimension processing.

SET @StepNbr = @StepNbr + 1;

INSERT INTO [dbo].[dimension_process_attribute_key_range]

( [olap_dimension_id], [dimension_key_table_schema],

[dimension_key_table_name], [attribute_table_schema],

[attribute_table_name], [min_key_value],

[max_key_value], [new_data_flag], [process_flag],

[created_date], [created_by] )

SELECT tmp.[olap_dimension_id] AS "olap_dimension_id",

tmp.[base_table_schema],

tmp.[base_table_name],

tmp.[referenced_table_schema],

tmp.[referenced_table_name],

-- In the case of ProcessAdd, set the Min Key value appropriately else it must be the AbsoluteMinKeyValue.

CASE WHEN (tmp.[process_add_flag] = 1) THEN

-- Note that the prev min key value was bumped by the update statement in the prior step so we can just carry this forward.

COALESCE(tmp.[new_min_key_value], @AbsoluteMinKeyValue)

ELSE

@AbsoluteMinKeyValue

END AS "min_key_value",

tmp.[max_key_value],

CASE WHEN ISNULL(tmp.[max_key_value], @AbsoluteMaxKeyValue) >= COALESCE(tmp.[new_min_key_value], @AbsoluteMinKeyValue) THEN

ELSE

END AS "new_data_flag" ,

1 AS "process_flag",

GETDATE() AS "created_date",

ORIGINAL_LOGIN() AS "created_by"

FROM [#foreign_key_tree] tmp

LEFT OUTER JOIN [dbo].[dimension_process_attribute_key_range] tgt

ON tmp.[olap_dimension_id] = tgt.[olap_dimension_id] AND

tmp.[base_table_schema] = tgt.[dimension_key_table_schema] AND

tmp.[base_table_name] = tgt.[dimension_key_table_name] AND

tmp.[referenced_table_schema] = tgt.[attribute_table_schema] AND

tmp.[referenced_table_name] = tgt.[attribute_table_name] AND

tgt.[process_flag] = 1

WHERE tgt.[dimension_process_attribute_key_range_key] IS NULL;

END TRY

BEGIN CATCH

SET @ErrCode = @ERR_GENERAL;

GOTO FAIL;

END CATCH;

GOTO SUCCESS;

FAIL:

-- Cater for max exception string length.

SET @ErrMsg = CASE WHEN (LEN(@ErrMsg) > 2044) THEN N'...' + RIGHT(@ErrMsg, 2041) ELSE RIGHT(@ErrMsg, 2044) END;

RAISERROR(@ErrMsg, 16, 1);

RETURN @ErrCode;

SUCCESS:

RETURN 0;

END;

Step 6

Create a user defined table function that accepts a dimension name, schema name and table name and returns the minimum and maximum values from the table created in point 2 above for a given dimension, schema and table.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF EXISTS ( SELECT *

FROM sys.objects

WHERE [object_id] = OBJECT_ID(N'[dbo].[udf_attribute_keys_for_process_add]') AND

[type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') )

BEGIN

DROP FUNCTION [dbo].[udf_attribute_keys_for_process_add];

END;

CREATE FUNCTION [dbo].[udf_attribute_keys_for_process_add] (

@i_OlapDimensionID SYSNAME,

@i_DimensionTableSchema SYSNAME,

@i_DimensionTableName SYSNAME,

@i_AttributeTableSchema SYSNAME,

@i_AttributeTableName SYSNAME )

RETURNS TABLE

* Returns the minimum and maximum keys for a given dimension's attribute

* table that need to be processed by Analysis Services.

* Input Parameters:

* ----------------

* @i_OlapDimensionID The Analysis Services identifier for the dimension

* for which attribute key ranges are required.

* @i_DimensionTableSchema The schema for the table that contains the key

* attribute for the dimension.

* @i_DimensionTableName The name of the table that contains the key

* attribute for the dimension.

* @i_AttributeTableSchema The schema for the table for the attribute to be

* processed.

* @i_AttributeTableName The name of the table for the attribute to be

* processed.

* Ver Author Date Comments

* --- ------------------- ----------- ---------------------------------

* 1 Philip Stephenson 01-Jul-2011 Original version.

-- Return results.

RETURN SELECT [min_key_value], [max_key_value]

FROM [dbo].[dimension_process_attribute_key_range]

WHERE [olap_dimension_id] = @i_OlapDimensionID AND

[dimension_key_table_schema] = @i_DimensionTableSchema AND

[dimension_key_table_name] = @i_DimensionTableName AND

[attribute_table_schema] = @i_AttributeTableSchema AND

[attribute_table_name] = @i_AttributeTableName AND

[process_flag] = 1 AND

[new_data_flag] = 1;

Step 7

Update the current views used to present data to Analysis Services to join to the function created in point 6 above, limiting the output to be where the surrogate key value is between the minimum and maximum values.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF EXISTS (SELECT * FROM sys.views WHERE [object_id] = OBJECT_ID(N'[dbo].[v_process_add_customer]'))

BEGIN

DROP VIEW [dbo].[v_process_add_customer];

END;

CREATE VIEW [dbo].[v_process_add_customer]

SELECT [customer_id],

[customer_name],

[address_id],

[customer_group_id]

FROM [dbo].[customer] att

INNER JOIN [dbo].[udf_attribute_keys_for_process_add]('Customer',

'dbo',

'customer',

'dbo',

'customer') prc

ON att.[customer_id] BETWEEN prc.[min_key_value] AND prc.[max_key_value];

Test Drive

Now that we have everything setup, let’s give it a whirl. First of all we need to identify all of the relationships for a given dimension, customer in this case, starting from the key table.

EXECUTE [dbo].[usp_reseed_dimension_table_relationship_by_dimension]

@i_DimensionID = 1,

@i_DimensionKeyTableSchema = 'dbo',

@i_DimensionKeyTableName = 'customer';

We can see what this did be executing the following:

SELECT * FROM [dbo].[dimension_table_relationship];

Next we need to identify the minimum and maximum surrogate key values in each of the tables, which we do as follows:

EXECUTE [dbo].[usp_derive_new_attribute_keys_for_processing];

We can see what this did by executing the following:

SELECT * FROM [dbo].[dimension_process_attribute_key_range];

All we need to do now is select from the view to see all new entries:

SELECT *

FROM [dbo].[v_process_add_customer];

To see how this works, we can select from the function we created earlier as follows:

SELECT *

FROM [dbo].[udf_attribute_keys_for_process_add]('Customer',

'dbo',

'customer',

'dbo',

'customer');

To actually test the concept of “process add”, let’s now add a new member:

INSERT INTO [dbo].[customer] ([customer_name], [address_id], [customer_group_id])

VALUES('Customer 4', 2, 2);

In order to capture the metadata around new key values etc. we need to execute:

EXECUTE [dbo].[usp_derive_new_attribute_keys_for_processing];

Which, once again, has captured the metadata as follows:

SELECT * FROM [dbo].[dimension_process_attribute_key_range];

To see what Analysis Services would see, run the following:

SELECT *

FROM [dbo].[v_process_add_customer];

Conclusion

Now I will admit that I haven’t fully test driven all of this myself to see if it all works in all situations etc. so please let me know if anything is broken and I’ll aim to fix it. Either way I hope the concept is clear, such that you can make any modifications as required.

Hopefully you will have noted that in the stored procedure that determines the min and max key values, that it can be easily updated to accommodate ProcessUpdate and ProcessAdd situations by not hard-coding the value for the “process_add_flag” but I’ll leave that as an exercise for the reader.

From the above code and sample, the thing to note is that immediately before processing dimensions the [dbo].[dimension_process_attribute_key_range] stored procedure must be called in order to “snapshot” all surrogate key values. Clearly you need to be careful not to get out of sync else things will not work. If that happens you will need to revert back to a “ProcessUpdate” and then continue as per normal.

Implementing Analysis Services Process Add Functionality – Part 2

2011-05-19T08:15:00.000+01:00

In a previous here I briefly mentioned some of the benefits and issues surrounding the implementation of incremental processing of Analysis Services dimensions. In this post I’ll go into a bit more detail with regards configuring the SQL Server schema to support incremental processing of Analysis Services dimensions.

I should point out upfront that I am specifically referring to SQL Server as the relational database here but it could be any database really. The thing is, I’m going to use some SQL Server features as a shortcut both for performance reasons and ease of understanding. I know Oracle has similar functionality but I cannot document each and every RDBMS so I have just chosen SQL Server as the basis for this post.

So straight off, we have a choice to make. Should we use an out-of-line DSV when incrementally processing or not? Depending on what is chosen here influences how you go about implementing incremental dimension processing, though in the solution presented here only the final steps will need to change. To get around the issue of having to configure Kerberos and submitting a potentially large DSV every time we wish to process, I have chosen not to go the out-of-line DSV route. Although there is nothing that I could see in SQL Profiler, I would presume that there is some form of overhead to Analysis Services evaluating an out-of-line DSV, no matter how trivial.

It’s also worth mentioning that a nice side-effect of my solution is that we also overcome the race-condition with regards attribute processing when processing dimensions whilst the relational database is loading dimensional data. For example, in the case where you load a new customer linked to a new customer group and Analysis Services has already processed the customer group attribute but not the customer attribute, dimension processing will fail. The reason being that before the new member was added, Analysis Services will already have captured the set of customer group attributes as it was before the new member arrived in the table. This means that processing of the customer attribute will fail when Analysis Services tries to link the customer to the customer group, as the related member will not exist in the processed set of members for the customer group attribute. This can be extremely annoying and there is no out-of-the-box way to force a consistent snapshot of all attributes for a dimension during processing.

One Solution:

So, looking at snapshots, it seems that we need to roll our own. Clearly one idea is to effectively snapshot all the rows we wish to load for the dimension across every attribute. We can then present only those rows for loading by Analysis Services at process time. Using this method we can ensure that we prevent the race-condition described earlier during a ProcessUpdate operation too. The trick is how to snapshot the rows quickly and cheaply. (i.e. We don’t want to have to copy an entire table or perform a join across the current dimension table and a previous snapshot if we can avoid it.)

So, in my proposed solution, we first of all need to ensure that every attribute that is to be loaded into the dimension is recorded in its own table. That is, the relational schema for the dimension needs to be snow flaked. I know Kimball die-hard fans will be screaming around about now but I haven’t said that you can’t also have a star-schema table. For large Analysis Services dimensions this is usually what I do. I ensure that I generate integer surrogate keys for every attribute using snow-flaked tables with identity columns and configure the Analysis Services DSV using those tables such that only a table scan of a small table is required for every attribute. However, I also ensure that the “key” table (or bottom level) of the dimension is a star-schema table for relational reporting and as such also contains the attributes from related tables.

For example, given a customer dimension with customer as the bottom level and being related to group and organisation, I would create an organisation table and a customer group table to capture the discrete entities and generate a surrogate key for each. The customer table however, will be a star schema table, generating a surrogate key for each customer but also containing the attributes of the related groups and organisations.)

Customer Relationships

The main “trick” here, is to ensure that every snow-flaked table for each attribute uses an identity column for its surrogate key. You should also endeavour to choose an integer that best matches the expected maximum number of entries, be it a smallint (2 byte signed integer), int (4 byte signed integer) or bigint (8 byte signed integer). Analysis Services is no longer partial to the tinyint data type so best steer clear of that though.

This next part isn’t really required, though it will make your life easier in the long run as well as being good practice. Make sure that you have foreign keys setup between all of the snow flaked tables including the star-schema table and the snow-flaked tables. Essentially “document” the relationship between all related attributes by means of foreign keys. The reason for this will become clear later.

So we now have our dimension configured such that we have integer surrogate keys for every attribute in our dimension and we have both star-schema and snow-flaked tables. We could go ahead and build an Analysis Services dimension from this right now, if we so wished, so we can consider the schema requirements component of the solution to be complete.

Now that we have our dimension schema I’ll attempt to explain how all of this is used. In part 3 I’ll present some actual code that can be used as a basis for a more substantial implementation but for now I’ll just present a conceptual implementation plan. So, here goes…

1. First we need to capture the list of dimensions we are interested in and the associated key table for that dimension. Note that by “key table”, I’m referring to the table that contains the attribute to be used as the key in the dimension. Create a table that captures this information.

2. Next we need to create a table in which we will capture the names of all the tables used by a given dimension on rows (i.e. the key table and all related tables) as well as the previous and current identity values for each table.

3. Populate the table created in point 1 above with the dimensions for which you wish to use process add and where the underlying relational schema has been configured as described in the main text.

4. Create a stored procedure that accepts a given dimension name and key table name and, using the information schema views etc, identify all related tables by means of foreign key relationships. Using this information, populate the table created in point 2 above with an arbitrary minimum value and, using the IDENT_CURRENT(‘’) function, populate the max value with the current identity value for the table. Note that you must ensure that you retrieve the values for the key table before any of the related tables. This is what provides protection against the race condition discussed earlier.
If you did not create foreign keys between all tables, you will need statically capture all tables used by a given dimension and maintain this over time as tables are added or dropped.

5. Create another stored procedure that iterates through each entry in the table created in point 1 above and calls the stored procedure created in point 4 above.

6. Create a user defined table function that accepts a dimension name, schema name and table name and returns the minimum and maximum values from the table created in point 2 above for a given dimension, schema and table.

7. Update the current views used to present data to Analysis Services to join to the function created in point 6 above, limiting the output to be where the surrogate key value is between the minimum and maximum values.

And that’s it really, or the basic nuances of it anyway. Clearly, in point 4 above, there is some work required to only set an arbitrary minimum value the first time the table is populated and thereafter to set the minimum value to the previous maximum value + 1. Where the minimum value is greater than the maximum value you get an indication that now rows have been added to the table since it was processed.

The final step in the solution of course, is to ensure that the stored procedure created in step 5 is always called before Analysis Services processing commences. In order to perform a full process of the database from SSMS say, you just need to reset the table created in point 2. (eg. Truncate the table and reseed with arbitrary minimum value and current identity value.)

And there you go, easy-peasy eh?

Implementing Analysis Services Process Add Functionality – Part 1

2011-04-26T23:16:00.001+01:00

I think most people who have used Analysis Services know about the ability to incrementally process dimensions using “ProcessAdd” and let’s face it, without the ability to partition dimensions it’s about the only data-reducing optimisation we can perform on dimensions. Sure, we have “by table” processing versus “by attribute” processing, the ability to process attributes in parallel and we can make sure that the relational layer can produce results quickly by either using snow-flaked tables or star-schema tables with indexes etc. However, we are still bound by the amount of time taken to process a given set of rows, where a subset of this information may already have been previously processed.

To this end we can use incremental dimension processing, i.e. the “ProcessAdd” processing type, which aims to reduce the amount of time taken to process a dimension by only processing new information into the dimension. Clearly changes in hierarchy relationships will still require “ProcessUpdate” or “ProcessFull” as the entire dimension data set will need to be reprocessed in order to identify and reflect any hierarchy changes.

However there are some fundamental limitations with regards how fast incremental dimension processing can go…

1. Analysis Services performs transactional processing using files on the file system. As such, when starting a transaction its first task is to take a copy of all relevant files. It then starts to perform processing and if processing is successful the newly processed file is used. Based on this we can see that the amount of time taken to process must include at least the amount of time taken to clone all relevant files for the dimension. This can be a non-trivial amount of time on large dimensions.
I guess an enhancement request could be to have Analysis Services clone existing dimension files using a background thread some time after processing. A per dimension configuration would be ideal so that we don’t automatically double disk space and waste resources on small dimensions.

2. In order to use incremental processing on dimensions there appears to be two general forms of implementation. There’s the method that presents only the new rows to Analysis Services during processing (the intended but clearly more arduous method) and the one that presents all rows but changes the error configuration to ignore duplicate errors. I consider the latter option a bit of a hack to be honest and not in the true spirit of incremental processing.

3. Even after providing only new rows, Analysis Services needs to ensure that attributes which have ordering defined based on some other attribute are updated to reflect this. Clearly, if ordering has been defined on an attribute with many members, the act of Analysis Services revisiting this set could take a considerable amount of time.

From what I’ve seen, the implementation of Analysis Services “ProcessAdd” on dimensions seems somewhat diverse and I’m not aware of anyone documenting a definitive guide to implementing this. Now I certainly don’t intend these posts to be considered a definitive guide but more of a starting point for discussion. To this end, I’ll try to cover off how I see “ProcessAdd” working with regards the relationship between Analysis Services and SQL Server. Don’t get me wrong, others have most definitely blogged about “ProcessAdd” and you can see Greg Galloway’s blog for more information on ProcessAdd performance metrics here: http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?List=83c71d76-a4d8-4197-9257-38d6b857634f&ID=7. There are others out there like this too. However, these posts tend to focus more on the Analysis Services side, detailing the performance characteristics or the options available such as Out-of-Line DSV’s etc. but not much with regards how to actually go about integrating the relational layer and Analysis Services with incremental processing of dimensions in mind.

So what are the challenges with integrating Analysis Services with SQL Server?

1. Well, first we need to be able to identify new rows for every attribute. Note that it is insufficient to only identify new “key” rows as each attribute in a dimension is processed separately. So it is well possible, and indeed likely, that any new “key” row will be related to attribute members that have already been processed.

2. Once we have the ability to identify new rows for every attribute, how do we only present these new rows to Analysis Services when processing the dimension?

Delving into the second point a bit more, we should note that this tends to be a bit of a double-edged sword. We could use an out-of-line DSV to point Analysis Services to some object(s) to present only new rows but the use of an out-of-line DSV requires a connection to be specified too. Now as soon as you specify a connection in an XMLA script you lose the option to allow Analysis Services to process under the service account. So you either have to specify a username/password combination in the XMLA connection configuration, not nice, or use Kerberos and ensure that the account submitting the process request has sufficient privileges both in Analysis Services and in SQL Server. We could also opt to just configure the base objects upon which the dimension is built to only present new rows, allowing the use of the service account during processing. However, if you do this then how do you go about performing a “ProcessUpdate” or “ProcessFull” on an adhoc basis in order to fix a rigid relationship issue or to refresh the hierarchies?

In my next post (here) I’ll try to cover off one solution to this problem. Clearly this is not going to be straightforward or risk-free but hopefully it’s a starting point.

Configure Reporting Services Database on a SQL Server with non-default port

2011-04-17T22:34:00.002+01:00

I know this is “so last year” when talking about SQL Server 2008 Reporting Services R1 but I figured I’d talk about it anyway, as it may apply to R2 as well. I haven’t checked so please feel free to leave a comment if you can confirm one way or the other.

I am also aware that it has been a long time since we discovered this problem and that there is now another more elegant solution to the problem as well. However, I figured I would provide the information on how we solved this same issue for reference.

So, given a SQL Server instance where the SQL Browser service is disabled (or cannot be reached) and/or SQL Server is using a non-default port number, when attempting to generate the “rights” script whilst configuring Reporting Services you get the following error:

System.Net.Sockets.SocketException: No such host is known

at System.Net.Dns.GetAddrInfo(String name)

at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)

at System.Net.Dns.GetHostEntry(String hostNameOrAddress)

at ReportServicesConfigUI.RSDatabase.IsLocalDbServer(String dbServer)

at ReportServicesConfigUI.RSDatabase.GrantRSConnectionRights()

at ReportServicesConfigUI.RSDatabase.CreateNewDatabase()

In our case, our UAT environment has the SQL Browser service disabled and we do not use the default port for SQL Server, both for security reasons I suspect. As such we need to specify our SQL Server as “servername.domain,port” which in the general case is fine. Even when creating the actual databases whilst configuring Reporting Services this is fine. However, it would appear that when generating the rights script, this form of connection string is not parsed into the individual server name and port number components. Really bizarre when one part of the product does and the other does not, especially when it is in the same part of the configuration!

For further confirmation that the connection string is not parsed, see:

http://blogs.msdn.com/b/psssql/archive/2009/03/05/socketexception-when-creating-a-new-report-server-database.aspx

So after much Googling at the time, we discovered the link above, as well as the following one, which at the time did not include a solution.

http://blogs.msdn.com/b/sameersurve/archive/2009/06/15/no-such-host-is-known-error-when-configuring-ssrs-2008.aspx

However, from both of those links we did get enough information in order to determine what we needed to do. Still, I’m not sure why specifying the instance name as well as the port number works (as detailed in the second of those links) over just the port number as that’s a bit weird, but if it works, hey why not use it. Note: I haven’t tested that the instance name and port number does actually work.

In our case though, we went with the *nix philosophy that you can always do more with the command line tools than you can with the GUI. So we did that. Essentially we used the GUI to configure Reporting Services as far as it would let us, including setting up the databases and allowing it to fail when it tried to configure the “rights scripts”. We then switched to using the command line tools by connecting as the relevant user and then ran the following:

rsconfig -c -m <SERVER_NAME> -s <DATABASE_NAME,PORT> -d reportserver -a Windows

Where <RS_SERVER_NAME> is the server name for the Reporting Services server and <DATABASE_NAME,PORT> is the database name and port number for the SQL Server where we were trying to install the RS databases.

After running the command, we restarted Reporting Services, went back into the configuration tool to apply the changes there and continued.

Clearly this is a bit of a hack, at best, and most likely not supported but in our case it worked and we were up and running after a couple of hours of head scratching and bemoaning the insanity that is Reporting Services.

SQL Server - PARSENAME Function

2011-04-13T21:11:00.000+01:00

Similar to the QUOTENAME function, another one of those often unknown or overlooked functions is the PARSENAME function. I’ll confess that I’m not entirely sure why it isn’t PARSE_NAME, inline with other functions such as OBJECT_NAME etc. but then I guess the string functions all seem to be concatenated. Anyhoo, let’s ignore all of that for now.

The beauty of this function is that it allows you to get the individual components of a given 4-part object name presented as a string. So, given '[ServerNameFoo].[DatabaseBar].[SchemaBaz].[ObjectCaz]' we can get the server name component or the database component if we want, maybe to ensure that what we are evaluating exists in the current database for example. We can also get the schema or object names too. I agree that its usefulness is probably limited to esoteric edge cases but it is one of those functions that when you need it, it’s really handy. Especially when you consider trying to implement this functionality yourself and catering for object names with or without square brackets or full stops etc. in their name.

To use the function you would write something like:

SELECT PARSENAME('server.database.schema.object', 2);

This would return the value “schema”. Note that the function doesn’t evaluate whether or not the object exists and doesn’t care. All it does is return the requested component of the string if it is available else NULL. For an example of it in use, have a look at the internals of the sp_spaceused stored procedure. (i.e. Execute sp_helptext 'sp_spaceused'.)

Also note that there are some articles on the web about using this function in order to split strings for other reasons (eg. IP Addresses) which could be quite a neat solution. (See: http://www.sqlteam.com/article/using-the-parsename-function-to-split-delimited-data) However, remember that the function is SQL Server delimiter aware so you need to be careful. Running the function on the string 'foo.[bar]' to return the object name component will return "bar" and not "[bar]".

I’ll be honest and say that I’ve never really had a reason to use it but at least I know it’s there if I do. And now you do too. :-)

Analysis Services Slicing

2011-02-22T08:31:00.003Z

I must admit that I never cease to be amazed how you can work with a product for years and still every now and again a little nugget appears that makes you go "Really? Does it really work like that?"

I had one of those moments recently whilst answering posts on the Analysis Services forum regarding why one query hit only one partition whilst another query hit all the partitions in a measure group. You can read about all of this here: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/b4b4f28a-2ddd-4335-91eb-1732b371eb74 where Greg Galloway provides some excellent insight into all of this.

So, for the most part people know about partitioning in Analysis Services and if not, someone at some point on the forums will mention that "you really should be partitioning your data" etc. This eventually evolves into "oh yes and if you are partitioning you should be setting the slice as well" then queue many links to various articles on slicing etc. and the world is good. Or is it...

I will admit that I have been one of those citizens posting about the virtues of partitioning and slicing whilst apparently being blissfully (and dangerously) ignorant of the real mechanics of slicing. As far as I can tell, all Microsoft documentation appears to indicate that "ProcessIndexes" is required for aggregations but nowhere does it state that it is required for slicing too. For those of you that are now going "huh, you what?", yessssssss, welcome to my world. Note too, how I am deliberately ignoring the debate regarding whether or not one should manually set the slice instead of relying on auto-slicing, as funnily enough manual slicing is affected too, albeit in a different way.

Essentially it boils down to this. If you are relying on auto-slicing (either by design or merely for those attributes where it may just happen to only contain a small subset of members) then you need to guarantee that you call "ProcessIndexes" (which is naturally "called" automatically when using "ProcessFull") else auto-slicing will not take place. Yup, that's the fundamental point here. Auto-slicing, or at least the capturing of the min and max data IDs for an attribute, does not happen whilst processing the data into the partition but rather during the processing of indexes. Similarly, if you set the slice on a partition, unless you call "ProcessIndexes" on the partition, the data it contains will not be validated against the slice and thus you can actually process incorrect data into a partition.

I guess this makes sense from a programming perspective, in that tracking every value whilst processing data into the partition would make the act of processing more expensive. However, when processing indexes this is essentially what this part of processing is doing anyway I would suspect and thus it makes sense to happen here. Note that due to this, slicing is also governed by the IndexBuildThreshold property too. The problem is, other than this link http://cwebbbi.wordpress.com/2007/11/08/viewing-partition-slice-information I have not seen anything else that indicates that you need ProcessIndexes in order to make slicing work completely.

Note also, that it is often stated in Microsoft performance tuning articles to use ProcessData followed by ProcessIndexes instead of the generic ProcessFull. However, once again it is usually stated that ProcessIndexes is required in order to rebuild aggregations and nowhere does it mention slicing.

Analysis Services Stored Procedure Best Practices

2010-10-23T10:00:00.001+01:00

Whilst answering questions on the Microsoft Analysis Services forum the other day, I came across a question regarding recursive MDX. At first read I thought that an Analysis Services stored procedure might be quicker than recursive MDX, which was an incorrect assumption.

However, in the discussion Darren Gosbell posted a list of best practices with regards Analysis Services stored procedures that I hadn't seen before, so I figured I would post them here too.

So, the "best practices" for Analysis Services stored procedures (ASSP) are apparently as follows:

for administrative functions
for static sets, scopes, LHS of assignment and other early-binding things
It is usually OK to use sprocs for query axes
It is usually OK to use sprocs if they don’t need to get cell data (but only look at metadata and members)
inside calculations if there is good NEB that can be defined on the calculation
inside calculations without NEB if sproc is executed over small amount of cells, i.e. inside WHERE clause etc.

The thread where you can see all of this is here: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/9feb1ec0-d5f4-4b8c-9d7c-0195d25d7adc

SQL Server Dynamic SQL

2010-03-04T22:38:00.001Z

The use of dynamic SQL is unfortunately one of those necessary evils that plagues developers and DBAs alike.

Now I know that the use of the word "necessary" will irk some people but after developing BI systems for about 9 years I can assure you that it is. Sure, there are alternatives in a lot of cases, which I have learnt over time, but even so there are occasions where even those don't help. Things like synonyms can help as can linked views but there are still times when dynamic SQL is required.

Either way, I'm not aiming this post to discuss the merits or demerits of dynamic SQL but rather at giving some airtime to a friendly yet oft unused SQL Server function. First off, hands up me 'cos I very rarely use this function too though I do have a mitigating factor.

Essentially we all know (or at least we should) that it is good practice to use qualified object names and that one should delimit object names to prevent runtime errors with bad practice object names. Some column and table names I've seen in my time include names with spaces, dollar signs, quotation marks etc. all of which I regard as evil. Let's not also forget the use of reserved words as object names which cannot always be easily avoided.

So here's my mitigating factor. I have been lucky enough to be the technical lead on most projects where I am a bit of a hardcase on naming conventions so I have control over what objects are called and created. For those that aren't so lucky...

Enter from stage left: The QUOTENAME function.

Look it up in BOL, it's great. That little function will place the correct delimiters around a given string to present a well formed object name. This means you don't have to specify square brackets, or whatever delimiter you are using in your dynamic SQL. Obviously though, you will have that function scattered throughout your code unless you declare object names up front using the function, placing the results into parameters and then using those parameters in the string concatenation.

Whether or not you use the function is by the by I guess, the main thing is to always delimit your object names. Still, it's good to know that the functionality exists if you ever need it.

- Posted using BlogPress from my iPhone

SSIS Script Task Custom Events

2010-02-11T19:38:00.000Z

So, an interesting observation regarding SSIS 2008, or should I say anoyance?

At the moment I am part of a team building a data warehouse for an investment bank. Part of this system uses SSIS 2008 (or DTS100 in object model speak) in order to load batches at regular intervals into the warehouse. To ease the monitoring and issue resolution aspects of systems of this nature, we are aiming to use NetAgent to poll the database for new events of interest. Events such as load starts, failures, completion and dimension and partition processing activities all need to trigger an email or something to notify support of the system state.

Well, now you might think, like me, that rather than just polling for "PackageStart" events or any of the other "standard" events we could create our own events that could be used to trigger alerts. Yup, now normally I'd say good idea, but alas this time I can't, I don't think...

See, now in SSIS you could add a script task and using either C# or, shudder, VB .net, use Dts.Events.FireCustomEvent, or a derivation thereof, depending on the language chosen, to fire an event. Using this approach you could quite nicely craft your own custom events, giving you the ability to uniqely name this event. (I would presume that doing this would provide a unique name in the [source] column in the [sysssislog] table such that the event would now be uniquely identifiable, though documentation around all of this appears sparse, at best). One snag though...

Now before I get to the proposed gotcha I guess I should 'fess up... I've only been using SSIS in any guise for about 9 months, so by no means am I a seasoned veteran of the product. Thus quite possibly there is a solution to this gotcha. Just maybe not an intuative one? (Hmmmm speaking of intuative, just don't get me started on case sensitive lookups. I know that they may well be quicker but really? WTF?)

Anyhoo, so now that you have your über cool custom event, here's the issue. How do you get the SSIS package to log it? If you edit the logging properties of the package, even specifically selecting the script task, you are only presented with the predefined events for logging. You may be able to "load" the custom events or perhaps edit the package XML but really?

So long story short, if someone knows of a way to get an SSIS package to log script task defined custom events, please don't be shy and let me know...

- Posted using BlogPress from my iPhone

Location:Somewhere on South West Trains

SQL Server 2008 SP1 - Cannot drop database because it is currently in use.

2009-12-17T06:49:00.002Z

Recently I came across an issue in SQL Server 2008 SP1 where I was trying to rebuild our DEV databases from script (we have Nant scripts that perform a continuous build process so we know that they work) but during the rebuild the process failed because one of the databases could not be dropped. At first I thought that there must be existing connections to the database but a quick review of the output of "sp_who2" showed no such thing.

Anyway, to shortcut the whole build process I figured I would just use SQL Server Management Studio to manually drop the database and enable the "force close existing connections" option. However, this too failed saying that the database was currently in use, even with the "force close" option set and even more bizarrely once again when I checked the output of "sp_who2", I could not see any connections to the particular database in question. Restarting the SQL Server service didn't help either.

A quick Google search on this error message produced quite a few hits (as you probably know if you found this page that way), a lot of which seemed to indicate that the way to resolve this is to alter the database into single user mode with the immediately rollback option set. Using this produced no joy for me either, which wasn't really surprising considering that there appeared to be no connections in the first place.

In the end, this is how I solved it… You need to get SQL Server to tell you who is blocking the drop command. Not rocket science is it, once you know the answer. ;-) So…

First of all, using SQL Server Management Studio, open a query window on to the server and ensure that it is connected to the [master] database.
Next, also using SQL Server Management Studio, right click on the database in question and select the option to drop the database and enable the "close existing connections" option. Click OK.
While this is trying to drop the database, quickly switch to the open query window and run "sp_who2". Look through the result set for the row with a status of "SUSPENDED" and note the value in the "BlkBy" column.
Also in the query window, execute the kill command to kill the spid with the value noted in the point above.

And there you go, the database will have been dropped as soon as you killed the SPID.

Wierd eh?

Analysis Services 2005 - Dynamic Default Member

2009-06-22T12:05:00.003+01:00

Whilst migrating a database from AS2000 to AS2005 I came across in interesting issue when trying to replicate the same behaviour as the "legacy" version. Firstly, a little background...

For the sake of expediency I used the migration wizard to convert most of the AS2000 database over to AS2005. I say most because although the validation checker stated that everything was a-okay, when it came to actually do the migration the wizard whinged about a dimension that was joined to different cubes at different levels within the dimension. In order to progress this, I deleted the dimension and migrated the rest.

So, what I was left with was a pretty complete system that, although not overly complex, it wasn't trivial either. The database contained a number of cubes and virtual cubes etc. and they contained a financial line dimension making use of unary operators and custom MDX for specific members. When designing the AS2000 database originally, I specifically avoided calculated cells so there were none of those but it did contain quite a number of calculated members. I must say that I was actually quite impressed with the conversion process once it worked. Certainly the dimensions were migrated quite well, though it made a bit of a mess of migrating the "alternate hierarchy" dimensions. I'm also not a big fan of using linked measure groups etc. so I'll probably back those out into a series of cubes using multiple measure groups etc. but I digress.

Whilst going through all the dimensions, to compare the migrated version to the original version, I noticed that the [Currency] dimension had not been migrated to be the same as the original. The dynamic default member definition was missing and needed to be added back. Now, based on AS2000 functionality, the [Currency] dimension had the default member defined using the "Filter" function to filter the members of the dimension based on a "Default Flag" member property such that when the member property was "1" it identified the default member.

In AS2005 the preferred method is to use the "Exists" function and it performs a lot better than filtering. Indeed, there is also a known issue in AS2005/8 where use of the "Properties" function when specifying a default member causes heap corruption during processing so it should be avoided. Now the interesting bit, The currency dimension does not have an "all" level so I set the "IsAggregatable" property to false, changed the "Default Flag" attribute's "AttributeHierarchyEnabled" property to true, to change it from a member property to a true attribute and set about deploying and processing the revised dimension. All of that resulted in the following error:
Errors in the metadata manager. The 'Currency' attribute with IsAggregatable=false must not have any relationship to 'Default Flag' attribute that has AttributeHierarchyEnabled=true

So, rather interestingly, you cannot have attribute 'x' related to attribute 'y' if attribute 'y' has its "IsAggregatable" property set to false. Converting attribute 'x' to a member property serves to allow the dimension to deploy but prevents the use of the attribute in the "Exists" function. One would have to revert back to the use of the "Filter" function which we really need to avoid. Now I'm not sure why this limitation exists, it just does. So how to work around it?

Well what I did, was to create a copy of the key attribute and have it related to the key. I changed the attribute relationship to "Rigid", as this should never change, and the cardinality to "one to one" even though this latter property isn't used in AS2005. Then, on the key attribute, I set the "IsAggregatable" property to true, the "AttributeHierarchyVisible" property to false and left the "Default Flag" attribute relationship on the key. The key also had to be renamed so I added an underscore to the end of it.

On the copied attribute (i.e. the copy of the key) I renamed it to the name of the key attribute but without the underscore, set the "IsAggregatable" property to false and defined the default member with MDX using the "Exists" function and the "Default Flag" attribute as follows:
Exists([Currency].[Currency].[Currency].Members, [Currency].[Default Flag].&[1]).Item(0)

This appears to do the trick at the expense of duplicating the attribute, providing a dynamically set default member on an attribute that is not aggregatable.

I hope this helps others out there who may face the same issue and hey, if you know why you can't do this straight on the key, please enlighten me!

Installing Subversion from source on CentOS 5.2

2009-05-11T14:36:00.006+01:00

Okay so this not strictly BI related but hey, most of us BI types have a slight geek inclination don't we, or is that just me?

I guess the main reason for blogging about this is that I really didn't expect this to be that hard. I mean, I've been installing Subversion from source since about Fedora Core 3 without issues (other than when the Subversion/Berkley DB integration underwent some changes that shafted access to my repositories) so I kinda expected the same here. Um, wrong! At least this time the issue related to a fresh install rather than suddenly finding that I can no longer access my repositories. Needless to say I now use FSFS rather than Berkley DB as the back-end but still. A friend of mine also works on FSFS (see: http://www.farside.org.uk/200704/tree_structured_fsfs) so he'd been going on at me for a while to switch. Still took that heartache to make me use FSFS though, groan...

Anyway, so a while back (meant to blog about this a long time ago but based on how long it has been since my last blog, I can see that it really was a loooong time ago) I set about compiling Subversion from source code. I guess there are many reasons for installing Subversion from source code but that's immaterial here. Needless to say that I started and soon came to realise after running ./configure that there were some missing dependencies when trying to enable SSL. After a little bit of searching and running rpm -qa etc. to find if certain libraries were installed I found that my install of CentOS was missing the openssl-devel library. So, a quick install using yum and I was away, or was I?

Okay, so maybe not. After installing the missing library another error occurred during the ./configure script run. This particular issue, however, is documented in the Subversion documentation and requires you to amend the parameters passed to the configure command in order to have the information passed through to the "sub-configure" scripts. Rather bizarrely you need to specify the --with-libs parameter, specifying "/usr/kerberos" as the value. (i.e. --with-libs=/usr/kerberos)

I guess the part that really got me for a while was the fact that when you run the configure script, passing in the above mentioned switch, a message is generated on the first line stating: "configure: WARNING: unrecognized options: --with-libs" however this message can be safely ignored. That little tidbit of information is not specified in the documentaion. So, if you leave the script to run it should actually run to completion, barring any other issues naturally.

Long and short of it is that in order to compile Subversion with SSL enabled on CentOS 5.2 (well with the my install base anyway) I had to do the following:

# Unpack the source
tar -zxvf subversion-<version>.tar.gz
cd subversion-<version>
# Check that openssl-devel has been installed.
yum list openssl-devel
# If not, install using the following:
yum install openssl-devel
# Configure and install subversion with FSFS,
# ignoring message "configure: WARNING:
# unrecognized options: --with-libs".
./configure --without-berkeley-db --with-ssl --with-libs=/usr/kerberos

And that's all there is to it!

Analytic Workspace Manager Locking

2009-03-10T22:38:00.003Z

So, following on from my previous blog about plugins for the AWM, I thought I would provide a little information about something I learned whilst writing my own plugin.

Writing a plugin for the AWM and testing it can be cumbersome if you have to constantly start the AWM, login and then navigate to a node in the tree that allows you to activate your plugin. No, arguably a better way is to create your own testbed to simulate the AWM environment. That way you can run your code with less effort and interaction and are also afforded the ability to step through and debug your code if, like me, you need to.

In my case I merely created a Java class that effectively created an Oracle database connection and then called my plugin code, passing in the connection and anything else that it required. This was all fine and dandy whilst developing the code and afforded me the luxury of being able to test my code with the minimum of effort. However, when I eventually placed my code into the plugin framework (which to be fair I was doing simultaneously anyway) my code, well, didn't really stop working but rather, well... just seemed to stall.

This was masked by the fact that I was using multiple threads and thus parts of the plugin, though still functioning, arguably produced incorrect results. After a little investigation (okay, okay, so more than a little and certainly more than I would have liked) I discovered the reason...

There appears to be no fine-grained locking in Oracle OLAP, certainly as far as the AWM is concerned.

So what do I mean by that? Well, just that it is geared towards a single developer updating a given analytic workspace. Multiple updates to different parts of the workspace simultaneously are not supported nor, as far as I can tell, even possible.

So what does that mean to us? Well, when the AWM connects to the analytic workspace it attaches it in "read/write" mode. This means that any plugin code can only affect the contents of that analytic workspace by using the connection provided to it when the plugin is invoked. Retrieving/creating a new connection using the data source made available in the Map object presented to the plugin means that any code executed over that connection is in fact in a different session. As such, any code intended to affect the same analytic workspace that was attached by the AWM session created when the analytic workspace was attached will be blocked and will not be able to make changes or even process any objects in that analytic workspace.

At first I thought that that was it, game over but there is a "trick". This "trick", albeit a bit of hack and one not to be taken lightly, involves the use of the DBMS_AW package . More specifically, the use of the DETACH, UPDATE and ATTACH sub-programs allowing us to detach the analytic workspace over the main connection provided by the AWM to the plugin code. We can then "re-attach" it on the new connection in read-only mode (or read/write if you want) and perform whatever actions you would like on the given analytic workspace. The UPDATE sub-program is required in order to commit any changes that have already been made to the analytic workspace whilst it was in read/write mode prior to detaching it.

Note that there is a potential issue here. Detaching the analytic workspace and re-attaching it introduces the risk that in the meantime another session locks the workspace before the plugin secures it for its own use. Also, failure to ensure that the analytic workspace is attached in read/write mode before the plugin exits breaks the environment in which the AWM expects to function. (i.e. The AWM still "thinks" that the workspace is attached in read/write mode and will allow a user to try to make changes even though this may not be the case.)

Clearly the functionality that this provides needs to be weighed in conjunction with the expected working environment and the associated risks. If the environment is inherently single-user then no harm is done. However, if there is a multi-user environment where multiple people are likely to request read/write locks on the same analytic workspace, well, then your mileage may vary. Clearly you may be able to use transactions to mitigate some, if not all, of these risks but that remains to be seen.

As mentioned previously, oh how nice it would have been to be able to lock a specific dimension or cube etc. which, based on the metadata stored in the database, appears quite possible but just not implemented. This could have opened up opportunities for multi-user development environments to allow teamed development, maintenance and testing etc. What a novel concept, though sorely lacking here...

Analytic Workspace Manager Plugin Best Practices

2009-02-16T08:23:00.006Z

The Analytic Workspace Manager (or AWM for short) is the tool to use when wanting to quickly create OLAP structures in the Oracle OLAP engine embedded within the Oracle relational database. Clearly you can use OWB etc. but AWM provides a lightweight easy to use interface for creating quick demos, restructuring content or processing etc. However, what the AWM provides may well not be everything to everyone and thus it is possible to create your own plugins, using Java, to get it to do what you want.

The documentation around doing this, I found, is a little light on the ground so to speak. So I have tried to document my findings here to help those that may wish to follow suit. However, before I start I should probably point out that in no way is the following post endorsed by Oracle. These are merely my observations after coding my own plugin for the Analytic Workspace Manager.

So here goes...
Step one is to find the Developing_AWM_Plugins_11g.pdf document. When I started coding my plugin for the 11g version of AWM, I could only find the 10g document which is not quite accurate for 11g. The OLAP API has changed somewhat in 11g and this is reflected in the OLAP API interface between the AWM and user plugin code. Looking at the document, I guess the pertinent points are that the 11g version of AWM requires the plugin to be compiled with Java 5 SE. (Or version 1.5 in "old school", just why did Sun confuse matters?) We'll come back to this but suffice to say that you'll need to remember that Java 5 brings with it support for generics.

Also in the document, if you have coded for 10g plugins, you may notice that the interface method signatures have changed a little. The AW object passed into the handle method is now an oracle.AWXML.AW object. The params parameter to the handle method provides a map, where the DATAPROVIDER key provides the ability to get a connection to the underlying Oracle database without requiring username/password information.

Step two, and arguably the most important step, is the understanding that on startup the Analytic Workspace Manager loads all Java code in the plugin directory and looks for any class files that implement the AWMPlugin interface. This is also documented in the above mentioned document, however, what is not made clear is that there will naturally be a performance impact on the startup time of AWM, the more classes and JAR files that you add to the plugin directory. Also, if you use 3rd-party JAR files you are at the mercy of those maintainers.

For example, placing the "jfreechart" JAR files in the plugin directory causes exceptions to be thrown in the AWM command window regarding missing Eclipse files. (I'm guessing that "jfreechart" is coded using Eclipse and some code dependencies are left behind.) Allowing this to happen with a user-defined plugin is pretty inelegant at best but most likely just down-right rude. We should avoid that, and we can.

The best practice I have come up with is as follows:

The interface between the AWM and your plugin should reside in its own JAR file and only contain the minimum number of class files etc. in order to start the actual plugin. This keeps the startup time for the AWM to the minimum.
The actual plugin code should be packaged into its own JAR file and placed elsewhere in the file system. (I use a subdirectory of the plugin directory so that it can be found using relative paths from the plugin interface.)
Associated plugin dependencies should also be placed into a subdirectory (I use the same one as my own code as mentioned above) of the plugin directory.

Now, Oracle kindly provide a directory from which plugins can be found and executed but this clearly isn't scalable from a startup point of view if you require the use of large dependencies or lots of plugins. Oracle unfortunately don't provide the equivalent of an "Ext" directory into which dependencies could be placed. So here's the dilemma... Do we customise the Windows batch file (or *nix shell script) to include our dependencies in the classpath, or do we place our dependencies in one of the directories that is already referenced by the Oracle prescribed classpath?

My answer... neither! Customising the startup script is prone to failure if Oracle ever decide to update it. It also means deploying the plugin is a pain, in that everyone's installation may be different so deploying the script needs to take that into consideration. Hijacking the Oracle directories is also pretty "rude" and prone to cause issues down the line, not to mention taking into consideration once again the uniqueness of each installation. No, best practice would be to customise a URLClassLoader in order to dynamically load the dependencies at runtime and start the plugin. This unfortunately requires the use of reflection and is a bit more long winded but it will be far more robust and stand you in good stead during further developments.

My plugin used both "log4j" and "jfreechart", both of which had to be loaded onto the classpath with "log4j" needing to be instantiated in order to log progress. I may blog more on this in detail in the future, as doing this can be tricky and I couldn't find a definitive guide on this on the web.

Finally, coming back to generics, note that the AWMPlugin interface needs to be backwards compatible and as such does not make use of generics. This tends to generates compiler warnings. The way I would recommend handling this is to annotate the interface methods with "@SuppressWarnings("unchecked")" to suppress those warnings but only on the methods provided by Oracle. If you need to assign one of the variables found in the interface parameter list to to your own variable, you can use the generic wildcard, such as declaring "Map<?>", indicating the supertype.

And that's all there is to it.

Analysis Services 2005/2008 Heap Corruption

2009-02-02T11:55:00.005Z

Recently I have been working for a financial institution who are using Analysis Service 2005 for their ad-hoc analysis. This particular client was experiencing issues with one of the Analysis Services servers producing memory mini-dump files during processing and causing service crashes and stalls whilst other servers were performing acceptably well.

The analysis of this issue started towards the end of 2007 and the reason for this has only now been diagnosed with the able help of Microsoft support. The particular system setup for this client is quite complex, using Oracle 10gR2 as the back-end relational database using flashback for referential integrity and consistency, the 11g (11.1.0.6.0) 64 bit OLE DB driver for connectivity from AS to Oracle and SSIS for scheduled processing. The AS database also makes extensive use of linked measure groups in order to provide a single cube of data with mixed granularity.

In order to diagnose the cause of the heap corruption we had to jump through quite a series of hoops. We initially went down the route of sending the mini-dump files to Microsoft for analysis but these naturally only provided limited and static information. Sending full dump files proved very tricky in the short term as the information that the cubes contain is confidential and cannot be sent off-site. In the end, restarting AS and only processing the dimensions and cubes (i.e. no queries being sent to AS) proved adequate to be able to send the full memory dump files off-site, as it would be extremely hard to put this in-memory information together in any meaningful way.

Still, the full dump files are also static and thus proved inconclusive. It could be seen from the dump files that heap corruption was indeed occuring during processing and there always appeared to be an Oracle error in the dump file as well. It seemed logical that the Oracle 11g driver could be the cause, as most clients tend to use the 10.2.0.3.0 Oracle OLE DB driver when connecting to Oracle 10.2.0.3.0 or later. In order to try and eliminate the 11g driver as the cause, we backed out the 11g driver and installed the 10.2.0.3.0 driver. (Note that in true Oracle fashion, this proved to be an exercise in and of itself. There is no Oracle 10.2.0.3.0 64 bit client installation. Noooooooo, that's waaaaay too easy! You have to install 10.2.0.1.0 and then patch it to 10.2.0.3.0. Oh, but wait, there is a patch before you apply the patch in order to get the patch to work etc. yadayadayada.) Anyhoo, I digress...

In the end, we found that using the Oracle 10.2.0.3.0 driver did not fix the problem. The next step then, was to divide and conquer so to speak...

Part one was to try and create a reduced test case by systematically removing complexity from the AS database. This was achieved by first removing the linked measure groups (I'm not aware of many people using this feature) and then to remove cubes and dimensions until a simple AS database could be created in which the issue could still be reproduced. The idea of this approach was to be able to get a small AS database with a tiny amount of non-confidential data so that Microsoft could perform in-house testing.

Part two was to use Time Travel Trace testing (TTT) which effectively allows one to debug the issue by being able to play forward and rewind the process over a given time span. This would allow one to see the changes made to the heap and replay them to identify the point at which the heap was corrupted. Rather amusingly though, there is a slight catch. This system was using AS2005 on Windows Server 2003 as are most installations I would suspect. However, TTT only works on Windows Server 2003 on a 32 bit architecture or Windows Server 2008 on a 64 bit architecture or if virtualised, only on Hyper-V.
Right, so a few changes to the test case then. As it happened we used a 32 bit environment and as luck would have it we were actually able to reproduce the issue, though TTT failed to work. Initially it exhibited the same symptoms as seen when used on Vista, where it "watched" the incorrect "thread" for activity. This should have been fixed be setting the processor affinity for AS. It wasn't...

As it happens, the TTT testing wasn't required. The reduced test case was effective and we were able to remove all confidential data. Microsoft took this in-house and managed to find the answer. So, are you ready for this? Do you want to know why memory heap corruption was occurring during processing? Well, do you?

Rather bizarrely, the reason for the heap corruption was the use of the ".Properties" function when specifying the default member in a dimension. Yup, the use of a specific function when specifying the default dimension member caused heap corruption during processing, not querying but processing. So how lucky am I that the reduced test case that I created used one of the only two dimensions out of 30 odd using that function when specifying the default member? Now I'm not sure why that function causes the issue and nor are the AS development team. They are still looking into it and from what I understand, were equally surprised. As it happens, the MDX that was being used to specify the default member wasn't particularly efficient in the first place (I must point out that it wasn't my code) and that once the code was changed to use the "Exists" function, it solved the problem.

Clearly your mileage may vary but nonetheless please be aware that the use of the ".Properties" function when specifying the default dimension member can cause heap corruption during processing. This is true for both AS2005 CU11 (or SP3 CU1) and AS2008 CU2. I'm still not sure why this only occurred on one of the servers when the other servers have the exact same database build but hey, removing the ".Properties" function solved the issue so clearly that was the problem.

You have been warned...

Is Oracle AWM 11.1.0.7.0A Better?

2008-10-21T18:55:00.004+01:00

So, I'm about to present at a workshop tomorrow on Oracle 11g's Interval Partitioning and on the OLAP option and in preparation for the event I have had to create a demo. A while back I patched our 11.1.0.6.0 relational database to patch 8 but although both patch 10 and 11.1.0.7.0 have since been released, I decided not to risk upsetting things too much by installing those and have thus gone with the status quo. (Note that we have lots of Oracle software on that machine for demos and breaking things at any time is just not on!)

Incidentally, I just have to ask, does anyone actually know what each number in Oracle's version numbers stand for? I searched a while back but to no avail. But I digress...

Searching around on 11g versions and patches I came across an article that said that in order to patch the OLAP option you need to install either Patch 'A' or Patch 'B' but in order to patch the relational side of the engine you use the Patches 6, 8, 10 etc. Huh? When I installed patch 8 it had to back out patch 'A' (which is what the person who posted on the forum also found) so does that mean that the OLAP patches are mutually exclusive from the relational patches? That's a question I have yet to answer 'cos maybe you are meant to re-apply Patch 'A' after patch 8 etc. but that's a subject for a later post. (Especially since 11.1.0.7.0 has been released which appears to be for both relational and OLAP with patch #6890831. See the OLAP Certification page.)

Anyhoo, my big grumble with the Oracle Analytic Workspace Manager is that it seems to contain more bugs than an anthill. Seriously, does anyone actually test this thing? Within 2 minutes of using it I can get it to break. Lol, now I appreciate that my ineptitude is clearly a culprit in this issue in that if I did everything correctly it wouldn't break. However, exceptions are just that, exceptions and they need to be catered for.

Documentation seems to indicate that the preferred method of creating analytic workspaces etc. is to use Oracle Warehouse Builder and that the Analytic Workspace Manager is for "end users" who don't have access to the ETL/ELT process. It can also be used where OWB is not the ETL/ELT tool being employed.

Really? Whenever an exception is thrown I seem to get some Java exception ramblings about not being able to create a cursor or transaction or something equally inane. I'm sorry. It's coding like this that gives Java a bad name! Issues such as not being able to switch between a "star-schema" dimension and "snowflake" dimension after it has been built. Sure, you click on the drop down box and select it but sometimes the relevant member is selected but you don't get the correct representation in the mapping pane (i.e. visible/invisible "parent" member) or it just won't select.

Other irritations include:

After defining a cube you can click on the cube name in the schema tree and even though the dimensions are greyed out, you can double click an available dimension and it will appear to be added to the cube's dimension list. It hasn't really, click off the cube and come back and the model is back as expected.
Try creating a dimension over the "Sales Territory" dimension as available in the Microsoft AdventureWorks database and it will build. You can even browse it. However, join that dimension to a cube built off of the Internet Sales fact table and whoa... nasty exception.
Why does the above nasty exception terminate my connection and cause random stuff to happen?
How come building cubes off a database using the WE8MSWIN1252 characterset causes ORA-12704 characterset mismatch errors seemingly at random? Sometimes when I merely process something but always when trying to create cube organized materialized views. Sure I should be using UTF8 but the default for the "warehouse" database created by dbca is to use WE8MSWIN1252. What gives with that?
...

It is probably worth pointing out that the version of AWM that I was using was 11.1.0.7.0A and apparently that is expected to work against an 11.1.0.6.0 database. Maybe I should log these as bugs for Oracle to fix, I get that. My complaint though, is how did some of the issues I experience (maybe not listed here) even get through the door?

Does anyone on the development team actually use the product in a way that is not based on contrived samples? We all know that clinical test cases will work but what about the real world?

After all that, is the new version of AWM better? Yes. The fact that you can now create dimension levels at the time when you create a dimension (which I always thought was odd that you couldn't) is accepted with open arms. Despite all the ramblings above, it does seem a little more stable though that really isn't saying much. The default way of mapping columns to OLAP structures has changed from the graphical mapping view to the table mapping view which I think is for the better.

Bottom line, if you haven't switched yet, switch now. It's better for sure but don't expect an "Analysis Services development environment (i.e. BIDS)" type experience or you'll be sorely disappointed!

Hyperion EPM on Windows Server 2008

2008-10-13T22:25:00.004+01:00

For an upcoming workshop we are running, covering the Oracle BI offering, I had to upgrade the relevant Oracle software to later versions. This included upgrading Oracle 11g to 11.1.0.6 patch 8 (11.1.0.7 not available for Windows at the time), upgrading Hyperion 9.3 to EPM Fusion edition and upgrading OBIEE plus. As an aside, the upgrade to Oracle 11g wasn't exactly seamless due to some Hyperion 9.3 service mainting a reference to the Oracle client dlls despite the fact that I had "uninstalled" Hyperion prior to the 11g patching. However, the use of ProcessExplorer soon highlighted the offending process and I was able to complete the patching in due course.

Installing Oracle/Hyperion Enterprise Performance Management Fusion Edition on Windows Server 2008 however, seemed to pose some interesting challenges. I'm not sure if this is as a result of running the Windows Server 2008 as the OS, the fact that the OS is actually running in Hyper-V, the fact that I uninstalled Hyperion 9.3 first and then installed EPM or something else but the net result was that it took me almost 2 days to solve an issue with OpenLDAP.

Attempts to start the Hyperion OpenLDAP service were unsuccessful and thus none of the other services were able to register with Shared Services. (i.e. The Shared Services service requires the OpenLDAP service to be up and running before it can start.) The failure to start the service provided little in the way of messages that would help debug the issue but consulting the EPM installation documentation (and Google) showed that you can start the OpenLDAP service from the command line using the "-d" switch in order to get some logging to sdtout. A switch of "-d 1" provides copious volumes of logging information and "-d -1" even more so. ;-)

Although this did produce some logging, in the end it was of little benefit as I got mixed results. Those mixed results were a combination of getting the OpenLDAP service to start from the command line (after tweaking some command line arguments to the "slapd" exe by adding a reference to the config file) to the service no longer starting thereafter. When the service would no longer start, I got the dreaded "slapd starting" message in the console whereupon the service would do little else. According to Google searches, the solution appeared to be to "download a later version of OpenLDAP" which clearly wasn't an option as I needed to use the Oracle implementation of OpenLDAP.

At this point I figured that a re-install was probably on the cards so I uninstalled EPM, deleted the directory into which it had been installed and then reinstalled the EPM software. Guess what... The OpenLDAP service started! oooOOOOOooooo. So a quick config of the services and... D'OH! still no joy! None of the services were able to register with Shared Services. Grumble, grumble, grumble. What now?

Even more debugging and looking at the Shared Service log files showed messages such as "Native Directory is not reachable. Attempting to re-connect" with references to the server's hostname and a port number of 28089. This seemed to indicate that services were attempting to connect to OpenLDAP on port 28089 and couldn't get through. Further investigations showed that OpenLDAP does not use that port number by default, so it needs to be set at startup.

Now that's where the fun and games started. Rather than go through all the possible places where they can be set depending on which article you read (which I unfortunately tried), I'll just tell you the right place. It would appear (and I'm guessing here) that the installation of EPM on Windows Server 2008 does not install some much needed registry entries. A colleague of mine installed EPM on Windows XP and it all went well. A quick check of the registry on that machine confirmed that this is what was missing. So...

What you need to do, is to ensure that the following registry entries (and structure) exists in the registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\OpenLDAP\Parameters]

with keys:

ConfigFile PathToOpenLDAPDirectory\slapd.conf
DebugLevel 0
Urls ldap://:28089

Note that the "ConfigFile" and "Urls" parameters are String settings whereas "DebugLevel" is a DWORD32

And that's it. A restart of the OpenLDAP service and Shared Services service meant that I could register all the other Hyperion services. The "Urls" parameter is the main setting, as that dictates that OpenLDAP is to listen on port 28089 which is what is required by the rest of the Hyperion services.

It may also be worth noting that a quick glance at the default slapd.conf file supplied by Oracle lists that a "pid" file and "arg" file be created in the ".../var/run" directory. Clearly a *nix setting but rather amusingly there is no "run" directory under the "var" directory in the Windows structure. If you create it, those files will be created. Also, it may well be worth you adding in an entry for the creation of a logfile. I added an entry in the "slapd.conf" file, immediately below the entries for the "run" and "arg" files to do this. The key looks like:

logfile PathToOpenLDAPDirectory\logs\slapd.log

To get logging to appear in this file you will need to change the value in the registry entry "DebugLevel" created earlier to a value other than zero. The number 4 is probably a good value to go for or 1 if you wish to debug issues.

And there you have it. I hope that this helps others to have more time to do things other than the configuration of EPM.

Good luck.

The ProClarity API - Programmatic changes to ProClarity briefing books

2008-04-19T08:53:00.008+01:00

We have a client who is using Analysis Services 2000 and ProClarity Analytics Server 6.3 in order to provide balance sheet and financial line information to their business. The system that I designed had to be able to aggregate financial data up a customer hierarchy but be able to aggregate the data into parents as they stood at the time the data was loaded and against the current parent. (i.e. A slowly changing dimension.) However, this dimension consists of over 1.5 million members and there are effectively two versions of this in the database.

Now this on its own is fine-ish, in that with the 3GB switch enabled and some tweaks to the cleaner thread interval the AS solution is stable and performs reasonably well. However, the original requirement did not specify security on these dimensions. As you can guess, there is now a requirement to place security on these dimensions, partially to limit access to the data but more so to provide a way for a given user's reports to open, defaulted to the customers for which they are responsible.

Now trying to add security using a "security cube" isn't really conducive to performance and adding 440 roles to AS, each with a different security context, isn't really good for maintenance or memory. (i.e. Think replica dimensions.) So, what else?

Well, as an approach, I wondered if it is possible to take a template ProClarity briefing book located in a PAS Store, load it and customise it for a given user and write it back to the PAS store. From previous experiences I know that just updating the XML in the PAS database for a given briefing book and pages causes problems down the line, to the point that they may no longer work. So how else? Can you make programmatic changes? I know that there is an API for ProClarity but the documentation is all for VB etc. and not .Net.

In the end I decided to give it a try and started to write an application that would retrieve a result set from a database specifying the template book and pages etc. that are to be customised and the customisations to be made. It also specifies the PAS store into which the book should be ultimately published and security assignments for the new book.

After contacting Microsoft on TechNet for some hints and tips (whose responses are pretty quick and impressive actually) I was on my way. See Hints and Tips for that thread.

The essentials to know, when using C#, are effectively as follows:

In order to have a briefing book loaded within a ProClarity application in memory, use the KProClarityClass.StartupBook method.
Once you have a KProClarityClass object, use the following objects, properties or methods as shown below...

Application (Object)
|
CurrentView (Property)
|
PageCtrl (Object)
|
KPCommand (Object)
|
GetAxisMemberSet (Method)
|
KMemberSet

As an example, use the KProClarityClass's Application property to retrieve a reference to the Application object. Now use the Application object's CurrentView property to retrieve a reference to the the ICurrentView interface etc. etc.
If you make any changes to a KMemberSet, you must use that object's associated KCommand object's execute method in order update all associated entities within the ProClarity object model.
If you copy elements etc. be sure to update the copied element's ID otherwise you will get errors when trying to publish the briefing book. (i.e. You will have two elements with the same ID which understandably is not allowed.) To do this, set the ID for the given element to: "{00000000-0000-0000-0000-000000000000}"
In order to commit changes you have made to a given briefing book page, you have to copy the "page" within the book, using the CurrentView's AddPageToBook method and then delete the original page. Please don't shout at me, I know, that sucks but that's just how it is okay?

In the end I changed my application to run as a custom SSIS task and to be multi-threaded using a blocking-queue in order to hand out template briefing books for updates to consumer threads etc. That all seemed to work although I did experience exceptions when running the task in debug mode. As can be seen by the following thread with Microsoft here, this was related to my code not being aware of the COM aspects of the ProClarity API.

All in all it was quite fun to develop, creating mini-caches for PStore connections etc. and to implement a solution that runs within SSIS. I added in the ability to set properties such as the number of threads to use and the ability to set the table and column names that store the relevant updates to be made.

Using simple test cases it appears to work quite nicely. Reports remain static, such that reports with both grids, totals and graphs etc. retain that layout when the member selections are changed. The task allows the ability to either update members by axis or to simply replace the MDX for the report. (Actually you could replace the MDX for a report and then customise the member selection immediately thereafter if you so wished but why?)

Before it's a finished beta v0.0.0.0.0.0.1 "product" (Oracle style versioning there) the task needs to accommodate pages within nested folders (i.e. currently only handles pages at the root or one level below) and to complete the security aspect. I also need to normalise the table structures that store the updates to be made against the template briefing book etc.

Anyhoo, if anyone out there is interested in using this please add a comment to this blog and I'll see if I can add it to our company's website for download. That naturally assumes anyone actually reads my blog...