The Biml Book, And Some Thoughts On Biml And SSAS

I know what you’re thinking: why is Chris blogging about a book on Biml when he’s not remotely interested in SSIS? Well, you’re right, SSIS isn’t my thing (though as an SSAS developer it’s important to keep up-to-date on the tools that the SSIS people on your team use) and yes, as you may have guessed, I got a freebie review copy because I’m an Apress author and I’m friends with one of the authors, Andy Leonard. There is, though, a good reason why I want to learn about Biml: you can use it to generate SSAS databases as well as SSIS packages.

First of all, the book itself. “The Biml Book” is a new book written by a team of Biml experts that teaches you pretty much everything you need to know about Biml. I’m always a bit worried by books with a large number of authors because I know how difficult it is to maintain a consistent style, but in this case I couldn’t see any joins. As someone with no previous experience of Biml. I found the book very clear, concise and easy to read. I highly recommend it as an introduction to the topic.

Of course the chapter on using Biml with SSAS was the most interesting for me. The main reason why SSAS people aren’t as excited about Biml as SSIS people are is that we just don’t usually have the same amount of boring, repetitive work that begs to be automated, and we already have AMO and TOM when we do need to automate the creation of SSAS objects. Indeed, I’ve only ever met one person who is using Biml with SSAS. So when would you use Biml and SSAS? The book provides a good, honest answer:

Biml can be a great fit for Analysis Services use cases, but there are some exceptions.

Just as with SSIS, one of the great advantages of Biml is that it easily allows scale-out architectures through automation. This makes it a good choice when it comes to multi-tenancy and/or multi-server environments.

Given the ability to automate structures and deployments through metadata, Biml frameworks can also include cube projects that can be driven with some of the same metadata that was used to build data integration frameworks.

Conversely, SSAS Multidimensional/Tabular can require the specification of additional types of metadata to automate its creation. After all, cubes and tabular models are largely just metadata containers on top of relational structures. If you want to use your own metadata to drive the creation of bespoke SSAS projects that could support any SSAS feature, you essentially need to duplicate the entire SSAS feature set in your metadata store. This will result in complex models that may be very difficult and time-consuming to maintain, potentially leading to longer instead of shorter development and deployment times. In a nutshell, Biml isn’t for all SSAS projects, but for the pattern-heavy, scale-out projects where it does fit, it’s tremendously valuable.

I’ll give you an example of where I think Biml and SSAS make a good match. I’ve worked with several companies who do what I call B2B BI: they create, host and manage Microsoft BI solutions for their customers. They have a standard template solution that connects to a particular type of data source (for example, their customers’ Dynamics databases), builds a data warehouse and then puts SSAS and maybe some reports on top. As a result they end up with multiple copies of the same solution, with the only difference being that each copy contains a different customer’s data – a classic example of a “multi-tenancy and/or multi-server environment” as described in the extract above. This style of BI will become more and more common in the future because cloud-based services like Azure SSAS and Power BI (both now support Azure AD B2B) make it much easier to implement, and I think Biml could play a very important role here: you don’t want to build and manage hundreds of near-identical SSAS databases, and their supporting ETL, manually.

New Book: Tabular Modelling In SQL Server Analysis Services

My good friends Marco Russo and Alberto Ferrari have published a second edition of the SSAS Tabular book that we wrote together a few years ago. It’s called “Tabular Modelling In SQL Server Analysis Services” and it covers pretty much everything you need to know about building models in SSAS 2016 Tabular. Although I didn’t have anything to do with preparing the second edition, it does include a few things I wrote for the first edition so I can’t pretend that this is an unbiased review – that said, I think it’s fair to say that everyone working with SSAS Tabular 2016 should have a copy of this book on their shelf. No-one knows SSAS Tabular better than Marco and Alberto and the chapters on engine internals and performance tuning are worth the price of the book alone. The only topic it doesn’t cover in detail is DAX, and of course if you want to learn DAX you should get a copy of Marco and Alberto’s equally brilliant book “The Definitive Guide To DAX”.

More details on the new book can be found in Marco’s blog post here and you can buy the book from Amazon UK here.

Two New Books: “The Definitive Guide To DAX” And “’M’ Is For Data Monkey”

I’m not going to pretend that this blog post is a properly impartial review – I know the authors of both of these books to varying degrees – but I thought it was worth writing a few words on two new books I’ve acquired recently which are worth additions to any Power BI enthusiast’s bookshelf or e-reader.

The Definitive Guide To DAX

Something I’ll never understand about my friends Marco Russo and Alberto Russo is their love of writing books – they generally have a new one out every year, sometimes two (personally I find writing books painful). Their latest publication is “The Definitive Guide To DAX” and it does indeed live up to its title. No-one outside the dev team comes close to Marco and Alberto’s knowledge of DAX, the language of Power Pivot, Power BI Desktop modelling and SSAS Tabular, and in this book they have documented everything that they know about it down to the smallest detail. Want to know what the KeepFilters() function does? Or the GenerateAll() function? How about all the new DAX functions and features in the latest versions of Power BI Desktop which will also appear in SSAS 2016 Tabular? They’re all here, and more. As such this is essential purchase for anyone doing serious work on the Microsoft BI platform, although probably more as a reference than a book to read end-to-end. It’s fair to say there’s a certain amount of overlap between this and some of their previous books on Power Pivot and SSAS Tabular, but the language – and the community’s understanding of it – has evolved sufficiently to justify buying this book too.

[I received a free copy of this book for review]

Buy it here from Amazon UK | US

‘M’ Is For Data Monkey

As the author of the only other book on Power Query, I suppose I should really be keeping quiet about “’M’ Is For Data Monkey” in case you buy it instead of mine. However 18 months of UI changes and functionality improvements mean my book is now a bit out-of-date, and what’s more important is that Ken Puls and Miguel Escobar have had the advantage of a lot of real-world experience with Power Query that I didn’t have (indeed no-one had) when I was writing in early 2014. The book itself is not a formal introduction to the M language but a guide to what you can do with it in Power Query; while a lot of what’s here will be useful in Power BI this is definitely a Power Query book and the target audience is Excel Pros rather than BI Pros. The decision to focus on Excel Pros was a good one to make, in my opinion, because it plays to the authors’ strengths and means that the book has a very practical focus. A lot of the tips and tricks here are ones I’ve used successfully myself, and I don’t mind admitting that I learned one or two things from this book as well.

Buy it here from Amazon UK | US

Other Books Are Available…

There are a couple of other new books out that, although I haven’t seen them, will also be worth checking out. Rob Collie has just released Power Pivot and Power BI, essentially the second edition of DAX Formulas For Power Pivot; Matt Allington has just released Learn To Write DAX; both are going to be good choices for Excel users wanting a DAX tutorial. Finally, last week Teo Lachev announced on his blog that he has published the world’s first dedicated Power BI book. Teo is another author whose books I admire so I’m sure it will be excellent, although I’ll be interested to see how he handles the problem of writing about a product that changes so much so quickly.

Power Query Book Published!

Looking for some summer holiday (or winter holiday, depending on which hemisphere you live in) reading? If so, may I suggest my new Power Query book? “Power Query for Power BI and Excel” is available now from the Apress site, Amazon.com, Amazon.co.uk and all good bookstores.

Power Query for Power BI and Excel Cover Image

It’s an introductory level book. It covers all of the stuff you can do in the UI, it has a chapter on M, and it goes into a reasonable amount of detail on more advanced topics; it is not a 500-page exhaustive guide to the product. I’ve focused on readability and teaching the fundamentals of Power Query rather than every looking at every obscure M function, but at the same time if you’ve already used Power Query I think there’ll be plenty of material in there you’ll find interesting.

Now for the bad news: the book is out-of-date already, although not by much. One of the best things about Power Query is the monthly release cycle; unfortunately that makes writing a book on it a bit of a nightmare. I started off writing in January and had to deal with lots of added functionality and changes to the UI over the next few months; I had to retake pretty much all of the screenshots as a result. The published version of the book is based on the version of Power Query that was released in early June rather than the current version. Hopefully you can forgive this – the differences are minor – but it’s a good reason to buy the book as soon as you can! I want to do a second edition in a year’s time once (if?) the release cycle slows down.

I’ve been teased a bit for blogging and teaching so much about Power Query recently, so the final thing I want to say here is why an old corporate BI/SSAS guy like me is getting so excited about a self-service ETL tool. Well, the main reason is that Power Query is a great piece of software. It does what it does very well; it does useful things rather than what the marketing guys/analysts/journalists think is hot in BI; it is easy to use but at the same time is flexible enough for the advanced user to do really complex stuff; it is updated regularly based on feedback from its users. I only wish all Microsoft software was this good… Honestly, I wouldn’t be able to motivate myself to blog and write about Power Query if I didn’t think it was cool, and even though it hasn’t been hyped in the same way as other parts of the Power BI stack it is nonetheless the part that people get excited about when I show them Power BI. It’s not just me either – every day I see positive comments like Greg Low’s here. I think it is as important, if not more important, than Power Pivot and I think it will be a massive success.

Oh, and did I mention that I’m also teaching a Power Query course in London later this year….?

“Expert Cube Development” Second Edition Available Soon!

Within a matter of days, “Expert Cube Development with SSAS 2012 Multidimensional Models” will be published. It’s the second edition of the very successful (19 5* reviews on Amazon US as of now) book on SSAS cube development that Marco, Alberto and I wrote a few years ago, updated for SSAS 2012.

image

You can pre-order it now from the Packt website, Amazon US or Amazon UK.

Before you rush off to order a copy, there are a three things I’d like to point out:

  1. This is basically the same book as the first edition with updated screenshots, a few bugs fixed, and several sections updated/expanded for SSAS 2012. There are no substantial changes. If you already have a copy of the first edition it’s probably not worth buying a copy of the second edition.
  2. The book only covers SSAS Multidimensional models, it does not cover SSAS Tabular models.
  3. This is not a basic introduction to building SSAS cubes – it’s aimed at intermediate-level SSAS developers who are already familiar with cubes, dimensions and MDX and who want to learn about best practices, design patterns, performance tuning and (most importantly) which features work well and which ones don’t. If you like the material I post here on my blog, you’ll probably like the book.

If you’re OK with that then by all means, go ahead and get your wallet out!

Microsoft SQL Server 2012: The BISM Tabular Model is now available to buy!

I decided to wait until I had a real, physical, made-of-dead-tree copy of it in my hands before blogging, but I’m pleased to announced that the new book that Marco, Alberto and I wrote on SSAS 2012 Tabular models is now available to purchase (even though, as Marco says here it was actually officially released a few weeks ago).

A sample chapter can be found here:
http://cdn.oreilly.com/oreilly/booksamplers/msp/9780735658189_sampler.pdf

You can buy it all all good bookshops, including Amazon UK. It has two five-star reviews on Amazon already, and Javier Guillén wrote a very detailed review here. Why not buy several copies so you can share it with your colleagues, friends, significant others, children, neighbours etc? It also makes ideal beach reading if you are currently on your holidays.

You may also have noticed there’s another SSAS Tabular book out, by Teo Lachev. Teo is an author I have the utmost respect for and I’m a big fan of everything he’s written; this book lives up to the high standards of his previous work. While it’s true there’s some overlap between his book and ours, the focus of his book is broader, covering topics such as Sharepoint, whereas ours has a narrower focus and goes into much greater detail on subjects such as DAX; so (again, as Marco says) you should probably consider buying both.