Root cause analysis in Power BI

Root cause analysis in Power BI

Microsoft Power BI has some great AI visuals which can provide an in-depth analysis of your data. In our last post we talked about an AI visual – Key Influencer visual. This visual helps in identifying factors that can impact an outcome. In that post, we analyzed what factors influence employee attrition. We also deep-dived into segments and clusters contributing to employee attrition with graphs and charts.

In this post, we will analyze and play with another AI visual – Decomposition tree.

Decomposition tree

The decomposition tree breakdowns a numerical measure into parts and analyzes what factors cause the measure to be high/low.

From Microsoft documentation:

The decomposition tree visual in Power BI lets you visualize data across multiple dimensions. It automatically aggregates data and enables drilling down into your dimensions in any order. It is also an artificial intelligence (AI) visualization, so you can ask it to find the next dimension to drill down into based on certain criteria. This makes it a valuable tool for ad hoc exploration and conducting root cause analysis.

Microsoft

Let’s take a well known example of employee attrition and understand why attrition is high. From the decomposition tree visual we plan to get answers to the following question:

What causes employee attrition to be high?

At the end of this post you will have an idea of how to use this visual for exploratory and visual analysis, decomposition of values by factors, and how you can use AI splits to dynamically split and understand the next factor for drill down.

Our final output could look like:

Getting Started

We install the latest version of Power BI Desktop, and click on the decomposition tree visual.

Power BI AI visual – Decomposition tree

You see two input fields “Analyze” and “Explain by”. In the Analyze field we put “Attrition %” and in Explain by we put several other fields say “Overtime”, “Department”, “BusinessTravel”, “MaritalStatus”, “Gender” etc. How to choose these fields in the first place? That’s a tricky question and we will answer this later.

Our decomposition tree when we drag Attrition % looks like:

Decomposition Tree with Attrition % metric

Attrition % overall is 16.12%. Our next step once we have added our metric is to understand:

  1. Which of the factors cause attrition % to be high?
  2. Which of the factors cause attrition % to be low?

Remember we have dragged several fields in “Explain by” section? Let’s click on the “+” sign next to the Attrition % bar.

You see the fields you have dragged. In addition, you see two more fields – High value and Low value.

Exploratory and Ad-hoc analysis

We begin with exploratory analysis by analyzing Attrition % by OverTime. Attrition % is 30.53% if OverTime is Yes. This means when OverTime is high attrition will be high.

OverTime

Let’s expand this level and understand when OverTime is Yes then what’s the next factor which contributes to attrition%? Let’s explore Marital Status.

Martial Status

Attrition is high among unmarried individuals and these are the ones who over time. Let’s try adding another level to this analysis, say Department.

Department

Unmarried individuals in the Sales department who over time contribute to 65.31% attrition! We can also verify this number by adding tooltips.

Out of 49 employees in Dept Sales with Marital Status Single and OverTime Yes, 32 of them left the company.

What if we start our analysis, not with OverTime? Let’s pick monthly income as the starting factor

Monthly Income

The visual flow is quite different here! Attrition is highest when monthly income is low and in the Sales department when OverTime is High.

With the decomposition tree, you can perform root cause and exploratory analysis by playing with the multiple factors and dimensions. You not only get a deep understanding of what’s happening in your data set, but you can also visually understand the data in a tree format.

AI Analysis

We started analyzing the factors based on our domain knowledge and understanding of the dataset. What was our rationale for choosing OverTime as the starting point of our tree?

The decomposition tree comes with another option to split the tree using AI algorithms. Remember we had two more options in our tree “High value” and “Low value”? It’s time to utilize them.

Let’s start with a blank slate and this time instead of selecting OverTime, let’s select “High value”.

AI Split

As we keep selecting High value at each level of the tree, the algorithm identifies the next level on its own. In the example above the levels chosen were Monthly Income followed by OverTime, Education Field and JobSatisfication. Attrition % is high when monthly income is between 0-2800, and so on.

In AI splits you see a bulb icon next to the level name. Once you hover on the bulb icon you get to see why this level was chosen.

On hovering the bulb icon

You can also select “Low value”. Once you select the low value you will observe that the factor and analysis changes.

A low value split

How to choose fields in “Explain by”?

Should we choose AI split or manual split?

How do we choose the fields in “Explain by”?

The best way to start analyzing the tree is using manual split based on the domain context and your understanding of the data. After 2-3 levels of manual split, you can then split the tree further using AI splits and understand the factors responsible for making a metric high or low.

There’s also a smart alternative to this. You can use “Key Influencer Visual” to understand what factors lead to Attrition = Yes. The visual will provide top factors impacting an outcome (attrition = yes), and you can put those factors in “Explain by” section of the Decomposition tree. When you run key influencer analysis on the employee attrition data set you will get the results as explained and shown in the previous blog post.

Power BI Key Influencer AI visual

You can put Age, OverTime, JobLevel, MonthlyIncome, YearsInCompany and others in the Explain by section of the decomposition tree visual and start drilling down the data.

Conclusion

The decomposition tree is a smart visual to breakdown a numerical measure into components. This AI visual aids in root cause and deeper analysis as shown above. You can perform ad-hoc analysis for the problem in question, understand the breakdown of values using manual and AI splits, and combine it with other Power BI AI visuals to strengthen your analysis.

One last note: to get the best of the output and results from this visual, you may want to convert numerical attributes like age, income, etc into categorical values (or bins – Example above: monthly income is broken down into 0-2800, 2800-5000 etc. bins).

PS: AI splits in the decomposition tree comes with two analysis mode: absolute and relative. We will cover this in detail in next blog post.

Next steps

If you are looking to explore the possibility of applying AI in your dataset or looking to evaluate the use of Power BI in your organization, don’t hesitate to contact us today.

Power BI Embedded Licensing

In our last post we talked about Power BI Pro and Power BI Premium. To recap, Power BI Pro is a per use license and is more towards content creation and consumption. On the other hand, Power BI Premium is a capacity license and is more for content consumption.

Rather than assigning a Pro license to every individual in your org, you can assign a premium capacity to a workspace to support large number of content viewers.

In this post we will tackle another Power BI offering – Power BI Embedded.

Power BI Embedded

Power BI Embedded is a Microsoft Azure service that lets independent software vendors (ISVs) and developers quickly embed visuals, reports, and dashboards into an application. This embedding is done through a capacity-based, hourly metered model.

Microsoft

Power BI Embedded is an offering by Microsoft where you can embed Power BI visuals, reports, and dashboards in a custom application or in associated Microsoft Services like Teams or SharePoint Online.

How does Power BI Embedded look like in reality?

Ok, here’s a screenshot of a Power BI report embedded inside a custom application. By custom application I mean an application which is not app.powerbi.com. It can be a plain vanilla website or a WordPress website or can be a heavy application with Reporting and Analytics section.

In the screenshot below, the sections highlighted in red are part of the custom application. The “SALES PERFORMANCE REPORT” or the part highlighted in green is the Power BI report securely embedded in the application.

You can embed a visual, report, dashboard and Q&A. We have used “report” as a general content for embedding. But the description apply to any of the contents.

How can I embed a Power BI report?

There are 3 ways to embed a Power BI report.

  1. Publish to web. Simplest (and not secure) way of embedding is publishing your Power BI report to web for public access. Note: Anyone with the URL will have access to your report.
  2. No-code Embedding – Simplest and secure. This approach gives you a secure URL to the Power BI report which you can put in your application. However, this will prompt you for Org authentication.
  3. Custom Embedding using JavaScript SDK. This gives you full power of embedding capabilities. You can read more about how to embed using JavaScript SDK in our other blog post.

What licensing do I need to support Power BI Embedding?

Now, that’s a tricky question. For embedding you can choose – P SKU, EM SKU or A SKU.

The licensing to go for really depends on your specific scenario. The general answer to choosing the SKU is “where” the content will be consumed.

  • If the Power BI content will be consumed in a custom application then choose A SKU**
  • Choose EM SKU if the Power BI content will be consumed in Teams or SharePoint online (SPO).
  • Choose P SKU if the Power BI content will be consumed in a custom application or Teams or SPO.

**You can even choose a P SKU if you are an enterprise or a Large ISV.

The P SKU is an umbrella SKU which not only gives embedding capabilities but additional feature sets including large read users, AI features, and other enterprise features.

Typically, enterprises go with P SKUs.

How are P, EM and A SKUs different in terms of performance?

Here’s a quick summary of each of the SKUs node performance:

Image source: Microsoft

So an A4 node is same as a P1 node in terms of performance.

It is generally suggested to start with A1 to test and benchmark your capacities, and then take necessary steps to increase the capacity.

Is Power BI Embedded free to use?

No. For production workloads you have to choose from either of the licensing types. For dev workloads, yes you can embed without purchasing a capacity. However, you may hit the token limits and reports may not render.

Quick URLs for further reading

Power BI Embedded Capacity: https://docs.microsoft.com/en-us/power-bi/developer/embedded/embedded-capacity

Developer Samples: https://github.com/microsoft/PowerBI-Developer-Samples

Power BI Embedded Playground: https://microsoft.github.io/PowerBI-JavaScript/demo/v2-demo/index.html


I hope this post provides some understanding on Power BI Embedded, licensing and consideration for choosing this type of offering.

Next steps?

If you have any questions on this or want to explore Power BI Embedded for your organization, do contact us today.

What we tell our customers when migrating to Power BI

Power BI has seen viral growth. It is one of the industry leaders in BI and Analytics Platform. One of the reasons for such growth is its integration with existing Microsoft investments. We would say, it’s easier and cost-effective to get started with Power BI.

Naturally, everyone would want to migrate to Power BI. Be it from Tableau, Google Data Studio, or Excel or SSRS or Telerik/HTML/Javascript charting libraries. There are few patterns that have emerged as we help customers migrate to Power BI.

Following points are a must read if you are looking to migrate to Power BI

Mindset

First and foremost, migration to Power BI needs a change in your mindset. You have been using your old tool for quite some time now. You expect Power BI to mimic its functionality and behavior? No, it may not. Power BI is a Modern Enterprise BI tool. With modern approaches to data modelling, data consumption, sharing and governance, Power BI pushes you to think beyond traditional tools.

Don’t Mimic Excel

For laymen, Power BI is Excel++. They expect to modify a “cell” in Power BI and hope to see changes reflected in Power BI. Wait, wait, wait! Power BI is not Excel nor a replacement for Excel. Power BI gives much more power to data extraction, modelling, and data storage. Don’t expect Power BI to work like Excel. Excel is best suited for calculations, manipulations, and tabular/visual reporting. Power BI is a modern BI tool with storage, compute, data flows, AI, governance – well integrated within the platform. Don’t worry, you can still use Excel with Power BI with “Analyze in Excel” feature. For enhanced functionality, you have Power Apps, Power Automate, etc.

Model driven tool

“Can I fire a dynamic SQL query based on filter selections and user login in Power BI”? Charting libraries use this concept quite often. Please note: Power BI is a model driven tool. There is a model behind which powers the visualization and analytics. You need to start thinking about models and relationships when working with Power BI. You can import the data in Power BI or connect directly/live with supported data sources. Once done, you can slice and dice they way you want.

In Active development

Power BI is a tool that is in active development and growth. There can be some features that are not yet available, but that doesn’t mean you will stop leveraging the power of this modern tool. Power BI has a monthly release cadence and they also publish a roadmap so you know what’s coming in future releases.

They just announced some fantastic updates in MBAS 2020.

A Product for all size needs

Power BI is a suite of products.

  1. Power BI Desktop for authoring and creating reports,
  2. Power BI Online for data distribution, consumption, governance, and management,
  3. Power BI Embedded for embedding in your website,
  4. Power BI Premium for enterprise needs,
  5. Power BI Report Server for on-premise needs,
  6. Power BI Report Builder for paginated/tabular reports (SSRS types reports) and
  7. Power BI Mobile for on the go needs.
  8. Plus, integrations with Excel, SharePoint, Teams, Power Apps, Power Automate, AI, Azure Analysis Services, and many more etc.

We can pickup one or more tools depending on your needs. Power BI has definitely come a long way. Many organizations are taking advantage of this modern BI tool. Why not you?


How to get started?

Here are simple steps you can take to get started on your journey to migrate to Power BI.

  1. Get your current data estate and BI systems audited
  2. Prepare a high level modern BI architecture, roadmap, and transition plan
  3. Implement and rollout in phases
  4. Train and educate your staff/audience
  5. Administrative Support

FAQs

What Licensing options does Power BI provide? Check out our latest blog on Power BI Licensing Guide.

What Features does Power BI provide? Check out our latest blog on Power BI Features – End to End.

Can I do real time reporting with Power BI? Yes, why not. Check this blog: Real time reporting with Power BI.

Can I connect directly to my data sources?

Are there any cases where customers have moved away from Power BI? Yes, sometimes, we will blog this soon. Do subscribe to know when that is released.


Contact us, if you want to get your BI and data estate audited. Or signup for one of our services to migrate to Power BI.

Or, use this form below to get started.

Our first impression of Tableau – intuitive, powerful, beautiful

#PowerBI experts, Crush on #Tableau

Power BI and Tableau has been ranked as leaders in Gartner’s Magic Quadrant for Analytics and BI 2020. It becomes critical for businesses to choose between one of these solutions for their analytics and BI workloads.

Of late, we have been receiving a lot of questions and interests in choosing BI tools. Recent trends show that companies are open to Power BI or Tableau as long as their needs are met within budget.


We are open to choosing and suggesting BI tools to our customers. We decided to give a try to Tableau.

Our first impression of Tableau

Tableau is intuitive. Tableau is quick. Table is powerful. Tableau is beautiful.

Tableau’s power is its data visualization capabilities.

You need a dual axis chart with bars and lines? You got it.

You need a dumbell chart? You got it.

You need advanced analytics? You need greater control over tooltips? You need annotations?

You got it!

Looks like Tableau was built ground up with data visualization in mind. And, it’s correct. Tableau founders were from graphics and visualization background.


How does it compare with Power BI?

After our hands on with Tableau we can say Power BI visualization capabilities though familiar, are limited. You have the same decade and century old charts.

You can be creative with visuals but then you need to learn Typescript and Node.js. You can create new and advanced charts but development and learning curve is high.

How about modelling capabilities?

Power BI excels in data modelling capabilities. DAX and multi-dimensional concepts are inbuilt in Power BI.

Tableau lacks much of these. Minimal multi-dimensional support, no functional language like DAX. Tableau relies on calculation scripts, LOD etc.

How about Dashboards and Reports on canvas?

Tableau has concept of Sheets and Dashboards. Power BI has concept of Reports and Dashboards.

We feel Power BI wins here as you have greater control over the layout and structure of your reports. You can create Business dashboards quite easily in Power BI.

What about Publishing options?

With Tableau you can publish to a Tableau server or Tableau online. Same with Power BI.

However, Power BI service (or Power BI online) though looks cluttered sometimes, is quite powerful and feature rich. Navigation can be confusing. It has a concept of workspaces.

Tableau online is clean and has simple interface. Admin capabilities and options look limited. It has a concept of projects. Great UX.

What about cost?

To a large extent Power BI wins here. Power BI Desktop is free. Power BI Online is free for personal use. This makes Power BI quicker to adopt and penetrate within your org.

Tableau unfortunately is not free. It’s costlier than Power BI. Though Tableau has a free version called Tableau Public, its functionality is limited.

Note: We need to evaluate cost in a larger scheme of things keeping view of number of creators, number frequent and occasional users.

How is the support for developer tools?

Microsoft is known for developing products for extensibility. Power BI wins here with APIs to manage everything you need. Power BI lacks APIs for designer and modifying models.

Tableau has some support for tools but it’s limited.

What do you recommend?

Power BI and Tableau, both are great tools. However, recent job trend shows greater demand for Power BI developers.

To sum up: Power BI is a great end to end BI tool. Tableau is a great visualization tool.

We recommend to check your existing BI investments, and BI and analytics needs. Based on this and your budget, take a call.


Given high demand for both the tools, we are working on creating a comprehensive guide comparing Power BI and Tableau. We will cover in detail their BI capabilities, Data Integrations, Enterprise support, APIs and costs with screenshots from both the tools.

Join our list to be the first one to know when the guide is available.

Do you want us to evaluate which BI tools fit your business needs? Contact us now.

Power BI Premium or Power BI Pro – the answer is here!

Power BI comes with multiple licensing model

  1. Power BI Pro
  2. Power BI Premium
  3. Power BI Embedded
  4. Power BI Free
  5. Power BI Premium Per User (new!! – read the post here)

In this post we will cover Power BI Pro and Power BI Premium licensing model.

The licensing model to go with is determined by following three factors:

  1. Cost
  2. Number of users (creators, viewers, occasional viewers)
  3. Features required

The first two factors are the most critical in deciding the licensing model.

It’s a choice between multiple Pro licenses or multiple Premium licenses.

What is Power BI Pro/Power BI Premium?

A Power BI Pro is a per user license currently costing around $10 per user per month, while Power BI Premium is a capacity license currently costing around $5000 per capacity node per month.

Yes, the cost difference is huge. But, wait, there are lots of things hidden in that $5000.

  1. Power BI Premium is a capacity license. It can support 450 users report viewing needs (see example below)
  2. Power BI Premium is for content consumption rather than content creation
  3. Large number of external readers (out of org users with no Power BI license)
  4. AI, Paginated reports, XMLA read/write and many other features
  5. Note: With 1 Premium capacity node you get 8 cores, 25 GB RAM and 6 parallel refreshes.

What does all this mean?

If you want to create, author and publish reports, you definitely need Power BI Pro licenses. You cannot get away with that. Whether to go with Power BI Premium or not, it depends.

Scenarios

Say, if you have 500 users in your org and out of 500 users

  1. 50 users will be creating content
  2. 200 users will be frequently accessing the content
  3. 250 users will be occasionally accessing the content

Then, you require

  1. 50 Power BI Pro licenses
  2. 1 Premium capacity node

With the premium capacity node we can serve the “consumption” needs for 450 users.

How did we come up with that conclusion? A simple Power BI Premium calculator is available to help us decide number of licenses (link below).

But, say your org has 100 users with 50 creating content and 50 viewing, it’s recommended to go with 100 Pro licenses (total cost $1000 per month) than a premium capacity node unless you need additional features like AI, external readers etc.


Power BI Premium vs Power BI Pro – Which licensing model should I choose? The answer is here!


Next steps?

If you are still not sure of the licensing model or worst, if you are not sure if Power BI is fit for your organization’s BI needs then you may request a free consultation.

You may fill the form below or directly setup a call

Or, fill up this form and we will get back to you with time slots within 12-24 business hours.

Notes

Power BI Premium Calculator: https://powerbi.microsoft.com/en-us/calculator/

Power BI premium also comes with additional feature sets including AI, Incremental refresh, Power BI Report Server, Paginated (SSRS types) reports, XMLA read/write and others – or better to say Enterprise features.

Power BI Pro vs Power BI Premium

If you need a quick comparison between Power BI Pro and Power BI Premium feature sets, please check this table provided by Microsoft. (Click the image to view the entire table)

https://powerbi.microsoft.com/en-us/pricing/#powerbi-comparison-table