Power BI for Business Analysts – Where do I start?

As a business analyst, what exactly should we know to be able to convert the requirements of end users into Power BI reports? What should we learn to be good at using Power BI?

These were questions directed at me by two business analysts at a recent meetup. They were getting into Power BI in their organization, and were given the task of converting requirements of several business stakeholders into analytical reports for BI purposes. I promised them a few links to help them learn. However, links without context would not be fair. Hence this post, with context and guidance on how business analysts can get started with analytics.

When it comes to business intelligence, the traditional role of business analysts in software houses cannot be used to define the business analyst who is deemed to work with data. They have a whole new, dynamic and a more interesting role to play. In a nutshell, this business analyst would need to understand the problem that the stakeholder or end users have and come up with a solution to solve that problem with data. For example, the end user, the director of sales sees that targets for sales are not being achieved nation-wide. They need to be able to see where things are going wrong, and what may be causing the problem. To solve the problem, the business analyst (BA) will have to collect the relevant data, and use a tool to churn out insights from this data easily and efficiently, and then present it to the end user for business direction.

Typically a BA would need to:

  1. Understand the data. Know what and where the right data lives, then, most importantly, understand the data: What information is stored, why it is stored the way it is stored, what are the rules associated with the data and so forth. Basically, the relevant data must be understood as if it were their own.
  2. Prep the data, and mash it up into a data model that represents the business process(es). Data needs to be pulled in from their multiple sources, cleaned, structured and stored in such a way that is intuitive for business users to understand. This is essentially the data-discovery aspect of things.
  3. Derive insights from the data by putting up visual reports and dashboards to tell stories of what is going on with the business. This is essentially the self-service aspect of things.

One of the first things you would want to do as a BA, is get to know the concepts of Power BI, and how it can be used as a business analyst’s tool. The best place to start would be Power BI Guided Learning: Getting Started.

Then comes understanding the data, which is something a business intelligence tool cannot help much with. This would be a task that mostly involves discussions and research involving the owners of the various systems, and finally documenting the findings of the source data. Power BI can come in handy at a later stage of this exercise when you examine and evaluate the data.

Prep Data

Once all the questions are asked and responses documented, you get into the next step of prepping and modeling the data. The easiest would be to bring in all the required data into a flat structure; one great table that has all you want to build visualizations and reports. However, this approach would not work beyond a certain level. When you bring in more than one business process such as sales invoicing and sales planning (targets) together, due to non-separation of common data elements, comparing sales against targets would be difficult, especially when granularity comes into play.

Learning a data modeling technique, such as dimensional modeling will greatly help with structuring the data into an easily consumable piece. Dimensional modeling is a relatively simple technique to learn. Of course it becomes a little complex as you get into the nitty-gritties of some of the concepts, but in most cases, at least in the context of business analysts it wouldn’t come to that.

Model data

A star schema would not be quite enough if you do not build upon it and create the measures you want for your analysis, for example you may already have a sales value measure which comes from the star schema, but if you would want to make more sense of it you would probably require a Last Year Same Period Sales measure and a Sales Growth % measure to better illustrate the state of current sales. Creating these measure on the model that you created would require a little more technical know-how. However, assuming that a business analyst already works with Excel including its functions and formulas, learning the DAX (Data Analysis eXpressions) language to formulate measures won’t be too hard. When it comes to self-service, your data model does not have to be the best representation of the business process(es), but the better it is modeled, the easier it is to derive insights.

  • Start with Power BI Guided Learning: Modeling to learn how to build relationships and enhance the star schema with analytics
  • Then, spruce up your technical prowess with Power BI Guided Learning: Introduction to DAX

Visualize data

Once the data model is done, the next step is to start on self-service analysis of the data. Self-service involves asking questions off the data, and depicting it on a canvas, and creating a story out of it. Not everyone can ask questions of the data. You need to know the business processes and the data in order to do that. And as a business analyst it becomes your job to know the data. This would allow you to ask the right questions off the data, in the right context and build a story out of the answers that you get.

  • Start with Power BI Guided Learning: Visualizations to get an understanding of Power BI visualizations.
  • Use this as a guide when determining which type of visual to use for what purpose: Extreme Presentations: Chart Suggestions – A Thought-Starter
  • Then go further on exploring data with Power BI Guided Learning: Exploring Data
  • Finally, these real-life stories can be used to inspire and provide guidance as to how you can tell stories: Power BI: Data Stories

One thing about visualizing data is that it is part-science and part-art, hence additional reading, practice and experimenting is encouraged.

Publish and share

Once you’ve built your story, which would be in the form of dashboards and reports in Power BI, you need ensure they are shared with the relevant stakeholders, feedback taken, and the solution refined until proper business value is seen.

Further learning

Once you’ve gone through the links above it would be a good idea to take some time and complete the following EdX course, which would give you a holistic and practical learning of Power BI: Analyzing and Visualizing Data with Power BI and then a read of Reza Rad’s (b|t) online book on Power BI:  Power BI From Rookie to Rockstar which will give you a real world feel of how you could you use Power BI. You might need to invest a few weekends for this, but it’s an endeavor that is quite worth it.

Finally, to top it all off, consider certifying yourself. Being certified gives you the validation that you are technically thorough in the subject area. The MCSA: BI Reporting cert is the ideal certification for a business analyst to to showcase their expertise in business intelligence and analytics; it just takes two exams to gain the certification.

Conclusion

Business analysts are expected to be technically savvy, especially when it comes to working with data. Just as you would expect a secretary to know office productivity tools nowadays, as compared to 10 years ago when it was only desirable for them to be Microsoft Office-savvy, it is expected that a business analyst too be tech-savvy about data. Knowing how to work with data, what tools to use, how to use the tools, the concepts behind working with data, and finally an endorsement of this knowledge will be key to excel in this field.

Note to all Power BI experts: Better and additional links to free learning resources would be helpful to keep this post up-to-date

Advertisements

Getting Started on Departmental BI with Power BI + O365

Getting into business intelligence is like a three-year-old getting ready to learn to swim, or take up ballet, maybe. You really don’t know what you are getting into. Unless of course you have someone with the related expertise involved in the initiative; then it would probably be like an eight-year-old getting ready to learn to swim, or take up ballet, maybe.

Getting into business intelligence is a big process, involving a lot of money, planning and effort, especially if you need to get it started for the entire organization. And then you have the added headache of ensuring that the relevant important people are all on board.

There is also another scenario, where a few individuals realize the potential of BI, whereas the rest of the organization is oblivious to the idea, or simply do not see the value. Yet.

If you are one of those individuals looking to get into BI, then departmental BI is probably the best way to go. It’s quicker and allows you to get to business value faster, thus allowing you to shape your department’s business direction better and be the pioneer of your organization’s BI initiative. And you would probably only need the blessings of those running the department to get started; Maybe you are one of those persons who gives the blessings even. Either way, getting started with departmental BI (i.e. business intelligence for a specific department, owned by that department) allows you to drive your department’s business better, and spearhead the drive for business intelligence across the organization in the future.

Leaving the preaching aside and getting technical, one of the most popular office productivity suites is Microsoft Office and in its evolution, Office 365, which is a set subscription-based office productivity + related services. Office 365 is quite prevalent across many organizations mainly due to Excel’s dominance for a very long time in the spreadsheet market, extensively used for number crunching and analysis purposes. Coupled with SharePoint Online, which allows for online access of these files as a service gives users the agility and flexibility to work on Excel wherever they are. Plug in Power BI into the mix, and you’ve got a real good recipe to get departmental BI going.

Initial Architecture
Initial Architecture for Departmental BI

The above architecture is a simple one to start off with. Here, relevant data sets are extracted in the form of flat files on a regular basis and uploaded to a SharePoint folder. A data model is then created off the data stored on SharePoint using Power BI, and reports and insights created on top of this. Users can then evaluate the value the model and visualizations provide them, and provide feedback based on an iterative approach, until a general consensus is arrived upon that BI is working for them.

The above, of course, would take a a few weeks to a couple of months to perfect, and for value to be seen. After which, integration with the source systems can be thought out. Until then we have a very cheap solution, which is server-less.

I will follow up this post on a later day on the technical how-to of the above solution.

What’s the difference between Live Connection and DirectQuery in Power BI, and where do they fit?

Hot on the heels of the question I was asked a few days ago, comes another closely related one: “What’s the difference between Connect live to a data source and DirectQuery a data source in Power BI?”

We had already established that there are two methods in which we could interact with data using Power BI: loading data into Power BI and accessing the data source directly.

Connecting live and DirectQuery both fall into the latter method, but there is a difference.

In DirectQuery mode, you access the data source, such as a relational database or data mart for data, but then you would create calculated columns or measures on top of it in Power BI generating a data model layer, something similar to a database view, if you may. The data still exists at the data source; but is pulled through the data model on Power BI onto the the visuals. The end users and report creators will see and interact with the data model on Power BI.

In the case of Connect live, the data model itself is at the source, you interact with it directly and no data model layer is created on Power BI. All measures, calculated columns and KPIs are provided by the data model at the source, along with the data. End users and report authors will see and interact with this data model through Power BI.

If you would compare these two methods on a conceptual level; DirectQuery mode is used in cases of self-service where you have data marts or a data warehouse on a relational database, and business users build their own data models off this for their business needs. The data marts or data warehouse will integrate data from various systems, and provide base measures with related dimensions.  Business user may create custom measures and calculated columns on top of this to suit their reporting and analytic requirements, and then explore data and build visual reports. Think of this as the data discovery phase of the self-service exercise.

Live connections would probably be used in scenarios where the analytic needs are better understood, and/or the type of analytics that were described above have matured and has become a mainstream in the organization. Here data models are built off the data warehouse using Analysis Services (multidimensional or tabular), with measures, calculations and KPIs that were earlier part of the self-service (and the data discovery exercise) incorporated in it. Business users now have established reports and dashboards that showcase organizational performance powered by established data models. Think of this phase where things have evolved into corporate BI that gives real value.

[SUBJECT TO CHANGE] Out of the whole bunch of supported data sources, Power BI currently supports the following in DirectQuery mode:

  • SQL Server
  • Azure SQL Database
  • Azure SQL Data Warehouse
  • SAP HANA
  • Oracle Database
  • Teradata Database
  • Amazon Redshift (Preview)
  • Impala (Preview)
  • Snowflake (Preview)

and the following using a Live connection:

  • Analysis Services Tabular
  • Analysis Services Multidimensional
  • Azure Analysis Services (Preview)

Azure Analysis Services

We’ve all seen how the world of data has been changing during the recent past. Many organizations have massive amounts of data. And many of them are running out of space to put them in. So naturally they turn to the cloud to store and process all of this data. The processed data can then be used for gaining insights in various ways. Apart from the popular forecasting and machine learning that is becoming a fad these days, there is a lot of traditional and “business” analytics that businesses still want to see. Business users want to dive into their data and perform self-service analytics and do data discovery.

However when you looked at the space on the Microsoft cloud, along with its data and analytics capabilities, you have the tools and services to store and process large amounts of data, but what you did not see was something that you could create a analytical model out of so that business users could easily consume as part of their business intelligence routine. Of course you had Power BI, but that was more of a next step, plus Power BI is lightweight and cannot handle more than 10GB.

The closest we had, on the cloud, was to build an Azure VM with SQL Server installed on it, and build the analytic model using Analysis Services. But then there was licensing, and the maintenance overhead among other things that did not make it a feasible option in a lot of cases.

And then Microsoft announced Azure Analysis Services a few months ago, a fully managed Platform-as-a-Service for analytic modeling. And suddenly there was hope. You no longer needed to write complex SQL against a SQL Data Warehouse, nor did you have to import processed data in its hundreds of thousands into Power BI to create your own analytic model.

Azure Analysis Services is currently in its preview phase, and hence Microsoft has given it only Tabular capability for the time being, with Multidimensional hopefully coming some time later. In my opinion that is just fine. One more thing though, if you would remember, the on-prem version of Analysis Services uses Windows Authentication only, in other words you needed to be on a Active Directory domain. So on Azure, in order to access Azure Analysis Services you need to be on Azure AD.

Let’s take a look at quickly setting up your first Azure Analysis Services database.

Creating a service instance is the usual process: Type in “Analysis Service”, and you would see it showing up in the Marketplace list:

Marketplace List Analysis Services
Analysis Services on the Marketplace

Once you select Analysis Services, you would see the Analysis Services information screen:

Analysis Services information screen
Analysis Services information blade

And then all you need to do this supply the configurations/settings and you are done:

Analysis Services Create blade
Settings/Configurations blade

When in Preview

At the time of writing, Analysis Services (preview) is only available in the South Central US and West Europe regions, so make sure that your resource group is created on one of those regions. The preview currently offers three standard pricing tiers, and one developer pricing tier (at an estimated cost of ~50 USD per month). The developer tier with 20 query processing units or QPUs (the unit of measure in Azure Analysis Services) and 3GB  of memory cache, is ideal to get started. More info on QPUs and pricing here.

Identity Not Found error

Another problem that I ran across was the “Identity not found” error that comes up a few moments after I click on the “Create” button, and Azure starts provisioning my service. It claimed that the user specified under “Administrator” cannot be found in Azure Active Directory, even though I did create such a user in AAD. The reason for this and how to resolve it is documented nicely here by @sqlchick. If you need further details on how to get your Office 365 tenant linked with your Azure subscription while integrating your Azure AD, you should definitely look at this.

Once provisioned, you can pause Analysis Services when it is not being used so that you could save dollars, while switching among pricing tiers is expected in the future.

Azure Analysis Services running
Analysis Services running

Cortana Intelligence Suite – Not just an intelligent personal assistant

Everyone knows Siri. Well, at least those who own an iPhone do. Then again that amounts to almost everyone. So I guess everyone does know Siri. A couple years ago however, we were introduced to Cortana (named after Microsoft’s Halo game character) on Windows Phone. She soon found her way onto Windows 10 devices, and is now even made available for iOS and Android devices. Think of her as something of a primitive version of Scarlett Johansson’s character in Her, if I may. She is Microsoft’s own intelligent personal assistant, and I use her almost everyday.

But what I am going to introduce you to in this post is something bigger. Much much bigger. It’s called Cortana Intelligence Suite; Microsoft’s Azure-based analytics and intelligence platform. Don’t panic just yet! D-Day hasn’t begun, nor have the machines risen, but I bet you can well imagine what Microsoft must be aspiring when they named it that.

Cortana Intelligence Suite (CIS) is a platform and process for building end-to-end advanced analytical solutions. The platform is made up of various tools and services, mainly running on top of Azure. It is not necessary that all the tools that make up CIS be used for your solution, but it is important that you follow a certain process to get it done. This way you know what you are going to do in a methodical manner, and it would be easy to choose the right tools and services from the suite to get things done.

The process that CIS solutions follow is known as Cross Industry Standard Process for Data Mining (CRISP-DM), which is a long-used time-tested means of performing data mining (which is very closely related to analytics, and is often employed to perform analytics).

CRISP-DM
CRISP-DM

For a complete overview of CIS and how it can be used, this easy-to-understand video by Buck Woody would be the ideal choice. It also details the tools and services that are part of the CIS platform. Once you are done with the video, you would probably get the gist of how Cortana fits into the business intelligence and analytics vision of Microsoft. Enjoy!

[VIDEO: Cortana Intelligence Suite – Overview by Buck Woody]