Office as a Platform, Issue # 2: Power BI - A New Approach to Creating Business Intelligence



    Today, one of the important tasks that we face in the process of work is the task of the correct and beautiful presentation of data. We strive to turn faceless figures into interesting and informative materials, revitalize our reports and presentations. Moreover, the amount of data received in the human brain increases every year, therefore, along with obtaining any results, we need to correctly process and structure them.

    Today, as an example of a number of tasks, we will consider a powerful cloud tool that allows you to create various bright and colorful reports based on a huge number of data sources. Namely, I hasten to share with you a new tool that will help you visualize your data - Power BI .

    Where do we start?



    To get started, we need to install Power BI . The Power BI tool is available as a standalone Power BI Desktop application ) or on powerbi.com . Our acquaintance with Power BI will begin with Power BI Desktop - in it we will first process the data, create a report on it, and then upload it to the portal.

    Installing Power BI Desktop is very easy - just follow the link, click the Download button. The program installation file will be loaded, which you will need to run and go through the standard installation steps.

    As I noted above, Power BI Desktopallows you to download and edit data, create beautiful visual reports.

    Each time you download Power BI Desktop , a welcome page opens, on which you are prompted to download new data or open a ready-made report.



    A separate word about data formats and services that you can work with. There are a lot of them! This includes the familiar csv, xlsx formats, the ability to download data from the web, SQL server, SharePoint, Dynamics CRM, GitHub, Oracle, Google Analytics and many others.



    There are two modes in Power BI Desktop — data editing mode and reporting mode. In edit mode, you can work with your data - add new columns, merge existing ones, sort data, change their format, etc.

    I decided to try to create a report based on open data from the open data portal of the city of Moscow . I downloaded the table with a list of city parking lots in csv format, uploaded it to Power BI Desktop , and began to look at what data it was possible to download from the portal. The table turned out to be several columns with information about the name of the parking lot, its address, telephone of the person responsible, etc. - The usual “faceless” information. In principle, working with the table is quite comfortable - you have the necessary information, you can copy the address and see the location on the map on the Internet. But I wanted to “revive” this data, which I tried to do with Power BI. I almost did not have to work with the table - it was enough to change the headers (for my own convenience) and change the type of some columns (everything by default has a text format, but we understand that the coordinates of the location are a decimal number).




    In the second mode, the reporting mode, you can visualize data, create beautiful charts and graphs for their best presentation. For example, if we upload data with the geographic coordinates of objects, we can put them on a map and add it to our report.

    Well, that’s what I need! For practice, I created 3 diagrams - the distribution of parking lots by districts (indicating the number in each district), information about the name and address of the parking for each selected area, and, most interestingly - plotted parking on a map. Now you can click on the parking lot on the map, see information about it, as well as about the rest of the parking lots located in that area. Fast and convenient. My report is ready!




    After you have finished working with the report, you can upload it to the portal - powerbi.com . Just go to the site and log in with your account for which Office 365 subscription is active.



    For the most part, Power BI portal performs all the same functions as Power BI Desktop , except for the lack of data editing. The main "feature" of the portal is the dashboard.



    You can pin data from various reports to the dashboard and track their change in real time. Also, the portal has the opportunity to share the report with everyone who works with this data.



    In general, with what Power BI we met, now let's look at another example of working with data. This time we’ll see what is meant by connecting data from Facebook and analyze the information available to us.

    Note: For those who want to watch a video with an example with bicycle parking - this example was considered at the opening of DevCon2015 .

    Power BI + Facebook. What is interesting stored in the group?



    As a group, the data of which we will analyze, I chose Community for the development of software , a social network Facebook. We will be connected to the page using the username and password from Facebook, you do not need to create any additional accounts.

    • Open Power BI Desktop, click Get Data , select Facebook as the data source, click Connect . A warning about connecting to an external resource (Facebook'y) is displayed, agree and click Connect again .

    • We fill in the necessary information. In the first field, enter the name of the group, the data we want to receive, and in the second - the data type (posts, links, comments, etc.).

      In this case, consider the msdnru group and analyze the posts that were made in this group.



    • Next, enter your Facebook account information.



      You will be offered the opportunity to select the data to which you give access.



    • After loading, a window opens with a list of data columns loaded from the page. In order to see what these columns are filled with, switch to the data view mode by clicking on the button with the table icon.



      While we remain in the report creation mode and begin to create a report.

    • For example, let's build a diagram of the relationship between types of posts (photos, links, videos, just statuses).



    • Immediately there was a nice diagram with the distribution of records. Let's change its type to Tree Chart . Please note that the buttons for changing the type of chart are located both on the toolbar and on the panel that appears when you click on the chart.

      You can point to each block and see the number of records in it:



      Next, we construct a diagram of the dependence of the number of reposts on the time of publication of the record. To do this, we need to work with the columns. We’ll go into data editing mode and see what and how to format.

      Under the spoiler, a detailed description of what I did with the data:

      Data Actions
      • Work with the created_time column . Divided it into two parts. The column contains information about the date and time the post was created. Divide this column into two: created_time and created_date . In order to separate the columns, we will use the Split Column command , we will separate by the T. symbol. Renamed the created_time columns obtained after the column was divided (I named them created_time and created_date ). Now transform the column over time. We only need the distribution of records by hours (minutes and seconds are not so important), so select the created_time column, go to the Transform tab , select Time, thenHour .
      • Work with the Shares column . This contains within itself additional "embedded" information. In order to see what is contained inside it, click on the “two arrows” icon, then, OK . Renamed the column shares.count to shares . It is necessary to change the column type of shares . Select it, right click, Change Type , Whole Number.



      We’ll go into report creation mode - we will begin to create our visual report. First drag the shares column , and then the created_date column onto the Stage . You should get a chart of the number of reposts on the date the post was published .



      Next, we add a schedule of distribution of reposts depending on the time of publication (by hour). Move the shares column and the created_time column to the stage . Move the created_time column to the Axis area and change the chart view to a Bar chart .




    Application for Windows, iOS, Android



    You and I saw that the generated reports can be easily viewed in the browser. But you must admit, it would be more convenient if they were all assembled in one application. And for Power BI there is such an application. It is available for both Windows and iOS and Android .

    Development API



    If you want to use Power BI charts in your application, then you can use the Power BI API, which is available here . I’ll talk more about development options with the Power BI API in the following articles.

    Conclusion



    So to summarize. Power BI is a cloud-based tool for visualizing data from various formats from various sources. With Power BI, you can create beautiful reports and share them with your colleagues. Power BI consists of several parts - Power BI Desktop - an application in which you can edit your data and create reports, the powerbi.com portal where you can download the created reports, configure automatic data updating, and just share the created reports.

    Oh yes! I almost forgot the application for Windows , iOS and Android - the latest data will always be in your smartphone.

    useful links



    I suggest you try creating your first report now. To make it easier, here is a list of the main links:

    Only registered users can participate in the survey. Please come in.

    Can you use Power BI in your work?

    • 57.1% Yes, applicable, now I will use it 72
    • 16.6% No, not applicable at all 21
    • 29.3% Some features apply 37

    Also popular now: