Overview of Power BI

Agenda 

u  What is Business Intelligence?

u  BI Usage Examples

u  Microsoft Power BI

u  Power BI Architecture

u  A look at the parts of Power BI:

u  Power BI Desktop

u  Power BI Service

u  Power BI Mobile

u  Power BI Embedded

u  Power BI Video: Demo of some features, publishing & creating a dashboard

u  Some useful DAX formulas

What is Business Intelligence?

u  The term Business Intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis, and presentation of business information. The purpose of Business Intelligence is to support better business decision making.

u  1 Provide quality data

u  2 Gain deeper insight and facilitate better decision making

u  3 Align decisions with corporate goals

 BI Usage Examples:

u  Effect of decrease in price of 1.5 liter Pepsi on sale of 0.5 Liter Pepsi

Last year, Pepsi reduced price of 1.5 liter Pepsi. It impacted sale of 0.5 liter pepsi.

With the help of BI, Pepsi can decide whether it shall reduce price of 1.5 liter or not, was it beneficial last year or not ?

u  Effect of introducing call and data packages by Telecom companies

They introduce various packages to increase their revenues on basis of analytics

 Microsoft Power BI

u  Power BI is a set of tools and technologies for business intellignence

u  Power BI has data acquisition and transformation, data modelling and visualization tools.  It is html 5, semi open source, app enabled and cloud enabled SaaS.

u  Power BI is intended more to “power users” as well as developers. It can create dashboards with great presentation. It can be easier to create visualizations with correct data, but it might not be great if you want to print something. It’s basically the evolution of Powerpivot and Powerview.

u  Free to use, works with unstructured data, user create their own reports by drag and drop of fields, minimal training required, modern rendering

u  PowerBI data goes to the cloud but using DirectQuery is workaround for On Premises data.

Power BI Desktop

•       Free download that starts your Power BI experience.

•       Not an end user tool, but a power user and designer tool.

•       Can be used to mash, model and design engaging experiences.

•       Transform and clean data

•       Design once and view anywhere

Power BI Service

•       Cloud based service (Part of Office 365)

•       Access to all data, wherver it may live

•       Ask questions, integrate with cortana analytics and more

•       Create curated content based on your company needs

•       Share insights across web, mobile and embedded within your own applications.

Power BI Mobile

•       Consume from almost any device

•       Microsoft

•       Android

•       Apple – Including Apple Watch

•       Set alerts that allow for proactive engagement

•       Offline cache for dashboard consumption without an internet connection.

Power BI Embedded

•       Ability to embed within any application or website.

•       Includes Dynamics ERP and CRM

•       Ability to deploy quickly with the scale of the Microsoft cloud.

Power BI Demo of some features, publishing & creating a dashboard

Some Useful DAX formulas

LookUp function : The following Lookup function is getting [Full Name] column from “User” entity where User[User Id] = opportunities[_ownerid_value]. This OwnerName column is being added in opportunities entity as calculated column.

OwnerName = LOOKUPVALUE (

        User[Full Name],

        User[User Id], opportunities[_ownerid_value]

    )

Related() function: The following statement is getting column named [Full Name] from User table on basis of PrimaryKey-ForeignKey relationship of current entity with User entity. i.e. if this column OwnerName is being added in Sales entity, then it will pick User.[Full Name] on basis of Sales. UserID = User.UserID.

OwnerName = Related(User[Full Name])

Null Values: To avoid null value, add 0 to a calculation.

TotalContractualValue = CALCULATE (sum(opportunities[new_licensefee])) + 0

Usage of Format function for numeric data:

TotalContractualValue2 =

IF (

    opportunity[TotalContractualValue] >= 1000000,

    FORMAT ( opportunity[TotalContractualValue], “$#,##0,,.0m;($#,##0.0)m” ),

    IF (

        opportunity[TotalContractualValue] >= 1000,

        FORMAT ( opportunity[TotalContractualValue], “#,##0,k” ),

        FORMAT ( opportunity[TotalContractualValue], “General Number” )

    )

)

Following are some sample formats.

“$#,##0.0;($#,##0.0)”  >> >> show 16.7million (16700000) as 167,00,000.0

“$#,##0,,.0m;($#,##0.0)m” >> show 16.7million (16700000) as 16.7m

“#,##0,,m”      >> show 16.7million (16700000) as 17m

Taking average days, on basis of dates difference:

AvgDays =  averagex(leads, Datediff(leads[estimatedclosedate],leads[createdon],DAY)  )  + 0

Taking average against a filter/where condition:

AvgDealAge = CALCULATE(

                averagex(opportunities,Datediff(opportunities[createdon],today(),DAY) ) + 0,

                FILTER(

                                opportunities,

                                opportunities[createdon] <= today()

                )

)

Calculating Week number (i.e. week# in month):

WeekInMonthNo = FLOOR( Day( leads[createdon] ) / 7 ,1) + if( mod( day(leads[createdon]) , 7) =0, 0, 1)

Using switch statement in PowerBI:  In following statement, if process[category]= 100000000 then it will return “A”

CategoryName = Switch( process[category],100000000,”A”,100000001,”B”,100000002,”C”)

Compiled by: M. Imran Iqbal

M. Imran Iqbal is a .Net and BI Developer/ Architect at Allied Consultants. His areas of expertise are business intelligence, website development and ODOO. i.e. Microsoft PowerBI, SSRS, SSAS, SSIS, Crystal Reports, Asp.net, Asp.net MVC, WCF, WebApi, Responsive design, Angular, MySQL, SQL Server 2000, 2005, 2008, 2012, 2014, 2016, ODOO development and implementation.

He can be reached at +92-322-4745773,

Skype: mianimraniqbal,

E-mail: [email protected]