Data Modelling is the most important aspect of a Business Intelligence Implementation. Without a proper data model,
the business will not get a true picture of the data. Data Modelling is a process of organizing the data elements and defining how they relate with each other. A good data model adheres to the business rules and assists in quick decision making.
Combining Queries Using Joins
Power BI Supports combining queries using the merge queries option in Query Editor. It supports all the different types
of SQL Joins. Joins are used to display the data from multiple tables or when merging two tables based on a join condition. These are the various types of joins present in Power BI
Inner Join: Only the matching rows between the two tables are returned, else it returns zero rows.
Left Join:It returns all the rows from the first table and only the matching rows from the second table.
Right Join: Right join is opposite to the left join. It returns all the rows from the second table and only the matching rows from the first table.
Full Outer Join:It returns all the rows present in the first and the second table.
Left Anti:It returns all the rows from the first table which do not have a match in the second table.
Right Anti:It returns all the rows from the second table which do not have a match in the first table.
Power BI Relationships
Relationship in Power BI is an important activity while creating a data model. Relationship between two tables work by matching the data in the Key Columns. In a typical environment, two tables are created based on the Primary Key on one table and foreign Key on another table. Usually the columns/fields having the same name between the tables are assumed to be related, though this may be true/not.
There are three Kinds of relationships/cardinalities between the tables which are as follows
Many to one(*:1):This is the default relationship and means that many rows in one table relates to one row in another table.
One to One(1:1):In this relationship, one row in the first table is related to one row in the second table.
Many to Many:In this relationship, many rows in the first table relates to many rows in the second table
Key Concepts to Consider in Data Relationships
Cross Filter Direction
Single Direction-Filter flows one way
Bi-directional-Filter flows both ways
Active/Inactive Relationship
Active-used by default in visuals
Inactive-Must be activated by DAX
(USERELATIONSHIP())
The Main difference between Joins and Relationship is a join combines tables into one single table while a relationship
links tables but keeps them separate
DIMENSION AND FACT TABLES
Dimensions and Facts: Dimension and Fact tables are the
main ingredients of any Business Intelligence implementation.
They are used to form the Star or the Snowflake Schemas which
are designed as part of building a data warehouse or a data
mart.
Dimension table:It contains the qualitative/descriptive
attribute of the data. For example, Customer Dimension may contain information about the Customer such as Customer Name,
address, contact number and so on. The dimensions field contain
the textual/character type of data. It contains the Primary Key
with the respective foreign key in the fact table.There are different types of Dimension Tables, some of the commonly used ones are as follows
Slowly Changing Dimensions(SCD):It is a dimension table where the row of data in the table varies with time. It is used
to track current and historical data.Conformed Dimensions:A dimension table is said to be conformed if it has the same context and content when used with
the different fact tablesRole-Playing dimensions:A Single dimension table can be joined many times to a fact table. This can be done by creating
multiple copies of the dimension.
Fact Table:It contains the foreign keys of the dimensions table. It stores the quantitative attribute of the data.
Some of the other common fact tables are
Fact-less fact-table:This fact table contains only the foreign keys of the dimension tables and does not contain any
measure values.Conformed fact tables:Similar to the conformed dimensions,
the conformed fact tables are used across multiple dimension
models.
The dimension table and fact table are the basis of the star schema.
Star Schema
Star Schemas are created in a data warehouse and data mart
environments. It consists of the fact and dimension tables. The
shape of the star schema is such that the fact table is in the
middle, surrounded by multiple dimension tables. The Schema
assumes the shape of a star and hence the name.
A star schema supports querying huge amount of data stored in
a typical data warehouse storage system. The queries run against
the star schema are faster due to the reduced number of joins to
query the data. Since the fact table contains the foreign keys of
the dimensions table, during the ETL Process, the dimension tables
are loaded before the fact tables.
Snowflake Schema
It is also used in the data warehouse and data marts. It is an extension of the star schema. In a star schema, each dimension is
stored in a single dimension table, whereas in a snowflake schema,
a dimension explodes or has a lookup table.
Top comments (0)