However, the Export to Excel function is often the only way out for a user to be able to transform data from an application into information for analysis, a report or a dashboard. The data is often combined with data from other systems, or edits are made to create new insights. So very legitimate use cases.
Unfortunately, this method of data extraction is very limited. Apart from the fact that these are manual, error-prone and often time-consuming actions, the export is often also functionally limited. For example, it's limited to the number of records, or the time span of a set of data (last 12 months only), or just a matter of all or nothing. In short, the user is not really optimally supported. And that's a shame.
An API alone is not the solution
“We offer an API for this!” is often the response of the Software (aaS) provider. But unfortunately, in most cases, that kite doesn't work either. Quite apart from the fact that an average business user doesn't know what to do with a “monkey”. In fact, there's a good chance that he or she thinks you're talking about the zoo...
The APIs that we usually encounter are very limited; documentation is brief, the performance is poor and the possibilities of the API calls are often very limited. The result? A costly customized process to achieve a technical link by IT colleagues or an expensive external developer. Sure, you can also purchase a paid service from a third party. But that is not ideal either. Because in many cases, this party will pull a copy of the data into its own data warehouse, causing additional trust, security & privacy challenges, regardless of the costs and dependencies of a third party. Surely that should be possible differently?
What we needed to do to get Simplicate sales and project data into a Power BI dashboard
We also ran into this ourselves. There was a need for a few standard reports and dashboards for our project leaders, account managers and service delivery managers. We use Simplicate and (how could it be otherwise) Power BI. This is what it took and what we all ran into.
To get the right information on the screen, we used the following step-by-step plan:
- First, we need to determine what information and insights we need;
- We then translate this into which datasets and structure we need from Simplicate for this;
- Now that we have determined this, we had to come up with the necessary technical infrastructure in Azure, such as an SQL Azure database, how do we deal with authentication, what is required for logging and how do we arrange a periodic data refresh;
- Once that was clear, we started building the link.
Of course, the dashboard must be up to date and therefore also the underlying data sets. This means that a data refresh must be performed periodically and automatically. Because, in the future, we also want to combine data from other systems in addition to Simplicate, we have chosen to build a data warehouse in Azure SQL.
Of course, we want to use as much low-code as possible in this setup, which is why we chose Azure Logic Apps; this allows Simplicate's API to be invoked with http actions. We could also have solved this with Power Automate, but these http actions require a Premium license, and for our scenario, it turned out to be cheaper to work with Azure Logic Apps.
The biggest challenge in using the Simplicate API was that the API returns a maximum of 100 items at a time. So to pick up all customers, we had to create an iterate functionality ourselves. And because the API doesn't allow us to return only changed records (since the last sync), we have to build functionality ourselves to determine which items have changed.
To make things even more complex, we don't want to use all the information from Simplicate; we don't need employees' address details or bank account numbers in our reports, and therefore we don't need to be in our data warehouse. For that reason, we had to make adjustments to the data model ourselves so that we only retrieve data that we really need.
Although all this is not rocket science for a seasoned IT professional, it would have saved a lot of time for our sales manager if there was just a decent one data connector was available for Power BI, but unfortunately it is not available (yet).
Surely that can be done differently...
You will understand that we believe that this can and should be done differently. Low-code platforms, such as the Microsoft Power Platform, are not without reason. They offer business users the opportunity to create accessible solutions in a short period of time that help them do their job better. SaaS vendors must accept that they won't get all the functionality in their application that all users could ever need. Accept that users also want to use the data in your application outside of this application. But in a safe, controlled and accessible way.
Unfortunately, many SaaS vendors see low-code as a threat because it could be cannibalizing. But we are convinced that it actually offers opportunities by having a good integration with low-code platforms such as the Microsoft Power Platform. After all, the use cases that arise as a result are endless, so answers like “Unfortunately, that's not possible yet, but it might be on the roadmap...” are no longer necessary. And by offering low-code links, one of your customers may come up with a very interesting new functionality that you can incorporate back into the application. Last but not least, it's also just a kind of good parenting by not “locking” your customers into your application, but rather offering an “open minded” platform that supports innovation instead of opposing it.
If you would like to exchange views with us on this topic, we would like to invite you for a (virtual) cup of coffee!
.jpg)
