Database Analysis and Design - PDF

CRICOS 00111D
TOID 3059
Database Analysis and Design
Week 04
Power BI - Importing Data, Visualizations, and
Access and Power BI functions

Microsoft Power BI
Microsoft have a number of Business Intelligence (BI) products / applications
Power BI is a suite of desktop & cloud based web application that allows you to
analyze datasets.
The datasets can be local or cloud based and may be connected to / imported from
RBDMSs, Spreadsheets, on-line data services etc.
Power BI can be added to the Office 365 suite of applications.
Through Power BI web app, users can analyze data with various visualizations and
dashboards.
One dataset may produce many different visualizations
• Best performing customers
• Worst preforming sales people
• Upward trending products
• Complimentary product sales

Microsoft Power BI
Power BI Desktop is a different product from Power BI web app.
Desktop is a Windows application.
It must be installed on a computer running Windows (or via a virtual Windows OS
on a MAC or Linux)
Importing data / manipulating data / writing formula / creating measures is done
using the Desktop app.
Once visualizations / reports / dashboards have been created, then they are
deployed to a cloud service so that Power BI web app can be utilized.
Power BI Desktop is most often used by BI analysts & developers
Power BI web app is used by end-users and BI analysts.

Microsoft Power BI Desktop
Business Intelligence software.
• Power BI Desktop is a Windows
only application.
Problem:
One known problem exists when PBI
connects to an Access Database:
• Both apps must be the same OS version
• If Access is 32bit then download Power BI 32 bit
• If your Access is 64 bit then download Power BI 64 bit
All Swinburne labs have 64bit Access and 64 bit Power BI Desktop installed.

Microsoft Power BI help
If you plan to install a version of Power BI on your own computer, then these links may be
useful:
What version of Office am I using?
https://support.office.com/en-us/article/What-version-of-Office-am-I-using-932788b8-
a3ce-44bf-bb09-e334518b8b19
Resolve Issues Importing Access Databases in Power BI Desktop
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-access-database-
errors/
Download Microsoft Power BI Desktop
https://www.microsoft.com/en-in/download/details.aspx?id=45331

Microsoft Power BI Desktop
In the next week or two, we will concentrate on Power BI Desktop.
We will create datasets, reports, dashboards.
We will not deploy / upload reports & dashboards to a cloud service yet.

Microsoft Power BI Desktop
Begin the Power BI Desktop application.
Start with selecting the Get Data option
The application can connect to / import data
from many popular sources (including
non-Microsoft applications).
We will concentrate on connecting to Access
Database sources.
If you cannot connect to Access (usually because of the 32bit
vs 64 bit problem), then you will need to export data from
tables to a CSV files. Finally you will import the CSV files into
Power BI Desktop. A bit messy – but easily done.

Microsoft Power BI Desktop
Typically, you will have to choose which tables you want to connect to.
You can also connect to queries (or views as they are called in other RDBMSs)
There is no difference between a Table and a Query from the Power BI point of view.
Both present data as a collection of records made up of multiple fields.

Microsoft Power BI Desktop
There are 3 work area icons on the left of the Power BI screen.
Report, Data and Relationship
Report Window – Where you add visualizations
Data Window – Where you view raw data, add columns & formula
Relationships Window – Where you view / create relationships between tables

Power BI Relationships Window
The relationships that you created in Access are transferred to Power BI
You can add additional relationships as required.
(e.g. when data is added from other sources) The symbols differ
slightly from Access

Power BI Data Window
The Data Window is where you
• Add additional columns / measures to the dataset
• Remove unwanted columns
• Write DAX (Data Analysis Expressions) formula. DAX formula are similar as Excel formula
plus the inclusion of additional functions designed to work with relational data and
perform dynamic aggregation. We will look at DAX formula later in the semester.

Power BI Visualizations
Data visualizations are created in the Report Window.
A number of visualizations are similar to Excel charts and Excel Power Pivot charts.
Many visualizations are unique to Power BI.
Typically, 1. Select a visualization. 2. Select fields to act as values for the visualization

Microsoft Power BI Desktop
Table visualization
Very similar to an Access Query.
Simply click that field names that you wish to add to the table visualization.
Each field name adds a column to the visualization.

Microsoft Power BI Desktop
Matrix visualization
Very similar to an Access Crosstab Query or Excel Pivot Table.
• Select a field for the row values (e.g BirthCountry)
• Select a field for the column values (e.g Gender)
• Select a field for aggregation (e.g. Count of ActorNo)

Microsoft Power BI Desktop
The Report workspace
Multiple visualizations can appear on a report at the one time
Actors by Birth CountryActors by Gender

Microsoft Power BI Desktop
Customize the visualization setting via the Paintbrush icon
Some visualizations have 10 or more categories of settings
Click the paintbrush icon
to see the various visual
settings for the
visualization

Microsoft Power BI Desktop
The settings for the visualization only apply to a single
visualization. The settings cannot be saved (although
you can save the entire workspace as a PBIX file).
If you delete a visualization and then recreate it, you will need
to recreate all of the settings again. 
Default style
Custom style

Microsoft Power BI Desktop
A slicer visualization is a visual filter.
The slicer applies to all other visualizations in the report

Microsoft Power BI Desktop
The report work area may have multiple slicers
The slicers have paintbrush icon settings that allow options such as
• Only one box to be ticked
• Multiple box to be ticked
• Select All box

Microsoft Power BI Desktop
Power BI allows you to use data from multiple sources
This example allows the developer to create a new table (and data)
within the Power BI dataset.
Click Enter Data and select Create Table.
This example creates the table MyLocations and creates the ID and Suburb fields
Imagine that every
customer record has
a location id
of 1 to 6

Microsoft Power BI Desktop
The new table can be added to the existing
relationship diagram
customer.locationcode references MyLocations.id

Microsoft Power BI
Map Visualization.
The Map visualization tool can highlight a
geographical point on a map.
You can use any (all) of these to indicate a
geographic point
• Suburb
• Town
• City
• State
• Country Name
• PostCode
• Longitude & Latitude co-ordinates
In this example the suburb name is used to
identify a geographic location on a map

Microsoft Power BI Desktop
Map Visualization.
Power BI places dots on the Map.
The size of the dot is specified in the Size property.
Here we have used ID – not very meaningful.
Typically we would use CountOfCustId or
SumOfSaleQty

Microsoft Power BI Desktop
There is a often a problem with individual suburb names, town names, postcodes etc.
Power BI is a little bit North America - centric.
In such situations you may have to provide more specific location information.

Microsoft Power BI Desktop
In this example, we will add a new field to an existing table.
Under the New Measure icon, select the New Column option.

Microsoft Power BI Desktop
This is where it can be confusing.
The formula bar is when you enter the new field name.
Replace the phrase "Column = " with …

Microsoft Power BI Desktop
…
The name of the new field (e.g. FullAddress)
Then =
The new calculation (e.g. [suburb] & ", Victoria, Australia"
Note: The ampersand character & is used to join / concatenate string values.

Microsoft Power BI Desktop
A new field named FullAddress has been built and populated.
This new data can be used by the Map visualization with far better results

Microsoft Power BI Desktop
Power BI also allows you to categorize type of data in a table
Click on a field and click a matching category type

Microsoft Power BI Desktop
Now when the data is displayed, all the locations should be confined to the
country, state etc. that you specified.

Microsoft Power BI Desktop
Tree Map visualization. A rectangle is drawn for each data value.
The bigger the rectangle the bigger the data values.
This example highlights the count of castings for each actor.
Many rectangles are very small (an actor may only have 1 casting).

Microsoft Power BI Desktop
Most visualizations have a Visual Filter setting.
In this case the minimum Count of Castings has been set at 10
Rectangles only appear for actors who meet this criteria.

Access / Power BI Functions
In Access queries and Power BI data window you often need to manipulate data.
There are many built in functions that can assist.
Many functions have identical syntax in Access and Power BI.
Use Google and www.techonthenet.com/access/functions to assist with the syntax.
Joining / Concatenating strings
Use the ampersand & character to join strings
= [movieno] & " " & [title]
If you include numeric data, the application will automatically convert the number
to a string
= [movieno] & " " & [title] & " " & [relyear]
https://www.techonthenet.com/access/functions/string/concat.php

Power BI Functions
Left(), Right(), Mid()
These functions grab a small part of a string from a larger string.
Access and Power BI are not zero-based (as distinct from many programming languages).
The first character in a string is in position 1 – not position 0.
Left(<original string>, <number of characters>)
Right(<original string>, <number of characters>)
Mid(<original string>, <starting position>, <number of characters>)
Title is "The Matrix"
Left([title],3) results in "The"
Right([title],4) results in "trix"
Mid([title],5,3) results in "Mat"
https://www.techonthenet.com/access/functions/string/mid.php

Power BI Functions
upper(), lower(), ucase(), lcase()
These functions convert a string into upper or lower case.
Any characters not a-z or A-Z are ignored and do not change.
Access uses ucase() and lcase()
Power BI uses upper() and lower()
Title is "The Matrix"
upper([title]) results in "THE MATRIX"
lower([title]) results in "the matrix"
https://www.techonthenet.com/excel/formulas/ucase.php

Power BI Functions
mod()
The MOD function returns the remainder of a number after it is divided by a divisor.
MOD(<number>, <diviser>)
e.g. MOD([relyear],4)
https://powerapps.microsoft.com/en-us/tutorials/function-mod/

Power BI Functions
Date(), Today(), Now() & DateValue()
Today() returns the current date in Power BI
Date() returns the current date in Access
Now() returns the current date and time in both Access & Power BI
DateValue(<original string date>) returns a date based on a string
DateValue ("1 Jan 2015") returns 1/1/2015
DateValue ("1 January, 2015") returns 1/1/2015
DateValue ("1 Jan 15") returns 1/1/2015

Access Strings Dates & Parameter names
You can use the value of a parameter name in a field expression in Access
Parameter [Enter Name] = "Fred"
= "The parameter value is " & [Enter Name] results in The parameter value is Fred
Parameter [Enter Year] = "1998"
= DateValue("15 May " & [Enter Year] ) results in 15/5/1998