获取MDX参数的行数

问题描述

我在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]）来自[型号]的栏目）））

$
$

解决方案

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])))



解决方案

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