How to create Relational Model and Manage Relationships in PowerBI Desktop

How to Load data from SQL Server Tables, Create Relations and Manage Relationships in Power BI

In this we will learn about :

--Get Data from SQL Server Tables

--Creating Relational Model & Managing the Relationships between the Tables

----------------------------------------------------------------------------

Get Data from SQL Server Tables :

----------------------------------------------------------------------------

1.Go to Home > GetData > SQL Server

2. Next provide Sever Name ; Database Name 

If you want load Multiple Tables , You can choose Data Connectivity Mode=Import

If you want to Load Data from a View/ a Query , you can Data Connectivity Mode=Direct Query and Write your Query in the SQL Statement Box.

Here I am Importing the Tables from a Database ( MyAnalyticsDB)

Next say OK.

3. Select the Tables that you want to use in your Reporting.

It will show all the available Tables,Views.. in the Database, You select the required one.

Here, I have selected 5 Tables.

Once selected, You can either Load directly or can Edit before Load, by Clicking on Edit button.

4. Editing the Tables in Query Editor before Load.

Before Loading the Tables, You can go for Query Edit to apply some Transformations like below :

A) Grouping the Tables :
In the Query Editor, Go to Queries and select the Table

Right Click on the Table > Drag and Move to Group > New Group

Next give the Group Name and Description , then say OK

Like wise create the Groups and move you Tables to that Groups.

B) Renaming the Table/Column Name:
You can Rename a Table by Just Double Clicking on the Name or you can do from Properties in the Query Settings window.

Renaming a Table :

                                                 

Renaming a Column :

C) Applying any other Transformations/Changing Datatypes/Adding Columns/ Splitting Columns..etc.

Here we will only see the available Options.In the upcoming articles, I will explain about various Transformations.

Query Editor > HomeTab :

Query Editor > Transform Tab :

Query Editor > Add Column Tab :

Query Editor > View Tab :

In the Query Editor When You right click on a Column we will see below options :

In the upcoming articles, I will explain about various Transformations.

Once we finish the basic Edits, Apply Changes and Close

After applying the Edits/Transformations the updated data Tables will be loaded to the Data Model...

Once they Loaded, you can see the Tables and Fields...

----------------------------------------------------------------------------

Creating Relational Model and Managing Relationships:

----------------------------------------------------------------------------

1. Go to Home > Manage Relationships

It will open Manage Relations ships window, where you can click on "Auto Detect" to Identify

Related tables, which has already a Primary Key and Foreign Key Relationships.

Still if you want add the Relationship between Fact Table with other Dimension Tables, You do it by Clicking on New.

Please note that to establish a Relationship between two Tables, there should be Common Key Column between both the Tables.

2 .Manage Relationships > New > Create Relationship

Select the FactTable and its Key Column, and select the DimensionTable and its Key Column then It will select the Cardinality of the Relation established.

Like wise you can Add and Mange the Relationships between the required Tables.

Next Close.

3.Viewing the Relational Diagram

Now we can See the Relational Model Diagram. This is a Star Schema Relational Model, where the Fact Table is surrounded by one or more Dimension Tables.

From here also we can manage the Relationships between the Tables.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------