Table of Contents
| Table of Contents |
|---|
Power BI
Overview of Power BI
When using Microsoft Power Bi to analyse your system information there are three possible approaches
...
| Note |
|---|
Power BI is more capable than Excel Power Query in understanding the data |
Microsoft Power Bi process
| Gliffy | ||||
|---|---|---|---|---|
|
Power Bi Desktop
Use this to:
- Define the data sources and add new data sources
- Define the data conversion steps (from raw data to ready to analyse data)
- Create reports / dashboards
- This environment can do everything except allow others to view the results. To do this you must publish your report to the online environment
Power Bi Online
- An online report editor to create reports / dashboards
- Note: Can load up single tables and create reports - but not link tables together nor do data cleansing
- Online Consumption of reports
- Reports once published are accessable by users
Where to get data
Power Bi can accept data from many sources into a dataset as you can see here...
Connecting to SQL Azure Database
If you have setup a SQL Azure database - then you can connect from the desktop or from the online version
How to populate the Azure database
Publishing the data to the Azure Database is from the Financial Period
| Note |
|---|
If you connect using the onine version and your data looks funny - it might be because the database does not have "Primary Keys" setup to link the tables together. If this is the case you will have to connect via Power BI desktop to define the relationships and then publish to PowerBI online |
Exporting from Lists in the system (CSV files)
Extracts from any list in the system - choose a date range etc if required.
...
| Note |
|---|
This data will have to be refreshed by you manually into Power Bi |
BI Extracts (CSV Files)
If your system has a BI Download configured - these files save as CSV with the "|" character as the delimiter
...
| Note |
|---|
This data will have to be refreshed by you manually into Power Bi The date sent to your datawarehouse will be based on the date range you define.It will over-write similar data if it already exists. |
Connection to a SQL Database
If you have large volumes of data and want regular automatic updates to it - an SQL Database is the ideal method.
The ideal place for this database is in SQL Azure - it is cheap and in the same place as Power Bi Online so data does not travel across your network for designing and refreshing the report data.
- For those that like excel it can also connect to Azure... https://blogs.technet.microsoft.com/office_online_support_blog/2015/10/29/excel-online-refreshing-data-to-a-sql-azure-database/ and https://powerbi.microsoft.com/en-us/documentation/powerbi-azure-sql-database-with-direct-connect/
...
If you connect to a database that is not in the same place (ie on your site) then every time you refresh the data in PowerBi online (it only has a complete data refresh - not an add updates) - there will be significant traffic on your network.
Data Cleansing
Once you have the data in Power Bi
You are likely to want to change headings, change data types, add columns eg calculations, etc. Most of these are similar to Excel
Column Transformations
Adding columns
An example additional column is for a bitwise operator. A bitwise operator means that a single column (OptionsProfile) contains a number that provides information about a number of different settings.
In the sample below - 32 = multiple vehicles, 64 = Favourite, 128 = Is bulk order, 16 = POD.
Review the changes made
Once you have cleansed the data you can review each step applied (it is applied again in order if you refresh the data)
Power Pivot in Excel
Related articles
| Filter by label (Content by label) | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...