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.

OWC scans all partitions (SSAS 2005)

Hello,

I have a cube with three partitions. Each partition holds data for the years 2005,2006 and 2007 respectively. In our web application we are creating OWC Pivot Table and putting "date dimension" in the filter area automatically. Altough only "2007-01-01" is included in filter, when we trace the query from profiler we saw that all partitions are read. Is there anyone encountered the same thing?

By the way, storage modes for all partitions are MOLAP.

Nilgun Celikok

Hi Nilgun,

First of all, have you set the Slice property on your partitions? Although in general AS is able to detect what data is in each partition, read the following article for details on when it will and won't work:

http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx

In your case it sounds like it will be quite straightforward to set the slice, and this will also detect whether your partitions hold the data you think they hold (which could be another reason why you're seeing AS scan all partitions).

If you have set the slice, however, you could be running into an issue I've seen before and which I think is fixed in the latest cumulative hotfix build for SP2:

http://support.microsoft.com/kb/936305

Look for the brief description under issue 50000890.

HTH,

Chris

OWC and AS 2005 Cube Drillthrough action

Hi There,

I setup drillthrough action on AdventureWorks sample cube, and am able
to view drillthrough through cube browser. However when I use OWC
(office web component) 11 (office 2003) to connect to the cube, I can
still open up drillthrough in the IE with all the dimension name, but
without any data.

Does OWC 11 support AS 2005 Drillthrough, we use OWC 11 on AS 2000 cube
drillthrough and work fine?

Does anyone get drillthrough working on AS 2005 cube in OWC 11?

Thanks
Dong

Hi Dong,

I haven't use plane OWC for a while and can't try this scenario right now. But if you still have a problem, please run SQL Profiler when issuing a drillthrough command and action (Discover request MDSCHEMA_ACTIONS) from OWC and Browser and see for any noticable differentces between the two. You can also post those commands in this post, may be we can find the problem together.

OWC - Distinct Count Error

hi,

i have a cube where i do a "distinct count" of my customers and it works fine if i select one member off a dimension or all of them. but if i try to choose two or more members it doesn't work anymore.

does anyone have a solution for this problem ?

thanks,

levogiro.

Are you using AS 2005 SP2 - in which case, could you describe the issue in more detail?

http://sqljunkies.com/WebLog/mosha/archive/2006/11/07/visual_totals_dc.aspx

>>

Visual Totals and Distinct Count

One of the most talked about improvements in Analysis Services 2005 was support for Distinct Count measure over arbitrary set of members.

...

Wrong results are always a bad thing, therefore in SP2 the implementation of Visual Totals (both function and the mode) underwent serious change. Internally, this implementation got unified with implementation of visual totals for subselects - so now they all behave similarly.

>>

|||

Deepak Puri,

I'm sorry, I forgot to say that I'm using SQL Server 2000. I did a test in SQL Server 2005 and it works fine.

Levogiro.

|||I believe there was a hotfix for this issue (in AS 2000) recently. Please follow up with CSS...|||I have the same problem. Does anyone know if there actually is there a hotfix for AS2000 for this?

OWC - Distinct Count Error

hi,

i have a cube where i do a "distinct count" of my customers and it works fine if i select one member off a dimension or all of them. but if i try to choose two or more members it doesn't work anymore.

does anyone have a solution for this problem ?

thanks,

levogiro.

Are you using AS 2005 SP2 - in which case, could you describe the issue in more detail?

http://sqljunkies.com/WebLog/mosha/archive/2006/11/07/visual_totals_dc.aspx

>>

Visual Totals and Distinct Count

One of the most talked about improvements in Analysis Services 2005 was support for Distinct Count measure over arbitrary set of members.

...

Wrong results are always a bad thing, therefore in SP2 the implementation of Visual Totals (both function and the mode) underwent serious change. Internally, this implementation got unified with implementation of visual totals for subselects - so now they all behave similarly.

>>

|||

Deepak Puri,

I'm sorry, I forgot to say that I'm using SQL Server 2000. I did a test in SQL Server 2005 and it works fine.

Levogiro.

|||I believe there was a hotfix for this issue (in AS 2000) recently. Please follow up with CSS...|||I have the same problem. Does anyone know if there actually is there a hotfix for AS2000 for this?

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.