Refresh Cloud hosted MySQL Database

[Agreenwood]

Hi, I have published a Power BI report, which points to a cloud based MySQL database hosted via AWS RDS.

To refresh the data I believe that a gateway is required. Is this correct?

It seems very peculiar that having built a serverless solution a server is now required for the single reason of facilitating a data refresh. If a gateway is required, are there any plans to change this so that all cloud based data sources are supported without the requirement of a gateway?

Thanks.

[v-piga-msft · Accepted solution]

Hi @Agreenwood,

If I understand your scenario correctly that you connect the data from My SQL database which hosted on AWS RDS and you connect it with Amazon Redshift in Power BI Desktop?

If it is, I'm afraid that you may require a gateway to refresh the data.

Here is a similar thread you could refer to.

Best Regards,

Cherry

[Agreenwood]

Hi,

That's correct - though the service is Aurora rather than RedShift.

I'm in a postition where I have a full serverless solution setup, though now I need to setup a server simply to refresh data from our cloud database to Power BI. This does seem very odd... is there an alternative way of facilitating a data refresh rather than setting up and maintaining a whole server? For example, perhaps this could be triggered through an API refresh? Or is there a third party application which may do the trick?

[ben_kog]

@Agreenwood have you had any success?

I am in a similar situation as you. I have a fully serverless setup with data source being MariaDB on AWS, but cannot refresh the dataset without a Gateway. It is confusing and strange to say the least.

[Agreenwood]

@ben_kog Hi Ben,

I'm afraid to tell you that your solution is doomed! I can confirm that you require the Power BI gateway setup on a Windows PC / Server to be able to finish the solution you are after.

My solution, which was built serverless with the intention of handling potentially hundreds of clients had to be redesigned. I intended on each client to have their own database, and then the reporting via Power BI would be pulled with individual connection strings. This would be doable in Tableau and other BI solutions but no so easily in Power BI.

My solution in the end:

Create a Data Warehouse which sits above all the individual databases. EC2 solution with Power BI Gateway installed, connecting to the Data Warehouse. Power BI to the Data Warehouse with Row Level Security (RLS).

It was a pain, though it resolved all of my issues.

Your options for the EC2 solution:

1) If your dataset is small, and doesn't require much power a T2 Nano Server could be powerful enough

2) If you need something more powerful and have small budgets, I have some Lambda scripts which turn a T2 Large Server on shortly before a Power BI scheduled refresh is due to happen, and then switches it off again after the scheduled refresh should have finished

3) Have an EC2 server switched on all the time

So basically, I did resolve all of my problems so I can continue to use Power BI for multiple clients, but (1) it was a real pain to setup a Data Warehouse, (2) it still didn't ensure my end solution was serverless, and (3) it cost $$$.

Hope this helps. If you want some Lambda scripts for switching EC2's on and off let me know.

[ben_kog]

@Agreenwood 

Many thanks for your update. I feared this. My main issue is the cost of running a server on EC2. I am going to run some tests with a nano instance to see if it can handle the load. I am hopeful that it can, but I would greatly appreciate access to your scripts for on-demand scaling of the EC2 instance in case it cannot or if our data grows to such an extent in the future.

[Agreenwood]

@ben_kog 

No worries - below are start and stop python 2.7 scripts. Put them in Lambda, schedule them to run by creating a rule in CloudWatch.

Remember that if you're scheduling reports to run in Power BI they can start upto 20 minutes (from experience) after they are scheduled and so you should only switch the server off after adjusting for this lag.

Start:

import boto3
# Enter the region your instances are in. Include only the region without specifying Availability Zone; e.g.; 'us-east-1'
region = 'eu-west-2'
# Enter your instances here: ex. ['X-XXXXXXXX', 'X-XXXXXXXX']
instances = ['X-XXXXXXXXXXXXX']

def lambda_handler(event, context):
    ec2 = boto3.client('ec2', region_name=region)
    ec2.start_instances(InstanceIds=instances)
    print 'started your instances: ' + str(instances)

Stop:

import boto3
# Enter the region your instances are in. Include only the region without specifying Availability Zone; e.g.; 'us-east-1'
region = 'eu-west-2'
# Enter your instances here: ex. ['X-XXXXXXXX', 'X-XXXXXXXX']
instances = ['X-XXXXXXXXXXXXX']

def lambda_handler(event, context):
    ec2 = boto3.client('ec2', region_name=region)
    ec2.stop_instances(InstanceIds=instances)
    print 'stopped your instances: ' + str(instances)

[ben_kog]

@Agreenwood 

Many Thanks again!