ssas表格模型 权限控制_Analysis Services（SSAS）表格模型中的时间智能

ssas表格模型 权限控制

In the analytical world, time is an important slicer. The ability to view data over time helps analyze measures in actionable steps. An Analysis Service (SSAS) database can help accomplish this important step. Business users are going to want the ability to see measures a like year to date as well as the period over period comparisons. SSAS enables Time Intelligence with 2 features: the date table and DAX functions.

在分析世界中，时间是一个重要的限制因素。 随着时间的推移查看数据的能力有助于以可行的步骤分析度量。 Analysis Service（SSAS）数据库可以帮助完成这一重要步骤。 商业用户将希望能够看到类似的年初至今的度量以及一段时间内的比较。 SSAS通过以下两个功能启用了时间智能功能：日期表和DAX功能。

The SSAS Tabular Model will need to be built with a date dimension. Some people still reference this as a time dimension, but in most cases, the table will have dates and no times. There is such a thing as a time dimension with a time like 12:30PM and attributes like an hour and minute, but this article is all about the date – Year, Quarter, Month and Day.

SSAS表格模型将需要使用日期维度来构建。 某些人仍然将其作为时间维度，但是在大多数情况下，表格中将包含日期且没有时间。 时间维度是这样的，其时间为12:30 PM，属性为小时和分钟，但是本文只涉及日期-年，季度，月和日。

The import of this table is essential for Time Intelligence in a tabular model. Once the date table is imported, the table is marked as Date table. Figure 2 shows the menu choice for marking a table as date table.

在表格模型中，导入此表对于“时间智能”至关重要。 导入日期表后，该表将标记为“日期表”。 图2显示了将表标记为日期表的菜单选项。

NOTE: The table has to have a column that is unique and this column has to be a date or datetime data type. Also, an entry of -1 as a surrogate key with Nulls or unknowns in the attributes can throw off the Marking as Date table as well as DAX Time Intelligence functions.

注意：该表必须具有唯一的列，并且此列必须是日期或日期时间数据类型。 同样，在属性中具有空值或未知数的代理键-1项的输入可能会导致“标记为日期”表和DAX时间智能功能失效。

The relationship between the date table and the date keys in a fact table must be established for the Time Intelligence functions to work. In this example, the Internet Sale fact table has 3 columns that are related to the date dimension: Order Date, Ship Date and Due Date. Only one relationship can be active. Figure 3 shows the order date as the active relationship and Ship and Due date are inactive.

必须建立日期表和事实表中的日期键之间的关系，时间智能功能才能正常工作。 在此示例中，“ Internet销售”事实表具有3个与日期维度相关的列：“订购日期”，“发货日期”和“到期日期”。 只能建立一种关系。 图3将订单日期显示为有效关系，而“发货”和“到期”日期未激活。

If you have multiple dates related to the date dimension from a fact, it might work best to name the date dimension like the related column – for instance, in this case, Order Date. The reason you might not want to do this is if the date dimension is related to another fact table in the same model and that column is not Order Date. More advanced DAX functions can be used to change the date that is related to the fact for a particular measure.

如果您有一个事实中与日期维度相关的多个日期，则最好将日期维度命名为相关列，例如在这种情况下为“订购日期”。 您可能不希望这样做的原因是，如果日期维与同一模型中的另一个事实表有关，并且该列不是“订购日期”。 可以使用更高级的DAX功能来更改与特定度量的事实相关的日期。

In order to use Time Intelligence, the model needs a measure to view over time. This example will use the Internet Sale Amount which has already been created as a SUM aggregation in the Internet Sales table. There are other measures like Total Product Cost and Gross Profit Margin that could be used.

为了使用时间智能，模型需要一种度量来随时间推移进行查看。 本示例将使用已在Internet Sales表中作为SUM聚合创建的Internet Sales Amount。 还有其他一些可以使用的指标，例如总产品成本和毛利润率。

The bottom pane of the table is where measures are created. It is best to keep them in the same tab as the fact table but it is not required. If included in another table, the references to columns used in the measure will have to have the table name specified.

表格的底部窗格是创建度量的位置。 最好将它们与事实表保留在同一选项卡中，但这不是必需的。 如果包含在另一个表中，则对度量中使用的列的引用将必须指定表名。

The first example will be Year to Date for Internet Sales. The formula is simple:

第一个示例是“互联网销售的年初至今”。 公式很简单：

YTD Internet Sales Amount:= CALCULATE ( 'Internet Sales'[Internet Sales], DATESYTD( 'Date'[Date]))

The CALCULATE function is wrapped around the measure in order to evaluate the previous created measure Internet Sales. Without this, the DAX would not know to use Filter Context in order to calculate the Year to Date values. The DATESYTD DAX function has the Date column from the Date table to create the YTD evaluation. It does not use the Date Key (OrderDateKey) from the fact table because it is not a date data type.

CALCULATE函数包装在度量周围，以便评估先前创建的度量Internet Sales。 没有此功能，DAX将不知道要使用“过滤器上下文”来计算“年初至今”值。 DATESYTD DAX函数具有“日期”表中的“日期”列以创建YTD评估。 它不使用事实表中的日期键（OrderDateKey），因为它不是日期数据类型。

Also, notice the Format was changed to Currency. This is necessary so the results do not display as a number with no formatting. Figure 6 shows the Year to Date for each quarter of the selected year. Quarter 2 is the sum of Quarter 1 and 2 and so forth.

另外，请注意格式已更改为“货币”。 这是必要的，因此结果不会显示为没有格式的数字。 图6显示了所选年份每个季度的“年初至今”。 第二季度是第一季度和第二季度的总和，依此类推。

This type of measure can be created for Month to Date and Quarter to Date. The DAX functions are DATESMTD and DATESQTD. Figure 7 shows the 2 additional Time Intelligence measures

可以为月份到日期和季度到日期创建此类度量。 DAX函数是DATESMTD和DATESQTD。 图7显示了另外两个时间智能度量

There are many functions available in Time Intelligence like Previous Period for Month, Quarter and Year. Figure 9 shows the SAMEPERIODLASTYEAR. Here, the expression used is the YTD Internet Sales. The new measure, YTD Previous Year Sales, uses the DAX function SAMEPERIODLASTYEAR and the same Date column from Date dimension as YTD Internet Sales Amount.

时间情报中有许多功能可用，例如月份，季度和年份的上一个期间。 图9显示了SAMEPERIODLASTYEAR。 在这里，使用的表达式是YTD Internet Sales。 新度量标准YTD上一年销售额使用DAX函数SAMEPERIODLASTYEAR，并且Date维度中的Date列与YTD Internet Sales Amount相同。

This can be repeated for Quarter and Month to Date measures. Figure 10 shows these in the SQL Server Data Tools project.

对于“季度”和“月初”度量，可以重复此操作。 图10在SQL Server数据工具项目中显示了这些内容。

These measures compare the Quarter or Month to last year’s Quarter or Month. If the measure needs to be the previous Quarter in the sequence, the PREVIOUSQUARTER DAX function can be used. Likewise, the month DAX function is PREVIOUSMONTH.

这些度量将季度或月份与去年的季度或月份进行比较。 如果小节需要是序列中的前一个四分之一，则可以使用PREVIOUSQUARTER DAX函数。 同样，月份DAX函数为PREVIOUSMONTH。

Using the SAMEPERIODLASTYEAR for Year to Date measure, the Year Over Year (YOY) difference can be calculated and then percent change from last year to present year. The formula for the difference is:

使用SAMEPERIODLASTYEAR的“年初至今”度量，可以计算出“年度”（YOY）差异，然后计算从去年到今年的百分比变化。 差异的公式为：

YOY Internet Sales Difference:= [YTD Internet Sales Amount] - [YTD Previous Year Sales]

With this sub-measure, we can create a ratio of growth (or shrinking) sales year over year

借助此子指标，我们可以创建逐年增长（或收缩）的销售比例

YOY Ratio:= DIVIDE( [YOY Internet Sales Difference] , [YTD Internet Sales Amount])

The YOY Ratio uses the DIVIDE DAX function. This function helps to not return error when the denominator is Null or 0. This was added recently in Analysis Services.

YOY比率使用DIVIDE DAX功能。 当分母为Null或0时，此函数有助于不返回错误。这是最近在Analysis Services中添加的。

So, with a few configurations and some DAX functions, Time Intelligence becomes an easy and doable option with SSAS Tabular Models. Business users will be happy this is embedded in an OLAP database and does not need to be written into multiple reports. The consolidation of business rules is indispensable for analytical reporting.

因此，通过一些配置和一些DAX功能，时间智能成为SSAS表格模型的一个简单而可行的选择。 商业用户将很高兴将其嵌入到OLAP数据库中，而无需将其写入多个报告中。 对于分析报告来说，业务规则的合并是必不可少的。

看更多 (See more)

For SSAS cube documentation, consider ApexSQL Doc, a tool that offers the possibility of documenting both Multidimensional and Tabular databases in different output formats.

对于SSAS多维数据集文档，请考虑ApexSQL Doc ，该工具提供了以不同输出格式同时记录多维数据库和表格数据库的可能性。

参考资料 (References)

• Tabular Time Intelligence DAX Function List 表格时间智能DAX功能列表
• SAMEPERIODLASTYEAR DAX Function SAMEPERIODLASTYEAR DAX功能
• DAX Function DAX功能

翻译自: https://www.sqlshack.com/time-intelligence-in-analysis-services-ssas-tabular-models/

ssas表格模型 权限控制