Analysis Services

Analysis Services Connect digest

It’s a fairly popular thing for SQL Server bloggers and MVPs to put together lists of items on Connect (bugs that need fixing, new bits of functionality that should be in a future version, etc) and ask readers to vote on them so they get more visibility. Jamie Thomson did a post for SSIS the other day, for instance:

While I’ve done something similar in the past, I thought it would be a good idea to put together a more comprehensive list of Connect items for SSAS in the hope that some of the more important issues will be addressed in the next major release. So, please look down the list below and vote on anything that you think is important!

  1. MDX needs a special division operator that returns null instead of infinity when dividing by zero or null. Why do we always have to trap this in code ourselves? I have never, ever wanted to return infinity from a calculation!
  2. BI Development Studio is ridiculously slow doing Deployment or Saving (when in Online mode) sometimes. This isn’t a BIDS issue, of course, more of a reflection on how long the XMLA it’s generating takes to run, but still it wastes so much time…
  3. It would be cool if, after every time the server crashed, SSAS could make a copy of the current Flight Recorder trace file so we can see what was happening on the server. I know PSS can get this information from the mdmp files that get generated, but sometimes I want to do some detective work of my own before opening a support call:
  4. Attribute relationships serve a second purpose as member properties, but sometimes you want to display an attribute as a member property of another attribute when there isn’t a direct relationship between them. This leads people to creating either redundant attribute relationships or duplicate attributes in the dimension, both of which are equally bad:
  5. We have role-playing dimensions, so why not role-playing measure groups? There are a few times when I’ve wanted to reuse the same measure group with dimensions joining onto different columns (eg when working with many-to-many relationships), and not have to create and process multiple different physical measure groups:
  6. Apart from the Slowly-Changing Dimension support that we already have, it would be useful to be able to create different versions of the same dimension and allow users to choose which version they want to see. This would allow us to expose snapshots of what a dimension looked like at any given point in time:
  7. Richard Tkachuk’s approach for handling ‘events in progress’ highlights the fact that SSAS isn’t very good at dealing with what is a reasonably common BI problem. Let’s have a new ‘Range’ dimension relationship type to do this out of the box:
  8. Let calculated members in MDX return sets. This would allow us to optimise calculations where expensive set operations currently have to be duplicated:
  9. At the moment, the IgnoreUnrelatedDimensions property controls what happens for measures when you query them with dimensions that have no relationship with the measure group, and when you query them with attributes below the granularity of the measure group. Very often, though, I do not want the same behaviour for these two scenarios, so can we have two different properties please? Or even be able to set this on a per-dimension basis?
  10. At the moment, when using connection string properties in SQLMS, after you’ve set them the properties get persisted for all future connections without being visible in the connection dialog. Either don’t persist the properties or show us which ones are being set!
  11. No more arbitrary-shaped set errors! When writing scoped assignments, I want to be able to scope on whatever area in the cube I want; at the moment, I often find I have to repeat the same assignment several times to get around the requirement that I can’t assign to an arbitrary-shaped set.
  12. We can’t currently deny access to an entire dimension, rather than individual members on it, yet this is another common requirement:
  13. It would be cool to be able to parameterise server-side MDX calculated members and named sets:
  14. Here’s an old one: we really, really need to be able to rename hierarchies on each instance of a role-playing dimension. Not being able to do so seriously limits the usability of role-playing dimensions.
  15. Similarly, I’d like to be able to set individual All Member captions for all of the attributes on a dimension:
  16. One mistake I make all too often is change something in the cube, redeploy it, process and then at the very end of processing have it fail because of an MDX Script error. I know it’s possible to use the ScriptErrorHandling mode to do something similar, but that’s a bit dangerous to set in production. What I have is an option for processing in a dev environment that is set by default and allows you to ignore MDX Script errors if any occur.
  17. Support for cross-dimensional user hierarchies would be useful for two reasons: first it would help us create (and possibly optimise) complex drillpaths; second, it would help when you have a ROLAP attribute that you want to expose only as the lowest level of a user hierarchy, to encourage people to cut down the scope of their query before they used it. Another approach to solving this second problem would be the ability to have attributes on the same dimension with different storage modes (which Greg Galloway and Harsh suggested on my older post).
  18. Dimensions are getting bigger and bigger, and it would be useful to be able to partition them for manageability and performance reasons. For example, doing this would allow you to delete some members from the dimension quickly by deleting the partition; you could also speed up Process Updates by only processing the partitions where dimension members have changed.
  19. I find it frustrating that dimension calculations (ie custom member formulas, unary operators etc) can’t be edited from the MDX Script. I want all my MDX calculations in one place!
  20. It would be really cool if we could declare parameterised MDX Select statements on the server, and then allow client tools to expose them as pre-canned queries, similar to what can be done with stored procedures in the relational world. This would mean that as developers we could write complex queries that most end users would never be able to create themselves (even if their client tools were capable of building them). Similarly, it would be useful if we could declare custom MDX functions in the MDX Script: for example, you could declare a complex set operation as a new function, then use it in your own calculations and also expose it to users so that they could use it in their own client tools.
  21. Fix parent/child hierarchies! They’re so commonly used, but there are so many ‘known issues’… I want to:
    1. Them to perform better, perhaps by building aggregations within them
    2. To be able to scope calculations on them properly
    3. Have more flexibility building them, by creating more than one per dimension and not having to build them from the key attribute
  22. I’d like calculated members to do everything, and have all of the same properties, as real members. This means I want to be able to
    1. Control the order they appear on a level, and mix them in with real members
    2. Allow them to have other calculated members as children
    3. Allow them to have member properties
    4. Allow all properties (member properties as well as properties such as Name) to be dynamically generated using an MDX expression
    5. Be able to dynamically generate whole sets of calculated members with a single expression; this would, for example, be useful for doing pareto analysis or dynamically generating bandings.
    6. Be able to expose the MDX definition as a property visible in MDX
    7. Be able to secure calculated members with dimension security
  23. From Jamie Thomson (from a long time ago) – automating partition generation when new data appears in the cube is a pain – this is something SSAS should be able to do for us:
  24. One from Thomas Ivarsson here – it would be good to have a flag for the BottomCount function to get it to automatically remove empty tuples from the set we’re passing in:
  25. From Darren Gosbell – we really need an official, built-in way of making calculations work properly when the current context is a set (for example when doing a multiselect results in a set in the Where clause):
  26. Also from Darren – at present, if you rename a database in SQLMS you then can’t deploy to this database from BIDS:
  27. From Marco Russo – at the moment, the DSV still has problems with handling TinyInt columns, casting them to different types in different scenarios:
  28. From Marco Russo – fix drillthrough! It’s almost unusably slow at the moment on large cubes and it doesn’t work with semi-additive measures, let alone calculated measures. This is such an important bit of functionality it needs to work properly:
  29. Following on from this, we need better control over the SQL that Analysis Services generates full stop. I’d like more influence over how SQL is generated for supported relational data sources and also official support (and help) for creating cartridges for new relational data sources.
  30. A tool that displayed MDX query plans would make performance tuning queries much easier:
  31. From David Clem – you can’t currently control the order in which calculated measures are displayed. It would be useful if there was a ‘display order’ property that allowed you to do this:

Of course, if you’ve got your own issues open on Connect that you’d like to publicise, please leave a comment below…

10 thoughts on “Analysis Services Connect digest

  1. here\’s a couple more:– make FE multithreaded — on large dimensions, especially unpartitioned dimensions (see your request above), this is an issue… I think, otherwise, one would have to rely on stored procedures.– make a whole lot more functions block eval compatible!

  2. Is there a connect entry regarding the way Roles are stored in BIDS SSAS projects? I am more annoyed with the fact that roles and its definition are stored in 5 different places (Role file, Database file, Cube file, Dimension file, Project file) if you\’re lucky. One developer creating a role with some security could pretty check out the entire project.

  3. 1) I agree with Arnaud on roles. Also make it so that when you script them out you get the WHOLE definition. 2) How about being able to script incremental changes to a DSV. Multiple developers co-ordinating adding new views to a cube and co-ordinating migration to test and prod is a pain. In fact EVERYTHING should be able to be easily scripted out in simple pieces to make DEV->TEST -> PROD as simple and modular as it is for SQL. Let us easily package changes to the MDX Script in an XMLA script to update TEST, then PROD.3) I definitely vote on 1 – Don\’t return infinity. What no more iif(mymeasure = 0,nulll?? How nice would that be. 4) IgnoreUnrelatedDimensions – Agreed. Allow per dimension.5) Forget measure folders. Create measure attributes so they can easily be filtered. Hunting and pecking through a tree is old school.6) Incorporate Mosha\’s MDXStudio into SQL MGT Studio7) Partitioned Dimensions – Great. Would open the door to new opportunities.8) Security on Perspectives please!

  4. All good feedback – but remember, the dev team are only going to notice it if you open a Connect and get everyone to vote on it!

  5. My #1 gripe with SSAS is: MEASUREGROUPS. They are extremely user-unfriendly as every single client application makes them seem significant even though most of the time they are pure technical constructs. Why can\’t we just define our own "measuregroups" with whatever measures we like?

  6. Excelent blog with excelent ideas !I contribute with other new suggestions for future SSAS releases (dreaming is easy!):Is it possible to define regions or blocks within MDX Script ? members dependency detailed and preaggregated fact data in SSAS at the same time able to create subcubes or views a subset of aggregations

  7. Maybe this is not the right place but i would really like the excel front end to support showing the description field from the as structures to give more meta data to the cube users.

  8. I had recently some requirements that may be useful for product enhacement. I\’ve posted them in Connect.Previewing dimension attribute data before processing dimensions with no underlying table or view custom members manually to dimension attribute as a data source

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.