Dipping a toe in the Power BI LakeFinding the visit comfortable, familiar and friendlyDavid Moore MScBlockedUnblockFollowFollowingJun 30Photo by Adeolu Eletu on UnsplashMicrosoft released PowerBI in 2014 and I have been following along with interest.
This article summarises my latest flirtation with PowerBI putting my toe in the PowerBI Lake yet again.
First, I will present you with a summary of PowerBI and then illustrate the use of PowerBI and then some Python to close us out.
Power BI and BI providersMy article on Apache Superset explored Apache Superset and looked at the market and proclaimed leaders.
By 2021 the market may reach 26.
5 billion US Dollars of revenue potential.
Below you can review the latest Gartner Magic Quadrant for Business Intelligence platforms.
It presents Microsoft as the clear leader with Tableau following behind.
Power BI Desktop and Tableau Desktop, to me, appear broadly similar in function set.
Source: GartnerWindows 10 users can visit the PowerBI site and download a copy of PowerBI Desktop.
Power BI has a yellow branding which seems calmingOn Launching a copy of Power BI we are presented with the standard Ribbon approach, a canvas to build our content, and the typical palette and properties controls.
So far very standard stuff.
A screenshot of the opening windowPower BI is more than a Desktop application rather it is a full BI platform.
Source: UdemyWork starts on Power BI Desktop where we create our Data model, create our content, do our testing and validation.
Next, we can publish to Power BI Service where there is a lot of magic.
We can share, collaborate, consume our content via Mobile and create powerful embed applications.
Completeness of vision is on display.
Everything works out of the box with very little to install on your local machine.
Ability to Execute is obvious.
Udemy is hosting an excellent course on Power BI which I highly recommend.
You can find that course here.
Ok, so it looks like other Microsoft products, seems familiar to a Tableau User, and seems to have a small palette of visuals.
What is it like to work with?Working with Power BIIndividuals who have worked with Microsoft Excel Query or Power Query will find Power BI familiar.
The Excel Data RibbonIngesting and cleaning dataClicking on the Get Data icon, in Power BI, we get a similar dialogue to Excel.
There is a long list of sources available.
For this illustration, we just need the Text/CSV input.
You can connect Power BI to most types of Databases so long as those databases are properly tuned, indexed and partitioned for performance.
All my toe dipping has been with relatively small datasets but please know that transferring data between a Corporate encrypted and secure warehouse to a local client and/or Power BI Service adds privacy and cybersecurity risks.
My article on Data Science approaches deals more fully with this topic.
Working with the Get Data function we can select our data file using the standard parser function.
Source: Udemy Microsoft Power BI — A Complete Introduction — reproduced for this article.
If you hit ‘Load’ you will get a messy data set.
Hit ‘Edit’ to refine the data before loading.
Welcome to the M-Language dialect= Table.
TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}})The Transform ribbon provides everything we need to get to a ‘Tidy dataset’.
All the usual suspects are here.
As an example, we see Unpivot columns, Split Columns, Pivot, and Transpose.
The applied steps windowEach step in the transformation process can be modified or reversed allowing us to build up a transformation pipeline.
= Table.
PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])Removing rows and setting column names is all supported.
Using relational modelsWith Power BI it is possible to ingest many data sets from varied sources.
In that context, you need to build a relational model.
From UdemyPower BI resembles the Cognos Framework manager and presents the Entity-Relationship Diagram (ERD) allowing all the usual join types.
It is important to plan the model and express the data in terms of FACTS and Dimensions.
One thing I found remarkable is that I did not use Notepad, Excel or other intermediatory tools to make summaries, or re-shape data fields.
Everything is Copy/Paste, and Filter, in Power BI.
You can do all the cleaning directly in the tool.
Making a pictureMoving back to the Visual editor we can make a picture.
Everything drags and drops to build a picture.
Similar to Excel you can have as many sheets as you desire.
Three slicers and a simple bar chart is easyPower BI is easy to work with.
Building a bar chart with three sliders is minutes of work.
Ok, so ingesting data, cleaning, and making a picture is easy.
MeasuresMeasure = Sum(‘FACT-Population’[Population])Welcome to the DAX dialect.
“ DAX includes the following categories of functions: Date and Time, Time Intelligence, Information, Logical, Mathematical, Statistical, Text, Parent/Child, and Other functions.
” from Microsoft.
With Measures you can create any aggregates you require.
Ok, but I am a Python Seaborn fan!Python SeabornIt works!.Power BI presents Python Seaborn visuals right out of the box!.I had some steps to follow.
Here is a summary.
ConfigurationThe Power BI Options.
It is best to create a Virtual environment for Power BI use.
Then tell Power BI about your chosen environment.
‘Set a Python home directory’ is perhaps misleading when really we need to point to the environment.
You can also set-up and point to the IDE.
Getting readyNext, you need to figure out your code.
Power BI is very kind and creates a Pandas Data Frame by default called ‘dataset’.
Install all the dependencies in the environment.
pip install numpy, pandas, matplotlib, seaborn==0.
9.
0# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:# dataset = pandas.
DataFrame(Population, Year)# dataset = dataset.
drop_duplicates()# Paste or type your script code here:import numpy as npimport pandas as pdimport matplotlib.
pyplot as pltimport seaborn as snssns.
set(style="darkgrid")dataset[“Population”] = dataset[“Population”] / 100000000sns.
relplot(x=’Year’,y=’Population’, kind="line", hue="Category", data=dataset)plt.
show()Working with Python is the same in Power BI as any other environment.
Create your visualUse the Py visual from the palette.
Then select the data you want to use.
Passing the data to Python is as simple as selecting the elements from the Fields.
We can use the Visual level filters to restrict the data frame size.
In our illustration, I restrict us to Europe and only show years up to 2020.
A Seaborn plot inside in Power BI — OMG!Finally, here is our Seaborn Plot courtesy of Microsoft Power BI.
MoreWe can do even more powerful things!.What about running a Python script as an ingest mechanism?We can Get Data from a Python script!How exciting and powerful is this?.Install pymongo and then exploit the MongoDB aggregation pipeline and feed the results directly to Power BI.
Go try that one out.