Remodelling Real-Time InfoCube
Introduction
A major department of the Dutch government developed a large Integrated Planning application in the last few years. It was built quickly, and after the go-live it seemed that the Real-Time InfoCube could be improved to enhance performance, by changing characteristics between dimensions.
If a Real-Time InfoCube need to be remodelled, what obstacles can we run into and what is the best solution for these?
Obstacles
By far the easiest is creating an InfoCube with the new model, copy the data and all the queries, IP-functions, documentation, etc. But this can be unacceptable due to time and/or budget restrictions.
Remodelling the original InfoCube is also not easy, because there is no source system where the data could be loaded in from. But, there are multiple ways to change an InfoCube. By far, the best way is the transaction RSMRT, which provides many frequently used modifications to an InfoCube. Besides adding and removing characteristics from the InfoCube, the transaction is capable of relocating characteristics from one dimension to another. But the obstacle here is that the information in those characteristic is lost with this procedure and there is no source system. Hence, the problem.
So, what is the best way to overcome these obstacles and remodel the Real-Time InfoCube?
The concept
By temporarily storing the data in a different InfoCube, the information is preserved. The other InfoCube can be an exact copy of the original InfoCube, but it can also be an InfoCube with the new proposed datamodel of dimensions and characteristics. It is even better to store the information in both the InfoCubes at the same time. After this, the information in the original InfoCube can be deleted and the InfoCube can be remodelled with the transaction RSMRT. When the remodelling is done, the information can be loaded back into the InfoCube from either one of the backup InfoCubes.
However, to make sure that the remodelling is a success, there are a few points in this procedure that require some attention. These details will discussed in the next few sections.
Step 1. Preparation
In preparation, two new InfoCubes have to be made, where the information is copied to. First, create an exact copy of the original Real-Time InfoCube. Then create the transformations and the Data Transfer Process. Create a new InfoCube according to the new datamodel . Complete it with transformations and DTP. Put the original InfoCube in “loadable”-mode to stop the creation of new data. Compress the InfoCube, and then copy the information from the original InfoCube to the two new ones. If you like, you can now prove that your new model is more efficient with transaction RSRV.
Now, prove the information represents the same in all the three InfoCubes. Create a MultiProvider over these three InfoCubes and identify all the characteristics. On this, create a validation query by having the InfoProvider characteristic in the Columns and a set of important characteristics in the Free Characteristics. Run the validation query to prove that the represented information in all the InfoCubes is the same. There may be differences in the InfoCubes and although these are insignificant by default, they must be explained. So do the preparation early! Store the queryresult for future reference. Put all these objects in a separate transport to the production system.
Step 2. Execution
Create the remodellingrules in RSMRT according to the new datamodel. Now make the Go/No-Go decision, based on the verification in step 1, that the information is the same.
Empty the original InfoCube (including dimension tables) and execute the remodellingrules. Put these rules in another separate transport to the production system.
Step 3. Roundup
First the transformation and DTP have to be created to reload the information back to the original InfoCube. Do this for both InfoCubes: the exact copy- and new datamodel InfoCube. Doing this will ensure a fall-back scenario.
Transfer the information back from the new model InfoCube. Reactivate the MultiProvider and run the validation query in step 1 to make sure the information is equal again in all the InfoCubes (again, store the queryresult). Put the InfoCube back into “Plannable”-mode. When the information is the same throughout, the remodelling is a success.
Reactivate all the other dependable objects. Clean up all the temporary objects; the validation query, the MultiProvider, the DTP’s, Transformations and finally the InfoCubes. Again, create a separate transport for the clean-up fase.
Conclusion
The remodelling is a simple procedure, but the danger is the details. If the transports don’t contain the mentioned objects or are transported in a different order, the transport could end in a Return Code 8. Finding out what should be done to fix this can be a problem, so be precise.
The aforementioned procedure has the following benefits:
- Remodelling is possible in a Real-time InfoCube.
- The information is preserved.
- There is a fall-back scenario thanks to the exact copy.
- A large reliability because of the validation.
The value of the information in BI can be priceless, and so is cost of losing this data. These benefits make it more easy for an organisation to give the go-ahead for a data removal and remodelling of the InfoCube and thus create a more efficient model.
Bastiaan Lascaris, 06-12-2011, Utrecht, Logica.
2 Comments
