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.
I have been struggling with a simmilar question and it would be interesting to see the solution on integrating smart with other BI visualization tools e.g powerBI.
@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.
Frank - there are pros and cons to pulling the data into another database. Whether you pull directly into the BI solution or pull into an SQL first depends on your needs.
Pros include:
Allows you to integrate with other databases
Allows you to optimize queries to run very fast
Allows BI uses to access the data using well document and widely used approaches
The main con is you need to periodically sync the data. There are others (e.g. you can end up with a large volume of duplicate stored data).
We are working on an approach to make it easier to go directly and use the API.
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
Pros:
full access to the raw data
Cons:
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:
Pros:
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
Cons:
Not all data directly available. Track data requires an additional step to extract
Data refresh involves executing of queries again
Introduction of middle layer:
Pros:
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.
