Microsoft Fabric, Power BI, Analysis Services, DAX, M, MDX, Power Query, Power Pivot and Excel
At long last I’ve got round to organising another BI user group meeting in London, on the evening of November 17th. We’ve got Ian Marrit talking about Master Data Services in the first session, that’s followed by Jamie Thomson and me talking about all the cool new stuff that will be announced for BI at PASS the week after next. For more details and registration see:
Hope to see you there!
So my blog birthday has come around again, and five years seems like some kind of milestone – I’m surprised anything in IT lasts five years. But here I am still going…
It’s been a pretty good year. Half of it was taken up with writing “Expert Cube Development with Analysis Services 2008” with Marco and Alberto, which was published in the summer; I was very happy with how it turned out, and we got some really good reviews on various blogs (see here for a list) as well as six five-star reviews on Amazon.com. Even the one bad review we got on Amazon, in my opinion, proves that we achieved our objective to write a book specifically for more advanced users rather than beginners. We heard the other day that we’ve already sold more than a thousand copies which I don’t think is bad at all for a book with a relatively small target readership.
The blog itself did quite well too: I was very proud when won an award for best BI blog post in the PASS Log Reader Awards for my post on SSRS Drilldown, and I was equally chuffed when Donald Farmer named me in his top ten SQL bloggers of 2009 the other day. It’s been harder to find SSAS and MDX-related issues to write about because, let’s face it, there hasn’t been much new SSAS and MDX functionality in the past few years, but I’ve got a lot of PowerPivot and DAX posts planned and there’s plenty of new stuff in the wider MS BI stack that will be worth a look. So far I’ve resisted the temptation to move with the times and start Twittering as well, not because I have anything against it but more because I’m sure I’d like it too much, and I spend far too much time tapping away at my laptop already.
Business-wise things have certainly been slower in 2009 than they were in 2008 or 2007, which was only to be expected. Luckily the quietest months for work were also the months when I was working hardest on the book, and it now looks like things have turned a corner (for the European market, if not the UK) and I’m extremely busy once again. I’m not sure whether PowerPivot and DAX will present many opportunities for me to make money from consultancy, but given that I’ve been doing a lot more training recently I’ll probably set aside some time to write a course on them too; I’ll be on the lookout for other opportunities to diversify away from my core expertise of Analysis Services and MDX, but I don’t think I’ve found a new technology that really grabs me yet.
Last of all, I couldn’t let Mosha’s ‘farewell to BI’ blog post from earlier today pass without comment. I started working with OLAP Services as it then was during the beta for SQL Server 7.0, more than ten years ago now, and right from the beginning Mosha made an incredible effort to engage with the user community, giving us advice, answering our questions and taking the time to understand what we were trying to do with the product. His posts on the microsoft.public.sqlserver.olap newsgroup, and latterly on his blog, were a goldmine of information and I firmly believe his efforts were one of the main reasons why Analysis Services has been as successful as it has been; his example is one other development teams at Microsoft would do well to follow. Of course it’s not like he’s died or anything and I don’t want to sound as though I’m writing his obituary, but he’ll be missed and Bing’s gain is our loss.
Here’s to 2010!
I thought I’d do a quick round-up on some of the interesting links that I’ve found concerning Gemini:
Something else I’m a bit late on, having been away on holiday this week, is the announcement that there won’t be a Microsoft BI Conference this year:
http://blogs.msdn.com/bi/archive/2009/04/10/microsoft-bi-conference-update.aspx
Apparently the same customers who told Microsoft to kill PerformancePoint planning have also been saying that they should only hold the BI Conference every other year. As an aside, did these marketing people ever get told on their MBAs not to treat their customers like idiots? Is there anyone out there that still falls for that ‘our customers have told us’ line? Surely the whole point of blogging – even when it’s done by a marketing person – is that it’s meant to be (or should appear to be) an honest, person-to-person type of communication and doesn’t work when you simply reuse the same content you would have stuck in a press release?
Anyway, while I agree with many of the points Marco makes about a separate BI Conference being necessary, it makes my life easier in a way – it means I no longer have to make a decision about whether to attend the PASS Summit or the BI Conference. PASS is the conference to attend and will have extra BI content; I’ve already submitted one abstract and will think of some more potential topics to talk about (any suggestions?), so with a bit of luck I’ll see you there.
By now just about everybody who has anything to say about the killing-off of PPS Planning has blogged, so I thought I’d post up some links. The judgement seems to be that it’s hardly a surprise that it wasn’t successful and that even if they could have got the tech side of the product right, they didn’t have the means to sell it; on the other hand, the news that the surviving parts of PPS will be effectively free for Sharepoint customers is certainly going to be a positive move for its uptake – although it can also be seen as another blow for another set of partners, those who provide web-based SSAS client tools.
Anyhow, the links –
Nigel Pendse: http://www.olapreport.com/Comment_Bizdemise.htm
Johan Pellicaan: http://officeapplications.typepad.com/bridge/2009/01/microsoft-business-intelligence-changed-strategy-unclear.html
Mauro Cardarelli: http://blogs.officezealot.com/mauro/archive/2009/01/25/21394.aspx
The Panorama blog: http://www.panorama.com/blog/?p=129
Cindi Howson: http://www.intelligententerprise.com/blog/archives/2009/01/microsofts_big_1.html
The official word from the Microsoft BI Blog: http://blogs.msdn.com/bi/archive/2009/01/23/microsoft-bi-strategy-update.aspx
Cubegeek: http://cobb.typepad.com/cubegeek/2009/01/microsoft-pps-dead.html
Last of all, Andrew Wiles makes a very sensible suggestion here:
http://andrewwiles.spaces.live.com/Blog/cns!43141EE7B38A8A7A!451.entry
Given that several partners and customers who were Planning early adopters have been pretty much [expletive deleted] by this move, why shouldn’t Microsoft open source it and make the code available somewhere like Codeplex? It would be a nice gesture.
Phew, another SQLBits done and dusted. And I think it was a good one, especially looking at the comments so far:
http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/14/busting-for-a-wii-at-sql-bits.aspx
http://andrewwiles.spaces.live.com/blog/cns!43141EE7B38A8A7A!368.entry
http://blogs.technet.com/andrew/archive/2008/09/14/sql-bits-cubed-thanks.aspx
Thanks to everyone that came and especially to everyone that helped out! And if you weren’t there, the best thing is that Microsoft brought along a camera crew and filmed the sessions (I think all of them, certainly all of the sessions in the room I was monitor for) which should be made available online somewhere soon. Apologies to everyone who stopped me to say hello – I was running around like the proverbial blue-a*sed fly all day so I couldn’t stop to chat with anyone for more than a few minutes, I hope I didn’t seem too rude…
Courtesy of Jamie Thomson, here’s some sample code showing how to create Analysis Services partitions from within an Integration Services package using AMO:
Although I should stress that if you’re using this in production, you do want to set the slice property on a partition to ensure you get the best possible performance in all cases.
Ahh, so I’m back from my holiday and feeling much better -even if it did manage to rain every single day while I was away (that’s the risk you take with holidays in England). Now all I have to do is get through the massive pile of emails waiting for me and steel myself in preparation for the next few months of hard work… roll on Xmas! Anyway, a few interesting things that happened/thoughts that occurred to me while I was away…
Of course the big thing that happened, the day after I left, was the RTM of SQL2008. Hopefully you’ve heard this news by now, but the big questions here are: is AS2008 any good? Do I want to migrate, and if so, when? Personally, I’ve been using it for a few months now on a project and my impressions of it are positive. As I’ve said before there aren’t any really amazing wow features that will make you want to upgrade, but the performance improvements can in some cases be quite significant, the new BIDS is easier to use, and there are a few obscure fixes/changes in behaviour which tie up some loose ends left over from AS2005. Since migration is very, very easy indeed I would encourage you to install it on a test machine if you haven’t already and start thinking about moving up. Of course the mantra of ‘wait until SP1’ is so deeply ingrained in people’s minds that most people will want to do exactly that – and there’s a lot of sense to that approach, since the first bugs are being found already (see here) but equally there are a fair few known problems with AS2005 SP2 and given the problems that all of the CU releases have (see here for example, and I’ve heard the same story for every single CU, they create as many new bugs as they fix) I wouldn’t recommend them; I suppose you could wait for 2005 SP3 but my feeling is that AS2008 is the better bet.
Meanwhile, in the cloud I see that Good Data have gone into beta, and there’s a new, mysterious MDX-queryable (Mondrian-based?) offering that has broken cover called BI Cloud. If I have time, I’ll try to check them out. Also on the net http://www.learnmicrosoftbi.com/ seems to have a lot of good videos explaining the basics of AS. And there’s a new podcast featuring Richard Tkachuk from the SQLCat team where he talks about the performance improvements in AS2008 and seems to suggest that it’s now possible to use hints in MDX with a new function whose name I couldn’t make out – I’ll post if I get more details.
I’ve also been thinking some more about the DATAllegro deal. There seems to be some discussion about when something that works with SQL2008 can be released, and the folks at DATAllegro are keen to stress that their architecture allows them to plug in new RDBMSs easily so the implication is that it will be sooner rather than later; clearly the investigation work has been going on for a while, and must somehow tie in with the MatrixDB stuff that got leaked a few months ago. All of this would be good for AS running in ROLAP/HOLAP mode on a MPP SQL Server, but can this technology but I wonder whether it could be made to work with AS in MOLAP mode? I think it could – surely the hooks are already there with the remote partitions/linked measure groups/dimensions stuff. Just conjecture though; I think we’ll find out more around the time of PASS and the BI Conference.
Lastly, I’ve booked my place for Mosha’s MDX Deep Dive pre-conf seminar at PASS this year. Who else is going?
I was using SQLMS with AS2008 the other day and noticed when right-clicking on the server node in the Object Explorer window some policy-based management options seemed to be available:
Now policy-based management is one of the new features of the relational engine in SQL2008; you can read more about it here:
http://www.mssqltips.com/tip.asp?tip=1492
For a moment I was excited, then I took a look at what facets were actually available for Analysis Services: there’s only one, and that gives you the same options to set as you got in the old Surface Area Configuration tool. Maybe in a future release we’ll get some more functionality – it would be great to be able to enforce policies like "Always associate a partition with an aggregation design" and so on.
There’s going to be another one of the UK SQL Server Community’s BI Evening events happening at Microsoft UK’s offices at TVP in Reading on the evening of July 17th. It’s been far too long since the last one, I know – we’ve all been a bit distracted by SQLBits (and there’s going to be another one of those on September 13th – do you or our company want to sponsor it?).
You can sign up here:
http://www.sqlserverfaq.com/
Here’s what we’ve got lined up:
"PEL vs MDX – what are the differences between the two languages?"
Jeremy Kashel, Adatis
Jeremy Kashel from Adatis presents an introduction to the PerformancePoint Expression Language (PEL). The content will be geared towards MS BI developers, and will highlight the differences between PEL and MDX, with the aim that those with MDX experience will be able to make a fast start with PEL
"Using Excel Services with Analysis Services and MOSS"
Jeremy Kirkup, TAH
If you are just starting to explore delivering BI solutions with Excel Services and Sharepoint then there are some issues that it is wise to be aware of in advance. This session will describe some real world lessons gained while creating BI solutions which expose Analysis Services data through the Excel Web Access web part.
The Excel 2007 client has first class support for the some of the advanced features of Analysis services such as drill-through. However, when exposing pivot tables to a browser through the Excel Web Access web part this feature is not available. The session will discuss a couple of approaches to implementing drill-through functionality for EWA based pivot tables using the MOSS SmartPart, AJAX and the Excel Web Services.