Recover data using @XREF

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

During a cube’s maintenance I accidentally cleared a slice of data and realized that after a day.  Meanwhile users have been loading their budgets.  So restoration of database from the backup wasn’t an option.  I have to recover only the partial loss of data and was thinking of various ways I could achieve it.  I could use a DATAEXPORT / report script / partitions / @XREF.  I chose @XREF to be the savior and here is how I did it.

Created a fresh database from the backup.  Then a location alias is setup on the production database.  Made a calc script in the production database to pull the data from the backup database.  Here is the calc script…

FIX ("member from Dim3", @IDESCENDANTS ("Dim4"))
"member from Dim2" = @XREF (Location Alias);
ENDFIX

Dim3 & Dim4 in FIX were sparse dimensions so I did not have the block creation problem.  Else we would have to use CREATENONMISSINGBLK etc to work around it.  Also the @XREF was straight forward, no member selection parameters as the two outlines were same.

Once this is done we have to test & validate the data.  I did that by building a difference cube.  Make a fresh cube and copy over the outline from any of the two cubes above.  Setup two location aliases one for each of the databases above.   Add a new dimension and two children to it as below.

Difference Dimension

Difference Dimension

Now open a excel & pull data from the difference cube and you should see all zeros.  If you see any numbers being retrieved then the data isn’t tallied.  Since there were loads after the maintenance I did see some numbers but you can zoom in and know if those were differences from the budget or something else.

To know more on @XREF & difference cubes you can click on the links below…

Difference Cubes: Making Testing Fast & Easy

Calculating Across Cubes: Using @XREF

***Liked it? Click the facebook like button below and share it with your friends!!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)