ABC Automotive (Europe) Ltd. – European Sales Network Reports

My original Microsoft Access “demo” database, described here, now contains 1,050,000 fictitious car Sales and Finance agreements, distributed throughout Europe (EU & EFTA) and is representative of the current actual ratio of registrations in this region. 

Click HERE to view the interactive reports in a new browser tab.

I have then created, using this database, a number of sample reports using Microsoft Power BI for a fictitious company, ABC Automotive (Europe) Ltd.

The primary focus of these reports is to demonstrate how a European Sales Network can be fully integrated into the business reporting for a company providing business insights that are based on geographic areas.

The European Sales Network created for ABC Automotive (Europe) Ltd. is based on the NUTS classification (Nomenclature of territorial units for statistics), a hierarchical system for dividing up the economic territory of the EU for the purpose of the collection, development and harmonisation of European regional statistics and Socio-economic analyses of the regions (most EU data is referenced to this classification thus providing links to this extensive data for your all of your own internal data).

The areas assigned to the fictitious Dealers in the European Sales Network for ABC Automotive (Europe) Ltd. are based on the NUTS CODE 2 (Province) areas which are an accumulation of the lowest level NUTS CODE 3 (District) areas.  It is, of course, possible to use any other geographic boundaries to create the network.


Image 1: 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.


Image 2: A report containing a number of different visualisations of data at the Country level (NUTS CODE 0). 

The “Asset Make Player” box when activated will automatically play through the report results for each individual Country, like a slide-show, with the ability to play continuously, pause or stop the player.


Images 3a, 3b, 3c: The above images demonstrate how the drop-down selection menus enable the user to drill down from all countries in Europe to the “Baden-Wuertemberg” area of Germany.  The report contains a number of different visualisations of data at the NUTS CODE 1 level (Region).


Image 4: A report containing a number of different visualisations of data at the NUTS CODE 2 (Province) level.  This level is used to represent the area assigned to a Dealer in the various reports.


Image 5: A report containing a number of different visualisations of data at the NUTS CODE 3 level (District).  The areas on this level are accumulated to form the area assigned to the Automotive Dealer in the various reports.


Image 6: This report shows how the different NUTS areas of Germany can be shown in reports, at different levels of detail, and how it is possible to link the areas directly to any data that contains the relevant geographic reference, in the case of these reports, the references are the NUTS CODES.


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

ABC Automotive (UK) Ltd. – Sales Network Reports

Using the Microsoft Access “demo” database, described here, I have created a number of reports using Microsoft Power BI focusing on the Dealer network of the Financial Services Division of a fictitious company, ABC Automotive (UK) Ltd.

Click HERE to view the interactive reports in a new browser tab.

DEALER NETWORK MAP
Image 1: Dealer Network Map for ABC Automotive (UK) Ltd. created using MapViewer 8 .

The primary focus of these reports is to demonstrate how a Sales Network can be fully integrated into the reporting for any company providing business insights that are based on geographic areas.

This Sales Network that has been created for ABC Automotive (UK) Ltd. is based on the common standard for the Automotive Sector in the UK (Regions / Areas / Dealers).

In addition there are maps showing UK Postcode Districts as it is also common for the Dealer areas to be defined on a Postcode District basis.  This has been done for the sales network of ABC Automotive (UK) Ltd.

All of the map visualisations that are seen on the various reports have been created using the GIS software, MapViewer 8.  This software is used to produce the “custom network maps” in the “ESRI Shapefile” format that will then be converted into “TopoJSON” formatted files. 

These “TopoJSON” files can then be imported into Microsoft Power BI using the “Shape Map” visualisation and linked to data sources that contain the relevant geographic reference data. 


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 - Network Regions Report

Image 2: A Network Regions report containing a number of different visualisations including a map showing the relative number of agreements within the geographical areas of the 2 Sales regions for ABC Automotive (UK) Ltd., “North” and “South”.

Of particular interest are the two matrix table visualisations that link Experian Mosaic data to agreements data and displays this information on the custom map.


ABC Automotive - Network Areas Report

Image 3: A Network Areas report containing a number of different visualisations including a map showing the relative number of agreements within the geographical areas of the 13 Sales areas for ABC Automotive (UK) Ltd., “AREA01” to “AREA13”.


ABC Automotive - Network Dealers ALL Report

Image 4: A Network Dealer Areas report containing a number of different visualisations including a map showing the relative number of agreements within the geographical areas of the 32 Sales Dealer Areas for ABC Automotive (UK) Ltd., “DLR001” to “DLR032”.

ABC Automotive - Network Dealers Report

Image 5: A Network Dealer Areas report containing a number of different visualisations including a map showing the relative number of agreements within the geographical areas of the selected Sales Dealer Areas for ABC Automotive (UK) Ltd., “DLR001” to “DLR006”.


ABC Automotive - Network Postcode Districts Report

Image 6: A Postcode District report containing a number of different visualisations including a map showing, in this particular report, the relative number of agreements for the Royal Mail Postcode District geographical areas for the selected Postcode Area, “RG” for Reading, UK.

ABC Automotive - Network Postcode Districts Report 2

Image 7: Another Postcode District report containing a number of different visualisations including two different map visualisations showing, in this particular report, the relative number of agreements for the Royal Mail Postcode District geographical areas for the selected Postcode Area, “RG” for Reading, UK.


ABC Automotive - Network Postcode Districts DLR009 Report

Image 8: A Postcode District report containing a number of different visualisations including a map showing, in this particular report, the relative number of agreements for the Royal Mail Postcode District geographical areas for the selected Network Dealer, “DLR009”.

ABC Automotive - Network Postcode Districts AREA06 Report

Image 9: A Postcode District report containing a number of different visualisations including a map showing, in this particular report, the relative number of agreements for the Royal Mail Postcode District geographical areas for the selected Network Area, “AREA06”.


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

ABC Automotive (UK) Ltd. – Financial Services Reports

Using the Microsoft Access “demo” database, described here, I have created Microsoft Power BI reports for various areas of the Financial Services Division of a fictitious company, ABC Automotive (UK) Ltd.

Click HERE to view the interactive reports in a new browser tab.

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


Image 1: 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.


Image 2: Asset report containing a number of different visualisations of data related to the asset that may or may not be financed.  It is possible, in this report, as well as in the others, to filter all data using Geographical, Asset and Finance 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 is an interesting way of visualising the selected data. 

The “Asset Make Player” box when activated will automatically play through the report results for each individual Asset Make, like a slide-show, with the ability to play continuously and pause or stop the player.   



Image 3: 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.



Image 4: 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.



Image 5: 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.



Image 6: Arrears report containing a number of different visualisations of data related to customer arrears.



Image 7: 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

ABC Automotive (UK) Ltd. – Demo Database

Based on my recently acquired detailed knowledge of Microsoft Power BI I decided to combine my knowledge of the software with my knowledge of the Automotive and Financial Services sectors.

However, before I could start to develop reports I needed to have a source of meaningful data for Microsoft 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 (UK) Ltd., based on my understanding of the Automotive sector.

To this end, I have developed a Microsoft Access database that can dynamically generate a portfolio of 100,000+ Automotive Sales & 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 Microsoft 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.

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

ABC Automotive - MS Access Database

Image 1. Microsoft Access creation of the “demo” database


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 records, one for each individual address in the United Kingdom, has to be loaded.  No problem for Microsoft Power BI!


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 Microsoft Power BI!


Image 7
Image 7 This shows the Mosaic 1 data after it has been aggregated and transformed using Microsoft 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 Microsoft 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 Microsoft 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 Microsoft 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

Power BI – Visualisation – Calendar

I am very impressed by the “Calendar” custom visual created by Andy Cross (@andyelastacloud) but having spent some time experimenting with the excellent “Synoptic Panel by OKViz” custom visualisation I missed the ability to define up to 5 states for the calendar data and also wanted to have the visualisation look more like a calendar. 

I therefore decided to develop my own approach to display data on calendars as I had some time to spare and wanted to experiment with Power BI.

Following is a description of my first attempt


Power-BI-Calendar-Image-1
 Image 1: Report showing Calendars for the years 2016, 2017 and 2018 with the associated source data table below (in this case a Calendar Table with a column containing a test amount for display purposes).  The calendars are SVG files that are added to the report using the “Synoptic Panel by OKViz” custom visual.

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

Click HERE to open up, in a new tab, a new version of the interactive report which demonstrates the ability to easily create calendars in other languages.


Power-BI-Calendar-Image-2Image 2: Calendar for 2016 (in focus mode) showing the day of the month with fill colours dependant on the range of values defined in the 5 States that are available in the “Synoptic Panel by OKViz” custom visual.


Power-BI-Calendar-Image-3Image 3: Calendar for 2018 (in focus mode) showing the day of the month and data value with fill colours dependant on the range of values defined in the 5 States that are available in the “Synoptic Panel by OKViz” custom visual.


INTERESTED?

If you are interested in having copies of the Calendars (other language versions will be considered – depends on how much free time I have) then send me an email at fred.williams@insightfuldatasolutions.com

I might also add them later to the Gallery at http://synoptic.design/ (need to investigate whether that would be possible).


Following is a brief description of the steps that I took to create the Calendars (for those of you who are interested) 

Step 1:

Create a calendar in the desired language using Microsoft Visio and export as a png file.

Power-BI-Calendar-Image-4


Step 2: 

Upload the png file exported from Microsoft Visio to the website http://synoptic.design/

Now spend a not short period of time creating index names for all the days in the year (365/366)!  it is recommended that the index names have the format YYYYnn as this can be automatically created when creating a Calendar table (which is to be linked to the source data using the date field).   The second box (Area Name for display) needs to be updated with the number of the day within the month for display in the final calendar.

Power-BI-Calendar-Image-6

When this task has been completed click on the “EXPORT TO POWER BI” button to save the SVG file on your PC.


Step 3:

Add the “YearDayOfYear” column to the Category field, the “Calculated Test Amount” column to the Measure field and any fields that you want to the Tooltips field. 

Power-BI-Calendar-Image-8

Add the required ranges to the State fields.

Power-BI-Calendar-Image-9


….. and here is the final report:

Power-BI-Calendar-Image-7


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