In the previous post I’ve given you the specs, the costs and some of the more abstract things. Now it’s time for a case study.
Providing external customers with data
Now lets tackle a very specific case in which you are already mixing and matching technologies of the Microsoft stack and are seeking to improve the mix. Ideally you would want less components, keep all existing features and add new ones, without paying (significantly) more.
You already have a solution in place, which works fine. But you are always looking into improving it keeping it up to date and as future proof as possible.
Your current architecture looks like this:
SQL -> AAS -> Power BI -> Power BI Embedded Premium
Why do I want to look at this specific case? Well, while reading up on a lot of things related to the Power Platform I get the very strong impression that Microsoft is aiming it to be a one-stop shopping platform for all your BI and reporting needs. And the aforementioned components are some quite basic building blocks that you would find in most solutions.
Power BI Embedded will probably not be widely used in these types of solutions. But I use it in my blog, so I wanted to include it so I can see how it could fit in the big scheme of things. So there :-P.
First stop: features needed
You typically choose your technology based on the needs of what your trying to build or provide for your client. In this case it’s quite straight forward:
- You need SQL for data storage and some preprocessing before the data gets into the data model on AAS
- You choose Power BI (and Power BI Embedded) since it’s a tool that provides lots of options of presenting data to clients. And is very easy to use, since it doesn’t necessarily require programming skills if the data (model) that it connects to has everything you need. Lastly, since you’re already running on Microsoft products, using it would require less integration pains. Since you want to potentially display these dashboards and reports to customers outside of the Power BI Service environment, we need Power BI Embedded.
- No building reports with lots of data in Power BI directly on SQL works perfectly fine. But if the report needs calculations that aren’t present in SQL, you need to enrich the data with these calculations (enter DAX and depending on your connection M Query). Plus chances are the reports will load quite slowly, since SQL’s strength is data storage not performance. So to tackle both the performance and extra calculations issue, we often introduce AAS. It boosts load performance, and packs a punch when you need lots of new calculations.
Since we’re interested in whether Power BI Premium can replace AAS, lets take a closer look at what features we need in order to determine whether the shift can happen at all.
- Is being fed from views in SQL
- Contains some calculated columns, primarily to build up hierarchies (such as DateTime)
- Contains a number of tables in Star Schema
- Contains a number of measures needed for reporting
- Contains some logic on which version of the calculations is needed depending on input filters
- Has Row-Level Security
Second stop: pricing
Third stop: migration effort
I’ve done a ton of research and tested some things on my on Azure account as well. And migration from AAS to Power BI Premium can vary between a just a few clicks to a lot of work. I do have some basic strokes to share, but that will constitute that this blog post will be longer than planned. So I’ll update this post soon, which a link with a guide to help you through this process.