Table of Contents
| Table of Contents |
|---|
Example PowerBI reports
Power BI
Overview of Power BI
When using Microsoft Power Bi to analyse your system information there are three possible approaches
- General Information and standard reports - Use our internal PowerBi Reports (coming soon)
- Detailed Analysis with Lots of Data - Setup your own SQL Azure and PowerBi environments (you need a Microsoft Azure license and PowerBI online licenses)
- We will create a SQL database with a number of linked tables in Azure
- We will publish data to those tables regularly
- Connect PowerBI to this table set - then publish to PowerBi online
- Edit and adjust your reports in PowerBi online Note PowerBi can also connect to other data sources like excel online or websites to enrich the dataOn 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
- Either / or
- import these to power bi desktop then publish the results
- Load these upto excel online that Power Bi is connected to
- Either / or
...
- - 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.
| Note | ||
|---|---|---|
| ||
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
| Gliffy | ||||
|---|---|---|---|---|
|
Power Bi Desktop
Use this to:
...
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/
...
In the sample below - 32 = multiple vehicles, 64 = Favourite, 128 = Is bulk order, 16 = POD.
| Note |
|---|
Bitwise operators are converted into readable text when using the vbi_ tables. Only when using raw database tables will you need to convert. |
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)
...
Then Select the Tables Required
Select the tables to view
You can filter the tables prior to import to reduce the data
Use preview and filter above
Then import the data
Then link the tables with Relationships
Review the relationships
Then hide columns a user would not require
How hidden columns look
Save and the start creating pivot reports
Related articles
...
| Page Properties | |||
|---|---|---|---|
| |||
|