Something happened on the way to Analytics

I just spent the week onsite with a client’s business intelligence team. Apart from the amazing cultural experiences after each day’s work, the experiences of working with a corporate technology team gave me hitherto unseen experiences from their perspective. The client was launching their new ERP system, and were starting to building an analytics system on Azure that would service the needs of the business users with information from this new ERP, and other systems.

Working as part of a BI technology solutions partner in the long run morphs your way at looking things: technology first, trying to fit client needs to the types of solutions we do best; assuming that all tech people will be comfortable with the next best thing in technology; assuming that business users will always know the first thing about BI; Your architecture is always good.

Technology first

Despite knowing, understanding, and architecting a solution for a client’s business needs, the zeal to implement the solution using the latest and greatest technologies is overwhelming and is even “believed” to be the need of the hour. Very often the solution itself is pre-designed in my head with new technologies.

Let’s say that an organization has indeed gone ahead and invested in a new platform such as Microsoft Azure as their initiative to put everything on the cloud. That should not be an automatic signal that they are ready to adopt everything Azure has to offer despite those being part of the package. They would rather work with the technologies that are closely related to what they are familiar with. So when I jumped in all guns blazing yelling out “Data Lake, Data Lake and Power BI, Power BI”, I should have rightly expected them to have put up their hands and say “hold up!”, so it was a good thing that I didn’t even whisper “Databricks”.

Being a consultant and evangelizing in technologies is good, but what is important when consulting with a team which has been working on traditional technologies for quite sometime, and also having had success doing it, the need for talking them through the paradigm shift in the way we do analytics, and guiding them to the new set of technologies plays a vital part because “they are not a test-bed for Microsoft technologies”, and can be easily misconstrued as such if I seem to be forcing different technologies down their throats…

Names are misleading

When Azure SQL Data Warehouse was chosen to implement a multi-dimensional data warehouse, it may have seemed like the ideal choice. Why? because it was plain to see: keywords: “SQL”, “Warehouse”. However, no, SQL Data Warehouse is ideal only when you have data loads that are quite high, not when it is only several 100GBs. Armed with a few more reasons as to why not (A good reference for choosing Azure SQL Data Warehouse), I had confronted them. But the rebuke then was that they did get good enough performance, and that cost wasn’t a problem. Until of course a few months later when complex queries started hitting the system, and despite being able to afford that cost, the value of paying that amount did not seem worth it.

This is not where as a consultant I should go in saying “I told you so”, rather this is where I acknowledge their errors, suggest alternatives, and work with them at looking into alternatives that they have come up with and plan for change (such as Azure SQL Database).

BI what?

After all of this, it finally came to which business intelligence tool should the business users be using. I “knew” the obvious choice “was” Power BI. However, the team was not convinced when I told them that it was easy to build reports and dashboards out of Power BI. Sure, and so was creating reports and dashboards using the new Cognos, since they all knew Cognos so much. They even brought up the tool that came with their ERP, despite not having grouping in reports, and being demonstrated by the consultant as “easy to build”. Yes, for each person who was familiar with a particular tool, building the report will be easy was the lesson of the day. Hence, it was decided to go about a fact-based approach, and not opinion-based.

Then came the interesting bit, just by chance, during the fact-based debate, when I asked the question: “What type of reports will we be running for the users”. The answers all started pointing towards operational reports, and ad-hoc reports. The ad-hoc reports looked to be analytical in nature, but provided in the form of a flat Excel, or a tabular report. The operational reports, were all pages of tabular reports. Further questions digging into this revealed that with the new ERP launch, the ask was for more and more operational reports. And whose job was it to churn out these reports? The BI team’s of course! A cringe-worthy moment.

Months after consulting with them, for the first time, after they had come down a path of business intelligence, that too on Azure with its many options for providing analytics, they were writing heavy queries for operational reports because the ERP was only so good in providing the reports that were needed, and “naturally”, as a lot of business users expect, the job had fallen on the BI folk to supply that demand. Heavy, complex queries were hitting the star schema on the SQL Data Warehouse. The results were then dumped on to flat Excels, and the Excel-comfortable users consumed them, and sometimes, interestingly… wait for it… <insert theme to Jaws> performing their own analysis on top of them because all what they wanted was not given the way they wanted it.

Back to the drawing board

It was evident with all the BI initiatives what the users were currently in need of were operational reports. Additionally, they also wanted ad-hoc reports that were presented in a tabular form. It was evident that the architecture that was adopted was not the right one.

Current Architecture

It was soon decided that the team falls back, perform a reality check, and serve the users’ primary objective: operational reports. The architecture will be altered, but with less effort to service this need, and with Cognos since it would give printable reports, and the BI team was already familiar with it. Everyone understood and agreed that BI was not the need of the hour. However, the need for BI would come soon. Hence, it was decided that most of the ad hoc reports will be built in an analytical fashion to Power BI to tease the users with what was possible over the Excel-based method that everyone was used to. Excel-based analysis will still be given, but through Power BI datasets.

First Iteration of the New Way Forward

Crude, you may say. But this was most agreeable. Users will not be overwhelmed; the BI team will not be overworked; technology will not be given priority. The need of the hour will be serviced, and the need to come will be slowly infused into the users’ DNA. Since this is Azure, it will allow for an initial architecture, with less spend and less resources, and allowing the BI folk to iteratively build an analytics platform with an iteratively improving architecture, on the road taking them on their BI journey.

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.


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

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.

Power BI Usage Metrics – A quick start

I had previously written a post on how you could go about building your own Power BI usage metrics solution. However, if you would want to get started on your own custom  usage metrics very quickly, there is a way.

Here are the steps on how you could quickly get started:

  1. Open up your favorite report or dashboard on the Power BI portal, and click on the  Usage Analysis button, which would bring up the usage analysis report:

    Image 01

  2. Then all you have to do is save a copy of the report from the File menu:

    Image 02

  3. You can now edit the report and make it look the way you want it to, including using new fields from the usage metrics data set, as if you would work with any other Power BI report.

    Image 03

If you need to go for a more complete usage analysis solution, then you could use the method described in my previous post: Report and Dashboard Usage Metrics in Power BI.

The effect of Summarize by Default on a Tabular Model

Another one of my experiences recently was this strange looking query generated off my Power BI reports. These reports were running off live a live connection to a tabular model on Analysis Services. And of course these queries at up my CPU, leaving it at 100% for quite a long while, with the reports not responding. One thing about the reports were that they had quite a number of parameters (which of course were a client requirement which we couldn’t do much about).

Here’s just a tip of the ice berg sample of the huge query that is generated:

Big Query

The data model was quite a large one with 30+ measures, and 10+ dimensions, and one thing that we had missed out was that all numeric fields that were not used as measures such as the Year, MonthNumber and WeekNumber fields of the Calendar dimension, and EmployeeAge field of the Employee dimension had their Summarize By property set to Default. But this was not indicated in any way in Power BI especially in the Fields section, where usually fields that are summarized show up with a Σ to indicate that the field is summarized when the data model is a Power BI data model.

The phenomenon is explained in detail in Pinal’s SQLAuthority. Make sure you take a look. Happy New Year!