Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Analysis Services Connect digest

with 10 comments

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…

Written by Chris Webb

July 10, 2009 at 1:33 pm

Posted in Analysis Services

10 Responses

Subscribe to comments with RSS.

  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!

    cosmini

    July 10, 2009 at 3:37 pm

  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.

    Arnaud

    July 10, 2009 at 6:12 pm

  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!

    Paul

    July 10, 2009 at 10:32 pm

  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!

    Chris

    July 11, 2009 at 7:21 am

  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?

    peter

    July 13, 2009 at 11:25 pm

  6. Oh yeah will put it on connect when vacation is over :)

    peter

    July 13, 2009 at 11:25 pm

  7. 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 ?https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=378977Calculated members dependencyhttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=378979Loading detailed and preaggregated fact data in SSAS at the same timehttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=389706Be able to create subcubes or viewshttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=402520Processing a subset of aggregationshttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=306110

    Leandro

    July 17, 2009 at 9:39 pm

  8. Opened a connect suggestion on my measure group thing: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=482149

    peter

    August 10, 2009 at 11:41 pm

  9. 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.

    Michael

    November 7, 2009 at 9:48 pm

  10. I had recently some requirements that may be useful for product enhacement. I\’ve posted them in Connect.Previewing dimension attribute data before processinghttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=510328Have dimensions with no underlying table or viewhttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=510331Add custom members manually to dimension attribute https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=510332Excel as a data sourcehttps://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=510333

    Leandro

    November 13, 2009 at 2:33 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: