MDX 查询.如何解决“IN"问题在“哪里"部分 [英] MDX queries. How can to solve "IN" in "where" part
问题描述
我有 sql 查询，我需要在 olap 多维数据集中执行此查询.
select count(distinct mi.id) from [MTD_DEV].[dbo].[MenuItemAttributes] as m内连接 [dbo].[MenuItemOlds] 作为 mi在 mi.id = m.MenuItemId内连接 [dbo].[RestaurantlistItems] 作为 rl在 rl.RestaurantId = mi.RestaurantId其中 m.AttributeId = 31 and rl.RestaurantListId = 69 and mi.PeriodId = 99 and m.MenuItemId in (select MenuItemId from [MTD_DEV].[dbo].[MenuItemAttributes] where AttributeId = 6并且我有工作 mdx 查询，我需要添加运算符IN" 或此查询的其他解决方案
<块引用> SELECT CROSSJOIN({[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]},{[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) 在列上，{[Menu Item Olds].[id]} ON ROWS来自 [MTD 开发人员]在哪里 ({[餐厅列表].[Id].[69]}，{[属性].[Id].[6]} ,{[Attribute Categories].[Id].[5]} -- 或者可以使用相同的参数 {[Attributes].[Id].[31]})为了更好地理解:https://drive.google.com/file/d/0B3rw0YPItJIIa3FfNEtrVC04SVU/view?usp=共享
对问题的其他评论n
在 ms sql 中，我必须通过一些参数 m.AttributeId = 31 对 MenuItemOlds 进行切片然后从结果我必须再次切片参数 AttributeId = 6.在 Sql 中它看起来像这样:
select count(distinct mi.id) from [MTD_DEV].[dbo].[MenuItemAttributes] as m内连接 [dbo].[MenuItemOlds] 作为 mi 上的 mi.id = m.MenuItemId其中 m.AttributeId = 31 和 m.MenuItemId in (select MenuItemId from [MTD_DEV].[dbo].[MenuItemAttributes] where AttributeId = 6我在 OLAP Cube 中遇到问题.我如何看待解决这个问题:
1.我获取 AttributeId = 31 的所有数据
SELECT CROSSJOIN({[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]},{[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) 在列上，{[Menu Item Olds].[id]} ON ROWS来自 [MTD 开发人员]其中 ({[Attributes].[Id].[31]})结果 - 所有餐饮菜单项
- 在此之后，在这个菜单项集合中，我需要找到 {[Attributes].[Id].[6]}(儿童菜单)的所有菜单项
当我尝试执行此类查询时:
SELECT CROSSJOIN({[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]},{[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) 在列上，{[Menu Item Olds].[id]} ON ROWS来自 [MTD 开发人员]在哪里 ({[属性].[Id].[6]} ,{[属性].[Id].[31]})我得到结果，其中我有带有 AttributeId 的菜单项.[6]+ 带有属性 ID 的菜单项.[31]例如:
<小时>具有 AttributeId 的菜单项计数.[6]= 11000 件商品
<小时>具有 AttributeId 的菜单项计数.[31]= 724000 件
<小时>结果是 724000+11000 = 735000 但我不需要它
<小时>我需要找到所有具有 AttributeId.[31] 的项目，在这个集合中我需要找到具有 AttributeId.[6] 的项目查询的正确结果必须小于11000条
NonEmpty 和 intersect 是否可以作为替代方案?
SELECT{[措施].[菜单项旧计数],[措施].[餐厅列表项数]}*{[时期].[Id].[99],[期间].[编号].[93],[期间].[编号].[75]在列上，相交(非空([菜单项旧].[id].[id].MEMBERS,([属性].[Id].[31],{[措施].[菜单项旧计数],[措施].[餐厅列表项数]})),非空([菜单项旧].[id].[id].MEMBERS,([属性].[Id].[6],{[措施].[菜单项旧计数],[措施].[餐厅列表项数]}))) 行来自 [MTD DEV]；I have sql query and i need this query execute in olap cube .
select count(distinct mi.id) from [MTD_DEV].[dbo].[MenuItemAttributes] as m
inner join [dbo].[MenuItemOlds] as mi
on mi.id = m.MenuItemId
inner join [dbo].[RestaurantlistItems] as rl
on rl.RestaurantId = mi.RestaurantId
where m.AttributeId = 31 and rl.RestaurantListId = 69 and mi.PeriodId = 99 and m.MenuItemId in (select MenuItemId from [MTD_DEV].[dbo].[MenuItemAttributes] where AttributeId = 6
and i have working mdx query and I need to add operator 'IN' or something another solution for this query
SELECT CROSSJOIN( {[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]}, {[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) ON COLUMNS, {[Menu Item Olds].[id]} ON ROWS FROM [MTD DEV] where ( {[Restaurant Lists].[Id].[69]}, {[Attributes].[Id].[6]} , {[Attribute Categories].[Id].[5]} -- or can use the same parameter {[Attributes].[Id].[31]} )for better understanding: https://drive.google.com/file/d/0B3rw0YPItJIIa3FfNEtrVC04SVU/view?usp=sharing
Additional Comments to question
In ms sql I have to slice MenuItemOlds by some parameter m.AttributeId = 31 annd then from result I have to slice again for parameter AttributeId = 6. In Sql it looks like this:
select count(distinct mi.id) from [MTD_DEV].[dbo].[MenuItemAttributes] as m
inner join [dbo].[MenuItemOlds] as mi on mi.id = m.MenuItemId
where m.AttributeId = 31 and m.MenuItemId in (select MenuItemId from [MTD_DEV].[dbo].[MenuItemAttributes] where AttributeId = 6
I have problem in OLAP Cube. How I see to solve this problem :
1.I get all data where AttributeId = 31
SELECT CROSSJOIN(
{[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]},
{[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) ON COLUMNS,
{[Menu Item Olds].[id]} ON ROWS
FROM [MTD DEV]
where ({[Attributes].[Id].[31]})
the result of this - all catering menu items
- After this , in this collection of Menu Items, I need to find all menu items where {[Attributes].[Id].[6]} (kids menu)
When i am trying to execute such query :
SELECT CROSSJOIN(
{[Measures].[Menu Item Olds Count],[Measures].[Restaurantlist Items Count]},
{[Periods].[Id].[99],[Periods].[Id].[93],[Periods].[Id].[75]}) ON COLUMNS,
{[Menu Item Olds].[id]} ON ROWS
FROM [MTD DEV]
where (
{[Attributes].[Id].[6]} ,
{[Attributes].[Id].[31]}
)
I get result , in which i have Menu items with AttributeId.[6] + menuItem with attributeId.[31] for example:
count of menu items with AttributeId.[6] = 11000 items
count of menu items with AttributeId.[31] = 724000items
and result is 724000+11000 = 735000 but i don`t need it
i need to find all items with AttributeId.[31], and in this collection i need to find items with AttributeId.[6] The right result of query must be less than 11000 items
Does NonEmpty with intersect work as an alternative?
SELECT
{
[Measures].[Menu Item Olds Count]
,[Measures].[Restaurantlist Items Count]
}
*
{
[Periods].[Id].[99]
,[Periods].[Id].[93]
,[Periods].[Id].[75]
} ON COLUMNS
,Intersect
(
NonEmpty
(
[Menu Item Olds].[id].[id].MEMBERS
,(
[Attributes].[Id].[31]
,{
[Measures].[Menu Item Olds Count]
,[Measures].[Restaurantlist Items Count]
}
)
)
,NonEmpty
(
[Menu Item Olds].[id].[id].MEMBERS
,(
[Attributes].[Id].[6]
,{
[Measures].[Menu Item Olds Count]
,[Measures].[Restaurantlist Items Count]
}
)
)
) ON ROWS
FROM [MTD DEV];
这篇关于MDX 查询.如何解决“IN"问题在“哪里"部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
