By Matthew Jackson
Business Intelligence Consultant at Stellar
Data is everywhere; today just about anything we do as a business or individual, professionally or personally is captured somewhere. With all of this data, it can be difficult to connect the dots and consolidate disparate data sources into a usable view by which to gain insight. In my experience, it is a problem every organization faces to some degree.
Recently I was fortunate enough to work on a project for a large financial institution. Initially, I was told I would be developing a cost centre reporting dashboard in Power BI. While ultimately, we did design a Power BI model and deliver a report to the client; there was quite a bit to do before we got there.
We cracked into workshops and learned there were around ten data sets we would need from around four different source systems. We would need to consolidate data from multiple sources in order to create one version of the truth. There were various types of data including timesheet, project, portfolio and cost centre data. So, first, we had to consolidate and define a process by which to feed data into our eventual Power BI model. We needed this in a way that did not create too much more work for the wider team. After all, we were there to make things less complicated.
The Power of PowerShell
In the current state, the client exported flat data files monthly and manually manipulated as required to answer business questions. The client wanted to continue with the monthly exports in order to allow this manual extraction if desired and to maintain current processes as much as possible.
This was all fine, but none of the files were in any sort of appropriate format for ingress into a data model! Exploring the current application environment of the customer, we landed on using PowerShell to transform the data. We manipulated and concatenated the data into a single file ready and formatted for consumption into Power BI. We had used PowerShell for a similar task in a previous engagement with the customer, so it was a good fit on both sides.
Power BI – A secure, unique view for each user
Now we had our data ready for Power BI. We just had to build the model – easier said than done, but after numerous development and feedback cycles; we got there. Ultimately, this report was to be the client’s first exploration into the Power BI cloud service. They required row-level security due to information confidentiality as well as relevance. Meaning, they didn’t want to show cost centres and portfolios to users that were not their managers. Each user required a unique view.
We created some visualizations, but it seems these were not that important. It turns out, we weren’t there to create a pretty Power BI dashboard. We were there to overcome issues with the data landscape and allow the client to easily access data. We handed over the data model, and the client tweaked our visualization work to meet their exact requirements themselves.
Win for all
Sometime later, when we were seeking sign off we had a meeting with our stakeholders. The dashboards received great praise from upper levels of management. They were even seeking to use the model for numerous other departments of the business. Now they are looking to move their other Power BI Desktop reports to the Power BI cloud service. The ability to securely share relevant and specific data with specific users in the cloud in a self-service manner was a big win for our stakeholders, and ultimately us.
