Class Twenty – Seven: Text (Data modelling)

The tables in a data model are called “Fact” tables and “Dimension” table.

A fact table contains quantitative information like sales transactions or customer purchase While a dimension table contains descriptives information like customer demographics or product categories.

Example;                                                                           

Let say you have two tables,” Sales “and “product”. The Sales table is a fact table that contains data about each sales transactions including the date, product, quantity, and sale amount. The product table is a dimension table that contains information about each product, including the product name, category and description. You can create the relationship between the table by linking the productid column in the sales table to the productid column. in the product table.

Cardinality is the direction in which the data flows.

1.    One to one relationship: It is called one to one because it connects directly to one key in the other table.

2.     One to many relationships; This is because one record in one table is linked to multiple records in another table.

3.    Many to one relationship: This is because many records in one table is linked with one record in another table

4.    Many to many relationships: This is because many records in one table is linked to many records in another table.

Data Analysis Expression

Data Analysis Expression commonly known as DAX is the formula languages that derives PowerBI.

With DAX you can (a) Add calculated columns and Measures to your model using intuitive syntax. (b) Go beyond the capabilities of traditional “grid style” formulas, with powerful and flexible functions built specifically to work with relative data models.

Two ways to use DAX

1.    Calculated columns

2.    Measures

DAX is a collection of functions, operators and values to performed advance calculations and queries on our data.

Calculated columns; This allows you to add new formula-based columns to tables.

·        No “A1-style” reference; calculated columns refer to entire table or columns.

·        Calculated columns generate values for each row, which are visible within the table in a data view.

·        Calculated columns understand row context; they are greater for defining properties based on information on each row, but generally useless for aggregations (Sum, count, average etc.)

NOTE; Used calculated columns when you want to fixed values to each row in a table (or used query editor).

Do not use calculated columns for aggregation formulas, or to calculate field for values area of visualization (use measure instead).

Measures; are DAX formula used to generate new calculated values. Like calculated columns, measures reference entire table or columns (no A1-style or grid reference).

·        Unlike calculated columns, measure values are not visible within the tables, they can only be seen within a visualization like chart or matrix (similar to calculated field in on excel pivot)

·        Measures are evaluated based on filter context, which means they recalculate when the fields or filters around them changes (like new columns or row labels are pulled into a matrix or when new filter are applied on a report).

Calculated columns VS Measures