Power BI – ABC Automotive Ltd. (Part 2 – Financial Services Reports)

Using the “demo” database, which I described in Part 1 of this set of blog entries, I have created a number of sample reports using Power BI Desktop for various areas of the Financial Services Division of a fictitious company, ABC Automotive Ltd.

The “demo” database, together with other data, were imported into Power BI Desktop and, where necessary, were transformed using the ETL, or transformation, functions that are embedded in the desktop / online Power BI software (Power Query / “M” language / “DAX” language ).

Having now got the measure of uploading the Power BI Desktop file (.pbix), via OneDrive Business, to my online Power BI Service account it is possible to interactively query the various reports that are described below.

Click HERE to open up the interactive reports in a new tab.


ABC Automotive - Hints
Hints: Three hints on how to select the display of information within the visualisations i.e. in addition to using the filters/slicers at the top of the page.


ABC Automotive - Asset Report
Image 1: Asset report containing a number of different visualisations of data related to the asset being financed.  It is possible, in this report, as well as in the others, to filter the finance agreement data using Geographical, Asset and Finance Product criteria. 

The pie and donut charts offer the ability to drill down through the data e.g. from Make to Model to Fuel Type.  The matrix visualisation displays the data that is held in the “demo” database that I have created.


ABC Automotive - Finance Report
Image 2: Finance report containing a number of different visualisations of data related to financial transactions.  Of particular interest are the KPI visualisations that provide a easily viewed status of some of the financial figures for the filtered finance agreements.


ABC Automotive - Customer Report
Image 3: Customer report containing a number of different visualisations of data related to the customer.  Of particular interest is the Treemap visualisation showing the link between the agreement data and external Experian Mosaic customer demographics data.


ABC Automotive - Residual Values Report
Image 4: Residual Values report containing a number of different visualisations of data related to the residual value of the assets.  Of particular interest is the “RV Change Percentage” filter/slicer which allows the user to select a percentage figure that will the dynamically change the displayed residual value totals for all financed agreements.

If a user selects the “Fuel Type” of “Diesel” and selects a percentage change figure  it would be possible to see what the reduction in residual values would be in the latest “anti-diesel” climate here in the UK.


ABC Automotive - Arrears Report
 Image 5: Arrears report containing a number of different visualisations of data related to customer arrears.


ABC Automotive - PCP Payments Diagram
Image 6: A diagram showing the amounts of deposit, monthly payments and residual value (GMFV) for the filtered agreements.


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com

Power BI – ABC Automotive Ltd. (Part 1 – Access Database)

Having got the hang of #POWERBI I have decided to combine my knowledge of the Automotive sector with my newly acquired knowledge of Microsoft’s Power BI.

However, before I could start to develop reports I needed to have a source of meaningful data for Power Bi to report on.  This data could not be “live” data from an Automotive Company so I had to develop my own representative “demo” database for a fictitious company, ABC Automotive Ltd., based on my understanding of the Automotive sector.

To this end, I have developed a MS Access 2016 database that can dynamically generate a portfolio of 100,000+ Automotive Finance agreements.  The database is very comprehensive and can be weighted to mirror the finance portfolio of any Automotive Finance company.

The “demo” portfolio can be weighted according to the following criteria:

1. Vehicle Manufacturer (Audi, BMW, Honda etc.)
2. Vehicle Type (Car, Bike, Van etc.)
3. Vehicle Status (New, Used)
4. Vehicle Fuel Type (Petrol, Diesel, Other)
5. Vehicle Categorisation/Segmentation (Private/B SUPERMINI 5DR Hatch etc.)
6. Vehicle Registration Date (to cater for the two annual peaks in the UK) 
7. Finance Product Type (HP, PCP, LP)
8. Financial Data (vehicle cost, deposit, monthly payments, arrears, residual values)
9. Agreement Status (Financed, Closed, Litigation, Arrears etc.)
10. Agreement Start Date (to cater for the two annual peaks in the UK) 
11. Geographic data (Sales Networks, Customers, Assets etc.)
12. Customer Demographics (Age, Family Status, Gender, House Owner etc.)
….. and other criteria

For example, it would be possible to create a “demo” portfolio for a particular Manufacturer Finance Company that contains 95% of agreements for their own vehicles and 5% for other manufacturers vehicles with 5% of agreements being in arrears and a defined Finance Product split.

Alternatively, it would be possible to create a “demo” portfolio for a non-Manufacturer Finance Company that contains a more even distribution of agreements for the cars of all manufacturers with 15% of agreements being in arrears and a majority of Hire Purchase agreements. 

The version that I will be using to create the Power BI reports that will be documented in later blogs will contain an distribution of manufacturer vehicles that match recent registration statistics, with 80% PCP products, 10% arrears and an even distribution of Vehicle Registration and Agreement Start dates.

The “demo” database will continue to be added to with the next major addition being the generation of cash flows ….. not going to be easy as it will depend on randomising payments (but not too much) for randomised agreements 🙂

It would have been possible to create most of the database using the ETL functionality and the embedded “M” language in Power BI Desktop but it was quicker for me to create it in MS Access 2016 (decades of experience) and not in the desktop (one years’ experience). 

ABC Automotive - MS Access Database
Image 1. MS Access creation of the “demo” database


This blog entry will be followed, in the very near future, by the following:

Part 2 – Power BI reports for the “Financial Services Division” of ABC Automotive Ltd.
 
Part 3 – Power BI reports for the “Financial Services Division” of ABC Automotive Ltd. (focussing on geographical information for Dealer Network, Customers & Assets)

Part 4 – Power BI reports for the “Sales Division” of ABC Automotive Ltd.


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com

 

 

Power BI – Viewing Experian Mosaic Data

Whilst working for a client recently I was asked to look into how they could utilise the Experian Mosaic data that they had access to.

Following are the results of this particular exercise.  It should be noted that the reports shown here do not display any of the client’s own data and some of the Experian Mosaic data has been “redacted” to hide the part of the key that would identify individual addresses. 

However, the reports should illustrate how the Experian Mosaic data can be linked, via Royal Mail Postcode Unit and Postcode District codes to a companies’ own data, if it contains geographic links (postcode, lat/long etc.)

Image 1
Image 1 This report has “slicers” that enable the user to select the Postcode District, Postcode Unit and a range of values for the “Mosaic_Code_1” and “Mosiac_Code_2”  codes for which they want to have data displayed.  This requires that the complete set of Experian Mosaic data i.e. 28,114,093 million records, one for each individual address in the United Kingdom, has to be loaded.  No problem for Power BI Desktop!


Image 1
Image 2 Hovering over the “MS1_DescriptionLong” column provides a very interesting description for those addresses with the “Mosaic_1” code with a value of “2” referring to “B Prestige Positions”. 


Image 3
Image 3 This report has “slicers” that enable the user to select the Postcode District and hierarchical values for the “Mosaic_Code_1” and “Mosiac_Code_2”  codes for which they want to have data displayed.   


Image 4
Image 4 A report with “TreeMaps” showing how the Experian Mosaic data can be connected to other data.  In this case “Car Model Agreements” (“ABC”, “DEF”, “GHI” etc…) and “Credit Score Grades” (“A”, “B”, “C” etc.).


Image 5
 Image 5  A report with “TreeMaps” showing how the Experian Mosaic data can be connected to other data.  The “Tooltip” shows an expanded text for “B Prestige Positions” category.


Image 6
Image 6 Complete set of Experian Mosaic data, in it’s original format, i.e. 28,114,093 million records, one for each address in the United Kingdom.  No problem for Power BI Desktop!


Image 7
Image 7 This shows the Mosaic 1 data after it has been aggregated and transformed using MS Access 2016 using a “CrossTab” query to the Postcode District code level.


Image 8
 Image 8 This shows the Mosaic 1 data after it has been aggregated and transformed in MS Access 2016 using a “CrossTab” query to the Postcode District code level and then “un-pivoted”.


Image 9
Image 9 This shows the Mosaic 2 data after it has been aggregated and transformed in MS Access 2016 using a “CrossTab” query to the Postcode District code level.


Image 10
 Image 10 This shows the Mosaic 1 data after it has been aggregated and transformed in MS Access 2016 using a “CrossTab” query to the Postcode District code level and then “un-pivoted”.


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com

Power BI – Mapping when “Disconnected”

Having recently read an article on the subject (Aaron Bator MSFT, Click here for his blog entry) I decided to share my own similar approach to providing “geographical” visualisations in a report.

I am a member of a team working on the creation of a Neighbourhood Plan for the Parish, Tilehurst, in which I live.  As part of my input to the plan I have been investigating how to visualise all manner of demographic data (from the Office for National Statistics) on standard and custom maps.

I, like Aaron, looked at the current options in Power BI Desktop and found none of them to be what I was looking for.  The need to be connected to the Internet was one issue as was the lack of any serious capability to use my own “custom regions”.  

Being a big fan of the “Synoptic Panel” Power BI Desktop visualisation and it’s sister the “Synoptic Designer” website (both by OKViz) I decided to create the custom maps that I needed as follows:

1. GIS software (Mapviewer 8)
2. Export the created map as a “.png” file
3. Upload the “.png”
to the “Synoptic Designer website
4. Define the areas and then export the results as a “.svg” file
5. Add the “.svg” file(s) to a Report using the “Synoptic Panel” visualisation 
6. Link the visualisation(s) to the data table


Office for National Statistics (ONS) Population Report for Tilehurst Parish

Image 01Image 1: Tilehurst Parish population by ONS Lower Layer Super Output Area (LSOA), Middle Layer Super Output Area (MSOA) and Output Area (OA).

Click HERE to open up the interactive report in a new tab!


Image 02
Image 2: Map showing the population of Tilehurst Parish by Ward and Output Area (OA)


Image 03
Image 3: Zooming in to map showing the population of Tilehurst Parish by Ward and Output Area (OA).  Note the use of the “States” settings to colour the Output Areas by population.


Image 04
Image 4: Map showing the population of Tilehurst Parish by Middle Layer Super Output Area (MSOA)


Image 05
Image 5:  Map showing the population of Tilehurst Parish by Lower Layer Super Output Area (LSOA)


Image 06
Image 6: Source ONS Census 2011 data consolidated by Geolytix


Image 07
Image 7: Data model showing Geolytix and Postcode tables


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com

I will describe how I create the maps using the GIS software (Mapviewer 8) and the  Synoptic Designer website” soon.

 

 

 

 

Power BI – Visualisation – Org Chart

Spent some time experimenting with the excellent “Synoptic Panel by OKViz” custom visualisation and came up with the idea of finding a different way to visualise the costs associated with a companies organisation structure.

Organisation Chart & Associated Salaries and Costs Report

Organisation Chart 01
 Image 1: Report showing Total Salaries and Costs for a fictitious organisation.  The Organisation Chart diagram in the center is an SVG file that has been added to the report using the “Synoptic Panel by OKViz” custom visual.

Click HERE to open up the interactive reports in a new tab.


Organisation Chart 02
Image 2:  The Organisation Chart that has been added to the report using the “Synoptic Panel by OKViz” custom visual.  The chart is linked to a structured table that contains descriptions of the roles and the associated salary and costs.  The colours indicate in which range/state the salary sits.


2017-04-01_161221
Image 3:  The table that is linked to the various visualisations in the report.


Organisation Chart 4
Image 4:  A “Stacked Column Chart” showing the Associate Salary Total by Department and Sub-Departments.


Organisation Chart 5
Image 5:  A “Pie Chart” showing the Associate Salary Total by Department.


Organisation Chart 6
Image 6:  A “Treemap” showing the Associate Salary Total by Department.


Organisation Chart 7
Image 7:  A “Matrix Table” showing the table of Associates Data with Totals.


Organisation Chart 2
Image 8:  A “Table” showing the table of Associates Data.


Organisation Chart 09
 Image 9:  Report showing Total Salaries and Costs for a fictitious organisation.  All the visualisations are filtered by the selections in the “Hierarchy Slicer”.


Organisation Chart 10
 Image 10:  The Organisation Chart reflecting the filtering by the selections in the “Hierarchy Slicer”


Organisation Chart 11
 Image 11:  The Organisation Chart that has been created using Microsoft Visio Pro that is exported as a “.png” file which can then be uploaded to the “SYNOPTIC DESIGNER FOR POWER BI” at the web address, http://synoptic.design/.


Organisation Chart 11
 Image 12:  The Organisation Chart that has been uploaded to the “SYNOPTIC DESIGNER FOR POWER BI” at the web address, http://synoptic.design/ before being exported as a “.svg” for use in the “Synoptic Panel by OKViz” custom visual.


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com