Have you ever needed to get data out to an internal customer quickly to meet an urgent business need, in an easy to access, updateable format?
Of course!
There are lots of ways to do this, and I’ve tried various routes at different times. Some were even successful, but I’ve never been entirely satisfied with meeting the combined requirements of security, control, and automation as well as customer satisfaction.
If I get the security aspect wrong, am I inadvertently giving the wrong person access to my database and the data it holds? If the control isn’t tight enough, are they going to be able to make changes or slow the performance of a system? To achieve sufficient control and security, am I sacrificing my ability to automate the data extract, leaving myself with a regular manual task to perform. If I tighten up all of these things, is the end result usable…or even recognizable…to the customer?
I know that in theory, an urgent task like this shouldn’t be repeatable without becoming a planned change or development, but think about the amount of time it takes to convert a request to a solution in production. Can I tell my Director that the requested data isn’t available on a regular basis until we’ve been through project management/ITIL/change control/any other relevant control systems? Then add in the development time needed, assuming my workload is already full to capacity.
This isn’t a show-stopping Problem-with-a-capital-P, it is more of an irritation, but it can be a recurring and persistent one. I’ve become more aware of it recently as I am in the middle of implementing a data warehouse to feed Power BI reports. Our team is combining data from different sources for the first time and making much more sophisticated information available, all of which takes time. In the meantime, the business has become aware of our progress, and they want to be included – every team needs something immediately.
While trying to meet an urgent need recently, it occurred to me that Power BI offers a simple solution. I can draw data from my new data warehouse, use views to combine the different sources, add filter criteria by entering a SQL query in the Power BI data source and present a simple data table for the end user. The resulting report can be published as a Power BI App, and the underlying dataset refreshed to a schedule.
My user then has straight-forward access to the data they want, in a recognizable format, refreshed as agreed. They can easily export to Excel for further analysis, and so they are happy. Security is set up during the App Workspace creation and the App publication, so as long as I add a member of my immediate team to the App Workspace Admin role, the whole process can easily be supported by someone other than me if I’m not around. The user can see their data and export it but does not have a direct connection to the data warehouse. Once the interim solution is in place, it doesn’t need my intervention.
There is a risk that these short-term solutions will never be replaced with properly scoped reports, but the plan is that all such solutions will be reviewed, as part of our on-going BI project. There are a few cases where extracting data in this way may even be the answer – an operations manager isn’t just looking for “how many of our properties need a survey within the next 60 days”, they need to know “which properties need a survey within the next 60 days”.
It is a simple solution to a problem that isn’t a big deal, but one I have found satisfying.
How do I do it?
Firstly, get the right data into the data warehouse (SQL Server in Azure in this instance) and create a view over it. The view should include enough data (columns and rows) to cover all related requests if possible. To demonstrate, I’m using a football World Cup data source I set up for a team sweepstake.
Create a new Power BI report with a SQL Server data source. In this example, the underlying tables and the view include all teams, whether or not they qualified. Those who qualified for the tournament have been assigned to a Group, so to restrict the view for this specific report I’m using a SQL statement in my data source.
Once the data is loaded into Power BI, I set up a very simple table which shows the requested details. As the point of the report isn’t a presentation, I’m not spending time on layout, format, labels or titles. As long as the table makes sense to the person or people making the request, it is good enough.
Now I save the report and publish it to a Power BI app workspace. I’m using new app workspaces, which are currently in preview, as they don’t create an entire Office365 group.
I set up a refresh schedule, if required, using an on-prem data gateway and add at least one other team member to the app workspace as with admin permissions.
Now the report is ready to publish as an app, giving the original requestor access to the app along the way.
When the user opens the report, they can see the data on the screen to check the content, then using the ellipsis on the corner of the table, export to Excel.
The user cannot edit the report, download the report file to see the SQL Server connection or access the original data, and no-one else can see it unless given permission.
My internal customer is happy as they can easily get to the data they need. My boss is happy because this is quick and easy to set up and frees up my time for the long-term projects he’d prefer me to focus on. The wider business need is satisfied until a more formal solution is available. And I’m happy to tick all of those boxes and be able to focus on the interesting, challenging big picture work I enjoy.
Where It’s At: The Intersection of Biz, Human, and Tech*
We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”
In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂
* – unless, of course, you have two turntables and a microphone. We hear a lot of things are also located there.
Get in touch with a P3 team member