We have a test server for our OLAP cubes which until now had a one
Sybase OLEDB data source to the live database server.
All the dimensions & cubes have been created using this data source.
Now I have added another data source pointing at the test database
server, but although I can change the data source of a cube, I can see
no way to change the data source of a dimension, and so to process all
the cubes on the test database as I wish would mean recreating all the
dimensions using the test data source.
I thought to get round this by simply pointing the existing live data
source at the test server but Analysis Services seemed to ignore the
change completely -despite a reboot of the server - and still processed
the cubes on the live server!
Now there was a problem with multiple Sybase data sources where AS
would just use the first one, but I have applied a fix for that - does
anyone have any ideas about the current problems - I notice that in
Books Online it seems to recommend NOT changing the data source of a
cube - why?
Thank you

How are you changing your cube data source? I wasn't aware such an
option existed.

Anyhow...dangerous stuff here...definitely not for the faint of
heart...do not do this without testing the heck out of it on a box you
don't care about.

You can run a query like this against your AS repository...

SELECT
a.[ObjectName],
a.[ObjectDefinition]
FROM [dbo].[OlapObjects] a
INNER JOIN [dbo].[OlapObjects] b ON
a.ParentID = b.ID AND
b.ClassType = 2 AND
b.ObjectName = 'FoodMart_MyVersion'
WHERE a.ClassType = 7

Substitute "FoodMart_MyVersion" with the name of the catalog that
contains the AS objects you want to manipulate.

This query simply lists out the dimensions (class type 7) that are
contained in the catalog and their corresponding XML definition. In
the XML Definition for your dimensions...you will see somethign like '
DataSource="FoodMart" '.

Simply modify that part of the XML to point to the DataSource you
want...and reprocess your dimensions. You can either modify the XML
manually...not necessarily recommended...or you can write code to do
it. Unfornutately since the ObjectDefinition column is a NText
column...you cannot simply do an update statement and use the replace
function...the code get's a little trickier...at least if you are using
T-SQL for your code.

This is a 100% undocumented hack...it will work if executed
properly...but be very careful.

If you edit the partition of a cube, it shows you the data source and
the fact table and there's a "Change" button that allows you to change
them - but no such facility exists for dimensions.
Thank you for your hack - but it was to avoid doing something like that
that I thought the easy way would be simply to point the existing "live"
data source at the test database server, leaving it named the same so
all the cubes & dimensions would use it without a problem.
What I can't understand is why it just ignores me!



*** Sent via Developersdex http://www.developersdex.com ***

Further to my previous post; what I really want to do is have two data
sources, one for the test database server and one for the live, and be
able to process some cubes on live and some on test - seems reasonable
I think, but from what I read it seems it can't be done once the cubes
are created, because they will include dimensions with a specific data
source and so the only way of pointing them at a different server is to
amend the Sybase data source - which will then affect any cubes using
that data source, so it is all or nothing!
Surely this can't be the way Analysis Services is designed to work? I
would really appreciate comments from any Microsoft people...

Sorry. You can't do it that way. AS2K does not support pulling data from
multiple data sources at the same time. You have to consolidate it into one
(using views is the preferred way as was previously mentioned).

This is one of the top new features in SQL Server 2005.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


<rachel.jones (AT) talbotuw (DOT) com> wrote

Thanks for this Dave.
I do understand that I cannot mix data sources within a cube but I do
not understand why if I amend the "live" Sybase data source via Sybase's
configuration tool to point at the test database server, all the cubes
and dimensions that use that data source do not then process on the test
server.
Would you agree that that should work?
Rachel



*** Sent via Developersdex http://www.developersdex.com ***

I have now tested a cube which was created using the test database
server data source for both the cube and the dimensions; I changed the
datasource to point at the live database server and successfully
processed it on the live server.
Therefore, I think the problem may be to do with the fact that some of
the other cubes have been copied & pasted from the live AS server and
somewhere the connection has been cached.
Could this be so?
Rachel

Yes. The AS services does cache connections. What happens if you restart the
service?
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


<rachel.jones (AT) talbotuw (DOT) com> wrote

If I change the data source to point at the test server and restart the
service, I find that the only cubes which still look OK are the two
which I have been unable to process (as they keep giving me a "socket
closed" error which I have been unable to resolve) - all the others now
say they cannot find the fact tables and have consequently lost all
their joins in the cube schema - but all these tables DO exist on the
test server.
When I test the connection in the Sybase OLEDB Configuration manager it
succeeds.

Rachel Jones
IT Developer
Talbot Underwriting Ltd

*** Sent via Developersdex http://www.developersdex.com ***

I don't know anything about Sybase. Might it be keeping the sessions open?
When you restart msmdsrv it should reset all connections.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Rachel" <anonymous (AT) devdex (DOT) com> wrote