Extract and Reload a Database To Allow Datatype and MaxItemNumber changes

Document created by Bob Gill on Feb 21, 2018Last modified by Bob Gill on Feb 23, 2018
Version 1Show Document
  • View in full screen mode

About the solution

As part of a project, we needed to change the datatypes of multiple cubes from Currency to Double. Given we are almost done the project, we want to do this with the easiest, and least risky, method. Using the extract and reload actions in a procedure, we were able to do so in just a few minutes. At a high level, we extracted all the trees, entities and cubes out to text files, made our changes, and then reloaded the cubes. Along with changing the datatype from Currency to Double, we also updated the the max item number for each dimension to make sure our saturation was less than 75%. This gives us some room for the solution to grow over time.



Extract/reload actions should not be applied in every application. In complex applications, we need to consider if this is the right approach or not. Fixed selections, rules, and other logic should be reviewed to see if it may have any side effects. As with any action that affects structures or data, be sure to take backups before trying any of these items. This worked for our particular case because we don't have any complex rules or fixed selections. We only have 30 cubes in our solution, so it's more manageable than solutions with hundreds of cubes to consider. 


Extract and Reload Trees and Cubes Process


  1. Extract trees / entities - make sure all trees and entities are saved off as text files

  2. Extract cubes - make sure all cube data is saved off as text files, to be reloaded later 

    At this point, we manually clicked the Clear All Cubes button on the Cubes tab. By clearing all the cubes, we are now able to make changes to the entities in use.

  3. Reload trees - reload the entities from the metadata in the text files. In our case, we decided not to use the Clear before load option. Since we were reloading from the same path as the extract on the same server, we did not need to enter any path information. 

  4. Reload cubes - reload all cube data from the files. Since many cubes were cleared when we changed the datatype from Currency to Double, this step reloads the cube data into these same cubes using the text files as the source.

  5. Exit procedure - as a best practice, we add this as a last step


Disabling/enabling Steps

We could have split the extract steps into one procedure and then the reload steps into another procedure, but we decided to keep everything in one place. Depending on what action we want, we just enable or disable certain steps and run the procedure.


Thank you to Jeremy Carter and Paola Mason for sharing this with me.

1 person found this helpful