What Are the Possibilities to Build Date Tables in Self-Service Environments?

在自助服务环境中构建日期表的可能性有哪些？

Data Engineering: What Are the Possibilities to Build Date Tables in Self-Service Environments? For years, I created date tables with DAX code whenever I didn’t have a way to create them upstream of the data flow. Now I’ve realised there’s another way to do it. Let’s see what the alternatives are and how they compare. 数据工程：在自助服务环境中构建日期表的可能性有哪些？多年来，每当我无法在数据流的上游创建日期表时，我都会使用 DAX 代码来创建它们。现在我意识到还有另一种方法。让我们看看有哪些替代方案以及它们之间的对比。

Introduction

引言

For years, I’ve built date tables in a tabular model with DAX code, when there was no other source for such a table. I created a template code and reused it over and over again. It works very well in a multitude of situations. I distributed it to my clients, and they are all happy with it. But about two weeks ago, I had a discussion with a colleague that opened my eyes to a way to do it, which I didn’t think about until now. So, let’s look at the variants to build a date table and compare them. But irrespective of how to do it, it’s important to know the requirements for date tables in semantic models. 多年来，当没有其他数据源时，我一直使用 DAX 代码在表格模型中构建日期表。我创建了一个模板代码并反复使用它。它在多种情况下都运行良好。我将其分发给我的客户，他们对此都很满意。但大约两周前，我与一位同事的讨论让我大开眼界，发现了一种我以前从未想过的方法。因此，让我们看看构建日期表的各种变体并进行比较。但无论采用哪种方式，了解语义模型中对日期表的要求都很重要。

What happens when there is a DWH?

如果有数据仓库（DWH）会怎样？

First, when I have a data store and a source for the semantic model, whether a relational database, a Fabric Lake, or any other centralised data store, I will build it there and consume it in the Semantic model. The options available there for building such a table are very extensive and flexible, and neither DAX nor Power Query is more efficient. Therefore, there is no question about how to do it in such a case. 首先，当我拥有数据存储和语义模型的数据源时（无论是关系型数据库、Fabric Lake 还是任何其他集中式数据存储），我都会在源头构建它，然后在语义模型中进行消费。在那里构建此类表的选项非常广泛且灵活，DAX 和 Power Query 在这种情况下都不具备更高的效率。因此，在这种情况下，无需纠结如何实现。

DAX tables

DAX 表

Generating a date table in DAX is relatively easy and straightforward. DAX offers a great number of functions to add columns and features to a date table. You always start with the CALENDAR() call to set the start and end date. You can either use fixed values, like MIN()/MAX() calls, based on available data to get start- and end-date from a data table inside the data model, or some (Power Query) parameters. 在 DAX 中生成日期表相对简单直接。DAX 提供了大量函数来为日期表添加列和功能。你总是从 CALENDAR() 调用开始，以设置开始和结束日期。你可以使用固定值（如基于数据模型中现有数据表的 MIN()/MAX() 调用）来获取开始和结束日期，也可以使用某些（Power Query）参数。

For example, something like this: 例如：

DimDate = CALENDAR ( 
    DATE ( YEAR ( MIN ( 'Online Sales Order'[Date] ) ), 1, 1 ), 
    DATE ( YEAR ( MAX ( 'Online Sales Order'[Date] ) ), 12, 31 ) 
)

As Microsoft requires having full years in the date table, I start with the first of January and end with the last of December (31.12.). Next, you can add further columns to add the years, quarters, months and days to the table. You can do it within the definition of the table by using ADDCOLUMNS(): 由于微软要求日期表必须包含完整的年份，我从 1 月 1 日开始，到 12 月 31 日结束。接下来，你可以添加更多列，将年份、季度、月份和日期添加到表中。你可以通过在表定义中使用 ADDCOLUMNS() 来实现：

(Code snippet omitted for brevity) (代码片段略)

The interesting part is that it is possible to pass the name or a locale setting to the FORMAT() function, for example, to create month names in different languages: 有趣的是，可以将名称或区域设置传递给 FORMAT() 函数，例如，创建不同语言的月份名称：

(Code snippet omitted for brevity)

This results in a table like this: 这将生成如下表：

Figure 1 – Date table created with DAX with columns in multiple languages (Figure by the Author) 图 1 – 使用 DAX 创建的包含多种语言列的日期表（作者供图）

Note the third parameter “de-de” of the FORMAT() call and the corresponding columns in the table—one in English and one in German. But with the advent of user-context-aware calculated columns, this can also be implemented differently. Read here for more information about this new feature. In case you need to calculate columns with a more complex logic, you can do it with calculated columns using Context transition to access the entire table. If you don’t know context transition, read this piece with an explanation of this concept. 请注意 FORMAT() 调用中的第三个参数 “de-de” 以及表中相应的列——一列是英语，一列是德语。但随着支持用户上下文感知的计算列的出现，这也可以通过不同方式实现。点击此处了解有关此新功能的更多信息。如果你需要使用更复杂的逻辑来计算列，可以使用计算列，利用上下文转换（Context transition）来访问整个表。如果你不了解上下文转换，请阅读这篇文章以获取该概念的解释。