在MDX中可以执行哪些转换? [英] What transformations can be performed in MDX?

问题描述

我是MDX的新手.我知道MDX是一种查询语言，而不是数据转换语言.但是，我也知道，这种区别在某种程度上是没有意义的.在转换和报告之间没有明确的界线，每种查询语言都可以进行某种转换.要熟练使用查询语言，需要知道哪些转换是合理的，哪些转换需要对基础架构进行重新设计.

I'm new to MDX. I understand that MDX is a query language, not a data transformation language. However, I'm also aware that this distinction is partially meaningless; there is no clear line between transformation and reporting, and every query language is capable of some transformation. Proficiency in a query language requires knowing what transformations are reasonable, and which require a redesign of the underlying schema.

从我对MDX的了解来看，它显然具有旨在在维内创建计算成员的功能.除此之外，我还不清楚它的转换功能.谁能提供MDX可以合理预期进行的哪种类型的转换的简要摘要?

From what I've seen of MDX, it clearly has features designed for creating calculated members within a dimension. Beyond that, however, I'm not clear on its transformation capabilities. Can anyone provide a concise summary of which types of transformations MDX can reasonably be expected to do?

我不希望这个问题仅限于我的特定报告挑战.但是，通过描述我的项目，我可以说明一些我感兴趣的转换类型.因此，这是我正在从事的工作的描述:

I don't intend for this question to be limited to my particular reporting challenge. However, by describing my project, I can illustrate a few of the transformation types I'm interested in. So, here's a description of what I'm working on:

我需要使用MDX创建一些库存和销售报告.我正在使用Microsoft SQL Server 2008 Analysis Services.数据被组织为三个不同的多维数据集:现有库存，在途库存和销售.我的报告要求以多种方式转换数据.例如:

I need to use MDX to create some inventory and sales reports. I'm working with Microsoft SQL Server 2008 Analysis Services. The data is organized into three different cubes: On-Hand Inventory, In-Transit Inventory, and Sales. My reports require that the data be transformed in several ways. For instance:

1)我需要使用4-4-5日历的规则从周"属性推断出月"属性.我相当确定这可以通过MDX轻松完成.

1) I need to infer a "Months" attribute from the "Weeks" attribute, using the rules of a 4-4-5 calendar. I'm fairly certain this can be done elegantly with MDX.

2)我需要从月份"属性中推断出日历月份"维度.我相信可以使用MDX做到这一点，但是我不确定是否应该使用优雅的解决方案或繁琐的方法，而应避免进行模式重新设计.

2) I need to infer a "Calendar Month" dimension from the "Months" attribute. I believe this can be done with MDX, but I'm not sure whether there is an elegant solution or a kludge which should be avoided in favor of a schema redesign.

3)我需要从仓库"维度推断出区域"维度.我没有看到任何证据表明MDX可以用一种优雅的方式来做到这一点.

3) I need to infer a "Region" dimension from the "Warehouse" dimension. I've seen no evidence that this can be done in an elegant way by MDX.

4)我需要将总库存计算为现有库存加上在途库存.从网上搜索来看，似乎可以查询两个不同的多维数据集，但不建议进行架构重新设计，但工作仍然很混乱.

4) I need to calculate total inventory as On-Hand Inventory plus In-Transit Inventory. From searching the web, it seems that querying two different cubes is possible, but is discouraged in favor of schema redesign, but the water is still very muddy.

推荐答案

我想说您的大多数需求都可以通过Analysis Services来完成，但不一定必须通过MDX来完成.相反，它们将在多维数据集设计中完成.通常使用GUI(称为BIDS(商业智能开发工作室)的Visual Studio)完成此操作.如果您绝对希望使用某种语言，则可以使用XMLA，这就是BIDS与Analysis Services服务器通信的方式.但这仍然不是MDX，并且没有很好的文档记录，因此很难学习.您可以使用.net和AMO，但最简单的方法是BIDS中的GUI.

I would say most of your requirements can be done with Analysis Services, but not necessarily with MDX. Rather, they would be done in cube design. This is normally done using the GUI, which is Visual Studio called BIDS (Business Intelligence Development Studio). If you absolutely want to use a language, you could use XMLA, which is how BIDS communicates with the Analysis Services server. But this would still not be MDX, and is not very well documented, and hence difficult to learn. You could use .net and AMO, but the easiest way is the GUI in BIDS.

您的某些要求将在多维数据集所基于的关系表的设计中得到最佳实现.需求的前三个最好在维表中实现，然后仅在多维数据集定义中的维对象中使用.对于第四个要求，您是正确的，可以在多维数据集计算脚本中的计算量度中轻松实现这一点.的确是MDX.

And some of your requirements would optimally be implemented in the design of the relational tables on which the cubes are based. The first three of your requirements are best implemented in the dimension tables, and then just used in the dimension objects in the cube definition. For the fourth requirement, you are right, this can easily be implemented in a calculated measure in the cube calculation script. And this, indeed, is MDX.

理论上，您还可以在MDX中以某种方式实现前三个要求.但这将是复杂的，难以维护的并且具有不良的性能. MDX并非仅针对这种类型的需求而设计.

In theory, you could also implement the first three requirements somehow in MDX. But this would be complex, difficult to maintain and have bad performance. MDX is just not designed for tis type of requirement.

这篇关于在MDX中可以执行哪些转换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！