Power BI Migration Tools: What They Can And Can’t Do

Sometime last year, hundreds of people working at Microsoft BI consultancies around the world all had the same idea. Here’s what they were thinking:

I have so many customers wanting to migrate from legacy BI tools to Power BI. They are concerned that their current BI tool has an uncertain future. Licence renewals are looming and in the current economic climate organisations are looking to save money. Power BI is not only a lot cheaper than other BI tools, it’s a better tool overall and since Microsoft continues to make big investments in it then migration is clearly a no-brainer.

As a Power BI consultancy owner I have a problem though: I don’t have enough skilled people working for me to keep up with all this demand. What’s the answer? I know! Let’s build a tool that can help migrate all these legacy reports to Power BI!

The result is that, so far this year, I’ve seen or heard of five or six different Power BI migration tools built by various consultancies. That’s great and here are Microsoft we’re naturally supportive of our partners and want as many people to use Power BI as possible. I have reservations about some of these tools though, and these reservations fall into two categories.

First of all, some of the tools I’ve seen do things that are unsupported. In particular they programmatically generate .pbix files in an attempt to generate Power BI datasets and reports, and it is not possible to do this in a supported way. Generating datasets is certainly possible if you’re using Premium and making calls via the XMLA Endpoint but there is no supported way to automatically generate Power BI reports in this way at the time of writing. If you see a demo or run some tests, reports created this way will appear to work but there are no guarantees that future changes to Power BI will not break them. If that happens and thousands of reports in production suddenly stop working then you’ll naturally open a support case with Microsoft – and be told there’s nothing we can do to help. We know it would be great if there was a supported way to programmatically generate .pbix files and I hope that there will be one in the future, but for now this is the way it is.

Second, trying to replicate exactly what you did in your old BI tool in Power BI is not a good idea. This is a topic I wrote about in detail here but the point is that what was a best practice in your old BI tool may not be a best practice in Power BI: maybe your old tool liked wide flat tables rather than star schemas; maybe your old tool generated SQL queries against your data warehouse but in Power BI Import mode would be a better choice, and so on. As a result using an automated migration tool might give you quick results but result in more problems further on down the line.

I want to be clear that not all migration tools suffer from these problems. Most of the tools I’ve seen work within the boundaries of what is supported and leave a lot of room for consultants to make design changes. I’ve been seriously impressed by a few of them. What’s more, some of these tools do a great job on things like identifying what reports exist today, which people use them, which data sources they use and other things which are essential to the migration process, so they do add a lot of value. Thousands of organisations have already migrated to Power BI and here at Microsoft we know a lot about what makes a migration project successful, and good tools will certainly make migration quicker, easier and cheaper. The point I want to make is that if you’re building a migration tool or considering buying one you should understand what is and isn’t supported and which problems they can and can’t solve.