Month: November 2010

Is it the end of the road for SSAS?

Is it the end of the road for SSAS?

November 12, 2010

If you have been following the PASS summit notes coming from the attendees, you would have noticed an unmatched sense of pandemonium in the SSAS community. The reason is none other than the announcement of Microsoft’s decision to move from the traditional multidimensional SSAS database to the new in-memory Vertipaq BISM. My first source of this news was from Chris Webb’s blog – PASS Summit Day 2 and after reading it, I was in a state of shock for quite some time. It seemed hard to digest that Microsoft could be doing this to one of their most successful tools in the market. This could have been aggravated by the realization that one of my core skills was going to get obsolete and the hesitation or resistance to change from my comfort zone. Even Teo Lachev seemed to mirror the moderate disappointment that seemed to be floating around in Chris’ writeup in his own blog – The Battle of the Models. It wasn’t disappointment everywhere as experts like Boyan Penev (Thoughts on BISM, SSAS and MDX) and Siddharth Mehta (SQL Server Denali – Boost to SSIS career, Start of End to SSAS and MDX career?) have tried to find reason with this development and at least my understanding is that they are more on the happier side. After this feeling had finally sunk in, I decided to write a pre-mature obituary for one of my favourite tools in my company’s technology page as given below:- “ Is it the end of the road for Microsoft SQL Server Analysis Services? It certainly seems so, Microsoft has dealt a deathblow to the multidimensional approach of OLAP and has announced in today’s PASS summit that it’s focus is on a newer and less multi-dimensional approach – the BI Semantic Model. The BISM – BI Semantic Model – is the name for the new type of Analysis Services database that gets created when you publish a PowerPivot model up to the server. This is clearly a strategy to bring in more ROLAP experts to the tool as the popular perception is that the learning curve for SSAS is pretty steep and many people are put off by it. What it means is that while the MOLAP SSAS won’t be deprecated, the efforts of the SSAS dev team are concentrated on BISM and PowerPivot and we shouldn’t expect any radical new changes for the future. This could mean a gradual death for SSAS by 2012 once Project Denali (which is expected to be renamed as SQL Server 2011, and rumored to be released in the last quarter of 2011) is released and established commercially. So, welcome DAX and bye MDX.” dead-end-sign
Why I termed this as pre-mature is because a new comment has appeared in Chris Webb’s blog from Amir Netz (Lead Architect for Microsoft’s BI offering) and it seems to be promising. Some excerpts from that

  • BISM and VertiPaq are all new features of SSAS. SSAS is gaining massive new capabilities in Denali. Nothing it taken away, nothing is deprecated. It is all net positive. I hope you can not only internalize it but also communicate it to others.
  • The best way I think of the relationship of the “MOLAP UDM” to the “VertiPaq BISM” is akin to the relationship between C++ and C# in the year 2000. While C++ will still stay with us forever, C# is advancing rapidly and is able to take on broader and broader workloads. And the most important thing, Visual Studio – offering both C++ and C# is a much better product then the one offering only C++. It offers developers the option of choosing the right tool for the right task. Now – replace C++ with MOLAP UDM, C# with “VertiPaq BISM”, and Visual Studio with “SSAS” and you got the exact situation of today. 
  • Even with VertiPaq BISM introduced, MOLAP UDM is still used for all the high end heavy lifting and it is just as important as it had always been.
  • As for the roadmap – MOLAP is here to stay. It will have new features every release (just like we have new important MOLAP features in Denali). Yes – BISM being less mature will see a faster innovation pace and being based on a more innovative foundation it will likely be the one creating exciting breakthroughs as we move forward.
  • We worked hard to preserve the investments you made in the UDM and MDX. For example, the BISM supports ODBO and MDX. In fact – this is the only way Excel connects to it. All of the MDX lovers can still send MDX queries and create calculated members in the BISM. This is how Panorama works with the PowerPivot model. AMO works with the BISM as well as with the UDM. etc. Make no mistake about it – MOLAP is still the bread and butter basis of SSAS, now and for a very long time. MDX is mature, functional and will stay with us forever.

Yippee! Again, if I can trust my sense of understanding things, it would mean all is not lost and SSAS is here to stay. I guess I would have to wait for another blog from Chris to completely enlighten common people like me and allay my fears. Till then, I would recommend you guys to go and read Amir’s comments completely in Chris’ blog. And definitely, don’t miss out on the other great blog links that I have given. Update (13/11/2010) Microsoft has come out with an official reply now in the SQL Server Team Blog – Analysis Services – Roadmap for SQL Server “Denali” and Beyond. The blog has tried to put out the fears by underlining that the new BISM model does not replace the traditional UDM model. This is also evident from the model diagram they have posted (given below also) where we can see the relational and multidimensional data models existing side by side. BISM (BI Semantic Model) Some salient points of the blog are:-

  • While PowerPivot is targeted at business users to build Personal and Team BI applications, Analysis Services is the platform for building Professional BI applications.
  • With the introduction of the BI Semantic Model, there are two flavors of Analysis Services – one that runs the UDM (OLAP) model and one that runs the BISM model. This is a side-by-side offering – you can choose to run one instance of Analysis Services hosting a UDM model and another instance hosting a BISM model on the same server.
  • You might ask – Why do we have two types of models in Analysis Services? Which one should I use? Is the UDM going to be deprecated now that we have the BISM? The answer is NO! Analysis Services just got a lot better in SQL Server “Denali” with the BISM and VertiPaq and DAX! However the UDM is a mature and industry leading technology and is here to stay.
  • Some existing UDM applications that are undergoing a major overhaul might be migrated to run on the BISM if appropriate. For new BI applications, the choice between UDM and BISM will depend on the complexity of the application. For BI applications that need the power of the multidimensional model and sophisticated calculation capabilities such as scoped assignments, the UDM is the way to go. For a broad majority of BI applications that don’t need that level of complexity, the BISM will offer a rich, high performance platform with faster time to solution
  • The Analysis Services team is committed to staying on the cutting edge of BI innovation and leading the industry with breakthrough technologies such as VertiPaq. At the same time, we recognize our rich heritage in OLAP and continue to be humbled by success of our product and the vast ecosystem of customers and partners that it has helped us build.

So this blog from Microsoft should answer the question which we asked, the answer – SSAS is here to STAY!

Posted by SQLJason, 1 comment
The Curious Case of Joins while Cube Processing

The Curious Case of Joins while Cube Processing

November 5, 2010

One of those days, I was just chatting with a colleague of mine about some database designs and he was showing me some exquisite piece of work in the database. That is when I happened to glance upon a stored procedure which apparently seemed to materialize an already existing account dimension view as a table. This stroked my inherent sense of curiosity and I unleashed a barrage of questions at him regarding it’s relevance. My colleague started explaining that somehow the dimensions were getting joined with all the fact tables during cube processing and hence the processing takes a very long time to complete if the dimension view was used as such. To add some justice to his words, the dimension view indeed was doing some complex logic and it looked natural that the cube processing would take time if the view was used in the joins instead of a table. But what didn’t look natural to me was that the dimension views were being use din the joins. After all, I was under the assumption that it was just a ‘select from fact table’ that gets executed or at max, the fact views if some logic is used. So I decided to open this curious case of joins for investigation. Even though it wasn’t because I didnt trust my colleague, I just had to see with my own eyes to believe, the doubting Thomas that I am. So I asked him to process a particular fact table and I looked up the query that was being used. With materialized Reference relation Sure enough, the dimension view was being used in the joins. I was dumbfounded and decided to take a break to think. Adding tobacco to my pipe, I put on my thinking cap and stared aimlessly at the setting sun. Random thoughts started racking my brain and I even ventured far to think that it could be a bug in analysis services. Suddenly, something clicked in my mind and I started running towards my colleague and delivered in true Sherlock Holmes style – “Come, Watson, come! The game is afoot. Not a word! Into your clothes and come!”. My colleague also decided to humour me and tagged along. Me : Chance has put in our way a most singular and whimsical problem, and it’s solution is it’s own reward. Colleague : Can you please cut the crap and let me know why this happens? Me : Watson, you know I can’t resist a touch of the dramatic. I have no desire to make mysteries, but it is impossible at the moment of action not to enter into long and complex explanations. Let’s go step by step and have a look at the query once again. Colleague : Duh, ok! Me : Perhaps when a man has special knowledge and special powers like my own, it rather encourages him to seek a complex explanation when a simpler one is at hand. The importance of careful observation can never be emphasized enough. If you would remember the way that you explained the problem to me, you mentioned that all the dimension views were getting joined in the fact table. But if you look at the query (especially the highlighted part), what do you observe? Colleague : Hmmm, just the account dimension is getting joined. Me : How often have I said to you that when you have eliminated the impossible, whatever remains, however improbable, must be the truth? We know now that all the dimensions are not getting joined. We also know that the account dimension is getting joined for some reason. What could be the reason? Colleague : <light dawning on him> It could only mean that we have defined the usage of this dimension differently. Me : Elementary my dear Watson! So let us open the dimension usage tab and check. Dimension Usage Colleague : Yes!!! There is a reference relationship between the Geography dimension and the fact table through Account, that should be the reason! Me : To let the brain work without sufficient material is like racing an engine. It racks itself to pieces. Why should a reference relationship induce a join? After all, it could also be just resolved at run time like the regular relationship. To understand more, click to view the relationship details. materialized option Colleague : Finally! It is because the Materialize option is ticked. Me : Elementary, my dear Watson. When a reference relationship is materialized, the joining across dimensions is performed during processing and not in querying. Also, the attributes in the materialized reference dimensions follow the aggregation rules of standard dimensions. If you remove the tick in the materialize checkbox, you will notice that the joins would not be present anymore. without Materialize Colleague : OK, so this was it. So simple once we know the reason. Me : There, Watson, this infernal case had haunted me for days. I hereby banish it completely from my presence. Colleague : Could you please cut out the Sherlock Holmes part, it’s freaking me out! So, here rests the curious case of joins while cube processing in my chronicles. Note : Special mention for eQuotes for help with the Sherlock Holmes quotes. Also, to read on how the ticking of the Materialize option would affect your data, click on the blog below by Alberto Ferrari – SSAS: Reference materialized dimension might produce incorrect results

Posted by SQLJason, 1 comment