Site logo white shadow

How to conduct a Power BI Pilot

Power BI is becoming popular. Therefore, many companies are interested in considering a Power BI Pilot project as a proof of concept. While interacting with customers, I have noticed that many such pilots fail. The failure is NOT due to the capabilities of the product, but due to other factors which are controllable. In this article, I have listed a process which prevents common errors and improves the credibility of the outcome.

Power BI Pilot - the process

Why is a Power BI Pilot necessary?

  1. To check if it is suitable for your data analytics needs
  2. To find out whether adequate skills are available with the existing team
  3. To show the real benefits of data discovery to business users
  4. To get buy-in from management to fund the larger Power BI initiative

What should not be done?

  1. Do not use sample data. The data used must be REAL.
    Otherwise the Power BI Pilot will not yield realistic results.
  2. Do not choose IT, HR, Legal, Admin as the target scenarios.
    Why not? Because these are support functions. Choose functions / teams whose efficiency directly improves business outcomes.
  3. Data must not be very complex .
    Just use one or two fact tables and relevant masters.
  4. Do not try massive and complex data.
    Initially we are testing the capability.
    Scalability comes later.
  5. Do not try to take up a new business scenario.
    Choose business scenario or context which already has known, reliable data.
    The scenario must already have statutory or standard reports which are currently being used.
  6. Do not try to add every feature and visualization of Power BI to the pilot scope.
  7. Do not expect an instant and mind-boggling success story. There will be failures and mismatched expectations. But the important thing is NOT to discard the pilot half-way and conclude that Power BI does not work for me!

What are we trying to achieve?

  1. Create internal evidence that Power BI is a great tool for end user level analytics.
  2. The feedback gathered during the Power BI Pilot will provide useful information for further planning.
  3. Next step is to create an internal reference project using Power BI in a given business unit or department or team.  The business users must be trained as a part of creating the analytics for their department.
  4. Apart from providing guidance, training and raw data, IT should not be spending time on each Power BI project. This defeats the purpose of self-service BI. The IT team should create a set of Power Users – at least two per department to play this role. Their managers should officially allow them to participate in the Power BI activities and preferably provide some tangible incentives.
  5. Once the departmental project is finished, the benefits should be published and presented to the top management. This presentation should be done by the head of the unit which implemented the Power BI project.
  6. Now that the value of Power BI is fully established, a functional template or SOP for Power BI usage at end user level should be created and published.
  7. At this stage, each business head should implement Power BI across their organizational unit.

Recommended Method

These are just guidelines. You can customize it to your specific needs.

Who should initiate the Power BI Pilot?

As you are reading this article, you are the best candidate for this. In most cases, it is initiated by IT or Microsoft rather than business users. Users do not even know that such a thing exists.

Who should sponsor it?

Even though the pilot may be supported by MS or partners for free, you need a senior decision maker as the sponsor. Otherwise, your pilot, even if successful, will never go to the next step.

Select business scenario

Choose a scenario which adds to the top-line or bottom-line of the core business. Do NOT choose back-end functions.

The data should be available easily and without much clean-up because this is a common business scenario.

Use a small sample of existing data

Initially use small amount of data. Small means few thousand rows and few tables rather than millions of rows.

Clean-up the raw data

If you are getting data from Excel, you will need to replace many VLOOKUP functions with Relationships in Power BI. Cross-Tab data will need to be UnPivoted. Depending upon how unclean the raw data is, many more transformational steps may be required.

Make the data nicer to work with

The technical term for this is “shaping the data”. This usually includes the following things:

  1. Remove unwanted columns
  2. Specify legible names for columns
  3. Set appropriate data types for each column
  4. Create Relationships between tables
  5. Set default aggregations (most commonly used for preventing auto-sum for Year column)
  6. Categorize data as Geography, Image URL, Web URL, etc.
  7. Set the Sort by Order (most often required for the “Month” text column)
  8. and more …

Recreate existing reports

I know that this is the worst way of using Power BI. But relax. This is just an interim step. We will not be using any of these reports. Then why recreate the existing reports?

  1. It helps the team get used to the Power BI platform.
  2. It provides a quick way of proving that the new reports are producing the same output as the old ones.
  3. Most  importantly, it shows shows you how difficult it is to interpret existing reports!

Rethink the objectives

Recreating existing reports is not very useful. We created those reports because someone asked you to use the specified format. That is not enough. We have to go BEYOND the standard report and learn more about the data.

Now, think what WILL BE useful. Do not worry about column names or type of visualization. Just think in business context.

How not to think?

Do not think like this: “how to show a stacked bar chart with Date on X axis and revenue on Y axis”

Think like this:

  1. What can I learn from this data?
  2. How does sales fluctuate across time?
  3. How can I quickly start somewhere and then try different levels of granularity with either of the data items (revenue and time).
  4. What  else must be influencing the revenue?
  5. How can I quickly check if a particular factor changes the revenue?
  6. What about going from high level to low level? (Year, month, day or High, medium, low transaction size)
  7. Think first and then find the way to get that information.

Which visualizations to choose?

Start from a fresh blank canvas and talk to users to find out what is really important to them. When you put any visualization, ask them to play with it and check if they are able to find a useful piece of actionable information from it. If not, delete the visualization.

This decision is a two-step process. Firstly, you have to learn how to use ALL available visualizations. Only then can you choose the right visualization for your specific needs.

Apart from the choice of visualization, you must also think about:

  1. Using the right data column (field) in the right place for the visualization
  2. Choosing the right settings for axis, plotting method, scale and so on.
  3. Is this a slicer?
  4. Does it participate in cross-filtering?
  5. Are more fields necessary for drill down?
  6. Does this visualization participate in the drill down?
  7. What should be the initial look-and-feel of the visualization?
  8. Is the font size adequate for easy interpretation?
  9. Is the visualization showing too much of data or graphics?
  10. Does it require any additional explanatory stuff like Data Labels, Annotations, Callouts?
  11. Does it require comparison with another piece of data like comparison with previous year, or competitors or industry benchmarks?
  12. Is one visualization enough or it needs to be broken down into multiple visualizations based upon another field?
    For example, you can see product contribution as a pie chart. However, to see the fluctuation across months (seasonality), you must create 12 pie charts – one for each month.

How many pages are required in the report?

One page with further drill down is the best possible report. But it may not work in every situation. Try to minimize the pages in the final reports.

Test the tangible business value of the Power BI Output

This should be done by the business user. At this stage match the output with existing reports or manually calculated values.

Review the outcome at every stage

I received inputs from my customers while writing this article.
Mr. Ashok RV , VP, Information Systems at  Sundaram Clayton Ltd. stressed upon the importance of conducting reviews with the stakeholders from business side.

In most cases, business managers  do not know (nor are they supposed to know) what to expect from technology. On the other hand, techies are not able to understand the business needs fully.

Therefore, if a business manager is confronted with questions like “How do you want the dashboard to look?” or “What do you want to include in the dashboard?”, they just do not know what to say. They say whatever they want in an idealistic world and that may not actually be feasible as per the capabilities of the technical platform.
This leads to frequent mismatches between expectations and delivery.

Therefore, frequent reviews are very helpful. The benefits are:

  1. Any expectation mismatches are noticed at an early stage
  2. Reviews help the Power BI team on focusing on business benefit rather than technical enthusiasm.
  3. The business users can also get a real feel of how the technology works and what it is capable of. Due to this, business users can imagine relevant scenarios and demand new, relevant capabilities. This is real business mapping.  If you want a more fashionable word, let us call it “Digital Transformation”!

Repeat the process with new data

Using new data (from other region or time period) is important to ensure that the data import and clean-up does not fail. Correct import errors as required.

Repeat with medium and then large size of data

This is to test the scalability. While increasing the data size, remember that more data requires faster processing power, lots of memory and a fast disc. Attempting to analyze enormous amount of data on an underpowered laptop will only lead to frustration.

Note the time saving in report generation processing using Power BI

This should be fairly significant especially if there was lot of data clean-up involved.

Highlight the new insights and abilities

The Power BI Pilot dashboards should unearth new and useful insights which were hidden earlier.

In addition, it will also provide new capabilities like ad-hoc impact analysis, understanding the geographical relationships, outliers, etc.

This is probably the most important step. This list will demonstrate how business opportunities were wasted in the older era of standard, statutory reporting.

These business opportunities are your growth drivers and competitive advantage.

Present the findings to the sponsor and related senior management

  1. Explain the process involved
  2. Show the time saved by using Power BI
  3. Highlight the other benefits – especially unearthing more business insights with minimal effort
  4. Show the actionable items which evolved from the new insights
  5. Let the senior management try out the Power BI Pilot output on their own for few days
  6. Provide seniors with the required hand-holding
  7. Ensure that the management team members can now play with existing reports as well as create new reports on-their-own. This is called empowerment!

Move away from static PowerPoint presentations

During review meetings try showing the live dashboard of related data. Avoid putting screenshots of the Power BI dashboard into the routine PowerPoint presentations.

Try sending the Excel reports using OneDrive links and educate concerned persons about the benefits of having “single place to find this report” concept.

If the Power BI Pilot is successful …

  1. Go back to them after a week and check if they have found any new insights themselves. Most probably they have found useful stuff there.
  2. Now standardize on Power BI based reporting for the specific business scenario
  3. Publicize this success story using internal communication channels
  4. Show a demo of the pilot project results to all Senior Management Members
  5. Create a plan for enabling all users with Power BI and present it to management

Test scalability at this stage

Now that the Power BI Pilot is proven to be useful from a functional point of view, we can test it with more data.

If you do not have large amount of data, do not worry. Big Data is not necessary to get big benefits!

What does large data mean?

It means expanding the analytical horizon.

Often, standard reports are monthly, quarterly, yearly or by region, product, etc. because combining the data was practically impossible using traditional Excel. I am sure you have seen users struggling with 5 separate yearly reports and trying to create a sixth summary report! This is no longer required.

Now is the time to use is Power BI Pilot to prove to everyone that these constraints no longer apply.

To achieve this, you will have to combine scattered raw data from separate sheets, workbooks, CSVs into a single block of data. This is easily done using Folder consolidation or the Append Query option in Power BI.

If the Power BI Pilot fails…

Things can fail. But do not get disappointed. Look at the process used for conducting the pilot and identify what went wrong. Correct it and try another pilot. Conducting the second pilot will be much easier and faster than the first one.

Now it is your turn…

Have I missed any points? Do you have any more things to add? Have you experienced some contradictory situations? All our readers would like to know. After all, we are here to learn from each other!

I am very keen to know if this methodology worked for you.

Feel free to post your comments, experiences and queries here.


2 Responses

  1. This is very simple to understand and thank you very much Dr. Nitin for the effort put in. I searched for Power BI presentations in YOUTUBE and your presentation was the most interesting one, which prompted me to follow your web site.
    The way you present makes it easy for any body to learn and practice.

Queries | Comments | Suggestions | Wish list

Subscribe to Blog

Join 1,760 other subscribers

Popular articles

Pivot Table Pro Course

Yes. You use Pivot Tables everyday. Now it is time to find out the real power and nuances. 5.5 hours video, exercises, samples, Q&A.

Excel to Power BI Course

Learn Power BI using the concepts you already know in Excel. Fast transition, in-depth coverage and immediately usable.