Analysis Services Top 10 Wish List

Analysis Services leads the OLAP server market and it should be your platform of choice for historical and trend reporting. In the spirit of the season, here is my Top 10 Wish List for Analysis Services. All items are related to regular cubes and none to PowerPivot which I am yet to try.

1. Improved client support – No 1 on my list has nothing to do with the server itself but with how well the Microsoft-provided clients support Analysis Services. To its credit, Microsoft continues improving the Excel OLAP support but still there are major functionality gaps from one tool to another. This is especially evident in the area of Reporting Services (see my No 1 SSRS wish list item). I am not sure if that’s possible from a technical standpoint but I wish the SSAS team re-factors the query generation logic into a MDX Query Generator binary of some kind which all tools can share to produce the same results consistently.
2. Focus on UDM (aka Corporate BI) – Although I see some useful scenarios for self-serviced OLAP with PowerPivot (previously known as Gemini), for various reasons I believe the focus should stay on UDM which now Microsoft refers to as Corporate BI. First, the business problems that I solve with UDM exceed by far Gemini’s capabilities. Second, if not a single source for reporting and a single version of the truth, I believe UDM should handle 80-90% of the reporting needs. Finally, based on my experience, data acquisition is the most difficult thing. This is why we have a dimensional model which IT would put together. But once data is cleaned and transformed, building UDM on top of it shouldn’t take that long
3. Silverlight OLAP Viewer control – A Silverlight-based control that ships with Visual Studio to let developers embed an Excel-like Analysis Services browser into their ASP.NET applications.
4. Improved performance – Version 2008 has brought significant performance enhancement and SSAS performs great when aggregating regular measures. Usually, it is the calculations that will get you in trouble. So, anything that can be done to improve the server performance further will be welcome. For instance, the calculation engine executes the query on a single thread. It will be nice if a more complicated query can be parsed and executed in parallel.
5. MDX Query Analyzer – Related to performance, it will be great if we finally get a graphical query analyzer (a-la the T-SQL query analyzer) with hints about how to improve the query execution.
6. Memory-bound partitions – Again related to performance, it will be great if you could configure a partition to be memory-bound for super-fast access, as I mentioned in this blog.
7. Detail reporting – I wish Analysis Services improves the ability to report on detail level. For example, it will nice to support text measures that the server returns for the lowest grain. I also want detail queries, such as a query that returns accounts for given customer to perform faster and don’t give up with “The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.” error.
8. Resource governor – Analysis Services should expand on letting the administrator manage resources, such as memory, connections, query timeouts, etc.
9. Custom security – Support custom authentication that is not Windows-based to facilitate Internet connectivity perhaps similar to custom security in Reporting Services.
10. Modeling enhancements – OK, this is a catch-all bucket for support issues that I logged and which were declared as “by design” and “future enhancements” [:D], such as aggregatable Many-to-Many relationships, fixing subselect issues, fixing exclusion filter with custom operator issue.

Happy holidays!