Copy OLAP database with different data source
-
Hello all -
I have Analysis Services and SQL2K (sp3). I have an OLAP database defined that I have completely copied (fact table, dimensions, everything) and recreated on the same Analysis Server, but with a different data source. (I am building the exact same cube on the exact same definitions, but they're on a different SQL Server with different data.)
My problem is the data source. I can change the data source, but I can't figure out how to change the data source name. Everything is working, but it is misleading having the data source name wrong because it refers to the old data source.
Does anyone have an idea how to do this?
Thanks in advance for your help!
apf
-
Hi, I've had several attempts at doing the same thing as we have customers who use the same cubes and we simply copy the cube from machine to machine.
I have tried everything (at least I think everything) and have been unsuccessful. I thought at one time you could create a second data source and simply delete the first, but this is disallowed by Analysis Services.
We have been partially successful by encouraging our customers to standardise the naming of their servers, this, though, would be of no use in your situation.
Sorry can't be more help, but at least you will know your not the only one out there.
Regards
Duncan
-
You're playing with fire if you do so but you could go into the metadata for the cube in MS Access or SQL Server DB and change the relational data.
I can't recall if you can use DSO (Decision Support Objects) to rename a datasource.
HTH,
Trey Johnson | Chief Business Intelligence Architect | Cizer Software (www.cizer.com)
Who? - Cizer - http://www.cizer.com/about.htm - Blog - http://www.sqlserverbi.com/
What? - Products enhancing Microsoft Business Intelligence - http://www.cizer.com/products.htm
Wow! - Empower your Developers.... NEW Drop In Reporting - http://www.cizer.com/cnr-drop-in-reporting.htm
How? - BI Training - http://www.cizer.com/training.htm - Cizer Solutions - http://www.cizer.com/solutions.htm -
There are two go-forward options, one that could help you now, one that could help you in the future.
If you use the DSO-XML conversion utility, you can extract the DSO objects into XML format (for update) from the source machine, change the datasource element information where required, and then apply the update to the target server. Keep in mind that although it comes from MSft, the conversion util is NOT supported, so basically anything you do with it isn't supported either
I have tried this a couple of times with mixed success, prob due to carbon-based failure more than the utility itself. Plus if it is a bug, you've got the src so you could always change it to suit your own instance anyway.In the future, start your cube design by creating the datasource(DS), letting the name be defaulted (you have no other option), but before you create anything else, copy and paste the DS and give it an appropriate name but pref not one that has 'prod' or 'dev' or 'test' or '<machinenamex>' (you get the idea) in it, maybe 'BobsDataWarehouse' - whatever suites. But by having a name thats related to the subject area not the machine, means that when you change the underlying connection info, the name still represents the datasource and doesn't confuse machine names etc.
Just out of interest, the second option above is from the Ops guide that can be found on msdn.
Cheers,
Steve.
-
Thanks to everyone for your input! Basically, it appears that the answer is:
You can't do it through supported means. That's fine, just so that I know.
Thanks again -
apf
-
Ok, here a way that works and will take about 10 minutes of your time. Not the best of solutions, but the only one that works for me.
Find out where your repositry is located.
It is either in MS Access and is called msmdrep.mdb
or it is on your SQL server in the MSDB database
Both contain a table called "OlapObjects"
sort the OlapObjects table on PARENTID
you will see your MAS database in the "OBJECTNAME" field
Now take the value of the "ID" field from the same record
------------------------------------------------------------------------
If you use a repositry located in MS Access do the following
Select the PARENTID field by the value found in the "ID" field.
Example
SELECT * FROM OLAPOBJECTS WHERE PARENTID = '4E5ED89D-B7D8-49C9-9F6A-3D93993925CE'
Now use Find and Replace on all records in the OBJECTDEFINITION field to replace your current datasource name by your required name.
------------------------------------------------------------------------
If your repositry is located in the MSDB database
1. Insert the result of the "SELECT * FROM OLAPOBJECTS WHERE PARENTID = '4E5ED89D-B7D8-49C9-9F6A-3D93993925CE'" into the OlapObjects table of the msmdrep.mdb database (for convenience I normally use a dts the export wizard in SQL server with the result from query option) The msmdrep.mdb database should be available as it is installed when installing Analysis Services.
2. Open msmdrep.mdb and
Select the PARENTID field by the value found in the "ID" field.
Example
SELECT * FROM OLAPOBJECTS WHERE PARENTID = '4E5ED89D-B7D8-49C9-9F6A-3D93993925CE'
Now use Find and Replace on all records in the OBJECTDEFINITION field to replace your current datasource name by your required name.
3. Delete all applicable records from the OLAPOBJECTS table in the SQL MSDB database
DELETE FROM OLAPOBJECTS WHERE PARENTID = '4E5ED89D-B7D8-49C9-9F6A-3D93993925CE'
3. Import your updated records back into the OLAPOBJECTS table in the SQL MSDB database
Open analysis services and your data source will be changed.
Note: Always make a backup of your "OlapObjects" table and ensure no changes are made to Analysis Services during this procedure
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply