Hi Daxians! Today, we will learn about the basics of DAX and data model.
What is DAX?
DAX stands for Data Analysis Expressions. It is popularly recognized as the programming language of Microsoft Power BI, Microsoft Analysis Services and Microsoft Power Pivot for Excel. Release of PowerPivot for Microsoft Excel 2010 saw the creation of DAX also in 2010. DAX is there in all tools which share the internal engine called Tabular.
DAX is a functional language – everything in DAX is a function. To implement many complex calculations, one must make use of nesting of multiple DAX functions.
Application of business formulas and to work on complex data models led to the introduction of DAX. To get good idea about what DAX can do, let’s first understand some basics regarding data model.
Basics of Data Model
Data model is a group or set of tables which are linked to each-other via relationships. These tables are a set of rows having data in them. A table is, indeed, a data model – the smallest data model. Each row has many columns. Each column equates to a list or array, i.e., all the rows in a column have same data type and contain information about a single unit. Every row in a data model is a record.
Multiple tables in a data model are associated with each-other via relationships. A relationship is a link between two tables. This link exists by means of keys or ID columns. Existence of a relationship between two tables makes them related.
An example of a Data Model
Following image shows simple example of a data model:
Some observations from the given data model include:
- The given data model has 6 tables – Calendar5, FactSales, DimDate, DimProduct, DimProductSubcategory and DimProductCategory.
- The given links or lines existing between different tables are relationships.
- Let’s look at the relationship between FactSales and DimProduct. We can see that DimProduct is on the “1” side of the relationship since “1” is written on the link on DimProduct side. The link on the FactSales side is having “*” written on it. This means that FactSales is on the many side of the relationship. That is, DimProduct has exactly one key (row) associated with one product while FactSales has many keys (rows) associated with one product. This makes sense also since FactSales is a transaction table. It records the data regarding the sales of different products. So, one product can have many sales associated with it. The arrow on the link tells us about the direction of the relationship. The arrow shows us the direction of filtering (cross-filter). If we carefully look at the relationship between both these tables, we can see that the direction of arrow points to FactSales. Thus, DimProduct can filter FactSales table. However, vice-versa is not true.
- In the same way, FactSales is related to Calendar5 via many-to-one relationship with FactSales being on the many side and Calendar5 on the one side. In this case also, Calendar5 can filter FactSales and vice-versa is not true.
One-to-One & Weak Relationships
- There are two more types of relationships which exist in the data model world – one-to-one and many-to-many relationships. One-to-one relationship has both the tables on the one side. Each table has unique row associated with the keys. Similarly, many-to-many has both the tables on the many sides of the relationship. Many-to-many relationships are also termed as weak relationships. We will talk about both these relationships in the upcoming tutorials.
- In the given data model, we can see that a relationship chain exists between FactSales, DimProduct, DimProductSubcategory and DimProductCategory.
A Chain of Relationship
- Starting with DimProductCategory and DimProductSubcategory, a one-to-many relationship exists between them – with DimProductCategory on the one side and DimProductSubcategory on the many side. That is, one product category can have many product sub-categories. The direction of the relationship starts from DimProductCategory and points to DimProductSubcategory. Therefore, any column from DimProductCategory can filter DimProductSubcategory. In the same way, if we look at DimProduct and DimProductSubcategory, we can see that DimProduct is on the many side of the relationship while DimProductSubcategory is on the one side of the relationship. Therefore, each product sub-category can have many products in its hierarchy. We can observe that the hierarchy starts from Product Category and ends on the Product.
The given example talks about a simple data model. In the upcoming tutorials, we will talk about more complex data model and how we can make use of DAX to build complex business formulas and calculations to fetch information out of those data models. Readers can also check out my channel on Power BI, DAX and data science. Stay tuned!