File Library

Over time, requirements can change and cubes are no longer built in a way where they can be of maximal use. The business owner may want to add more dimensions, remove dimensions, restructure dimensions or some combination of all of these so that they can get additional use out of the cube and enhance their reporting needs. Depending on the change and the requirements associated with them the cube may need to be modified or redesigned and rebuilt.

We have modified many cubes, but recently we did a complete redesign of a cube. The reason was to enhance reporting by our Finance team to meet new business requirements and to make the cube functional for an additional department and their reporting, which was currently completed through SQL queries and manual manipulation. The big win out of the process, was bringing the two departments reporting inline with each other and reducing the number of inconsistencies in the individual departments reporting, which was distributed companywide. Requirements included removing a couple of dimensions, merging two dimensions into one, adding six new dimensions, and restructuring one dimension. All of this needed to be done, while still maintaining 8 years of historical data. In addition to the requested business changes, we took the opportunity to make made some systematic changes. These changes includes going from text file loading to SQL table loading and additional dimension builds of shared hierarchies within dimensions.

The first thing we worked on was changing our data source. To gather our data set we ran a PeopleSoft SQR process that sent output to data files. We changed the SQR output source to PL SQL tables so we SQL load. The next step to make the required dimension changes in the cube. Once the cube was built in the new format, the final two steps were to load historical data and to build a new load process.

For historical data, a decision was made that it would be useful to have the new dimensions defined, rather than defaulted to a not specified member within the dimension. We ended up updating the historical data in two separate ways. A few years ago, as a part of another project, we started loading the text output files into a SQL table so that they could be used as part of another process. The oldest two years of data in the table were not accurate enough to use. For these two years, we wrote a report script to export the data. We appended a not specified member for each of the new dimensions. For the remaining 5 historical years, we wrote SQL update scripts that would update the current records with the new dimensions, based upon the source PeopleSoft tables. Had the data not been in a SQL table already, we could have done a column export and loaded it into the SQL table to achieve the same results.

Once the historical data was loaded, it was critical to validate that all data was loaded and that the new dimensions were updated correctly. To do this, we leveraged the power of Essbase, since there was far too much data to even think about using Excel. To validate, loaded an “Actual” scenario from the PeopleSoft tables and loaded a “Test” scenario with the updated SQL tables. We then used a calculated “Variance” scenario and SmartView retrieves for our validations. We were able to see that all expected instances were zero.

We were then able to build a load process and MAXL automation. Build rules were created, with SQL sources for all dimensions that could potentially have new members on a weekly basis. In addition, we automated the build of some our secondary, shared hierarchies with build rules that created shared members. This process will allow for all hierarchies in a dimension to contain all members without having to manually copy and paste each week.

Finally, the cube was QCed by selected users, training was created and presented to the user population, and existing retrieves and reporting was transitioned over to the new format. The project took and extensive period of time to be completed, given the number of changes involved, but the value added from the process definitely exceeded the time investment.