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:
http://blogs.conchango.com/jamiethomson/archive/2009/07/05/ssis-connect-digest-2009-07-05.aspx

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!
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=448127
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…
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=447405
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:
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=442606
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:
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=436023
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:
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=436081
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:
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=436064
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:
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=428465
8. Let calculated members in MDX return sets. This would allow us to optimise calculations where expensive set operations currently have to be duplicated:
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=426719
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?
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=350232
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!
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=358956
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.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=339861
12. We can’t currently deny access to an entire dimension, rather than individual members on it, yet this is another common requirement:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410
13. It would be cool to be able to parameterise server-side MDX calculated members and named sets:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=188842
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.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=144500
15. Similarly, I’d like to be able to set individual All Member captions for all of the attributes on a dimension:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=144234
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.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=144248
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).
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473684
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.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473687
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!
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473688
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.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473694
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
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473698
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
   https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473693
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:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127378
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:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=472560
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):
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=267570
26. Also from Darren – at present, if you rename a database in SQLMS you then can’t deploy to this database from BIDS:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281595
27. From Marco Russo – at the moment, the DSV still has problems with handling TinyInt columns, casting them to different types in different scenarios:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126798
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:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=403083
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.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=473689
30. A tool that displayed MDX query plans would make performance tuning queries much easier:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=321161
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:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=294907

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