获取MDX参数的行数 [英] GET COUNT OF NUMBER OF ROWS FROM MDX PARAMETRIZED

问题描述

我在OLEDB源中有以下MDX参数化。问题是几个用户名在对立方体执行MDX时没有返回任何行，我想要过滤掉那些返回空行的那些。

并且，我想存储从这个MDX返回的行数，并在包内的流中使用它。我试图使用数据流任务 - 具有RowCount转换的OLEDB源，但查询成功，而不按照我的优先级
约束向前移动到下一个组件。优先约束检查RowCount变量是否> 0并且如果为true，则它继续向下游。但这不会发生.Pls引导或建议另一种方法来计算返回的行数并存储在变量中以便在包中重用。

我上传DFT图：

和我的MDX变量：

  SELECT {[Measures]。[Total Revenue]} ON COLUMNS，TOPCOUNT（NONEMPTY（{（[DimDates]。[Date Year]。[Date Year] .ALLMEMBERS）}，[Measures] 。[总收入]），10）维度属性MEMBER_VALUE ON行（SELECT（{[EndCust]。[UserName]。& [" + @ [User :: Login] +"]
}） ON COLUMNS FROM（SELECT（{[Cust]。[UserName]。& [" + @ [User :: AT_Uname] +"]}）ON COLUMNS FROM（SELECT（[DimReportingDates]。[Date Year]。& ; [2015]：[DimReportingDates]。[Date Year]。& [2030]）ON COLUMNS FROM [Model]）））

我看到这是错误的验证错误需要新的元数据，而当传递具有空记录的用户时，意味着空度量。现在，

我有这个DFT，如图片和下面的查询中捕获rowcount，并且只有当我从ole db输出中取消选中维度输出列：

  SELECT {[Measures]。[Total Revenue]} ON COLUMNS，NONEMPTY {（[DimDates]。[Date Year] 。[日期年份] .ALLMEMBERS）}维度属性MEMBER_VALUE ON行（SELECT（{[EndCust]。[UserName]。& [" + @ [User :: Login] +"]}）ON COLUMNS
FROM（SELECT（{[Cust] 。[UserName]。& [" + @ [User :: AT_Uname] +"]}）ON COLUMNS FROM（SELECT（[DimReportingDates]。[Date Year]。& [2015]：[DimReportingDates]。[日期年份。& [2030]）来自[型号]的栏目）））

$
$

解决方案

嗨  msdnpulic1234，

我对你的问题不太清楚，你的意思是MDX上面会返回错误吗？在您的MDX中，您使用参数，我认为您需要给出参数默认值，然后它将获得相应的值。另外，你可以 尝试下面的表达式
以查看它是否有效

 SELECT NON EMPTY {[Measures]。[Amount  -  Factname]} ON COLUMNS，
 NON EMPTY {（[USERNAME]。[ID]。[ID] .ALLMEMBERS * [St]。[Name]。[Name] .ALLMEMBERS）} 
 ON ROWS 
 FROM（SELECT（StrToMember） （"[USERNAME]。[NAME]。& [" + @ username +"]"））ON COLUMNS FROM [CASE]）

我我不是SSIS的专业人士，我将与其他SSIS专业工程师讨论这个问题。我会尽快通知你。

最好的问候，

Zoe Zhi

Hi,

I have the following MDX parameterized in OLEDB source .The problem is with few usernames which return no rows upon executing the MDX against the cube,I want to filter out those which return null rows.

And,i want to store the count of rows returned from this MDX and use it in the flow within package.I tried to use Data Flow Task-OLEDB source with RowCount Transformation,but the query succeeds without moving forward to next component as per my precedence constraint.The precedence contraint checks if RowCount variable>0 and if true ,it proceeds downstream.But this is not happening.Pls guide or suggest another way to calculate count of rows returned and store in variable for reuse within package.

I upload the DFT diagram:

and my MDX in variable:

 SELECT { [Measures].[Total Revenue] } ON COLUMNS, TOPCOUNT(NONEMPTY( { ([DimDates].[Date Year].[Date Year].ALLMEMBERS ) },[Measures].[Total Revenue]),10) DIMENSION PROPERTIES MEMBER_VALUE ON ROWS FROM ( SELECT ( { [EndCust].[UserName].&["+@[User::Login]+"] } ) ON COLUMNS FROM ( SELECT ( { [Cust].[UserName].&["+@[User::AT_Uname]+"] } ) ON COLUMNS FROM ( SELECT ( [DimReportingDates].[Date Year].&[2015] : [DimReportingDates].[Date Year].&[2030] ) ON COLUMNS FROM [Model])))

I see that this is failing to validate with error Needs new metadata whereas when a user with null records is passed,meaning null measure.For now,

I have this DFT as in the picture and below query to catch rowcount and was possible only when i unchecked the dimension output column from the ole db outputs:

 SELECT { [Measures].[Total Revenue] } ON COLUMNS, NONEMPTY{ ([DimDates].[Date Year].[Date Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_VALUE ON ROWS FROM ( SELECT ( { [EndCust].[UserName].&["+@[User::Login]+"] } ) ON COLUMNS FROM ( SELECT ( { [Cust].[UserName].&["+@[User::AT_Uname]+"] } ) ON COLUMNS FROM ( SELECT ( [DimReportingDates].[Date Year].&[2015] : [DimReportingDates].[Date Year].&[2030] ) ON COLUMNS FROM [Model])))



解决方案

Hi  msdnpulic1234,

I am not very clear about your problem, did you mean that above MDX will return error? In your MDX, you use parameter, I think you need to give parameter default value , then it will get corresponding value. In addition, you could  try below expression to see whether it will work or not

  SELECT NON EMPTY { [Measures].[Amount - Factname] } ON COLUMNS, 
   NON EMPTY { ([USERNAME].[ID].[ID].ALLMEMBERS * [St].[Name].[Name].ALLMEMBERS ) } 
   ON ROWS 
   FROM ( SELECT ( StrToMember("[USERNAME].[NAME].&["+@username+"]")  ) ON COLUMNS FROM [CASE]) 

And I am not professional in SSIS, I will discuss this with other engineer who are professional in SSIS. I will inform you as soon as possible.

Best Regards,
Zoe Zhi

这篇关于获取MDX参数的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！