Connecting Power BI Desktop to MS SQL Server on AWS and Publishing to Power BI Service

[manoj_0911]

Connecting Power BI Desktop to MS SQL Server on AWS and Publishing to Power BI Service: Detailed Guide for Creating & Publishing Dashboards

Hello Power BI Community,

I'm seeking comprehensive guidance on connecting Power BI Desktop to our CXI product's database, which is hosted on a Microsoft SQL Server instance on AWS. Additionally, I'm interested in the process of creating dashboards from this data and publishing them to the Power BI service. Specifically, I have the following questions:

1. Connection Setup: What are the steps involved in establishing a connection between Power BI Desktop and a Microsoft SQL Server database hosted on AWS? Are there any specific configurations or considerations I need to keep in mind?

2. Data Import: Once the connection is established, how can I import data from our CXI product's database into Power BI Desktop? Are there any limitations or best practices for data import, such as filtering criteria or data shaping options?

3. Dashboard Creation: I know a little bit to do it.

4. Publishing to Power BI Service: Once the dashboard is created in Power BI Desktop, what are the steps for publishing it to the Power BI service? Are there any considerations or permissions required for publishing dashboards to specific workspaces or sharing them with other users?

5. Data Refresh: How does Power BI handle automatic data refreshes for dashboards connected to a Microsoft SQL Server database on AWS? Are there any scheduling options or limitations I should be aware of for data refreshes?

I would greatly appreciate any detailed insights, tips, or step-by-step guides that can help me navigate the process of connecting Power BI Desktop to our CXI product's database, creating dashboards, and publishing them to the Power BI service. Additionally, if possible, please provide the answers in a table format for easy understanding, as I need to present a demo to my CEO.

Thank you for your assistance!
Manoj Prabhakar

[Anonymous · Accepted solution]

Hi @manoj_0911 

Perhaps the following will help.

1. Connection Setup: 

Enter the AWS instance endpoint into the server name. Select either "Import" or "DirectQuery" mode as needed.    "Import" mode imports data into Power BI's in-memory engine, while "DirectQuery" mode maintains a live connection to the database.

2. For some limitations of the different connection modes, please refer to the following documentation:

DirectQuery in Power BI - Power BI | Microsoft Learn

3. One thing that needs to be corrected is that dashboards are created in the Power BI service, not in Desktop. First, create reports in Power BI Desktop using visualizations. For more details, you can read related document link:

Create reports and dashboards in Power BI - documentation - Power BI | Microsoft Learn

4. You can publish reports to my workspace or workspace. myworkspace is a personal workspace that allows you to use your own content, not collaborate with others. if you need to collaborate, you need to create a workspace. to collaborate in a workspace or share content with others in Power BI services, you need a Power BI Pro or Premium Per User (PPU) license. For more details, you can read related document link:

Publish from Power BI Desktop - Power BI | Microsoft Learn

Basic concepts for designers in the Power BI service - Power BI | Microsoft Learn

Workspaces in Power BI - Power BI | Microsoft Learn

Create a workspace in Power BI - Power BI | Microsoft Learn

5. Before configuring refresh, you need to configure the gateway. Gateways are the bridge between local data and cloud services. For more details about gateway, you can read related document link:

What is an on-premises data gateway? | Microsoft Learn

Add or remove a gateway data source - Power BI | Microsoft Learn

About Refresh:

Data refresh in Power BI - Power BI | Microsoft Learn

Configure scheduled refresh - Power BI | Microsoft Learn

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.