power bi - finally i can make decisions based on facts

Enterprise Software Development

Join the Conversation #PowerBI @UlyssesMac

Power BI

Finally I can make decisions based on facts

Join the Conversation #PowerBI @UlyssesMac

.NET User Group - October 2016

SSW General Manager

Ulysses Maclaren #ulyssesmac

ulysses.maclaren ulyssesmaclaren

At SSW for over 10 years Obsessive Scrum Master Teaches Salsa and won a World Latin Dance

Cup in 2014Join the Conversation #PowerBI @UlyssesMac

Click icon to add picture

Disclaimer: I am not a developer

Making a New Report from Scratch

Power BI to the Rescue

History

The Age of Traditional Reporting

Table of Contents

Tips

Summary

1970 – First Database systems invented, known as Decision Support Systems (DSS)1985 – Excel1991 – SQL Server 1.11991 – Crystal reports (AKA Quick Reports)1992 – Access 1.02004 – SQL Server Reporting Services2011 – Telerik Reporting2015 – Power BI Join the Conversation #PowerBI @UlyssesMac

Click icon to add picture

Google Trends

Join the Conversation #PowerBI @UlyssesMac

Making a New Report from Scratch

Power BI to the Rescue

History

The Age of Traditional Reporting

Tips

Summary

I have questions:Who could I book

for upcoming client work?

Are my salespeople

keeping on top of their

opportunities

How is the company going

financially?

No problem… I’ll make you a report in SQL

Reporting Services

Reporting Services (old)

Once reports have been developed, I can get key information quickly

Join the Conversation #PowerBI @UlyssesMac

Reporting Services (old)

Once reports have been developed, I can get key information quickly

These reports allow me to answer FAQs

Join the Conversation #PowerBI @UlyssesMac

Reporting Services (old)

Once reports have been developed, I can get key information quickly

These reports allow me to answer FAQs

I can see dashboards, and with enough work, they can look

pretty good

Join the Conversation #PowerBI @UlyssesMac

The ProblemsLong report development timeHigh cost of report developmentDelayed answers to key questionsMissed opportunities I can’t freely ask new questionsThis does not breed a culture of curiosity

Join the Conversation #PowerBI @UlyssesMac

The Big ProblemDue to the difficulty in getting quick answers,

I was making decisions based on guesses and war stories

Join the Conversation #PowerBI @UlyssesMac

Join the Conversation #PowerBI @UlyssesMac

Examples:Should I talk the boss into opening a new

office in another state? – high $$When should I hire a new guy (or sack)?What technologies should we be focussing

on?Plus more… like seeing trends on an Ad Hoc

basis

Making a New Report from Scratch

Power BI to the Rescue

History

The Age of Traditional Reporting

Tips

Summary

Click icon to add picture

I can’t show you client reports - Private data

Timesheet data from

Join the Conversation #PowerBI @UlyssesMac

Finance data from

Join the Conversation #PowerBI @UlyssesMac

Sales data from CRM

Join the Conversation #PowerBI @UlyssesMac

Click icon to add picture

Phone call data from

Phone call data from

Click icon to add picture

Source Control data from

Here’s some I prepared earlier

Click icon to add picture

Click icon to add picture

My Journey• A developer setup my 1st dataset• Started with the browser editor• Moved to Desktop• Using Git for source control• Discovered calculated columns• Discovered the Custom Visualisations Marketplace• New Power BI reports are now very fast to spin up

Click icon to add picture

Custom Visualisations Marketplace

Power BI - ProsReports can be made by power usersEasy to drill up/down

Can interactively cross filter charts

Easy to merge multiple data sources into one reportDashboards are mobile friendly (responsive)

Join the Conversation #PowerBI @UlyssesMac

Power BI - ProsData gets refreshed on a schedule

Can easily be triggered manually

Can also be set up with direct query

Join the Conversation #PowerBI @UlyssesMac

Join the Conversation #PowerBI @UlyssesMac

Power BI - Unexpected benefitsData cleanedBusiness processes improved – new fields

added

Power BI – Cons – our version is at http://bettersoftwaresuggestions.com/category/microsoft/powerbi/

Power BI - ConsNo alignment helper controls for

visualisations

Join the Conversation #PowerBI @UlyssesMac

Power BI - ConsNo alignment helper controls for

visualisations

No year-month slicer (I had to add a custom dimension for this)

Join the Conversation #PowerBI @UlyssesMac

Power BI - ConsNo alignment helper controls for

visualisations

No year-month slicer (I had to add a custom dimension for this)

Can’t hide slicer section

Join the Conversation #PowerBI @UlyssesMac

Power BI - ConsNo alignment helper controls for visualisations

No year-month slicer (I had to add a custom dimension for this)

Can’t hide slicer section

No easy way to specify financial year rather than annual year

Join the Conversation #PowerBI @UlyssesMac

Power BI - ConsNo alignment helper controls for

visualisations

No year-month slicer (I had to add a custom dimension for this)

Can’t hide slicer section

No easy way to specify financial year rather than annual year

No source control for collaboration

Join the Conversation #PowerBI @UlyssesMac

Making a New Report from Scratch

Power BI to the Rescue

History

The Age of Traditional Reporting

Tips

Summary

Creation ProcessConnect to a data sourceUse the modelling tab to manipulate your data setCreate visualisations on reports and dashboards

Join the Conversation #PowerBI @UlyssesMac

Join the Conversation #PowerBI @UlyssesMac

Warning: Live Demo

Join the Conversation #PowerBI @UlyssesMac

SummaryPull in the data you want…Tables from SQL Server in this case

Join the Conversation #PowerBI @UlyssesMac

Click icon to add picture

SummaryClick on Edit Queries | Choose Columns… To focus on what you care about

Join the Conversation #PowerBI @UlyssesMac

Join the Conversation #PowerBI @UlyssesMac

SummaryDrag and drop fields to create visualisations

Join the Conversation #PowerBI @UlyssesMac

SummaryAdd slicers and/or page filters… To limit the dataset

Join the Conversation #PowerBI @UlyssesMac

SummaryAdvanced: Create measures or columns if necessary to view your data in different ways…Using DAX

Click icon to add picture

Join the Conversation #PowerBI @UlyssesMac

SummaryChoose the visualisation type that works best

Join the Conversation #PowerBI @UlyssesMac

SummaryPlay with formatting (e.g. changing titles, removing unnecessary headers, using Aussie state colours, etc…)

NSW = Blue, QLD = Maroon

Join the Conversation #PowerBI @UlyssesMac

SummaryPublish/share reports

Making a New Report from Scratch

Power BI to the Rescue

History

The Age of Traditional Reporting

Tips

Summary

TipsCreate measures and new columnsThis will let you see metrics and slice up your data any way you want

Join the Conversation #PowerBI @UlyssesMac

Calculated Columns and MeasuresCalculated columns:

• Stored in the database• Often used to filter/group dataMeasures:

• Computed on aggregates of values• Computed at query time• Often used to give a numerical metric

Learn some basic DAX – even just IF statements – it’s cool and less scary than I thought

Join the Conversation #PowerBI @UlyssesMac

TipsIf you can do Excel formulas…

Click icon to add picture

Join the Conversation #PowerBI @UlyssesMac

TipsIntegrate with SQL Server Reporting Services

Your 1st reports will be from the starting point of “What can I do with the data I have?”

After that, your best reports will have the starting point of “Who am I and what do I want to know?”

Don’t get stuck on using your “favourite visualisation” – use the best one for the situation

TipsKnow when to use Dashboards vs Reports

Put slicers on the left when possible… the check boxes line up

Click icon to add picture

Tufte Principles: Think of your Data-Ink Ratio

Click icon to add picture

Sort Order (Alphabetical sucks)

Click icon to add picture

Question: Date Sorting

Making a New Report from Scratch

Power BI to the Rescue

History

The Age of Traditional Reporting

Tips

Summary

Remember The Big Problem?Due to the difficulty in getting quick answers,

I was making decisions based on guesses and war stories

Join the Conversation #PowerBI @UlyssesMac

Should I talk the boss into opening a new office in another state? – high $$

When should we hire a new guy (or sack)?

What technologies should we be focussing on?

Plus more, like Ad Hoc seeing trends

Thank you!

https://www.ssw.com.au/ssw/Consulting/Power-BI.aspx https://rules.ssw.com.au/rules-to-better-power-bi

uly@ssw.com.auwww.ssw.com.au

Sydney | Melbourne | Brisbane

Price of Pizza Please fill out this short feedback survey.

Note: Search your app store for “QR”… or http://svy.mk/2dXvswt