Problem for setting up connection between Power BI service and AWS RDS database (PostgreSQL)

[ymao2]

Service name

Team: Staff and service team Project: Correspondence Tool Staff is the first project we want to look at github repo: https://github.com/ministryofjustice/correspondence_tool_staff Name space: track-a-query

Power BI Service: this service may be hosted in Azure (not fully sure about it, maybe the IT desk who manages the Power BI license has more information about it). Usually Users login into the Power BI service WebUI from link https://powerbi.microsoft.com/en-us/landing/signin/ with your DOM1 credential

Service environment

• [ ] Dev / Development
• [X] Staging
• [X] QA
• [X] Prod / Production
• [ ] Other

Impact on the service

We are trying to establish different dashboards for our key stakeholders, one of the key features which we want to deliver in our roadmap. The dashboard will improve the visibilities of data to users, therefore to improve/monitor the efficiency of their work.

Problem description

We are still in the early stage of development dashboard, what we have managed is to use 'importing mode' (uploading your data set into Power BI service) as the input to our dashboards which have so many limitations such as the size of data and long delay, and potentially having security issues as you need to copy live data out. What we want is to use 'direct-query' mode for those dashboards -> making queries into our AWS RDS directly. The AW RDS is only visible within our name spaces (cluster), no way for Power BI to see it, Now we are blocked by this issue entirely.

We have thought about using alternative BI platform/tools such as open source tool, or Tableau, after having internal discussion, we feel Power BI is more suitable as it has better security integration with our MOJ users and license fee is relevant small as it is part of MS solution, although from our developers' view, we more prefer BI which can be cross different platform not only Windows.

No matter which solution we try to use, how to set up the connection between BI platform/service to AWS RDS database stays the same, maybe PowerBI service is little bit more difficult. We did some research and found the following link which may be useful to this problem https://aws.amazon.com/blogs/big-data/integrate-power-bi-with-amazon-redshift-for-insights-and-analytics/

Contact person

Yikang Mao (developer): yikang.mao@digital.justice.gov.uk , on slack Deepika Patel (DM): deepika.patel@digital.justice.gov.uk

[AntonyBishop]

Hi @ymao2,

Is this issue still required? I'm not sure that my team is best placed to answer this question.

[davidread]

Just to re-express where we left it on chat:

From Cloud Platform's perspective this should be do-able by @ymao2's team - this is just an RDS instance in the Cloud Platform VPC to connect Power BI or whatever to. Suggest you or your tech architect looks at how to do the integration as there appear to be a number of options to evaluate.

If we need to discuss changes to the design of the Cloud Platform to support this use case, then we're happy to look at the reasoning.

[ymao2]

@davidread thank you for updating this ticket, we have spoken to the tech architect, we have been passed to speak to analytic platform team, and had a chat too, at least from their perspective, Power BI not their choice and they haven't found the one they need, it may take a long time.

I had a chat within my team after that, they suggested we can try ourselves which I didn't realise this before, I thought we must try it through the cloud platform team. so we will try ourselves within our name spaces and not sure whether can copy the architecture from the link I found via installing a gateway on Window server. Alternatively we have another solution in our mind too, but anyway if we need cloud platform help, will come back , for now I can close this issue. Thank you for your help!