BI Strategies When Migrating from Analysis Services 2008 to Analysis Services in Microsoft SQL Server 2012 Tim Peterson, SolidQ 3 rd DRAFT - 31 October 2011 Microsoft Business Intelligence is going through a period of transformational change with the release of SQL Server 2012. The new Business Intelligence Semantic Model has the possibility of greatly expanding the reach of Microsoft BI. The new tabular model in Analysis Services that is so closely integrated with PowerPivot in Excel and SharePoint has the potential to bring many more organizations into the world of Microsoft Business Intelligence. But what about those who are already using Microsoft BI with A nalysis Services 2008 (or 2005)? What are the best strategies for bringing existing BI systems into the new world of Analysis Services 2012? This paper discusses the pros and cons of the various options: 1. Stay with the multidimensional model 2. Switch to the tabular model 3. Keep the multidimensional model for legacy cubes, but use the tabular model for new development 4. Evaluate the applicability of both models for each project, and use either the tabular model or the multidimensional model based on the specific situation For the second and the fourth options, it would be useful to have a tool that converts existing cubes in the multidimensional model to the tabular model. Microsoft has not provided such a tool (at least not as of this writing) and it is impossible to provide a tool that will do a full conversion because some features in the multidimensional model do not exist in the tabular model. But it is possible to build a multidimensional to tabular migration tool which will automate much of the work of conversion. SolidQ has built such a tool, which, depending on the particular structure of the cube, will do between 70% and 100% of the work of the conversion. SolidQ’s migration tool does the following: 1. Starts with any AS2005/AS2008/AS2008R2 cube or with an AS2012 multidimensional cube. 2. Creates the equivalent AS2012 tabular model . 3. Maintains all names of measures, calculated measures, attributes, hierarchies, and dimensions, so that you can simply change the connection string for all existing reports, Excel pivot tables, and other client tools, and see how they work with the tabular model . 4. Converts some multidimensional calculations (MDX) to tabular calculations (DAX). The calculation appears in the tabular mo del with a default value in those situations where the tool cannot convert the MDX to DAX. 5. Removes any dimension or measure that the user wants removed . 6. Filters the model on any member or members selected by the user. SolidQ is working on expanding the cap abilities of the migration tool so that, in addition to the above, the tool will be able to: 1. Convert more MDX calculations into DAX calculations . 2. Convert multidimensional parent-child dimensions into regular dimensions that can be used in DAX . 3. Migrate unary operators into the tabular model by creating DAX calculations that use the unary operators appropriately . 4. Migrate many-to-many hierarchies into the tabular model by creating DAX calculations that express the many-to-many relationships. 5. Do a reverse migration from a tabular model to a multidimensional model. Some features of multidimensional cubes cannot be reproduced in a tabular model. Experienced developers will need to decide what to do about these features when doing the migration: 1. MDX scripting commands 2. Complex MDX formulas 3. Time Intelligence hierarchies Strategy #1. Stay with the multidimensional model The new Business Intelligence Semantic Model contains two types of data models – the multidimensional model and the tabular model. Existing cubes can be easily migrated to the AS2012 multidimensional model, with no required modifications. So, if you have existing cubes and you are mov ing to Analysis Services 2012 and you don’t do anything to change your cubes, you will have cubes in the AS2012 multidimensional model. After switching to AS2012, you can continue using the multidimensional model for your new development. If you don’t want to, you never have to use the tabular model. Here are the advantages of Strategy #1, staying with the multidimensional model: 1. All your existing reports and client tools work, with basically no changes. 2. You can continue to use your existing skills and knowledge, with no need to learn about new tools. 3. You will be using a model that has gone through several versions and is more mature. 4. The multidimensional model with MOLAP storage can be used for much higher data volumes than the tabular model in VertiPaq mode. 5. MDX, the language used to create calculations in the multidimensional model, has many functions that are unavailable in DAX, the language used to create calculations in the tabular model. 6. Expressing hierarchical relationships (parent, child, descendants) in particular is much easier with MDX than with DAX, because DAX has no hierarchical functions. 7. The multidimensional model allows you to include data modification commands in the MDX script, which is not allowed in the tabular model. 8. Multiple language translation is well-supported in the multidimensional model, but it is not supported in the tabular model. Here are the disadvantages of this strategy: 1. In the initial release of SQL Server 2012 you will not be able to use the new Power View tool in Reporting Services, because it only uses the DAX querying language, which can only be used with the tabular model. 2. You will miss all the new things that are available with the tabular model, as outlined in the list of advantages for Strategy #2. Strategy #2. Switch to the tabular model If you are currently using the multidimensional model and decide to switch to the tabular model, you will have a lot of additional work to do. There is no wizard or publicly available tool to migrate from an existing cube to a tabular model. And if you create new tabular models to replace existing cubes, you will have to ensure that all the names us ed in the model are the same, so that existing reports and Excel spreadsheets that use the cube will function properly. Here are the advantages of the second strategy, switching to the tabular model: 1. Tabular models can be used with the new Power View tool in Reporting Services, to give users a simple yet powerful way of examining their data. 2. PowerPivot projects in Excel and SharePoint can be upgraded to tabular models in Analysis Services, integrating self-service BI with corporate BI. If you are not using tabular models, you will not be able to use this capability. 3. The tabular model is always held in -memory and, as a result, is expected to deliver much faster query results than the multidimensional model in certain situations. 4. For people who understand relational databases it is easier to understand the tabular model than the multidimensional model. 5. The tabular calculation language DAX is modeled on Excel formulas and is easier for most people to learn than MDX. 6. The tabular model is easier to administer, with no need to manage aggregations and less complexity with par titions. 7. While there are advantages in using both models (as outlined in strategies #3 and #4), there would be less training expense for developers and administrators to be working exclusively with the tabular model. Here are the disadvantages of this second strategy: 1. You lose some of the capabilities that are available in the multidimensional model – some MDX functions, MDX scripting, translation, etc. 2. Unless you move to the tabular model’s Direct Query mode (which has even fewer multidimensional capabilities) , you will be more limited in the amount of data that you can analyze in a single model. Strategy #3. Keep the multidimensional model for legacy cubes, but use the tabular model for new development This third strategy will be the preferred course of action for those that believe the tabular model will be easier and more effective than the multidimensional model, but that it would be difficult and unnecessary to migrate existing multidimensional cubes. Here are the advantages: 1. Existing cubes and all the reports and Excel spreadsheets that reference them can continue without modification. 2. For new development, the advantages of the tabular model can be used. Here are the disadvantages: 1. Power View will not be able to be used with any of the pre-existing cubes that are left in the multidimensional model . 2. The organization will have to maintain skills for both the multidimensional model (with MDX) and the tabular model (with DAX). Strategy #4. Evaluate the applicability of both models for each project, and use either the tabular model or the multidimensional model based on the specific situation This fourth strategy tries to take advantage of the best in both models. There are times when an existing multidimensional cube would be migrated to the tabular model so that it will perform better or so that it can be used with Power View. There will be times in future development when the multidimensional structure is used because it has capabilities that are not available in the tabular model. For companies using this fourth strategy, it would be useful to have a tool that would convert in both directions – from multidimensional to tabular and from tabular to multidimensional. It would be expected that there would b e times when development would be started with one of the options, but the situation would change so that it would be better to switch to the other option. In some cases it would be good to test with both options, to see which one would give better perform ance for a particular set of data. The advantage: 1. The strengths of both models can be used. The disadvantage: 1. Even more so than with Strategy #3, the organization will have to maintain skills for both the multidimensional model and the tabular model. Conclusion SQL Server 2012 provides you the ability to continue using your Analysis Services multidimensional cubes, while offering you the possibilities of the new tabular model. Each organization moving to SQL Server 2012 will have to decide what combination of multidimensional cubes and tabular models will work best for them. This white paper has outlined four distinct strategies in making this choice. Some of these strategies require the availability of a multidimensional to tabular migration tool.
Pages to are hidden for
"Strategy #1. Stay with the multidimensional model"Please download to view full document