Bill Watts - July 20, 2020
I just completed a Power BI project for a regional flooring company in Cincinnati.
The Cincinnati-based flooring provider uses Turning Point as its accounting software. Turning Point has some built-in reports via Crystal Reports 8.1, and additional reports in a third-party application called Clickbase, but the flexibility and UI of these reports is very limited. A reliable, user-friendly, and extensible reporting system was needed for its accounting data, including the Balance Sheet and the Income Statement.
Power BI was chosen for these reports. A Power BI environment was already in place, for use with sales and inventory data. And, Power BI would change the accounting reports from static spreadsheets into robust data analysis tools.
This project was a huge success.
- The underlying data for the reports can now be easily verified and corroborated.
- The reports are much easier to use and distribute, as they were built specifically for the target business structure.
- The financial statements can be shared as needed, while still maintaining complete security.
- Executives and employees can drill down into the financial numbers for further review.
There were some interesting challenges that occurred during this project:
FoxPro Data Access
Turning Point uses FoxPro as its database. The only way to externally connect to FoxPro data is through a 32-bit OLE DB Provider. Microsoft has discontinued its ODBC driver for FoxPro.
When developing the reports, I was able to use the 32-bit version of Power BI Desktop to connect to the 32-bit OLE DB Provider. However, when I was ready for deployment to production, I couldn't get the 32-bit OLE DB Provider to work with the 64-bit Power BI Data Gateway.
Solution
I wrote a simple C#.Net application that uses the OLE DB Provider and copies the data from FoxPro into tab-delimited text files. Then, I adjusted the Power BI file to query those text files instead of querying the FoxPro database files directly.
Data Secrets
Some documentation was available for the Turning Point database, which was very helpful when I queried that data. However, there are no data relationships in FoxPro to guide the data mapping process.
There was no documentation for the existing Clickbase reports.
Solution
The GL Accounts were manually identified as needed for the reports.
Recreating the Clickbase reports was a guess-and-check process. I made an Excel spreadsheet that defined the data relationships between line items and account numbers, to align the Balance Sheet and the Income Statement with the underlying data. This allows the report integrity to be verified and controlled, whereas the data was previously hidden inside the Clickbase report definition.
I was very careful and meticulous in how I structured the FoxPro data in Power BI, to promote data integrity and proper data relationships.
This may sound crazy, but it was a lot of fun to hunt for the relationships between the data points and then to use my discoveries to build these important financial statements.
This project brought back some great memories from Molly Rogers's ACCT 7012 class during my MBA days at UC.
Reporting Usage
The old financial statements were difficult to use and publish, especially considering the corporate organizational structure.
Solution
The new reports were designed to fulfill specific layout and filtering needs. Building the Income Statement in a way that works for a multi-tiered department structure was fun.
I was able to use some of the newer Slicer features in Power BI to build user-friendly reports, including syncing multiple slicers across multiple pages.
Version Control
Version control is essentially non-existent in Power BI.
Solution
I stored the Power BI template (pbit) file in a Microsoft DevOps account. The pbit file has the structure of the report, without the data. The pbix file with the data is too large to store in DevOps. It's not possible to compare versions of pbit files, but at least the different versions of the file, with documentation, are visible as we progressed through the project.
Production Controls
I did not want any authority over the production environment, since this data is sensitive in nature. The role of gatekeeper and controller of all reports and data is important in a Power BI environment, to control what updates occur and which people have access to any report.
Solution
I did not deploy the completed pbix file to the Power BI website myself. Production controls could still happen if I did the deployment myself, but this further ensures that control. During development and testing, I deployed a temporary pbix file myself to the Power BI website for review purposes.
Production deployments were more complex than usual, due to this added level of security, and so extra patience was required as I fixed bugs and made tweaks to the reports.
Conclusion
Power BI is an excellent reporting tool for financial statements. The reports are user-friendly, dynamic, and incredibly powerful, allowing users to extensively review the data and make informed decisions accordingly.