API endpoints for Power BI

[askbulle]

User story As an end-user or leader I want to bring statistical VSM data to other tools, e.g. Power BI dashboards so that I can visualise current process health, improvement pace and use the data to ensure progress

Requirements

• [ ] Data setup to contextualise the data (MVP according to existing Power BI needs)
• [ ] GDPR is not violated (e.g. user data in the changelog)

[SjoenH]

Virtual sql-table-views vs API-access.

We talked about if a proper api would be better. I'm leaning towards it since that would be a more proper way to do it for everyone...

Also, some GDPR things to consider. What is considered sensitive data? Names, and timestamps in the change-log could be used to check when people are working etc...

The importance of free data

Might lead to higher adoption and usage. Supporting the power-users. 💪 -> Value

[knudsvik]

@Magos @SjoenH : I have discussed use of API's in Power Bi with a colleague who tells me some API's are very straightforward to connect to from Power BI, such as the Centuries API (api.centuries.equinor.com). So I agree with @SjoenH here that we should just go for that from the start. I guess we can start with a simple endpoint with some basic data to verify and then gradually improve it based on need?

[Magos]

Desired statistics:

• A process listing with additional statistics :
  • Process ID and title
  • Task counts by category & Solved/unsolved (number of problems, questions, ideas, risks, solved problems, mitigated risks)
  • Created datetime
  • Last updated datetime
  • Is to-be process (true/false)
  • To-be process (reference to process)
  • Duplicated from (reference to process)
  • Number of favourite markings
• A label listing with the connected processes
  • process ID, Label text
• A category listing with the connected processes
  • process ID, Category text

We want access to this to be open to all Equinor users, by default.

[knudsvik]

@Magos For the changelog I am currently using the following SQL query:

SELECT [fkVsm], [ChangeDate] As Updated, [UserIdentity] As UpdatedBy
  FROM [dbo].[ChangeLog]
  WHERE fkVsm is not null
UNION ALL

SELECT [PkVsm] As fkVsm,[Updated],[UpdatedBy]
FROM [dbo].[VSM]
  FOR SYSTEM_TIME ALL
where updated > '2022-03-16 10:47'

order by ChangeDate desc

It is however returning short names. To be safer wrt GDPR I would rather have a UUID or similar representing the user instead of the shortname.

[Magos]

@knudsvik @SjoenH I currently have a version on Dev & Test environments which I believe covers this request, with one outstanding issue. The new API routes are found under /api/v1/statistics. For the previously-mentioned personal information concerns, the identity of the updater in /activity is obscured using a salted SHA2-256 hashing function (computed on the SQL side). This means it should be stable, allowing edits from the same user to be identified as being the same user, but hard for API consumers to identify the user from our output.

In my testing, the /activity route gives a 415 Unsupported Media Type response and does not enter my code at all - I have not yet found out why this happens. However, since Torjan mentioned this might be possible to leave out of an MVP I'm deploying to test now.

Would be good if you could try some experiments with consuming these statistics APIs and give any suggestions for improvement. Currently, all endpoints allow filtering on an individual VSM by id, or on a set of labels (using e.g. ?labels=Vanaheim&labels=Svartalfrheim for multiple labels). The /category endpoint does not support labels, since those didn't seem relevant to it.

[knudsvik]

I have troubles testing these, seems like an access issue, let's have a chat @Magos

[SjoenH]

@anheri @pagodo :wave: We might need some input here...

How would you open the API for everyone in the Equinor tenant? Specifically for use in PowerBi.

[Magos]

Thorjan has mentioned being able to use the Organizational Account method offered by PowerBI in the Centuries API. Looking at their code, all their controllers have a [Connect2DbAsUser] attribute which I'm not entirely sure where is defined yet. For the statistics controller this method might actually work, as I've defined these statistics views with a GRANT SELECT TO PUBLIC permission.

[knudsvik]

@Magos Any updates here?

[HavardNot]

Created new endpoints listed here:

The endpoints provide the data listed in this comment, except for "to be" as it is deprecated: https://github.com/equinor/flyt/issues/160#issuecomment-1054054044

See illustration of data flow here for guidelines for other data that is wanted. Not all data is available to the flyt-api so it may need to be sourced elsewhere.