15个很具代表性的MDX查询语句
读者请求最多的是更多的MDX信息。他们通常要求更多的MDX例子,在这里,我提供15个典型的MDX语句,用的是SQL Server 2000 Analysis Services' FoodMart 2000 sample cubes,以下例子都以Sales多维数据集为例。
1. 在美国所有州都销售的有那些产品品牌?
Listing 1 创建了个自定义集合SoldInUsa,该集合排除了在整个美国Unit Sales为空值的品牌。该查询定义了一个计算成员,描述Current State是否销售Current Product;如果有销售返回Yes,否则返回No。这个查询在columns显示States,在Rows显示Produts,单元值区域依据product-state的聚合交集显示Yes或No.
或许你已经发现,该查询会返回的结果同个品牌会返回多次。Sales Cube是依据brand来给Products分类的(把产品类别分成不同的品牌),所以如果某品牌生产多种产品,该品牌在层次结构中将多次出现。乍一看,这种重复出现确实是个问题,大概你会把多次出现的同个品牌当成不同的品牌。举个例子,一个公司(对应一个品牌)生产luggage和clothes,作为本例分析,最好将其当成不同品牌来理解,这样才不至于因为其不销售luggage就判断该品牌没有销售(实际情况其可能销售clothes)而导致错误。
LISTING 1: Determining Products Sold in Each State
说明: 查询在所有州都销售的品牌。
with set [SoldInUSA] as 'Filter([Product].[Brand Name].Members, Not IsEmpty( ([USA], [Unit Sales]) ))'
member [Measures].[SoldInState] as 'iif( IsEmpty(([Product].CurrentMember, [Unit Sales],
[Customers].CurrentMember)), "No","Yes" )'
select [USA].children on COLUMNS,
[SoldInUSA] on ROWS
from Sales
where ([SoldInState])
注 :
Filter | 返回根据搜索条件对集合进行筛选所得到的集合。 |
例子 | SELECT {[Measures].[Store Sales] } ON COLUMNS, { filter(time.allmembers,[Measures].[Store Sales]>50000) } ON rows from sales |
iif | 返回由逻辑测试确定的两个数值或字符串值之一。 |
例子 | with member measures.abc as 'iif(isempty(measures.[unit sales]),"空了","不空")' SELECT { { { [Time].&[1997] } * { [Measures].[Unit Sales],measures.abc } } } ON COLUMNS , { DESCENDANTS( [Store].[All Stores], [Store].[Store Name] ) } ON ROWS FROM [Sales] |
2.所有商店中销售前10名的产品类别有哪些?
Listing 2 直接利用TopCount()函数查询销售前10名的产品。
(这是一种最直接的方法,TopCount()函数本身自带排序(降序)的功能)
LISTING 2: Determining Top 10 Product Categories
说明:查询销售前10名的产品类别
select {[Unit Sales]} on COLUMNS,
TopCount( [Product].[Product Category].Members, 10, ([Unit Sales]) ) on ROWS
from Sales
查询结果展示:
3. 在美国,刚刚过去的三个季度里都有销售量的食品和饮料有哪些?
(这个查询也可以理解为“过去三个季度里食品和饮料销售量都不为0的产品有哪些?”)。查询Listing 3 示范了如何动态的确定对应的时间集合—这是一项很有价值的技巧。时间集合动态随着cube数据的更新而改变(也就是说该查询无论你在什么时候执行,无论cube的数据作了多少次更新,结果都是最近三个月的数据)。首先,自定义集合LastQuarter定义了时间维度中有销售记录的最近一个季度。自定义集合Last3Quarters在LastQuarter的基础上利用Range()函数(实际没有range()函数,冒号:就是作者说的函数)指定了以LastQuarter为最后一个季度的连续的三个季度。我不直接在Last3Quarter(原文是LastQuarter,我认为是作者笔误)的定义中使用Tail()函数是因为这样做返回的可能不是连续的三个季度;因为空记录的季度肯能出现在任何季度,Filter()函数只能排除掉空季度。Lag()函数结合range()函数,确保了返回的三个季度是连续的。
在这个查询中,item(0).item(0) 函数取得指定集合的第一个成员,因为集合在技术上就是一组元组[如:来自不同维度的一系列成员也可以组成一个元组],所以用第一个Item()函数选择元组,第二个Item()选择该元组里的成员。(我们可以这样理解,集合由元组组成,元组由成员组成)。
Listing_03.Determining Brands Sold During the Past Three Quarters.txt
说明:在过去三个季度里都存在销售量的商品销售记录
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not
IsEmpty([Time].CurrentMember)),1)'
set [Last3Quarters] as ' [LastQuarter].item(0).item(0).Lag(2) : [LastQuarter].item(0).item(0)'
select [Last3Quarters] on COLUMNS,
Non Empty Union(Descendants( [Food], [Product].[Brand Name] ), Descendants( [Drink],
[Product].[Brand Name] )) on ROWS
from Sales
查询结果展示:
注:
TopCount | 从集合顶端开始返回指定数目的项,可以选择首先对集合排序。 |
例子 | SELECT {[Measures].[Store Sales] } ON COLUMNS, Topcount(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 10, [store sales]) ON rows from sales |
Subset | 从集合中返回元素的子集。 |
例子 | SELECT {measures.[store sales] } ON COLUMNS, {Subset(Time.allMembers,0,6)} ON rows from sales |
Tail | 从集合尾部返回子集。 |
例子 | SELECT {measures.[store sales] } ON COLUMNS, {tail(Subset(Time.allMembers,0,6),4)} ON rows from sales |
Lag | 返回指定成员的所在维度上的上一个成员。 |
例子 | with member [measures].[a1] as 'time.currentmember.lag(1).name' SELECT {[measures].[a1] } ON COLUMNS , { [Time].allmembers } ON ROWS FROM [Sales] |
Filter | 返回根据搜索条件对集合进行筛选所得到的集合。 |
例子 | SELECT {[Measures].[Store Sales] } ON COLUMNS, { filter(time.allmembers,[Measures].[Store Sales]>50000) } ON rows from sales |
Item | 从集合中返回指定元组或者从元组中返回指定成员。 |
例子 | with set kkk as '{{[Time].[1997], [Time].[1998]}*{[Store].[All Stores].[Canada],[Store].[All Stores].[USA]} }' member measures.jjj as 'TupleToStr(kkk.item(0).item(0))',solve_order=1 select { measures.[store sales],measures.jjj } on columns, {kkk} on rows from Sales |
Union | 返回两个集合的并集,可以选择保留重复项。 |
例子 | SELECT {[Measures].[Store Sales] } ON COLUMNS, Union(USA.Children, CANADA.Children, ALL) ON rows from sales |
|
|
4.最近销售趋势最好的产品有哪些?
查询Listing 4首先利用TopCount()查得销售最好的产品,然后利用上个查询Listing 3 介绍过的动态时间技巧定义最近6个月的销售量。该查询安排在行显示销售最好的10种产品,列显示最近的6个月,值区域为这6个月的Unit Sales。你可以用线状图展示该查询以便监视产品的销售绩效。
(注:这是比较常用的报表查询,特别是在KPI展示中。用TSQL也可以实现该报表的展示,但是将没有用MDX来得简洁,方便;因为AS在仓库建模的时候已经在后台做了多层预先的处理。)
Listing_04.Determining Recent Trends for Best-Selling Brands.txt
说明:查出最近6个月销售趋势最好的前10个商品及其各自销售量
with set [TenBest] as 'TopCount( [Product].[Brand Name].Members, 10, [Unit Sales] )'
set [LastMonth] as 'Tail(Filter([Time].[Month].Members, Not IsEmpty([Time].CurrentMember)),1)'
set [Last6Months] as ' [LastMonth].item(0).item(0).Lag(6) : [LastMonth].item(0).item(0)'
select [Last6Months] on COLUMNS,
[TenBest] on ROWS
from Sales
查询效果展示:
5, 哪些产品品牌构成公司(指超市)的前80%的销售量?
TopPercent()函数与TopCount()函数类似,只是TopPercent()返回的是最少项,如本例返回组成unit sales 80%的最少项(换句话说,这些项是unit sales数值大的项)。Listing 5 在行显示产品品牌,列及对应区域显示Total unit sales,从高到低排列。
Listing_05.Determining Brands that Make Up 80 Percent of Sales.txt
说明:找出组成销售额80%的商品销售及其记录;
select {[Unit Sales]} on COLUMNS,
TopPercent([Product].[Brand Name].Members, 80, [Unit Sales]) on ROWS
from Sales
查询效果展示:
注:
TopCount | 从集合顶端开始返回指定数目的项,可以选择首先对集合排序。 |
例子 | SELECT {[Measures].[Store Sales] } ON COLUMNS, Topcount(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 10, [store sales]) ON rows from sales |
TopPercent | 对集合排序,并返回顶端的 n 个元素,这些元素的累积合计至少为指定的百分比。 |
例子 | SELECT {[Measures].[Store Sales] } ON COLUMNS, TopPercent(Descendants([Store].[All Stores].[USA],[Store].[Store City] ), 90, [store sales]) ON rows from sales |
6. 那些产品构成销量总量最少的20%?
查询Listing 6 用BottomPercent()返回仅构成Total unit sales 20%的最多的产品项,也就是说,这些产品的unit sales 最小。查询用关键字Non Empty排除了没有销售量的产品。需要注意的是,Non Empty跟Filter()与IsEmpty()的组合使用(见Listing 3)略有不同,因为Non Empty最用在某个轴(如rows)的所有项上。
Listing_06.Determining Brands That Make Up the Bottom 20 Percent of Sales.txt
说明:按销售量排序,找出组成20%销售量的商品销售记录
select {[Unit Sales]} on COLUMNS,
Non Empty BottomPercent([Product].[Brand Name].Members, 20, [Unit Sales]) on ROWS
from Sales
查询结果表展示:
查询结果图展示:
7. 销量最好的五个商店是哪五个?这五个商店中消费最高的五位顾客?
查询Listing 7 示范了很实用也比较复杂的Generate()函数。如果你有过开发经验,你会发现Generate()类似VB或则C#中的For each 语句。下面对Generate()做具体的说明:
如:Generate( {Miami, Atlanta}, Customers.CurrentMember.Parent) Generate()对第二个参数Customers.CurrentMember.Parent进行计算,计算第一个参数{Miami, Atlanta}中的所有项。在本例,第二个参数的mdx表达式返回当前项的父成员,所以最终结果是{Florida, Georgia}---第一个参数中每一个项的父成员的集合。
(注:我们可以这样理解,第一参数是要计算的范围,第二个参数是要计算的对象)
本查询同时使用Generate() 函数嵌套了递归。确定了五个销售最佳的商店,每个商店的消费最高的五个顾客后,Generate()合并了顾客集合从而创建了一份由25项store-customer组成的列表。
Listing_07.Determining the Top Five Stores and the Top Five Customers.txt
说明:查出销售量最好的前5名店和每个店的前5个顾客 及其销售记录
select {[Unit Sales]} on COLUMNS,
Generate( TopCount([Store].[Store Name].Members, 5, [Unit Sales]),
{ [Store].CurrentMember } * TopCount( [Customers].[Name].Members, 5, ([Unit Sales],
[Store].CurrentMember) ) ) on ROWS
from Sales
查询结果表展示:
查询结果图展示:
注:
BottomPercent | 对集合排序,并返回底端的 n 个元素,这些元素的累积合计至少为指定的百分比。 |
例子 | select {[Unit Sales]} on COLUMNS, Non Empty BottomPercent([Product].[Brand Name].Members, 10, [Unit Sales]) on ROWS from Sales |
Generate | 将集合应用到另一集合的每个成员,然后用 union 运算合并所得的集合。 |
例子1 | SELECT { [Measures].[Store Sales] } ON COLUMNS, { Generate({ USA, Canada }, Descendants(store.CurrentMember, [store state])) } ON rows from sales |
例子2: ca,wa是USA的,加all则简单复制 | SELECT {[Measures].[Store Sales] } ON COLUMNS, { Generate({USA, Canada}, {ca, wa} ,all)} ON rows from sales 如果通过 CurrentMember,«Set1» 与 «set_expression» 无关,那么 Generate 生成 «set_expression» 所指的集合的简单复制,它包含的复制与 «Set1» 中的元组一样多。如果指定了可选的 ALL 标志,结果中将保留所有重复项。如果未指定 ALL,重复项将被删除。 |
例子3: 合并字符串 | with member [Measures].[合字符串] as 'Generate({Time.allmembers}, Time.CurrentMember.name," and ")' SELECT { [Measures].[合字符串] } ON COLUMNS, {[Store].[All Stores]} ON rows from sales |
例子4: 应用扩展 | with member [Measures].[合字符串] as 'Generate({Time.[1997].children}, cstr((Time.CurrentMember, [Measures].[Unit Sales],store.[all stores]))," and ")' SELECT { [Measures].[合字符串] } ON COLUMNS, { [Store].[All Stores] } ON rows from sales |
8.查出每一种品牌销售最好的两种产品的销量额,以及分别占销售总额的百分比。Listing8相对比较复杂,综合运用了计算成员和Generate()函数。计算成员确定了每个品牌销售最好的前两个产品占所在品牌unit sales总量百分比。Generate函数查询每个品牌并返回每个品牌下销售最好的两个产品,以及每个产品的销售额和百分比。
Listing_08.Determining Two Top-Selling Products.txt
说明:查出每种品牌 前2名 产品的销售记录,以及各自分别占所在品牌的百分比
with member [Measures].[PercTotalSales] as
' Sum( TopCount([Product].CurrentMember.Children, 2, [Unit Sales]),
[Unit Sales] )/([Product].CurrentMember, [Unit Sales])',
FORMAT_STRING = '##.0%'
select [Store].[(All)].Members on COLUMNS,
Generate( [Product].[Brand Name].Members,
Union(
TopCount( [Product].CurrentMember.Children, 2, [Unit Sales] ) * {[Unit Sales]},
{([Product].CurrentMember, [PercTotalSales]) }
)
) on ROWS
from Sales
9.显示四个季度所有品牌的销售情况,高亮显示各个季度销售组成最少10%的品牌。单元格属性是在查询中突出展示异常数据的便捷方式(如,改变字体风格或者颜色以吸引读者对重要信息的注意)。Listing9,通过向计算成员HLUnit Sales添加单元格属性Font_FLAGS以黑体显示各个季度销售居于最低10%的品牌。由于一个MDX只能设置一个单元格属性,所以只能通过条件逻辑判断是显示罗马字体还是黑体。在本例中,使用的条件逻辑是判断当前品牌与组成最低10%的所有品牌的交集是否为空。如果产生的交集为0,说明该品牌不再组成10%的品牌中,罗马字体显示;如果交集是1,说明该品牌在这10%中,黑体显示。
Listing_09.Highlighting Products in the Bottom 10 Percent.txt
说明:查出4个季度中,每个时期销售量在后10%的产品销售量,并显示为粗体
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not
IsEmpty([Time].CurrentMember)))'
set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'
member [Measures].[HLUnit Sales] as '[Unit Sales]',
FONT_FLAGS = 'iif( Count(Intersect( BottomPercent( [Product].[Brand Name].Members, 10, ([Unit Sales]) ),
{[Product].CurrentMember})) = 0, 0, 1)'
select [Last4Quarters] on COLUMNS,
[Product].[Brand Name].Members on ROWS
from Sales
where ([HLUnit Sales])
cell properties VALUE, FORMATTED_VALUE, FONT_FLAGS
附:
LISTING 1: Determining Products Sold in Each State
说明:
with set [SoldInUSA] as 'Filter([Product].[Brand Name].Members, Not IsEmpty( ([USA], [Unit Sales]) ))'
member [Measures].[SoldInState] as 'iif( IsEmpty(([Product].CurrentMember, [Unit Sales], [Customers].CurrentMember)), "No","Yes" )'
select [USA].children on COLUMNS,
[SoldInUSA] on ROWS
from Sales
where ([SoldInState])
LISTING 2: Determining Top 10 Product Categories
说明:
select {[Unit Sales]} on COLUMNS,
TopCount( [Product].[Product Category].Members, 10, ([Unit Sales]) ) on ROWS
from Sales
Listing_03.Determining Brands Sold During the Past Three Quarters.txt
说明:在过去三个季度里都存在销售量的商品销售记录
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)),1)'
set [Last3Quarters] as ' [LastQuarter].item(0).item(0).Lag(2) : [LastQuarter].item(0).item(0)'
select [Last3Quarters] on COLUMNS,
Non Empty Union(Descendants( [Food], [Product].[Brand Name] ), Descendants( [Drink],[Product].[Brand Name] )) on ROWS
from Sales
Listing_04.Determining Recent Trends for Best-Selling Brands.txt
说明:查出最近6个月销售趋势最好的前10个商品及销售量
with set [TenBest] as 'TopCount( [Product].[Brand Name].Members, 10, [Unit Sales] )'
set [LastMonth] as 'Tail(Filter([Time].[Month].Members, Not IsEmpty([Time].CurrentMember)),1)'
set [Last6Months] as ' [LastMonth].item(0).item(0).Lag(6) : [LastMonth].item(0).item(0)'
select [Last6Months] on COLUMNS,
[TenBest] on ROWS
from Sales
Listing_05.Determining Brands that Make Up 80 Percent of Sales.txt
说明:找出组成销售额80%的商品销售及其记录;
select {[Unit Sales]} on COLUMNS,
TopPercent([Product].[Brand Name].Members, 80, [Unit Sales]) on ROWS
from Sales
Listing_06.Determining Brands That Make Up the Bottom 20 Percent of Sales.txt
说明:按销售量排序,找出组成20%销售量的商品销售记录
select {[Unit Sales]} on COLUMNS,
Non Empty BottomPercent([Product].[Brand Name].Members, 20, [Unit Sales]) on ROWS
from Sales
Listing_07.Determining the Top Five Stores and the Top Five Customers.txt
说明:查出销售量最好的前5名店和每个店的前5个顾客 及其销售记录
select {[Unit Sales]} on COLUMNS,
Generate( TopCount([Store].[Store Name].Members, 5, [Unit Sales]),
{ [Store].CurrentMember } * TopCount( [Customers].[Name].Members, 5, ([Unit Sales], [Store].CurrentMember) ) ) on ROWS
from Sales
Listing_08.Determining Two Top-Selling Products.txt
说明:查出每种产品大类 前2名 产品小类型号的销售记录,以及小类型号占大类的百分比
with member [Measures].[PercTotalSales] as
' Sum( TopCount([Product].CurrentMember.Children,2,[Unit Sales]),[Unit Sales])
/([Product].CurrentMember, [Unit Sales])',
FORMAT_STRING = '##.0%'
select [Store].[(All)].Members on COLUMNS,
Generate( [Product].[Brand Name].Members,
Union(
TopCount( [Product].CurrentMember.Children, 2, [Unit Sales] ) * {[Unit Sales]},
{ ([Product].CurrentMember, [PercTotalSales]) }
)
) on ROWS
from Sales
Listing_09.Highlighting Products in the Bottom 10 Percent.txt
说明:查出4个季度中,每个时期销售量在后10%的产品销售量,并显示为粗体
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))'
set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'
member [Measures].[HLUnit Sales] as '[Unit Sales]',
FONT_FLAGS = 'iif(Count(Intersect( BottomPercent( [Product].[Brand Name].Members, 10,([Unit Sales]) ), {[Product].CurrentMember})) = 0, 0, 1)'
select [Last4Quarters] on COLUMNS,
[Product].[Brand Name].Members on ROWS
from Sales
where ([HLUnit Sales])
cell properties VALUE, FORMATTED_VALUE, FONT_FLAGS
Listing_10.Comparing Sales with Those of Parallel Months.txt
说明:比较具有相同相对位置的时间点的销售量,例如今年7月和去年7月
with set [PromoMonths] as 'Filter([Time].[Month].Members, Not IsEmpty( ([Unit Sales], [Double Your Savings]) ) )'
set [PromoRange] as 'Head( [PromoMonths] ).item(0).item(0) : Tail( [PromoMonths]).item(0).item(0)'
member [Measures].[Uplift] as '([Unit Sales], [Double Your Savings])'
member [Measures].[This Quarter] as '[Unit Sales]'
member [Measures].[Last Quarter] as '( ParallelPeriod( [Time].[Quarter] ), [Unit Sales] )'
member [Measures].[Growth] as ' [This Quarter] - [Last Quarter]'
select [PromoRange] on Columns,
{ [This Quarter], [Last Quarter], [Growth], [Uplift] } on Rows
from [Sales]
Listing_11.Determining Sales That Exceed Store Cost by 160 Percent.txt
说明:查出利润率在16%以上的产品及销售记录
with member [Measures].[SalesRatio] as '([Store Sales] - [Store Cost]) / [Store Cost]',
FORMAT_STRING = '##%'
select { [Store Sales], [Store Cost], [SalesRatio] } on COLUMNS,
Filter( [Product].[Brand Name].Members, [SalesRatio] > 1.60 ) on ROWS
from Sales
Listing_12.Determining Brands that Have Grown by More Than 50 Percent.txt
说明:找出最近一季度比前一季度销售量增长幅度大于50%的产品销售记录
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))'
member [Measures].[CurrQSales] as '([LastQuarter].item(0).item(0), [Unit Sales])'
member [Measures].[PrevQSales] as '([LastQuarter].item(0).item(0).PrevMember, [Unit Sales])'
member [Measures].[Growth] as ' ([CurrQSales] - [PrevQSales]) / [PrevQSales]',
FORMAT_STRING='##%'
select { [PrevQSales], [CurrQSales], [Growth] } on COLUMNS,
Filter( [Product].[Brand Name].Members, [Growth] > 0.5 ) on ROWS
from Sales
Listing_13.Determing the Top 10 and Bottom 10 Product Brands.txt
说明:找出销售额在前、后10名的产品销售记录,并列出总排名,就是找出销售情况最好和最坏的产品
with set [OrderedBrands] as 'Order( [Product].[Brand Name].Members, [Unit Sales], BDESC )'
member [Measures].[Brand Rank] as 'Rank( [Product].CurrentMember, [OrderedBrands] )'
select {[Brand Rank], [Unit Sales]} on COLUMNS,
Union( Head( [OrderedBrands], 10 ), Tail( [OrderedBrands], 10 ) ) on ROWS
from Sales
Listing_14.Comparing Product Trends.txt
说明:比较一下产品销售趋势
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))'
set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'
member [Measures].[GroupAvg] as 'Avg([Product].CurrentMember.Siblings, [Unit Sales])'
member [Measures].[AllAvg] as 'Avg( [Product].[Product Name].Members, [Unit Sales])'
select [Last4Quarters] on COLUMNS,
{ [Unit Sales], [GroupAvg], [AllAvg] } on ROWS
from Sales
where ([Ebony Plums])
Listing_15.Determining the Top 10 Middle-Tier Brands.txt
说明:查出一定条件下的前10名产品的销售记录,例如销售量在500到3000之间的
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))'
set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'
member [Measures].[GroupAvg] as 'Avg([Product].CurrentMember.Siblings, [Unit Sales])'
member [Measures].[AllAvg] as 'Avg( [Product].[Product Name].Members, [Unit Sales])'
member [measures].[abc] as '[Product].CurrentMember.uniquename'
select [Last4Quarters] on COLUMNS,
{ [Unit Sales], [GroupAvg], [AllAvg],[measures].[abc] } on ROWS
from Sales
where ([Ebony Plums])
with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not IsEmpty([Time].CurrentMember)))'
set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'
member [Measures].[GroupAvg] as 'Avg([Product].CurrentMember.Siblings, [Unit Sales])'
member [Measures].[AllAvg] as 'Avg( [Product].[Product Name].Members, [Unit Sales])'
member [measures].[abc] as '[Product].CurrentMember.uniquename'
member [measures].[abcd] as 'lookupcube("Trained Cube","MemberToStr([Customers].[All Customers].[Canada])")'
select [Last4Quarters] on COLUMNS,
{[Unit Sales], [GroupAvg], [AllAvg],[measures].[abc] ,[measures].[abcd] } on ROWS
from Sales
where ([Ebony Plums])