Use Sidebar LHS to navigate
For global help click here
PowerBi and Excel Power Pivot
Table of Contents
- 1 Example PowerBI reports
- 2 Power BI
- 3 Exporting from Lists in the system (CSV files)
- 4 BI Extracts (CSV Files)
- 5 Power Pivot in Excel
- 5.1 To turn on Power Pivot in Excel
- 5.2 Connecting to an Azure Database
- 5.3 Enter the details
- 5.4 Select the tables to view
- 5.5 You can filter the tables prior to import to reduce the data
- 5.6 Then import the data
- 5.7 Then link the tables with Relationships
- 5.8 Review the relationships
- 5.9 Then hide columns a user would not require
- 5.10 Save and the start creating pivot reports
- 5.11 Related articles
Example PowerBI reports
Power BI
Overview of Power BI
When using Microsoft Power Bi to analyse your system information there are three possible approaches
On system reports - On system reports are the preference for operational management reports they are accessable while you work in the system.
Analytical Reports - What if, Graphical, Analysis - these reports are best in a reporting Tool like PowerBi
Every night your data gets copied to a reporting data store. The reporting data store is secure and has everything and you can build complex queries without concern about affecting the performance of the live system.
Any reporting tool can be used - however the PowerBI report building tool is free.
You can author and run reports in the report building tool = but anyone with access to this tool can get to all information
PowerBI publishing license
We provide a single license publishing environment so you can provide online reports to report consumers.
Note additional charged licenses will be required (or you can setup your own Microsoft PowerBi licenses) if not all report consumers can see the same information.
Export from lists in the system regularly - any grid can be exported to excel
Reporting Tables (vbi_)
Customers = Debtors
vbi-journal lines = includes all information required for analysis of journals. Example - control line will be indicated. Every line has debit / credit / link to a GL account.
Purchase order is only the header (but joining 3 tables of different types of orders - sales, purchases, returns etc) - hence the reason for this being there
purchase order lines references purchase order table - so use this
Sales Order - no sales order lines - sales order has all the information
Stock tran item is sales order line or po line join - so use vbi purchase order to stock tran item table (key on order id). Stock Tran item works for both PO and SO.
vbi_item for sale = can be service or stock item, v_bi item for sale = generic both goods and services have in common, vbi_article is stock item
Projects are linked to journals – project_id is made negative, a dummy entry in orders is created to satisfy database integrity. So project 100 would be store as order_id -100 in journals.
the link between vbi_journal_lines and orders is source_id to order_id.
SQL Managment Studio - ssms
If you connect to the reporting using Microsofts SQL Server Management Server ( a free tool) to connect to the reporting server you will see diagrams that have been created to explain the data connections.
Microsoft Power Bi process
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 Power BI can get data
One power BI model can have data from lots of places at the same time
Power Bi can accept data from many sources into a dataset as you can see here...
Exporting from Lists in the system (CSV files)
Extracts from any list in the system - choose a date range etc if required.
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
Downloaded data will have to be refreshed by you manually into Power Bi
The date sent to your datawarehouse will obe based on the date range you define.It will over-write similar data if it already exists.
Connection to a SQL Database (in Azure)
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/
We will need to set up a SQL database for you and the regular refreshes of data to it from our system.
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.
For information about SaaSplications go to http://saasplications.com.au