HTML report and SQL queries

Started by wiebow, July 16, 2021, 19:32:32 PM

Previous topic - Next topic

wiebow

Hello there,

Analytics and insight using ArchiMate models and Archi as tool.

I was searching for more information on this subject and could not find a lot of information, if any.
First, to find out more about the technical skills needed to be able to perform any of this, I started making notes and writing it down in a short walkthrough.

You can find my first attempt here: https://wiebow.github.io/archi_sql.html
Any comments or tips are welcome!

I am also looking for more use cases. Does anyone here have any practical tips to share or more practical situations to describe and walk through?
One point I would like to explore is using more metadata on relations.

Any thoughts?

Phil Beauvoir

Hi Wiebo, thanks for sharing, it looks very useful.

You're right, the SQL stuff needs documenting so I also created a blank wiki page where we could capture information and perhaps link to your article:

https://github.com/archimatetool/archi/wiki/SQL-queries-in-the-HTML-report

I must admit I haven't looked closely (yet) at this aspect of the HTML report as it was contributed by JB. But I will now!
If you value and use Archi, please consider making a donation!
Ask your ArchiMate related questions to the ArchiMate Community's Discussion Board.

Jean-Baptiste Sarrodie

Hi,

Quote from: Phil Beauvoir on July 17, 2021, 10:13:39 AM
I must admit I haven't looked closely (yet) at this aspect of the HTML report as it was contributed by JB. But I will now!

Some information you might find useful:

1) I'm working on a new version (that should be available in the final version of Archi 4.9) which now rely on SQL.js (javascript port of SQLite) instead of alasql. This is a bit slower that alasql but is better documented and includes every SQLite functions.

2) It is possible to create views and I always wanted to provide some by default, for example to provide a direct access to concept type, name, documentation and properties, or relationships with type, name and documentation for source and target concepts. If you can tell me which views you think would be useful by default, then I can add them.

3) The new version I'm working on uses DataTables to browse the result set, and allows you to create pivot tables and pivot charts, thanks to PivotTable.js. If all goes well, you should be able to "save" you custom report (ie. pivot table or chart with a title) to a URL that encode the query, the pivot configuration an the title.

Regards,

JB
If you value and use Archi, please consider making a donation!
Ask your ArchiMate related questions to the ArchiMate Community's Discussion Board.

wiebow

Thanks for the replies!

Good to see that this part of the HTML report gets an overhaul in the 4.9 release. I know you also work on a new version of the one-page export, Jean-Paul.  Will that offer the same functionality?  We're using the current export at our company because we can upload it to a SharePoint site and it just works, like magic.  Downside it that the query functionality is not included in that, so for now we always need the Archi tool and the model file as well.

But looking at the bigger picture: analysis and the added value of models in this is something that is underappreciated and I want to do more with it, and share it. Good to see there is a WIKI page for it now. I will keep an eye on it.

Thanks!

And for everyone else reading this: please let me know the questions you have about analysis etc. We can work together on solutions, I'm sure.

Phil Beauvoir

> Good to see there is a WIKI page for it now. I will keep an eye on it.

It's an open wiki so if you, or anyone else, would like to edit it, please do.
If you value and use Archi, please consider making a donation!
Ask your ArchiMate related questions to the ArchiMate Community's Discussion Board.

danisman

#5
I've ported our model to SQLite and Postgres (in a Docker image) using Herve's plug-in .. with which to query using various SQL tools such as DBeaver and Beekeeper Studio. Next job is to build a simple web-app to allow users to query the model (that is uploaded to the database once a week from Archi). e.g. which model elements such as application components server various business capabilities and are served by which technology components. And property-based searches and filtering. Problem is traversing the relationships table as there are numerous services between the app. and the business capabilities. :-)

Was there any movement in the SQL feature of the HTML reporting? I'm on v5.1.
Thanks all, especially Messers Beauvoir & Sarrodie.