We periodically get variations of the above request.
Currently, the best approach is to use SMART Connect APIs - either to pull the data directly into the BI solution (e.g. by developing a custom connector) or to pull the data into an SQL or other database that can then be more easily directly from the BI solution.
Both of these approaches require engineering/programming resources and so we appreciate most sites don’t have the resources to implement this.
If a group of SMART users was interested in integrating with a specific BI tool, we’d be happy to dive deeper and explore what it would take to develop a shared solution.
@jpalmer , why would one first pull data into a database before feeding it to a BI solution? Would one not make an export from SMART and feed that to the visualizer?
Sorry, if I am overlooking something obvious. I am quite new to SMART.
In my opinion there three different options of how to access the data by a dashboard:
Direct Access to the underlying PostgreSQL database
Direct Access to the query API of Smart Connect by PowerBI or similar
Introduction of a middle layer to store data extracted by queries
Pros and cons:
Direct Database Access
full access to the raw data
can create severe impact on the overall performance of the system. Smart database is not designed for concurrent access. For example running a long query from the dashboard might block or break a sync by another user. There is also the possibility of inconsistency
Database design might change with every update resulting in problems for the dashboard
Security implications. The dashboard would require nearly full read permissions to the database
The data structure is very difficult to understand
Direct Access by PowerBI or similar systems using Query API:
Consistent data guaranteed by the platform
Concurrency is less of an issue but still needs to be kept in mind. Don’t for example execute the same queries every few seconds as this might overload the whole system
Query api not likely to change in the future
Least amount of work required
Not all data directly available. Track data requires an additional step to extract
Data refresh involves executing of queries again
Introduction of middle layer:
Better performance by decoupling of query api layer and dashboard layer
Possibility of gathering of data from multiple Smart Connect Servers
Data can be expanded by other datasources
New and changed data can be integrated by incremental update process
We hope to have an update shortly on our plans to offer SMART dashboards. The tech council are working on requirements and we hope to have something to share in the weeks or months ahead.
On the tech side we’ll also build off Oliver’s evaluation below. I think we can pull track data via the API. We can pull other information via the API also e.g. configuration model.
The other key thing is access to effort calculations, you can’t get these through direct DB access. Maybe we should make these algorithms publicly available so they can be incorporated into solutions making direct access. They are already available in the source code.