Improving Asset Finance Reporting: First Steps in Power BI
Author Simona Skorupskaite
Our prospective clients regularly ask about the reporting capabilities of Soft4Leasing. As long as any Soft4 product is built on the MS Dynamics NAV platform, we provide classical list and table style printable reports. But some time ago, we started to explore the potential of Power BI so we could expand to interactive, fresh and stylish reporting.
In general, business intelligence (BI) is considered an essential decision-support tool. By implementing any BI tool, asset-finance companies are able to have their finger on the pulse – from keeping an eye on working capital to analyzing the current application pool in detail. Most importantly, you create a dashboard according to your need for report visuals, and pin the information that matters most.
In this blog I am going to review how successful we were in creating a Power BI report for active lease-contract analysis, also called an active contract pool.
The following steps describe the flow for producing a unique perspective on activated contracts:
- With Power BI, data from various well-known sources can be uploaded. In particular, MS Dynamics NAV can feed the Odata web service. We started a certified Odata service and published demo data on the source of applications and contracts. After that, we were free to connect to Power BI and upload data.
- Pulling data though Power Query. At this step, you have to shape, combine and clean your dataset. In other words, you want to transform your dataset in order to fit a report need. I think the amount of work you spend on transforming greatly depends on your data source structure – the more measured and orderly it is, the fewer corrections will have to be made. In our experience, we pulled a very well formed dataset from NAV, so we did not spend much effort and time at this stage.
- Processing to Power Pivot. By default, you add columns, calculations and define aggregations at this step. Same as in step 2 – we basically skipped this step, as we extracted well-defined financing calculations from Soft4Leasing.
- Adding visualizations to form a report. The available predefined visualizations include classical charts, lists, tables as well as funnels, gauges and even maps. For an active contract pool, we added totals for capital amount, financed amount, net receivable and current balance. Financial product and financing program slicers are then added for filtering purposes, while other visuals representing customer type, county, age range, introducer type and financed asset group are added as analytical dimensions.
We also created similar reports for current applications: added slicers, visuals and totals. Only this time, the reports were grouped by application status.
Next we were able to pin a simple but important dashboard. You can pin any visuals from your reports so the dashboard is linked to the report and you are able to reach it by clicking on a corresponding visual.
- Publishing and sharing so your colleagues observe what you do. You can easily share your dashboard by emailing a dashboard link. However, anyone you share with has to have a Power BI account, and in addition the shared content cannot be modified.
- ‘Stay connected to your data from anywhere, anytime…’ says Power BI. The fact is that Power BI is widely accessible. Also, one of the latest updates is that users are able to print out reports! Call me old fashioned, but in my opinion printouts are essential for completing the reporting process.
Talking about the casual routine of using Power BI, one should mention that there are some differences between the free-trial and pro versions. In particular, the free-trial Power BI refreshes your dataset daily, while the Pro version grants permission to setup a dataset refresh schedule. Anyway, that is really a matter of what you want. Another difference is data storage – with Power BI Pro you expand your storage from 1GB to 10GB. However, we have never exceeded 1GB in our experience. With the Power BI Pro version, you can also benefit from a content pack that allows for leveraging all the features of dashboards and reports at the organizational level. In addition to a shared report, your colleagues can obtain their own copy of a report from the content pack.
Finally, we now have a live operational dashboard for financing activities, which we can offer to leasing companies. While in standard RDLC reporting we are able to construct flat reports: detailed lists and document style printouts that reflect what happened. Power BI now supports the analytical purpose of reporting, where you can ask why something happened and dig deeper to find out by interacting with the data. In addition, the process of creating reports for lease applications and contracts was straightforward with Soft4Leasing, as the system provided a well-designed dataset.