📉 PowerBI in AP: Implement requirements for PowerBI Desktop

[julialawrence]

User Story

As a user of Analytical Platform, I would like to be able to publish visualisations of my AP data using PowerBI Gateway

Value / Purpose

PowerBi is the preferred tool for this purpose across MOJ and has a high rate of adoption across the org, so being able to use it would improve AP customers' experience.

Useful Contacts

@julialawrence

User Types

Analytical Platform Users

Proposal

This story captures the implementations of the authoring workflow drawn here: https://mojdt.slack.com/archives/C062MEEF5SQ/p1699457775225609?thread_ts=1699456879.652999&cid=C062MEEF5SQ

Components to create are as follows:

• IAM Policy mirroring that of the alpha role of the master author.
• Github Team in the ministryofjustice org.
• Permission Set mapping the team to the iam permissions defined in the policy
• Document to provide instructions to the users about configuring Athena connectivity in PowerBI Desktop using AWS ODBC driver

There are complicated factors about how the iam policy is to be managed since we can't simply use the same IAM policy object as that is defined as an inline policy. Meaning, as part of the work, we need to consider how to keep the iam policy used by the permission set aligned to the alpha role permissions.

Additional Information

The workflow mentions a critical assumption that all readers/authors of the report have the same set of permissions. This assumption isn't correct, however, the requestor will appoint a master author of the report who will have access to the full data and then will utilise capability native to PowerBI to partition the report with smaller subsets of data. As members of the DPAT team, we are not implementing that portion of the solution: that will sit with the customer.

Definition of Done

• [ ] IAM Policy complications resolved
• [ ] IAM Policy defined
• [ ] Permission set defined
• [ ] Github Team defined
• [ ] Document authored
• [ ] Tested with Customer (build a Windows EC2 if needed)
• [ ] Delete Click Ops'ed Windows EC2 used for testing

[julialawrence]

Need more clarity on who the point of contact and master owner is.

[andrewc-moj]

Hey @julialawrence - sorry about the delay - we have four github usernames from Finance in order to copy permissions. Hope i'm putting this in the right issue for you.

Their names and associated usernames are:

Winnie Sibanda: clr65o Mark Bettis: MBETTIS Daniel Ukponmwan: sov67j Hasaam Khan: gzm57i

I told Winnie that they will (likely, though i'm not sure) have to make requests for Power BI desktop (if they don't have already) and the ODBC drivers. Please advise as and when it's necessary.

[andrewc-moj]

Hi @julialawrence - Anis asked me to additionally add usernames from Data and Analytics engineering teams:

myself andrewc-moj Gwion Aprhobat gwionap Ben Waterfield benwaterfield Phil Hall phil-hall-moj Chris Sheldon Csheldon155

[jacobwoffenden]

me and @Gary-H9 have picked up this ticket

we've so far in analytical-platform-development:

• created an IAM role (arn:aws:iam::525294151996:role/powerbi-desktop)
• created an EC2 keypair (key-05900ca440f153e4b)
• created a Windows Server 2022 instance (i-0af7f05db86af254a)
• connected via SSM
• installed PowerBI Desktop
• installed Amazon Athena ODBC

[Gary-H9]

Note: We need to destroy the click'opsed Windows EC2 instance in analytcial-platform-development called powerbi-desktop. Added to Definition of Done.

[jacobwoffenden]

Moving back to blocked as unclear how to proceed, work has been done but can't find any documentation

[Gary-H9]

Initial work done on team configuration in terraform was in this branch.

[Gary-H9]

Awaiting details from Gwion as per this thread. When these are provided they need to be added to this branch - add-powerbi-author.

[julialawrence]

If we don't get ublocked by end of sprint, these go to the backlog and another story raised to test with DMET. Testing is 1 meeting for 2 hours.