Connecting to SQL Server while keeping relationships active

[Austen229022]

New to dealing with SQL Databases in Power BI so would love some help

Currently I am working with SQL database that I am wanting to import the Live Data into Power BI, which I can do then chose the tables that I am wanitng to add.

The issue is that there is a fairly complex data relationship model in the background which I need to set up in SQL.

How can I have the live data from SQL come into Power BI while also keeping the relationships from SQL Manager?

Do I still need to import all the tables into Power BI and create the relationships between the tables in Power BI as well as having the relationships from the SQL database?

[parry2k]

Correct

[parry2k · Accepted solution]

@Austen229022 I would recommend creating a view in the SQL putting the logic there and then using that view in Power BI to pull the data. If tomorrow there is a need to change the logic, you can do it in the view, and no need to touch Power BI.

[Austen229022]

Thanks for replying

So when you go through importing the DB from SQL, you would select the view?

[Alef_Ricardo_]

Yes, you can import live data from SQL into Power BI while keeping the relationships from SQL Manager. Here are the steps you can follow:

1. Go to the `Home` tab of Power BI and click on `Get Data` drop down and further click on `SQL Server`³.
2. A new SQL Server database window will open. Provide server name and database name³.
3. Select `DirectQuery` as data connectivity mode³.
4. Expand `Advanced Option` and paste your query in `SQL statement`³.

This will allow you to import live data directly from the SQL query into Power BI³.

As for the relationships, it's recommended to create views in your SQL database instead of importing tables directly². This creates an indirection layer that simplifies the change management of the database structure². When you import these views into Power BI, it should maintain the relationships defined in SQL.

However, you might still need to define some relationships within Power BI depending on your specific use case and how you plan to use the data within your reports.

Remember, it's always a good practice to only include the columns that are useful and will be used in the Power BI data model when creating your views². This can help improve performance and usability.

I hope this helps! Let me know if you have any other questions.