Strategy #1. Stay with the multidimensional model by V5YS2d

VIEWS: 16 PAGES: 6

									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.

								
To top