Do You Know? What is Microsoft Power BI ?
A Complete Introduction [2020 EDITION]
- INTRODUCTION
Microsoft team worked through long period of your time to create an enormous umbrella called Power BI, this big umbrella isn’t just a visualization tool like Tableau, it is not just a self-service data analysis tool like PivotTable and PivotChart in Excel, it’s not just a cloud based tool for data analysis. Power BI is combination of all of these , and it’s far more . With Power BI you can connect to many data sources (wide range of data sources supported, and more data sources add to the list every month). You can mash up the info as you would like with a really powerful data mash up engine. You can model the data, build your star schema, or add measures and calculated columns with an In-Memory super fast engine. You can visualize data with great range of data visualization elements and customize it to tell the story behind the data. You can publish your dashboard and visualization tool in cloud and share it to those that you would like . You can work with On-premises as well as Azure/cloud based data sources. and believe me there are far more things that you simply can do with Power BI which you can’t do with other products easily.
Definition:
Power BI cloud based data analysis, be used for reporting and data analysis from source. Power BI and user friendly enough that business analysts and power users can work with it benefits of it. On hand Power BI is powerful and mature enough be enterprise systems by BI developers for complex data mash-up and modelling scenarios.
2. INTRODUCING POWER BI DESKTOP
Power BI Desktop is that component in Power BI suit. Power BI Desktop a holistic development tool for Power Query, Power Pivot and Power View. With Power BI Desktop everything under a same solution, and it’s easier to develop BI and data analysis experience thereupon . Power BI Desktop updates frequently. This product has been in preview mode for a period of with name of Power BI Designer. There are such great things about Power BI Desktop that can’t a little paragraph here, you’ll examine this tool in future chapters. great features of this product I’ll write the “Power BI Hello World” with a demo of this product. have a view of newest features of Power BI Desktop here during this blog post.
Power BI Website
Power BI solution can be published to Power BI website. In Power BI website source scheduled to refresh (depends on the source and is it supporting for schedule data refresh or not). Dashboards can be created for the report, and it can be shared with others. Power BI website even gives you to slice and dice online without requiring tools, just. You can built report and visualizations directly on Power BI site.
Power BI Mobile Apps
There are mobile apps main mobile OS providers: Android, Apple, and Windows Phone. These apps gives you an interactive view of dashboards and reports Power BI site, share them even from mobile app. Highlight the report, write a note and share it to others.
3. Working in Query Editor
Power Query may be a data extraction and transformation engine. The engine comes with a formula language and a graphical tool. The graphical tool has two major setup versions; one embedded in Power BI Desktop tool, and the other one as an Add-In for Excel. The graphical tool has list of transformations that can be applied on a data set, and it also supports different data sources. However the facility Query formula language is far more powerful than the GUI. Actually there are some features in Power Query engine that not yet has been implemented through GUI, but they are available through M (formula language).
Power Query can connects to set of data sources and read data from them. Set of data sources is variable from text files, to web urls, from database systems to some applications. Wide range of data sources is supported. So to reply to at least one of the very first questions that sometimes appears once I introduce this product which will Power Query hook up with Oracle? Sure it does! Not only Oracle, but also MySQL, PostgreSQL, DB2, Sybase, and Teradata.
Power Query can apply many transformations to the info set. you can apply simple transformations such as trimming a text value, and applying numeric calculations to complex transformations easily such as pivot and unpivot. Power Query uses a function library for applying transformations, and the function library contains heaps of transformations for every data type such as table, text, record, list, date, number and so on.
Power Query graphical interface is very easy to figure thereupon even business analyst or an influence user can work with it, on the opposite hand Power Query M language is so powerful that can be used for complex real world challenges of data transformations. Power Query can load the result set into an Excel spreadsheet or it can load it into Power Pivot for data modelling. The version of Power Query utilized in Power BI Desktop load the result set into an influence Pivot model. I will go through details of Power Pivot in future chapters, for now it would be enough to know that Power Pivot is In-Memory tabular data model engine.
4. Understanding the Data Model: Data and Relationship View
Relationships are one among the fundamentals , however, one among the foremost essentials a part of an influence BI modeling. If you are coming from the database background you already have a good understanding of relationships, If you are coming from Excel or business background, you can consider each relationship like a lookup table.
The relationship is what makes the filtering across multiple tables possible. In this section, you learned about the basics of relationships, such as why we need it, and how to create the relationships, and what happens behind the scene of a relationship logically.
Relationships
When we want to query data from two tables, and the data of those two tables should be somehow connected, then you can create a relationship between them (if these two tables are not related through other tables).
To create a relationship between two tables, we need a field that can link these two tables together. We call this field a relationship field. You can simply drag that field from one of the tables (doesn’t matter source or destination), and drop it on the same field on the other table. The two fields from the two tables can have the same name or different names. If they need an equivalent name (or let’s say similar name pattern), Power BI can automatically detect that relationship.
Cardinality in Relationships
When you create a relationship between two tables, you get two values, which can be 1 or * on the two ends of the relationship between two tables, called as Cardinality of the relationship. The two values 1 or * tell us that the field in that relationship has how many of that value per line in that table.
Types of Cardinality
There are 4 types of cardinality:
1: 1–1 : one to one
2: 1-* : one to many
3: *-1 : many to one
4: *-* : many to many
1: one to many OR many to one Relationship
This is the most common type of cardinality used in data models. This type of cardinality means one among the tables has unique values per each row for the connection field, and therefore the other one has multiple values.
There are two ways of calling this relationship; One-to-Many or Many-to-One. Depends on what is the source and destination table.
2: One-to-One Relationship
A one-to-one relationship happens only in one scenario once you have unique values in both tables per column. When you have a one-to-one relationship between two tables, they’re an honest candidate to be combined with one another using Merge in Power Query. Because both tables have the same number of rows most of the time, or even if one of them has fewer rows still considering the compression method of Power BI , the memory consumption would be an equivalent if you bring it in one table. So If you’ve got a one-to-one relationship, then believe combining those tables seriously.
3: Many-To-Many Relationship
You have multiple records for every value within the joining field between tables, for both tables. If you think about it for a second, you can see that this scenario happens when you have tables that are related to each other with no shared dimension in between. You will end up with creating a many-to-many relationship between every two tables, and then because it causes circular filtering in the relationship, you end up with an inactive relationship! There are tons of other issues with the many-to-many relationships, and most of the time, they are all result of not having a shared dimension in between.
The many-to-many relationship causes tons of issues, and that is why it is called a weak relationship too. Most of the time, it can be resolved with creating a shared dimension creating one-to-many relationships from the shared dimension to the fact tables. AVOID this type of relationship in your model.
M-Language:
What is M?
M is that the powerful language behind the scene of Power Query. Any transformation you apply are going to be written in M language. There are MANY operations that you simply can with M, but you would possibly not be ready to roll in the hay simply with the graphical interface.
M is informal name of Power Query Formula Language. The formal name is very long that nobody uses that, everyone call it M! M stands for Data Mashup, or Data Modeling. M may be a functional language, and it’s important to understand functions of it. However, each language features a structure and syntax which is that the beginner level of learning that language. In this post, i will be able to explain the syntax of M. Before learning M, i might such as you to read this sentence loud.
M is far more powerful than the graphical interface of Power Query.
What is DAX?
DAX is Data Analysis expression Language. This is the common language between SQL Server Analysis Services Tabular, Power BI, and Power Pivot in Excel. DAX is an expression language,M,very Excel functions. In fact, DAX has many common functions with Excel. However DAX more powerful than Excel formula in some ways.
DAX calculations are sense mostly for Excel users. Normally Excel users are very comfortable with this language. Everything goes through functions. DAX doesn’t have programming blocks in it, and is combination of function uses, filters, and expressions.
M vs DAX, Which is better?
Power Query (M) option in my view, because normally ETL your BI solution, simply build your model and data sets in a way you like it to be. But if something like Year To Date; Obviously in Power Query or M, but be code, and you have many combinations of possibilities result, while in DAX simply create that with usage of Total YTD function. So the answer is; there is no best language between these two. The type of usage identifies which one is best. Normally any changes for the model is best to be M, and any analysis calculation on top of the model is best to be done in DAX.
What questions can DAX answer?
DAX is the analytical engine in Power BI. It is best language to answer analytical questions which their responses will be different based on the selection criteria in the report. For example, want to calculate Rolling 12 Months Average of Sales. It is really hard if to calculated that in M, because all of possibilities; Rolling 12 months product, for every customer, for every combinations and etc. However if a DAX calculation for it, the analytical engine of DAX of all different combinations selected through Filter Context report.
What questions can M answer?
M is Data Transformation engine in Power BI. You can use M for doing any data preparation and data transformation before loading that into your model. Instead of bringing three tables of Dim Product, Dim Product Subcategory, and Dim Product Category, merge together in Power Query,Dim Product including all columns from these tables, and load that into the model. Loading all into the model and using DAX to relate these with means consuming extra memory for something not required to be model. M can simply combine those three tables with and “Step Based” operational structure of M, be simply create a final data set.
5. Creating Visuals in Report View
Fortunately Power BI gives you ability to create your own custom visualizations. You can the share your visualization with others in your organization or maybe with the planet through public custom visuals website.
The Report View in Power BI can be used to create beautiful visualizations in Power BI. Power BI has several interconnected modules such as Query Editor, Data View, Relationship View, Report View, etc. Each module has its own functionality.
Before you start creating visualizations using Report View, you need to import data to your model. To do this, from the top menu, click on “Get Data”.
Once you click the “Load Button” after importing the data, The screen is empty at the moment. On the left side of the screen, you should see three options stacked vertically. The topmost option, highlighted in yellow in the screenshot, is the Report View. Click this icon to open the Report View if you have not done so already.
A visualization in Power BI can span multiple pages. Having multiple pages is way to customise our visualizations.
We can create various visualizations from simple clustered column chart to complex visualizations as well. Power BI proves to be an excellent intelligent tool used for visualizing the data graphically. Power BI also allows us to change the color and add tool tips to the visuals created in report view.
Tree Maps:
The Tree maps in Power BI are very useful to display the most important data during a small region. Treemaps in Power BI display hierarchical data as a group of nested rectangles. Each level of the hierarchy in a treemap is represented by a colored rectangle (also called as “branch”) containing other rectangles (“leaves”) Treemaps are a part of visualizations in Power BI.
Power BI Treemap is employed to display the info in Rectangle Boxes. You can define the rectangle box using any Dimension column, and its size by Measure value.
Treemaps show various levelled information as an arrangement of settled square shapes. Each level of the pecking order is spoken to by a shaded square shape (regularly called a “branch”) containing different square shapes (“takes off”). The space inside every square shape is distributed in light of the quantitative esteem being estimated, with the square shapes orchestrated in a measure from upper left (biggest) to base right (littlest).
For instance, in case I’m investigating my business, I may have top-level square shapes (branches) for the attire classifications: Urban, Rural, Youth, and Mix. My classification square shapes would contain littler square shapes (leaves) for the apparel makers inside that class, and these little square shapes would be measured and shaded in light of the number sold. In the Urban branch above, heaps of Maximus attire was sold, less Natura and Fama, and next to no Leo.
Waterfall Model
A waterfall chart are often used for analytical purposes, especially for understanding or explaining the gradual transition within the quantitative value of an entity which is subjected to increment or decrement. Often, a waterfall or cascade chart is employed to point out changes in revenue or profit between two time periods.
A waterfall graph demonstrates a running aggregate as qualities are included or subtracted. It’s helpful for seeing how an underlying worth (for instance, net wage) is influenced by a progression of positive and negative changes.
The segments are shading coded so you can rapidly tell increments and abatements. The underlying and the last esteem sections frequently begin on the flat pivot, while the transitional qualities are skimming segments. As a results of this “look”, waterfall diagrams are additionally called connect outlines.
For data about utilizing the Filters sheet, see Add a channel to a report.
Featuring a section in a waterfall outline cross-channels alternate representations on the report page. Furthermore, the other way around. Notwithstanding, the Total section does not trigger featuring or react to cross-separating.
6. Taking the project to the Cloud with Power BI Pro
Power BI Pro is an individual user license that lets users read and interact with reports and dashboards that others have published to the Power BI service. Users with this license type can share content and collaborate with other Power BI Prousers.
Microsoft Project Web App may be a flexible online solution for project portfolio management (PPM) and everyday work. Project Web App enables organizations to get started, prioritize project portfolio investments and deliver the intended business value. The Project Web App Template App for Power BI allows you to unlock insight from Project Web
App to assist manage projects, portfolios and resources.
How to connect
Select Apps within the nav pane > select Get apps within the upper right corner.
In the Services box, select Get.
In AppSource, select the Apps tab, and search/select Microsoft Project Web App.
You will get a message saying — Install this Power BI App? select Install.
In the Apps pane, select the Microsoft Project Web App tile.
In start together with your new app, select Connect data.
In the Project Web App URL text box, enter the URL for the Project Web App (PWA) you would like to attach to. Note this may differ from the example if you have a custom domain. In the PWA Site Language text box, type the number that corresponds to your PWA site language. Type the single digit ‘1’ for English, ‘2’ for French, ‘3’ for German, ‘4’ for Portuguese (Brazil), ‘5’ for Portuguese (Portugal) and ‘6’ for Spanish.
For Authentication Method, select oAuth2 > Sign In. When prompted, enter your Project Web App credentials and follow the authentication process.
You’ll see a notification indicating your data is loading. Depending on the size of your account this may take some time. After Power BI imports the info , you’ll see the contents of your new workspace. You may need to refresh the dataset to get the latest updates.
After Power BI imports the info you’ll see the report with 13 pages and dataset within the nav pane
Once your reports are ready, plow ahead and begin exploring your Project Web App data! The Template App comes with 13 rich and detailed reports for the Portfolio Overview (6 report pages), Resource Overview (5 report pages) and Project Status (2 report pages).
Collaboration
Collaborate on Power BI Desktop files during a workspace
After you create an influence BI Desktop file, you’ll publish it to a workspace so everyone within the workspace can collaborate thereon . In Power BI Desktop, select Publish on the house ribbon, then select the workspace within the Select a destination box.
Workspaces are better for collaboration than My Workspace, because they permit co-ownership of content. You and your entire team can easily make updates or give others access. My Workspace is best employed by individuals for one-off or personal content.
Now, imagine you have a finished dashboard you need to share with your colleagues. What’s the best way to give them access to the dashboard? The answer depends on a number of factors.
If colleagues got to keep the dashboard up so far , or need access to all or any the content within the workspace, add them to the workspace as Members or Contributors.
If colleagues just need to view the content in the workspace, add them as Viewers.
If colleagues just need to see that dashboard and not all the content in the workspace, you can share the dashboard with them directly.
If the dashboard is part of a set of dashboards and reports that you need to distribute to many colleagues, then publishing an apply the best choice.
Publish app in power bi
To publish App in Power BI, please click on the Publish App button on the highest right corner
Clicking the Publish App button opens the subsequent Power BI window. Under the Details section,
Description: Please provide a legitimate description of this App. It is a required field so, you have to fill something here.
Background Color: Please select the background color of this App. By default, Green selected.
Under the Content tab, specify the App landing page
Specific Content: If you select this option then use the dropdown list to select the dashboard
None: There is no landing page
Under the Access tab
Permissions: Either you can give access to the entire organization or a few individuals or groups.
Once you completed, please click the Finish button.
Click Publish button to publish Power BI app.
Please save the link. This link helps you to open the App. For now, let me click the Get App button.
Power bi mobile
Power BI supports mobile devices through Power BI app for Android, Windows Phone, and IOS. Dashboards of Power BI can be seen nicely in mobile devices. However there are some difficulties to urge a mobile report up and running during a way that it’s useful for mobile users.
7. Creating Visuals in Report View
Some files are uploaded to Power BI(for example, Excel tables or PBI files), some (regular .txt or .xml tables) be converted first. The File category Power BI Desktop download menu offers data options for the connection: Excel, CSV, XML, Text, JSON, and Folder.
Power BI can both local databases cloud. At time connections to cloud storage are dynamic, create a visualization report, Power BI requestsset to local databases,Power BI gateway it up,to contact your local administrator or, again, contact your consultant for help. Currently, Power BI Desktop provides connectivity to databases: SQL Server, Access, SQL Server Analysis Services, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata, and SAP HANA.
REST API
REST API of Power BI is revolutionary to embedding content in Power BI, ability to refresh data set from API, it also gives functions with Gateways and Data Sources. With this API, new data sources, clone data sets, check the credentials of source, get list of all data sources under a gateway, and do many other operations. In other words; Data source management fully automated with REST API.
JSON
Our JSON Connector delivers metadata information established standards that allow Power BI data fields as text, numerical, location, date/time data, and more, BI tools generate meaningful charts and reports.
Utilize server-side functionality and intelligent row-scanning to detect data types.
Drill into unstructured data to parse column names and data types.
Expose existing relationships foreign keys or parent-child structures.
8. How to stay up-to-date in the Power BI World
This is one of the very important module in the complete course because in this module we learn about the right and correct resources to stay up-to-date in the world of Power-BI. Power BI gets extensive support from Microsoft, and hence it receives monthly updates. Not all these monthly updates are crucial enough, so there are a number of minor updates as well as improvements and a lot of similar things.
But if we want to work and stay up-to-date in Power BI world, then we need to stay updated and aware all the updates and improvements applied with the core changes applied in the tool. All the updates are not only applicable to Power BI desktop but to all the other services like Power BI Pro, Power BI servers. To learn about the various updates, a very powerful resource to see is the official Power BI Blog.
To use all the updates and improvements e need to have the latest updated version of Power BI desktop, whereas if we talk about Power BI Pro, as it is a Cloud Application, so it is always up to date but still it is preferable to refresh the files in settings.
Now, as we know that Power BI gets regular updates but an interesting thing is that Microsoft lets the users already use the Preview Features. These features are not implemented in Power BI Desktop.
9. Creating Custom Visuals
Power BI visuals are packages that include code for rendering the info served to them. Anyone can create a custom visual and package it as one . pbiviz file, which will then be imported into an influence BI report.
These visuals are written in TypeScript, a superset of JavaScript. You’ll need to develop visual styling through cascading styles sheets (CSS). Power BI uses the Less pre-compiler, so you can handle advanced features like variables, loops, and nesting.
Custom visuals are created by developers using the custom visuals SDK. Developers use JavaScript libraries such as jQuery, D3, R-language scripts, etc. to create custom visuals from scratch. Once a custom visual is ready, it is then tested and debugged. After it is ready and secure, a developer packages it in the .pbiviz file and share it within an organization or upload it on AppSource as an open-source data visualization. From the AppSource, any Power BI user (author) can import the custom visual to use it in their report.
When to Use Custom Visuals for Power BI?
Custom visuals are a great opportunity to gain deeper insight into your data. Power BI is filled with amazing pre-built visualizations, but your business is exclusive , then are your data needs. Custom visuals are often employed by anyone with the facility BI platform who needs better filtering options, detailed charts, interactive drill-down capabilities, or maybe industry-specific visuals to enhance their dashboards and reports. At the end of the day, it’s about making improvements to the way your organization consumes data and makes decisions.
Using d3.js
The basic idea behind this visual is to ‘lift and shift’ an existing D3. js visual and use it with Power BI. Some alterations are need, of course, but basically the dimensions and the data retrieval. Optional it’s possible to change the colour assignment supported the provided color array.
The ever-increasing variety of data and analytics has resulted in an ever-increasing need to build customized visualizations for diverse types of storytelling. D3.js may be a JavaScript library for producing dynamic, interactive data visualizations in web browsers. It makes use of the widely implemented SVG, HTML5, and CSS standards. Power BI has a variety of built-in charts. It allows you to create statistical visualizations using R libraries and allows use of visualizations from the Power BI visuals gallery as well. The infographic designer tool allows you to create custom infographics also . The limitation is that all these are fixed controls targeted towards a certain type of chart. There are a variety of custom visualizations that are created and used on web applications. There is often a need to have the same visualization in reports as well. This results in a need to display custom charts created using D3 library in Power BI. In this tip we will learn how to create these charts.
Solution
D3.js Visual may be a control that’s available in Power BI Visuals Gallery, which may be wont to render custom D3 visualizations in Power BI Desktop.
One needs to know D3 programming to develop custom visualizations. In this tip, the intention is to only take the D3.js code and port it to Power BI Desktop to render the visualization. So, we’ll be that specialize in sample D3.js code and can understand the way to use this in Power BI Desktop.
Binding real data
When the binding is determined at the point of embedding, as opposed to being predetermined earlier, the binding is known as dynamic binding. When embedding an influence BI report using dynamic binding, you’ll connect an equivalent report back to different datasets counting on the user’s credentials.
This means that you can use one report to display different information, depending on the dataset it’s connected to. For example, a report showing retail sale values are often connected to different retailer datasets, and produce different results, counting on the dataset of the retailer it’s connected to.
The report and therefore the dataset don’t got to reside within the same workspace. Both workspaces (the one containing the report, and therefore the one containing the dataset) must be assigned to a capacity.
As a part of the embedding process, confirm you generate a token with sufficient permissions, and adjust the config object.
getting started with bars
First Approach to feature Data Bars to Table in Power BI. In this example, we are using the Power Bi conditional formatting option available in Desktop. Select the Column Name (metric Value) on/for which you would like to use data bars.
Selecting Data Bars opens the following window.
Base Value: Column that you selected
Show bar Only: If you checkmark this feature , then only data bars display for every row; otherwise, both value and data bar will display.
Minimum: By default, it selects the Minimum value (Minimum Order Quantity). Use the drop-down to vary the Minimum to Number, and assign Minimum value inside rock bottom Value Text box.
Maximum: By default, it selects the Maximum value (Maximum Order Quantity). Use the drop-down to change the Maximum to Number, and assign Maximum value inside the Highest Value Text box.
Positive Bar: Please select the colour of a Positive bar.
Negative Bar: Please select the colour of a Negative bar.
Bar Direction: Change the info Bar direction from left to Right or Right to the left.
Axis: Change the Data Bar Axis color
10. Course Round up
In course round up, the course sheds light up on majority of the power bi essentials and introduces learners with the required knowledge to explore the field by themselves. The best part about Power BI course outweigh the minor improvements and make the learners aware about the various updates and improvements made in the power BI desktop and in the last the most important thing is to always be up to date with the latest version of Power BI desktop.
If you liked the story and want to appreciate us you can clap as much as you can. Appreciate our work by your constructive comment and also you can connect to us on….
Youtube: https://www.youtube.com/channel/SocietyOFAI
LinkedIn : https://www.linkedin.com/company/society-of-ai
Facebook: https://www.facebook.com/societyofai/
Website : https://www.societyofai.in/