Microsoft Power BI Review

Microsoft Power BI is used to find insights within an organization's data. Power BI can help connect disparate data sets, transform and clean the data into a data model and create charts or graphs to provide visuals of the data.

What are the parts of Microsoft self-service business intelligence solution ?
A) Excel BI Toolkit - It allows user to create interactive report by importing data from different sources and model data according to requirement.

B) Power BI - it is the online solution that enables you to share the interactive reports an queries that you have created using the Excel BI Toolkit.

What is power BI?
Power BI is a cloud-based data sharing environment.
Once you have developed reports (or presentations, if you prefer to call them that) using power query,PowerPivot and Power View, you will probably want to share your insights with your colleagues. This is where Power BI enters the equation. Power BI, which technically is an aspect of SharePoint online, lets you load Excel workbooks into the cloud and share them with a chosen group of co-workers. Not only that, but your colleagues can interact with your reports to apply filters and slicers and to highlight data.
They are completed by Power BI—a simple way of sharing your analyses and insights on PCs and mobile devices from the Microsoft cloud.

What is power BI?
Power BI features that allow you to
• Share presentations and queries with your colleagues.
• Update your Excel file from data sources that can be on-site or in the cloud.
• Display the output on multiple devices. This includes PCs, tablets, and HTML 5-enabled
mobile devices that use the Power BI app.
• Query your data using natural language processing (or Q&A, as it is known).

What are the different Excel BI add ins?
Power Query—find, edit and load external data
PowerPivot—for data modeling for analysis
Power View—design visually and interactively reports
Power Map—display insights on 3D Map

Which language we use for calculated column/calculated field in Power Pivot.?
DAX(Data Analysis eXpression)

What is xVelocity in-memory analytics engine used in power pivot?
The main engine behind power pivot is the xVelocity in-memory analytics engine.
It can handle large amount of data because it stores data in columnar databases and in memory analytics which results in faster processing of data as it loads all data to RAM memory.
For best result use 64 bit operating system as 64 bit can load more data to RAM.

What is power pivot Data Model?
Data Model in Power pivot is made up of tables, columns, data types, and table relations.
Typically data tables are constructed to hold data for a business entity

What is DAX?
To do basic calculation and data analysis on data in power pivot we use Data Analysis Expression (DAX).It is formula language to compute calculated column and calculated field.
-Dax works on column values
-Dax can not modify or insert data
-We can calculate calculated column and measures but we can not calculate rows using dax
Sample DAX formula syntax:
For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount ] column in the Sales table.

What is DAX?
A- Measure Name
B- equal sign - indicate beginning of formula
C- DAX Function
D- Parenthesis for Sum Function
E- Referenced Table
F- Referenced column name

What is power query?
Power query is a ETL Tool used to shape, clean and transform data using intuitive interfaces without having to use code.
Import Data from wide range of source from files to databases , big data to social media
Join and append data from multiple data sources
Shape data as per requirement by removing and adding data

Which language is used in power query?
A new programming language is used in power query called M-Code. It is easy to use and similar to other languages. M-code is case sensitive language.

What is Power Map?
At present Power Map is only available as Excel Add-in, using this we can visualize data on 3D map.
Power Map is an Excel add-in that provides you with a powerful set of tools to help you visualize and gain insight into large sets of data that have a geocoded component. It can help you produce 3D visualizations by plotting up to a million data points in the form of column, heat, and bubble maps on top of a Bing map. If the data is time stamped, it can also produce interactive views that display how the data changes over space and time.

What are the primary requirement for a table to be used in Power Map.
For a data to be consumed in power map there should be location data like:
Latitude/Longitude pair
Street, City, Country/Region, Zip Code/Postal Code, and State/Province, which can be geolocated by Bing
The primary requirement for the table is that it contains unique rows. It must also contain location data, which can be in the form of a Latitude/Longitude pair, although this is not a requirement. You can use address fields instead, such as Street, City, Country/Region, Zip Code/Postal Code, and State/Province, which can be geolocated by Bing.

What are the data destination for power queries?
There are two destination for output we get from power query
Load to table in a worksheet or
Load to the Excel Data Model
Power Query gives you two choices for where the output of a query will be loaded: a table in a worksheet or the Excel Data Model

What is Power BI Designer?
It is a stand alone application where we can make Power bi reports and then upload it to Powerbi.com, it does not requires excel.
Actually it is a combination of Power query, Power Pivot, and Power view.

Can we refresh our Power bi reports once uploaded to cloud (Share point or Powebi.com)?
Yes we can refresh our reports through Data Management gateway(for sharepoint) and PowerBI Personal gateway(for Powerbi.com)

What is data management gateway and PowerBI Personal Gateway.
Data Management Gatway is a application which is installed on source data machines so that reports deployed on sharepoint can scheduled to automatic refresh
Similarly PowerBI Personal Gateway is for reports which are deployed on Powerbi.com

What is power bi Q&A?
It is a natural language data querying where we can directly plot charge by simplying typing question on data set like:
-Total sales by year and area
It is availabe only when reports are published to colud (Powerbi.com or sharepoint)

What are the main difference between older power bi and newer power bi?
New power bi have new designing tool called Power BI Desktop which is a stand alone designer for power bi which in back-end include Power view,Power-pivot and power query
where as older excel version have power bi add ins for excel (power view , power pivot, power query and power map )
And there are more graphs available in new version of power bi like combo chart, line area chart, water fall, tree map etc

Can we have more than one active relationship between two tables in data model of power pivot?
No we cannot have more than one active relationship between two table, but we can have more than one relationship between two tables but there will be only one active relationship and many inactive relationship as shown below dotted lines are inactive and continuous line as active.

For which platform Power BI App is available?
1 Android Mobile
2 iPhone and iPad
3 Windows Desktop and Windows Tablets
4 Windows Phone- Coming soon

What's the difference between Power BI and Power BI Pro?
Power BI provides all sorts of features to help you get started exploring data in a whole new way. Power BI Pro provides all of the same great features in Power BI, plus additional features like more storage capacity, scheduling data refresh more frequent than daily, live data sources with full interactivity, groups, and more. Learn more about the differences between Power BI Pro and the free Power BI.

What is Power BI Desktop?
Power BI Desktop is a free desktop application you can install right on your own computer. Power BI Desktop works cohesively with the Power BI service by providing advanced data exploration, shaping, modeling, and report creation with highly interactive visualizations. You can save your work to a file, and publish your data and reports right to your Power BI site to share with others.

What are content packs?
Content packs for services are pre-built solutions for popular services as part of the Power BI experience. A subscriber to a supported service can quickly connect to their account from Power BI and see their data through live dashboards and interactive reports that have been pre-built for them. We've released content packs for popular services such as Salesforce.com, Marketo, Adobe Analytics, Azure Mobile Engagement, CircuitID, comScore Digital Analytix, Quickbooks Online, SQL Sentry, and tyGraph. Over the coming months, we'll extend this to include content packs and integrations for Sage, SpaceCurve, Sumo Logic, Zuora, Planview, Insightly, Troux, Inkling, and others.

Organizational content packs provide users, BI professionals, and system integrators the tools to build their own content packs to share purpose-built dashboards, reports, and datasets within their organization.

What are the different Excel BI add ins?
Power Query—find, edit and load external data
PowerPivot—for data modeling for analysis
Power View—design visually and interactively reports
Power Map—display insights on 3D Map

What are the different steps involved in Power BI data refresh ?
To use data refresh, you need a way to pull data from the data source (SQL Server, in this example) and push it directly into the SSAS model generated by Power BI.

Configuring automatic refresh in Power BI.
Power BI now has all of the information it needs to refresh the data, both on demand and on a scheduled basis. Expanding the Schedule Refresh section, you can define when Power BI attempts to refresh the dataset.

What is the Differences between visual level filters, page level filters and report level filters in Power BI.
Visual-level Filters

These filters work on only an individual visualization, reducing the amount of data that the visualization can see. Moreover, visual-level filters can filter both data and calculations.

Page-level Filters

These filters work at the report-page level. Different pages in the same report can have different page-level filters.

Report-level Filters

There filters work on the entire report, filtering all pages and visualizations included in the report.

We know that Power BI visual interactions feature, which makes filtering a report a breeze.
Visual interactions are useful, but they come with some limitations:
=> The filter is not saved as part of the report. Whenever you open a report, you can begin to play with visual filters but there is no way to store the filter in the saved report.
=>The filter is always visible. Sometimes you want a filter for the entire report, but you do not want any visual indication of the filter being applied. In other

The following are the Building Blocks (or) Key components of Power BI
- Visualizations

- Datasets

- Reports

- Dashboards

- Tiles

Visualizations
Example:
Pie Chart, Line Graph, Side by Side Bar Charts, Graphical Presentation of the source data on top of geographical map, tree map etc.

Datasets
Example:
Excel sheets, oracle or sql server tables.

Report
In Power BI, a report is a collection of visualizations that appear together on one or more pages. Example:
Sales by Country, State, City Report, Logistic Performance Report, Profit by Products report etc

Dashboard
Dashboard is single layer presentation of multiple visualization. i.e we can integrate one or more visualizations in to one page layer. When you're ready to share a single page from a report, or share a collection of visualizations, you create a dashboard.

Example:
Sales dashboard can have pie charts, geographical maps and bar charts

Tile
Tile is a single visualization in a report or on a dashboard.

Example:
Pie Chart in Dashboard or Report.

Power Bi Can be saved to
Cloud,
content pack, as an individual Excel workbook, and as a .pbix file.

Power BI GUI
File , Home, View, Modeling

Power BI Data
navigator - shows db objects

Home Tab
Visualization, Fields

Modeling Tab
Report / Data / Relationships

Power BI Web My Workspace
Dashboard, Reports, Workbooks, DataSets

Parts of Power BI
Windows desktop, Online (sas) software as a service, Mobile Bi Apps. Desktop, service and mobile

5 Basic Building Blocks
Visualizations, Datasets , Reports,dashboards,Tiles

canvas
A dashboard has to fit on a single page, often called a canvas (the canvas is the blank backdrop in Power BI Desktop, or the service, where you place visualizations).

Power Bi Service
Content Packs - git hub, Out of the box dashboards,reports

Clean Data
clean and format your data to make it more usable, including splitting and renaming columns, changing data types, and working with dates. You can also create relationships between columns so that it's easier to model and analyze your data

Apps
Apps are the new way to distribute content to large audiences in Power BI. We recommend using apps instead of organizational content packs or read-only workspaces.

App Workspaces - (repackaging )
1.App Workspaces seem to be rebadged Power BI Groups

App creation- (repackaging )
very reminiscent of that of a content pack

Content Packs (drawbacks)
1.No formal support for Application Lifecycle Management (ALM)
2.No ability to push content to end users
3.End users have a hard time distinguishing shared content

App Workspaces - (drawbacks)
The issue in play is that every time a new Group/App Workspace is created in Power BI, a new Office 365 Group is created as well. This is good and bad. Good because Office 365 Groups are very powerful from collaboration perspective particularly when you factor in the capabilities offered by Microsoft Teams. Bad is because Office 365 administrators don't seem to appreciate when someone other than them has power to create Office 365 groups. Looks like this dependency on Office 365 Groups will soon go away. For now, however, we do not have any other choice but to use App Workspaces for everything

My Workspace Navigation (web)
Dashsboards, reports,workbooks,datasets

Premium capacity nodes
P1 8 v-cores 4 cores, 25GB RAM 4 cores 30 per second
P2 16 v-cores 8 cores, 50GB RAM 8 cores 60 per second
P3 32 v-cores 16 cores, 100GB RAM 16 cores 120 per second

Custom Column
tool gives advanced users the option of writing query expressions from scratch using the powerful M language

Transpose data
Cleaning Irregularly Formatted Data
Transpose you can flip data (turn columns to rows, and rows into columns) so you can break data down into formats that you can manipulate

Format data Cleaning
Irregularly Formatted Data
promoting rows into headers into to break headers, using Fill to turn null values into the values found above or below in a given column, and Unpivot Columns, you can cleanse that data into a dataset that you can use in Power BI.

Query editor menu item
File /home/transform/add column/view

Modeling Your Data
you can use multiple tables from multiple sources, and define the relationship between them
Relationship view-visually set the relationship between tables or elements.

calculated column
new column that you create by defining a calculation that transforms or combines two or more elements of existing data.

Hiding fields
their data is still available and is still part of the model, they're just hidden from view

Sorting fields
The Sort by Column tool, available in the Modeling tab, is very useful to ensure that your data is displayed in the order you intended. , "August" appears before "February".

New Measure
Modeling tab., DAX, the Data Analysis Expression language in Power BI
ytd = totalytd(sum9sale[reveune]),'date'[date])

Home Tab
Report/data/relationships (relationships-edit queries)
close apply,manage parm, keep row, merge append etc...

Calculated Tables
function within DAX that allows you to express a whole range of new modeling capabilities

Explore Time-Based Data
automatically include generated fields that let you drill down through years, quarters, months, and days with a single click

Visualizations display data - Default.
Visualizations display data at the year level by default, but you can change that by turning on Drill Down in the top right-hand corner of the visual

combination chart
two measures that have very different scales, such as revenue and units

Slicers
most powerful types of visualizations, particularly as part of a busy report. A slicer is an on-canvas visual filter in Power BI Desktop that lets anyone looking at a report segment the data by a particular value, such as by year or by geographical location.

Create bubble maps
select the Map option in the Visualization pane. You must add a value to the Location bucket in the Visualizations options to use a map visual

Create shape maps
As with bubble maps, you must add some kind of value to the Location bucket to use this visual.
A warning icon in the top left corner of your visual indicates that the map needs more location data to accurately plot values. This is a particularly common problem when the data in your location field is ambiguous, such as using an area name like Washington that could indicate a state or a district. One way to resolve this problem is to rename your column to be more specific, such as State. Another way to resolve it is to manually reset the data category by selecting Data Category in the Modeling tab. From there you can assign a category to your data such as "State" or "City".

Matrix
A matrix is similar to a table, but it has different category headers on the columns and rows. As with tables, numerical information will be automatically totaled along the bottom and right side of the matrix.There are many cosmetic options available for matrixes, such as auto-sizing columns, toggling row and column totals, setting colors, and more

tabular visualizations.
In fact, when you grab a categorical field or text field and drag it onto the report canvas, you get a table of results by default. You can scroll up and down through the table, and initially it will be sorted alphabetically