|
Over the course of the next few weeks we're going to be releasing a series of videos in our "Complete Introduction to" series. The idea behind the series is to provide a comprehensive introduction to a variety of different data analysis and business intelligence tools and techniques so that business users can fully understand the tools available to them, and how they can work smarter not harder. The first video for the series will introduce you to Microsoft Power BI. One of the biggest selling points of Microsoft Power BI is that, because it's based on the Excel platform, the millions of Excel users working with data on a daily basis will find it easy to navigate and not be intimidated by this incredibly powerful tool. You will see how quickly and easily I produce an interactive report from an Excel spreadsheet using Microsoft Power BI. An Excel spreadsheet of Sales data has been used as the source of the data, but data from many different sources such as databases, .csv and Web sources can just as easily be used. In the video you'll see how easy it is to format tables of data, perform more advanced functions, like cross filtering, and add visualisations, such as column charts, line graphs and pie graphs, resulting in a detailed, interactive sales report emerging in minutes!The Microsoft Power BI Query Editor gives you a wide variety of options that allow you to transform, manipulate and prepare your data. It allows for perform simple and advanced tasks such as:
A big component of Power BI is it's unique programming formula language called DAX but don't be put off because, as I said at the beginning, Power BI is based on Excel, so many of the DAX formulas are the same and when you start using Power BI you should find yourself picking it up very quickly. At the end of the video I show you how to publish the report into the Power BI Service. The Power BI Service allows you to share the report to other users using a web browser, smartphones or tablets. I also show how to create a dashboard in the Power BI Service which makes it easier to access your content through mobile devices. Last but not least, I demonstrate how to use natural language queries (Q&A) to ask questions of your data. This powerful feature allows you to type in questions in a natural way and have Power BI provide the answers to them. I can ask 'What is my total Sales?' or 'What is my Sales in Italy?'and take it even further by asking 'What is my Sales by Product Category in Italy as a Column graph? This function fundamentally changes that way that users can interact and ask questions of their data. I hope that you enjoy this video and look forward to sharing the next one with you. If you are looking for Power BI training, please have a look at our Power BI Master Class and Excel BI Boot Camp.
0 Comments
One of the most common challenges that faces Excel users is having two separate sets of data that need to be referenced for reporting purposes. In this video I show you how to use Power BI to create a relationship between two spreadsheets in order to create a report using information from both. I've used a common challenge which many HR professionals encounter, where there are two sets of data, an employee training file (which records all training done by every employee) and an employee master file (which has details for each employee such as department, gender, marital status etc). I show you how to create quick reports and graphs to easily visualise information such as total training cost by department or percentage training costs by gender. In Excel, these reports traditionally take a long time because they require complicated formulas and VLookUps but in Power BI it's done in a few simple clicks.
For more information on our training, please look at our classroom training courses. Our previous post "Data or People? What is the most important resource in a business?" discussed the importance of developing your staff data analysis capabilities. Here we will outline the key steps in the journey to being able to use the latest technologies and techniques to produce reports and analysis. The following is focused on users who primarily work with spreadsheets and use Excel. Step 1 - Learn to use Pivot Tables Pivot Tables will speed up the time taken to produce reports and analyse your data tremendously. An important aspect of Pivot Tables is that you need to understand how to structure and format your data for Pivot Tables to work effectively. Step 2 - Create a Data Model New technologies such as Power Pivot and Power BI allow Excel users to easily create powerful data models. Data models allow you to easily centralise the storage of your data, calculations (business logic) and create relationships between the tables of data. Data models make it much quicker and easier to develop reports and speeds up your processes. Step 3 - Learn some Analysis Techniques Once you have an engine for the production of your reports, you need to learn how to ask questions of your data. There are many different types of analysis that can be performed on your data - comparison, trend, ranking, contribution, variance, frequency etc. are some examples. Add a bit of simple statistics and you will find a whole new world of interpreting and understanding your data. Visual analysis is a growing field of importance. Learn which graphs to use with which type of data. Visual analysis provides critical understanding of patterns and relationships within data. Step 4 - Time for New Data Technology such as Power Query makes it easy to access new data and to mash them up with existing tables of data. However the data may not always arrive in a format that is easy to use for data analysis. This is where you need to know how to use different methods to transform, append and merge data that creates powerful new data sets for different insights. Step 5 - Share your Insights Data analysis is of no use if we are not able to share the new insight and knowledge. Learning how to create effective reports and dashboards that can easily communicate key insights and information to your audience. Being able to tell a story about what your data means is a skill that will develop over time as your experience develops. Unfortunately often trial and error is required to find the best methods of what resonates with your audience and what does not. My advice is start simple with your presentations. Your audience will quickly tell you what they want. Every year, Gartner releases their annual Magic Quadrant for Analytics and Business Intelligence platforms. In the report, they evaluate the strengths & weaknesses of the main service providers in the marketplace as well as providing a graph plotting these providers based on their ability to execute and their completeness of vision. For the past 12 years, Microsoft has been slowly moving higher, but since 2016, the movement has been significant, with them being one of the leaders for the past 3 years and now pulling ahead to take the lead in 2019.
Since it's release in 2014, Power BI has rapidly evolved and grown to become a formidable challenger in the analytics and BI sphere. What a lot of people don't realise is that Microsoft is offering an ecosystem, not just Power BI. Low cost of entry has contributed to the high adoption rate of a product that is only 4 years old. Anyone can use Power BI Desktop for free. This single user offering includes data cleaning and preparation, custom visualizations and the ability to publish to the Power BI service. The Power BI Pro plan includes data collaboration, data governance, building dashboards with a 360-degree real-time view and the ability to publish reports anywhere and includes a 60 day free trial. Microsoft have also bundled it with the Office 365 E5 version, as well as embedded it in Microsoft Azure, so there really are a huge number of options available with total implementation costs being significantly lower than the competition.
Looking forward, Microsoft is now putting a lot of focus onto "Pervasive AI using Power BI". Their advancements in AI are being integrated into their BI stack for generic use, for both citizen data scientists and business analysts. Power BI’s automated capabilities with AI-infused experiences such as natural language, quick insights, image recognition, text analytics, and key driver analysis are new and has leaving the competition behind. They are leveraging the innovation from Microsoft Research with heavy investments into these integrations and constant updates. Add to this the frequent updates (Microsoft has been releasing monthly updates for the last 12 months) in a wide spectrum of categories, including reporting, modeling, analytics, data connectivity, data preparation, Power BI service, Power BI mobile and Power BI embedded as well as the investment in a broad set of visionary capabilities and integrating them with Power BI (examples include enhancements to augmented analytics, new AutoML features available in Azure Machine Learning and new Azure cognitive services) it's clear that they are committed to keeping their lead! In a nutshell, there are a number of reasons, aside from ease of use (which users gave top-third ratings across all aspects for) and cost that so many of the estimated one billion Excel users worldwide are adopting Power BI as their go-to BI and analytics tool. In the fourth video of our Power BI series we will show you how to easily edit data in Power BI. Power BI has the ability to create Tables and Reports in minutes, which can then be shared via the cloud. Power BI Desktop puts visual analytics at your fingertips with intuitive reports and dashboards. Using drag-and-drop to place content exactly where you want it on the canvas allows you to quickly discover patterns as you explore a single view of linked, interactive visualizations. Power BI for Office 365 is Microsoft's latest Business Intelligence solution. It is cloud-based and works from within Excel and Office 365, to analyse and visualise data quickly and easily. The BI solution is designed to help business users gain insights from their data and, according to Microsoft, includes the following: - Power Query - Power Map - Power Pivot - Power View There is no shortage of data available today, with huge amounts of it being constantly generated by ERP systems, POS systems, PLC systems and Biometric systems, to name but a few. Companies are needing to find ways to effectively analyse the data, to leverage the information and make informed decisions, however many of the options available are very expensive and require a lengthy period of implementation, plus human natures makes most staff resistant to learning new systems so all in all, decision makers are faced with a challenge on many levels.
Excel has become the main tool for manipulating and analyzing data, and developing reports in business today, and with the release of Excel 2013, Microsoft introduced even more powerful business intelligence tools. To understand how the Excel Business Intelligence tools work together in Excel 2013, you need to understand the function of each tool and how it fits into the “traditional” Excel model. Extract, Transform, LoadPower Query is an intuitive, easy to use tool for importing, transforming and working with data. Data Manipulation. Data ManipulationSpreadsheets are the basic feature of Excel. Launched in 1985, there are now over 700 million Excel users worldwide. Tables were introduced in Excel 2007, they allow you to do quick sorting and analysis of your data. Power Pivot was introduced as a free add-in for Excel 2010, it gives you the ability to access, process and handle well over a million rows of data, more than a standard spreadsheet can process. PresentationPivot Tables are an easy and convenient way to build intelligent, flexible summary tables, allowing you to quickly derive insight from your business data. Pivot Charts enable you to visualize a PivotTable. You can quickly change a portion of data displayed, making PivotChart ideal for presentation of data in reports. Power View is an interactive data exploration, visualisation and presentation tool that gives you strong insight in to your business . So what is the bottom line?Before spending time and money on new data analysis tools, find out exactly how learning how to use all the Excel Business Intelligence Tools effectively will allow you to quickly and easily analyse your data and gain insight into it without any major resource investments. Over a billion people around the world use Excel today, far more users than any other BI vendor can claim.
I'd like to revisit an old article which is still very relevant today. Thanks to Jen Underwood in SQL Server BI Blog for this amazing article. Love it or hate it, export to Excel is still the most specified requirement in contemporary analytic tool selections, despite all the advances in business intelligence (BI) technologies. Excel is comfortable, flexible and with the new Microsoft Office 365 Excel Power BI add-ins (Power Query, Power Pivot, and Power Map), it's growing to become exponentially more powerful—pun intended. With the latest Microsoft strategy shift of embedding self-service BI applications right within Excel, could Microsoft's Excel Power BI release become a BI "killer app?"
Killer apps take the market by storm. Microsoft Excel was the spreadsheet killer app of the 90's, eliminating VisiCalc, Lotus 1-2-3, Quattro Pro, and many other players. According to Forrester's Q3 2013 Global Productivity Suite survey, Microsoft Office had a dominant, 95 percent or more, market share in the productivity software market. Through the years, we've seen a few productivity suites introduced including OpenOffice, LibreOffice, Google Docs, and Apple's iWork, yet none have been widely embraced. Over a billion people around the world use Excel today, far more users than any other BI vendor can claim. The Excel reach advantage is undeniably huge. Microsoft Office Continues to Dominate On the other hand, the inevitable shift to the cloud and browser-based apps, could level the playing field. In 2014, Google Docs is no longer a curiosity but rather a legitimate future threat to Microsoft Office. Google Docs has more chart options than Excel, a richer API, and predictive features already in beta. You never hear about Google Docs for analytics, but it's silently making a few substantial strides. Google and Microsoft are both mega-vendors. Anything is possible, but what is likely to happen? In the Forrester survey cited earlier, most firms that deployed Google Docs are still using Microsoft Office due to employee needs, resistance to change, and technical compatibility issues with things like pervasive Visual Basic for Applications (VBA). Despite widespread marketing and increased interest in Google's offerings, Microsoft Office continues to dominate. Excel is still at the heart of business decision-making today and it's not easy to leave. Excel in the BI World Business users may love Excel, but in the professional BI world, it carries emotional baggage. Countless data warehouses and reporting applications have been built with the intent of removing Excel risks and dreaded "spreadmarts." Excel has been blamed for high profile analytic disasters such as JPMorgan's $6 billion trading loss. Keep in mind, people make mistakes using many tools—not just Excel—yet Excel remains an extremely popular attack target. Quite a few data discovery and BI vendors advertise anti-Excel messaging in one way, shape, or form. Since Excel is where most analytic tasks are performed today, if Microsoft makes the right investment decisions and quickly executes, popular data discovery applications are most at-risk and they know it. Reviewing an admittedly subjective list of top requested data discovery tool capabilities, Excel Power BI already arguably meets or exceeds more than half of them and rapid releases new features monthly. Data discovery requirements typically are business user driven requirements (a.k.a. the masses). If Excel can meet most data discovery requirements, why would you buy another stand-alone tool? Currently, Excel 2010, the version most organizations have deployed, and even Excel 2013 Power BI, have key data discovery gaps. Excel 2013 is getting a lot better, but has not been displacing the data discovery vendors on a global scale, yet. Right now, Excel and Power View often get dismissed based on visualization capabilities, an area that users highly value in data discovery tools selections. Excel Power BI will continue to improve and could certainly become "good enough" for most business users in an organization if the visual analytics features vastly improve. Timely customer migration to latest Microsoft Office versions are a pre-requisite for potential data discovery market disruption. In the meantime, all of the data discovery vendors are in sales overdrive trying to secure as much market share as possible, offering free personal solutions and pitching a story of better analytics for the masses. To stay relevant in the future, they'll need to convert freemium users to paid subscriptions and offer much more compelling, differentiated solutions than they do right now. An Exciting Time for BI Applications It's an exciting time for Microsoft Office 365, Excel Power BI, and other BI applications. Looking ahead, we'll continue to see more innovations around information search, automation, cognitive contextual computing, predictive, and personalized analytics. Watching how those future trends are being brought to the masses via Excel and other platforms is fun! Office 365 Excel Power BI, Q&A, and Power Query are just the beginning of a significant BI and data discovery industry transformation. Controversial or not, Excel might not be the next killer BI app per se, but rather the next data discovery killer app in a few years. What do you think? Organizations seeking to stay ahead of the competition recognize the importance of investing in analytics and visualization tools to deliver insights from their data. They need a modern, powerful business intelligence a(BI) platform that will stand up as an industry leader and a vision that will meet the needs of tomorrow. Industry analysts have taken note of our efforts and we are excited to share Gartner has positioned Microsoft as a Leader, for the ninth consecutive year, in the Magic Quadrant for Business Intelligence and Analytics Platforms. For the first time, Microsoft is placed furthest in vision within the Leaders quadrant.  "The evolution and sophistication of the self-service data preparation and data discovery capabilities in the market have shifted the focus of buyers in the BI and analytics market -- toward easy-to-use tools that support a full range of analytic workflow capabilities and do not require significant involvement from IT to predefine data models up front as a prerequisite to analysis," Gartner said in its report. "This significant shift has accelerated dramatically in recent years and has finally reached a tipping point that requires a new perspective on the BI and analytics Magic Quadrant and underlying BI platform definition -- to better align with the rapidly evolving buyer and seller dynamics in this complex market." This is the first video in a five-part series showing you how powerful the Excel "Power" tools are and how learning to use them can vastly improve your productivity, while at the same time giving you insight into your business far beyond anything you ever imagined. ‘five seconds to sign up, five minutes to wow!’ Following six months of public preview and a continuous flow of user-driven enhancements from their rapidly growing community, Friday 24 July marked an exciting milestone for Microsoft as they officially released the new Power BI service and Power BI Desktop. Over 500,000 unique users from 45,000 companies across 185 countries helped shape the new Power BI. Add to the already impressive list of features and capabilities the advances in their Power BI service and the new Power BI Desktop, there really is plenty to talk about! But the news doesn’t stop there. They’re rolling out new content packs weekly, ensuring that business users can connect to and experience data without technical setup. This means you can connect to your data in popular services like Quickbooks and Salesforce, and view a curated collection of dashboards and reports that continuously update with the latest data from these services. They’ve also contributed their Power BI visualization framework and its complete library of visuals to the open source community to enable everyone to easily extend and build custom visuals. The project is available on GitHub, and in the future, anyone will be able to extend and bring custom visuals into Power BI for use in their dashboards, reports and content packs. Power BI sets the standard for modern business intelligence and Microsoft wants us to change the way we do business and experience the cloud-hosted, business intelligence and analytics service for ourselves. At the Boys & Girls Clubs of Greater Fort Worth in Texas they are using Power BI to understand how their various programs are performing and their impact on the kids they serve. With a mission to change each kid’s life for the better, the leaders at the Boys and Girls Club use Power BI to see their data update in real time and discover new insights visually to take action. At Metro Bank in London, the team is using Power BI to quickly and accurately provide information to guide analysis and decision-making as they set out to create fans, not customers. With all the new features and capabilities they’re delivering today, plus their commitment to a weekly release cadence, it’s time to join the 500,000+ people who have tried Power BI to start discovering the stories your data will tell. Find out more, watch our Power BI Intro now. The business world has changed fundamentally over the past few years.The speed of change means that mangers need to be able to make decisions quicker, but at the same time need to be able to process far more data to be able to make an informed decision. Business today has no lack of data – transactional systems record every part of the business process. The key challenge for managers is how to turn the vast quantities of data into useful insight and information. In recent years new technologies has allowed vast quantities of data to be processed, analysed and increasingly visualised. The new technologies allow management to understand trends, identify outliers and to see new patterns in the data that could not be processed previously. Excel as a Business Intelligence Tool Excel has become the main tool for manipulating and analysing data, and developing reports in business today. With the release of Excel 2013, Microsoft introduced even more powerful business intelligence tools. To understand how the Excel Business Intelligence tools work together in Excel 2013, you need to understand the function of each tool and how it fits into the “traditional” Excel model:  How is this relevant to Business Users? If you work with Excel, using Spreadsheets and Tables to build reports, you probably find yourself regularly copying and pasting data between worksheets then clicking the same sequence of buttons to clean and shape the data. You’ve probably found that complex formulas, dirty data and inevitable errors take up a lot of your time, making month end reports a nightmare that consumes large portions of time you don’t have. Learning how to use all the Excel Business Intelligence Tools effectively will free you from the dull, repetitive tasks and give you time to focus on what’s important, analyzing your data and gaining insight into it. This will enable you to make good decisions about your business. |
