Packaging changes to a cube - what are my options?

I have some changes I want to make to an existing cube - that is, I'd like to add some Measures and Dimensions to the Team Foundation Server cube. I would like these changes to be applied in one quick process, where the system administrator can just run an MSI that makes the changes.

I looked at XMLA but there doesn't seem to be much support for making incremental changes to the cube. Is AMO the way to go? How have people dealt with this in the past?

Suggestions appreciated,

Paul

Hey Paul,

AMO is probably the way to go. It still uses XMLA when it talks to SSAS, but it will handle getting the current structure from SSAS and building the appropriate scripts. You're right, there is not much in the way of incremental support. SSAS seems to have taken the approach of saying "send me how you want the database to look and I will figure out what needs to be changed".

If you are adding brand new dimesions you might be able to use a hybrid approach. You could script the new dimensions as XMLA and execute them via AMO using the server.Execute() method. But as far as adding these dimensions and new dimensions to existing cubes - I would definitely use AMO, this should cater for cases where they may have made slight changes to the cubes (eg. service packs?) so you can apply your new stuff without regressing the cubes.

A couple of things to consider:

The person running the msi would need to be in the administration role for SSAS or be in a role that has Full Control of the TFS SSAS database. I'm not sure off the top of my head how you test for this, but if you need help, let me know and I'm sure I can figure it out. If you alter the cubes like this you will need to consider the effect it will have on the aggregations, altering a cubes structure will cause any existing aggregations to be dropped and you will most likely need to re-design these.

overflow of the data type while processing dimension

While trying to process a cube the error below came up while processing one of the dimensions.

The database is an ORACLE database running on a UNIX platform even though that piece of information would not be neccesary in solving this riddle.

I believe it has to do with date format differences on both database platforms. Is there anyone out there who has been able to resolve such a problem or point me out in the right direction.

See below for an excerpt of the log.

Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:05:02; Duration: 0:00:15
SQL queries 1
SELECT
DISTINCT
"T_0"."DEALER_CODE" "DIMM_SUBSCRIBER_DEALER_XREF0_0"
FROM "DLRPROFILE"."DIMM_SUBSCRIBER_DEALER_XREF" "T_0"
Processing Dimension Attribute 'ENTRY DATE' failed. 1 rows have been read.
Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:06:06; Duration: 0:01:19
SQL queries 1
SELECT
DISTINCT
"T_0"."ENTRY_DATE" "DIMM_SUBSCRIBER_DEALER_XREF0_0"
FROM "DLRPROFILE"."DIMM_SUBSCRIBER_DEALER_XREF" "T_0"
Error Messages 1
Errors in the back-end database access module. OLE DB reported an overflow of the data type for column 0. Errors in the OLAP storage engine: An error occurred while the 'ENTRY DATE' attribute of the 'DIMM SUBSCRIBER DEALER XREF' dimension from the 'DEALER_SALES_CONNECTION_ANALYSIS' database was being processed.
Processing Dimension Attribute 'REGION CODE' completed successfully. 10 rows have been read.
Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:06:06; Duration: 0:01:19

Please another question is there some sort of literature addressing SSAS and ORACLE database working in tandem.

I need Help.......

What is the data type of the column for the 'ENTRY DATE' attribute in Oracle compared with the data type that is setup for the Attribute in the dimension? It sounds like the attribute is using a smaller data type.