Recover Saved Cube Views after migrating the CMDB database to a new SQL server

Products

IT Analytics

Issue/Introduction

Resolution

With IT Analytics it's possible to save the views you create for the cubes. The saved view information is stored inside the Symantec_CMDB database. Along with the information on how the cube was formatted the system also saves the connection information for the Symantec_CMDB SQL server at the time the view was created. When the Symantec_CMDB database is migrated to a different SQL server these saved views become invalid due to their references back to the old SQL Server. All this information is saved in the State field for the cube in an URL encoded format. To recover these views you need to perform the following steps.

1. Inside the SMP Console go to Reports\All Reports\IT Analytics\Cubes and right click on any cube that has saved views and choose properties.
2. Copy the guid of the cube.
3. Open SQL Management Studio & open a new query window against the Symantec_CMDB database.
4. Run the following query replacing the text XXXX with the guid of the cube.

SELECT Name, Guid, Description, CAST(State as XML) AS 'State' FROM Item
WHERE Guid = 'XXXX'

5. Now click on the hyperlink displayed for the State field. This will cause a new query window to open displaying the contents of the State field in a readable XML format. When the new query window opens you will see a line called <savedViews> which stretches off the right hand side of the screen. This line contains all the information for the saved views for this cube. It will look similiar to the line below.

<savedViews><SavedViews><SavedView ID="a5b761b3-55e6-4700-a199-42d350696fcd" Name="My_x0025_20Test_x0025_20View" UserID="epm\Administrator" Public="True" Display="Table"><PivotTableXmlData>%3Cxml%20xmlns%3Ax%3D%22urn%3Aschemas-microsoft-com%3Aoffice%3Aexcel%22%3E%0D%0A%20%3Cx%

To be able to easily read the information we will need to decode the information.

6. Using a URL Decoder, such as http://www.url-encode-decode.com/, decode the savedViews information. Once you've decoded the information it now appears like the example below.

<savedViews><SavedViews><SavedView ID="a5b761b3-55e6-4700-a199-42d350696fcd" Name="My_x0025_20Test_x0025_20View" UserID="epm\Administrator" Public="True" Display="Table"><PivotTableXmlData><xml xmlns:x="urn:schemas-microsoft-com:office:excel">
<x:PivotTable> <x:NoDisplayAlerts/>
<x:DisplayScreenTips/>
<x:CubeProvider>msolap.2</x:CubeProvider>
<x:DisplayFieldList/>
<x:FieldListTop>226</x:FieldListTop>
<x:FieldListLeft>812</x:FieldListLeft>
<x:FieldListBottom>595</x:FieldListBottom>
<x:FieldListRight>1012</x:FieldListRight>
<x:CacheDetails/>
<x:ConnectionString>Provider=MSOLAP.3;Cache Authentication=False;Integrated Security=SSPI;Initial Catalog=IT Analytics;Data Source=10.149.184.23;Impersonation Level=Impersonate;Location=10.149.184.23;Mode=ReadWrite;Protection Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=1;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=2;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False;MDX Missing Member Mode=Error;Packet Size=4096</x:ConnectionString>

7. Using the decoded information do a search for the words 'Data Source='.This entry will be found in the ConnectionString property for each saved view. When you find the entry look after the equal sign to see the SQL Server specified to be used for this saved view.

8. Copy the name / ip address specified after the equal sign.
9. Use the following query to update all saved views for all the cubes in the Symantec_CMDB database to now use the updated SQL server information. Remember to properly replace the oldSQLServer & newSQLServer text with the proper information.

DECLARE @oldServer AS NVARCHAR(50)
DECLARE @newServer AS NVARCHAR(50)

SET @newServer = 'newSQLServer'

UPDATE Item
SET [State] = CAST(REPLACE( CAST([State] AS NVARCHAR(MAX)), @oldServer, @newServer) AS NTEXT)
WHERE Guid IN (
SELECT Guid FROM vItem
WHERE ClassGuid = 'FD283A60-19DD-4775-92E7-A0429D948D10')