代码之家 › 专栏 › 技术社区 › Sibeesh Venu

使用MDX查询在多维数据集中查找度量值的数据类型

adomd.net mdx ssas c#

Sibeesh Venu · 技术社区 · 9 年前

我正在处理SSAS MDX查询。现在,我需要从MDX查询中识别每个列的数据类型。我已经试过了。

WITH MEMBER PageSize AS 1  Member [Measures].[My Type] as TypeName([Measures].[Budget Delivered COGS])

但它返回了货币字段的两倍。首先,立方体的数据类型是否为货币、百分比?

如果不是,是否有任何方法可以确定该列是货币、百分比、双精度还是整数?

我看过这个帖子 Find the data type 。但从货币和百分比来看,这是不可行的。

我在这里粘贴整个查询。

 WITH MEMBER PageSize AS
  1
MEMBER [Measures].[My Type] AS
  TypeName ( [Measures].[Budget Delivered COGS] )
MEMBER [PageNumber] AS
  1
MEMBER [Measures].[Orderby Measure] AS
  [Measures].[Budget Delivered COGS]
MEMBER [Orderby] AS
  "BASC"
SET ROWAXISWOF AS
  NonEmpty (
    ( [Time Periods].[Fiscal Year].[Fiscal Year].Members, [Time Periods].[Fiscal Quarter].[Fiscal Quarter].Members, [Time Periods].[Fiscal Month].[Fiscal Month].Members, [Time Periods].[Fiscal Week].[Fiscal Week].Members ),
    {
      { [Measures].[Budget Delivered COGS] },
      { [Measures].[Break Even Delivered] }
    }
  )
SET ROWAXISWF AS
  Filter ( ROWAXISWOF, [Measures].[Budget Delivered COGS] )
SET ROWAXIS AS
  IIf ( 'Filter' = "NotFilter", ROWAXISWF, ROWAXISWOF )
MEMBER [Measures].[MaxRowCount] AS
  ROWAXIS .Count
SET ROWAXIS_Count AS
  IIf (
    'Paging' = "Paging",
    (
    CASE
    WHEN [Orderby] = 'BASC'
    THEN Tail (
      TopCount ( ROWAXIS, PageSize * PageNumber, [Measures].[Orderby Measure] ),
      PageSize
    )
    ELSE Tail (
      BottomCount ( ROWAXIS, PageSize * PageNumber, [Measures].[Orderby Measure] ),
      PageSize
    ) END ),
    ROWAXISWOF
  )
SELECT ( IIf (
  'Paging' = "Paging",
  (
  { [Measures].[Budget Delivered COGS], [Measures].[Break Even Delivered], [Measures].[MaxRowCount], [Measures].[My Type] } ),
  { [Measures].[Budget Delivered COGS], [Measures].[Break Even Delivered], [Measures].[My Type] }
) ) ON COLUMNS,
ROWAXIS_Count ON ROWS
FROM (
  SELECT (
  { [Time Periods].[Fiscal Year].&[2011], [Time Periods].[Fiscal Year].&[2012], [Time Periods].[Fiscal Year].&[2013], [Time Periods].[Fiscal Year].&[2014], [Time Periods].[Fiscal Year].&[2015] } ) ON COLUMNS
  FROM [Homestore Sales]
)

如果我们在查询中得到了数据,我将循环该列并在C#中找到。任何帮助都非常感谢。提前谢谢。

1 回复 | 直到 7 年前

GregGalloway 9 年前

如果您谈论的是物理度量,那么您可以在SSAS项目中查看SSDT-BI以查看度量类型。或者,您可以使用以下可以安装的SSAS存储过程批量导出它 here :

CALL ASSP.discoverXmlMetaData("\Database\Cubes\Cube\MeasureGroups\MeasureGroup|Name\Measures\Measure");

计算出来的指标更为棘手。由于计算中的IIF语句和范围语句,CalculatedMeasure1可能在一个单元格中为Double类型,在另一个单元格为String类型。您可以运行一个查询并返回一个CellSet,然后查看每个单元格的类型,我认为它应该告诉您类型。但我不确定,即使在相同的计算度量中,给定的类型也会因单元格而异,这对你有什么好处。

可能要检索计算度量值的FORMAT_STRING属性?这可能预示着会发生什么。在生成的CellSet中,您可以从每个单元格中检索FORMAT_STRING属性。

select {[Measures].[Budget Delivered COGS], [Measures].[Break Even Delivered]} on 0
FROM [Homestore Sales]
PROPERTIES VALUE, FORMAT_STRING

最后一种方法是,如果要假设每个度量值对多维数据集中的所有单元格都具有相同的格式字符串,则查询 MDSCHEMA_MEASURES DMV。它有一个DEFAULT_FORMAT_STRING列。您可以使用针对SSAS的SQL查询调用它,如:

select * from $system.mdschema_measures

或者你可以通过ADOMD调用它。NET,代码如下:

AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add(new AdomdRestriction("CATALOG_NAME", cube.ParentConnection.Database));
restrictions.Add(new AdomdRestriction("CUBE_NAME", cube.Name));
System.Data.DataTable tblExactMatchMembers = conn.GetSchemaDataSet("MDSCHEMA_MEASURES", restrictions).Tables[0];

如果这不能回答你的问题,你可能会提供更多的背景,你想完成什么。