ssas计算度量_如何在Analysis Services（SSAS）中创建中间度量
ssas计算度量
The whole premise of Analysis Services (SSAS) is to place business logic into a central repository (a database). This central repository should be easy to understand from the average reporting user. Simplicity for SSAS databases starts with the Dimension and Fact tables in a data mart or data warehouse. These tables need to have as little additional computed columns as necessary. With this design, adding additional Measures to the SSAS database, both Multidimensional Cubes, and Tabular Models, can help with performance monitoring of the company’s indicators.
Analysis Services（SSAS）的整个前提是将业务逻辑放入*存储库（数据库）中。 普通的报告用户应该易于理解该*存储库。 SSAS数据库的简单性始于数据集市或数据仓库中的Dimension和Fact表。 这些表需要具有尽可能少的附加计算列。 通过这种设计，在SSAS数据库中添加多维多维多维数据集和表格模型的其他度量，可以帮助监控公司指标的性能。
The fact tables should be full of keys to the dimensions and measurable columns to use in an Analytical database type structure. If this is done, SSAS can use its own language to calculate ratios, percentages, targets, and indicators. The MDX language is used for cubes and DAX language is used in tabular models. These measures will assist the reporting of performance measures. Reporting tools like Excel Pivot Tables and Power BI can benefit from these databases while using visualization objects.
事实表应包含用于分析数据库类型结构的维和可测量列的键。 如果这样做，SSAS可以使用其自己的语言来计算比率，百分比，目标和指标。 MDX语言用于多维数据集，而DAX语言用于表格模型。 这些措施将有助于绩效报告的报告。 使用可视化对象时，诸如Excel Pivot Tables和Power BI之类的报告工具可以从这些数据库中受益。
This article will look at a calculation and how SSAS can help centralize the logic in cubes and tabular models. The ratio is called Gross Profit Margin. Figure 2 shows the simple counts and sum for aggregate measures in a Multidimensional Cube. These were created using the cube wizard and will be used in the Gross profit Margin calculation.
本文将研究计算以及SSAS如何帮助将逻辑集中在多维数据集和表格模型中。 该比率称为毛利润率。 图2显示了多维多维数据集中汇总度量的简单计数和总和。 这些是使用多维数据集向导创建的，并将用于毛利润率计算中。
多维立方体 (Multidimensional cube)
The Gross Profit Margin (GPM) calculation is the Sum of Line Item Sales minus the Sum of Line Item Costs divided by the Sum of Line Item Sales or GPM = ( Sum( Sales ) – Sum( Cost) ) / Sum( Sales). This ratio can be easily created in SSAS and formatted as a percent. The Cube measure will be created in the Calculation tab. Figure 3 shows the Calculation tab once the Cube is opened for editing.
毛利润率（GPM）的计算方式是：订单项销售额总和减去订单项成本总和除以订单项总销售额或GPM =（总和（销售）–总和（成本））/总和（销售）。 可以在SSAS中轻松创建此比率并将其格式化为百分比。 多维数据集度量将在“计算”选项卡中创建。 打开多维数据集进行编辑后，图3显示了Calculation选项卡。
A new Calculation can be added by right-clicking the CALCULATE List under the Command on the upper-left part of the screen shown in Figure 4. There are additional options for New Named Set and New Script Commands in addition to the New Calculated Member option.
可以通过右键单击屏幕左上方的Command下的CALCULATE列表来添加新的Calculation，如图4所示。除了New Calculated Member选项之外，New Named Set和New Script Commands还有其他选项。 。
Once New Calculated Member menu is chosen, the editor is enabled for the Measure. We will change the Name to Internet GPM and use this as the expression:
选择“新计算所得成员”菜单后，将启用“度量”编辑器。 我们将名称更改为Internet GPM并将其用作表达式：
( [Measures].[Internet Sales Amount] – [Measures].[Internet Product Cost] )
/ [Measures].[Internet Sales Amount]
（[措施]。[互联网销售金额]-[措施]。[互联网产品成本]）
/ [措施]。[互联网销售金额]
The Format String should be “Percent” and the Non-empty behavior needs to have [Internet Sale Amount] selected. This prevents divide by zero errors. The calculation result will be Null rather than an error in Non-empty behavior is used.
格式字符串应为“百分比”，并且非空行为需要选择[Internet Sale Amount]。 这防止了零误差除法。 计算结果将为Null，而不是使用非空行为时出现错误。
Now, deploy the cube and Analyze in Excel. Analyze in Excel is an option in the browser tab of the cube. Multidimensional Cubes have to be deployed before viewing changes. The Tabular has an in-memory copy that can be viewed after the change.
现在，部署多维数据集并在Excel中进行分析。 在Excel中进行分析是多维数据集的浏览器选项卡中的一个选项。 在查看更改之前，必须先部署多维多维数据集。 该表格具有一个内存中的副本，可以在更改后查看。
The ratio or percentage is really valuable to end users that display the measure this one Analysis Service Database. No need to duplicate the calculation in multiple reports and risk having different values. If you do see different values, look at what the end user is using as filters.
该比率或百分比对于显示此Analysis Services数据库度量的最终用户而言确实有价值。 无需在多个报告中重复计算，而不必承担具有不同值的风险。 如果您看到不同的值，请查看最终用户用作过滤器的内容。
表格模型 (Tabular model)
To create the same measure in a Tabular SSAS database, we need to select the table tab to contain the measure. Usually, this is the same table as the columns needed for the calculation but does not have to be. In this example, that is the Internet Sales tab.
要在表格SSAS数据库中创建相同的度量，我们需要选择表选项卡以包含度量。 通常，此表与计算所需的列在同一表中，但不必如此。 在此示例中，这是“ Internet销售”选项卡。
The bottom portion of the table is where Measures are created. The Internet Sales and Internet Product Cost are shown in Figure 7. The calculation will be like the Multidimensional Cube already shown above but with the DAX language syntax and not MDX.
表格的底部是创建度量的位置。 Internet销售和Internet产品成本如图7所示。计算将类似于上面已经显示的多维多维数据集，但是使用DAX语言语法而不是MDX。
Internet GPM := CALCULATE( ( [Internet Sales] – [Internet Product Cost]) / [Internet Sales])
互联网GPM：=计算（（[互联网销售] – [互联网产品成本] / [互联网销售]）
The CALCULATE is needed to use the existing measures for Sale and Cost. The Measures are computed based on Filter Context. Row Context is used for row-level computed columns. The measure also needs to be formatted as Percentage in the Properties of the measure.
需要使用“计算”来使用“销售和成本”的现有度量。 度量是基于“筛选器上下文”计算的。 行上下文用于行级计算列。 度量还需要在度量的属性中格式化为百分比。
Once the results are Analyzed in Excel, the measure displayed will be the same as a cube.
在Excel中分析结果后，显示的度量将与多维数据集相同。
Once we have a ratio like Gross Profit Margin in an SSAS database, there is another powerful function available. The KPI or Key Performance Indicator allows for a target to be compared with the value along with an upper and lower limit. The KPI has a status indicator for something like a red light. Green is good, yellow is average and red is below average. Figure 11 shows the setup for a Tabular Model SSAS database.
一旦我们在SSAS数据库中获得了毛利润率之类的比率，便可以使用另一个强大的功能。 KPI或关键绩效指标允许将目标与值以及上限和下限进行比较。 KPI具有状态指示灯，用于指示红灯。 绿色好，黄色是平均水平，红色是低于平均水平。 图11显示了表格模型SSAS数据库的设置。
The fixed or absolute value is .41. So, the Internet GPM on average should be 41%. The lower is 39.5% and the upper is 42.5%. These numbers are used in order to preview data that is above and below the thresholds and do not represent actual uppers and lowers. The icon style provides various visualizations of the status. This KPI has a diamond for red, triangle for yellow and circle for green. Figure 12 now shows the KPIs with the previous Analyze in Excel results.
固定或绝对值为.41。 因此，互联网的GPM平均应为41％。 较低的是39.5％，较高的是42.5％。 这些数字用于预览高于和低于阈值的数据，并不代表实际的上下值。 图标样式提供状态的各种可视化。 此KPI的钻石为红色，三角形为黄色，圆形为绿色。 现在，图12显示了具有先前在Excel结果中进行分析的KPI。
Notice in Figure 9, the KPI can be compared to a value in a Measure. This allows a company to place the Target values in a database table. The table can be added to the model in order for values to be used in a more flexible means. The data can be based on Category and Year. This eliminates the need for an absolute value for all comparisons.
注意，在图9中，可以将KPI与Measure中的值进行比较。 这使公司可以将“目标”值放置在数据库表中。 可以将表添加到模型中，以便以更灵活的方式使用值。 数据可以基于类别和年份。 这消除了所有比较都需要绝对值的需求。
参考资料 (References)
翻译自: https://www.sqlshack.com/create-intermediate-measures-analysis-services-ssas/
ssas计算度量