Sunday, November 15, 2015

Single Cube vs Multiple Cubes in SSAS

Multiple Cubes

  • Having multiple, smaller cubes may result in faster query performance than one large cube in some cases, especially if the fact tables have very different dimensionality.
  • While it is possible to apply dimension security to the Measures dimension, it is much easier to allow or deny access to a cube with the multiple cube approach than it is to apply security to all the measures in a measure group using the single cube approach.
  • Having multiple, simpler cubes can be much more user friendly than one monster cube with loads of dimensions and measure groups. If you have Enterprise Edition you can of course use Perspectives to counter this, but if you are using Standard Edition then Perspectives aren’t available.
  • Maintenance can be easier and less disruptive with multiple cubes: if you need to make changes to a cube while users are querying it, you might end up invalidating users’ connections and dropping caches. With one cube the chances of this disruption affecting more users increases.
  • It’s easier to scale out with multiple cubes: if you find your server is maxing out, you can simply buy another server and distribute your cubes equally between the two. With a single cube approach you end up having to look at (admittedly not that much) more complex scale-out scenarios like network load balancing.

Single Cube

  • If you ever need to work with data from two fact tables in the same query or calculation, or if you think you might ever need to in the future, you should go with the single cube approach. The two options for cross-cube querying, linked measure groups and the LookUpCube MDX function, should be avoided. Linked measure groups are a pain to manage, carry a slight query performance overhead, and can result in the same MDX calculations being duplicated across the original cube and the cube containing the linked measure group (which means maintenance becomes more difficult). The LookUpCube function is probably the worst MDX function to use in a calculation from a performance point of view and should be avoided at all costs. So a single cube is the only feasible option.
  • Even if your users tell you they will not ever need to analyse data from two fact tables in the same query, be prepared for them to change their minds. In my experience, SSAS projects have a tendency to grow in complexity over time, and cubes that start out simple in a first release often grow lots of new functionality as time goes on – and the more successful the project, the quicker things get complicated. As soon as your users see what’s possible with SSAS they will start to have new, more ambitious ideas about the kind of analysis they want to do with their data, and it’s very likely that they will realise they do need to do cross-measure-group queries and calculations. If you started out on the multiple cube approach and then this happens you will have no choice but to use linked measure groups, and as I said this can make maintenance difficult; using the single-cube approach from the start means you won’t have this problem.

Thanks Chris Webb.

Technorati Tags: ,

Saturday, November 14, 2015

DAX Editor

DAX Editor is an extension for Visual Studio that makes easier to work with DAX.

It supports the following features

  • DAX syntax highlighting
  • DAX formatting. Support for increasing and decreasing format depth using Edit->Advanced->Increase Format Depth or Decrease Format Depth
  • IntelliSense support for keywords, functions, columns, and measures. Press Ctrl+Space to show IntelliSense.
  • Autocomplete for keywords, functions, tables, columns and measures. Press Ctrl+Space to autocomplete.
  • Syntax error reporting using squiggles/Error List
  • Brace matching
  • QuickInfo support in AutoComplete list that gives description of functions
  • F1 help for functions & keywords

Download URL

Technorati Tags: ,

BISM Normalizer

BISM Normalizer manages Analysis Services Tabular models for Enterprise Implementations.

  • Database Compare – Allows to compare different versions of the tabular models.
  • PowerPivot Model Migration – When migrating a Tabular model from PowerPivot to enterprise-level in Analysis Services, the “Import from PowerPivot” can be used. But this will create separate PowerPivot database for each migration. BISM Normalizer allows to merge models migrated from PowerPivot model into appropriate Analysis Services database.
  • Allows to maintain a master model that can be extended by different implementations within the Enterprise.
  • Enables Partial Deployments by scripting selected differences from the model.
  • Reports differences between models in an easily distributed Excel format.
  • Full Integration with Visual Studio allowing tight integration with TFS and enables Code Merging.
  • Reuse definitions between tabular models for alignment with common business definitions in an enterprise organisation.