One of the projects which I’ve on the go at the new job is migration a collection of reporting function into the Microsoft way of doing things. There is a tight “timebox” on this project, and a diversity of reporting “cubes” which need to be migrated into Analysis Services Cubes.
The other constraint on the project is that the new Cubes should be a direct replacement of the original reporting solution. There should be no “enhancement” of the solution (to start with at leats). If we get the project to the finish line early, then there could be an opportunity to do some dimensional modelling on the implementations an “tidy up” the resulting Analysis Cubes.
My plan is a four pronged attack on the problem. Those prongs are:
- Use the xml file, which is used to the data (and metadata) into the current system, to defined the Analysis Cubes. The developer I’ve working on this is making good progress building a program which is defining a Cube programmatically. This is using the .Net object which Analysis Services for .Net (Namespace: Microsoft.AnalysisServices Assembly: Microsoft.AnalysisServices (in microsoft.analysisservices.dll)). Defining the translation between in existing products conceptual model to the Analysis Services model will come next week. Then we will start to see how this could come together.
- A program which will traverse the object model and report all of the properties of a cube. Currently, I’ve a version of this which produce text file. Moving it to a WinForms interface could be an option for next week, or I may “put it to bed” at it stands.
- A program which will compare the results of the same queries into the 2 product programmatically. I expect that the developer building this will make some good inroads into this over the coming week as well.
- “Sweeping up the loose ends”. I’m uncertain for all of the things which will end up in here. There is a bunch of documentation on the continued operation of the solution, already in this bucket.
- The object model for the Analysis Services looks good (so far). It would appear to allow these type of interactions will the product.
- There are a number of issues which the project will need to address as part of the migration which are not immediately part of the translation process, but part of the bigger “making it work well” process. These include:
- Storage management. I’ve not delved into this part of SQL Server and Analysis Services. These Cubes will consume space, and how that space is managed will be an issue to manage.
- Performance tuning. Another part of SQL Server and Analysis Services Cubes which is unknown at present. The generation of aggregates which are commonly used (or maybe all rollups), the choosing an approach and storage management will be an issue.
- Instrumentation. What we currently get out of the systems, and what we should be getting (to make things more robust) is another open question.
- Integration Services. It is unclear at present how much new ETL will need to build to deliver an automated solution. There is some ETL processes in existence which is used in the current implementations. How much the migration will need to modify is yet to be seen.