用 MDX 创建时间命名集总结 SSAS
来源：互联网 发布：大数据分析道与术 编辑：程序博客网 时间：2024/06/05 23:54
用 MDX 创建时间命名集总结 SSAS
在数据分析中，大多数分析都和时间有关。时间条件经常是反复使用的，如昨天、上周、最近三个月等等。
为了便于快速地进行时间筛选，可以在多维分析模型中，创建一个时间命名集。
创建时间命名集的首要问题是确定当前时间。
一种方法是判断度量值的非空
见下面的语句，通过 Not IsEmpty 进行判断。最后一个有度量值的定为昨天
CREATE DYNAMIC SET CURRENTCUBE.[过去一天]
AS Tail(Filter([日期].[日期层次].[日期], Not IsEmpty([日期层次].CurrentMember)),1), DISPLAY_FOLDER = '命名集';
CREATE DYNAMIC SET CURRENTCUBE.[过去三天]
AS [过去一天].item(0).item(0).Lag(2) : [过去一天].item(0).item(0), DISPLAY_FOLDER = '命名集';
如果能保证每天都有数据，上述算法没有问题。
不过，现实中，因为种种原因可能造成数据延迟、数据丢失或者最后一天确实没有数据。
最后的非空数据不是最近的时间
这时，要用另外的方法计算当前时间。
首先使用 now() 生成一个成员
CREATE MEMBER CURRENTCUBE.[Measures].[TimeNow]
AS now()
然后用 StrToMember、DateAdd、 Format 函数转换为时间成员
CREATE DYNAMIC SET CURRENTCUBE.[今天]
AS StrToMember(
"[日期].[日期层次].[日期].&["
+
Format(DateAdd('d',0,CDate(TimeNow)
),"yyyy-MM-dd")
+"T00:00:00]"
), DISPLAY_FOLDER = '时间段' ;
CREATE DYNAMIC SET CURRENTCUBE.[昨天]
AS StrToMember(
"[日期].[日期层次].[日期].&["
+
Format(DateAdd('d',-1,CDate(TimeNow)
),"yyyy-MM-dd")
+"T00:00:00]"
), DISPLAY_FOLDER = '时间段';
CREATE DYNAMIC SET CURRENTCUBE.[前天]
AS StrToMember(
"[日期].[日期层次].[日期].&["
+
Format(DateAdd('d',-2,CDate(TimeNow)
),"yyyy-MM-dd")
+"T00:00:00]"
), DISPLAY_FOLDER = '时间段' ;
月份的用DateAdd('m', ...
时间集使用 ":" 、 StrToSet 生成
CREATE DYNAMIC SET CURRENTCUBE.[最近三个月]
AS StrToSet(
"[日期].[日期层次].[月份].&["
+
Format(DateAdd('m',-3,CDate([TimeNow])
),"yyyy-MM-01")
+"T00:00:00]:[日期].[日期层次].[月份].&["
+
Format(DateAdd('m',0,CDate([TimeNow])
),"yyyy-MM-01")
+"T00:00:00]"
), DISPLAY_FOLDER = '时间段' ;
季度的特殊一些，用了 datediff 和 dateadd 计算是哪个季度
CREATE DYNAMIC SET CURRENTCUBE.[最近三个季度]
AS StrToSet(
"[日期].[日期层次].[季度].&["+
Format(dateadd('q',datediff('q','2000-01-01',[TimeNow])-3,'2000-01-01'),"yyyy-MM-01") +
"T00:00:00]:" +"[日期].[日期层次].[季度].&["+
Format(dateadd('q',datediff('q','2000-01-01',[TimeNow]),'2000-01-01'),"yyyy-MM-01") +
"T00:00:00]"
), DISPLAY_FOLDER = '时间段' ;
年的用 DateAdd('yyyy' ...
CREATE DYNAMIC SET CURRENTCUBE.[最近三年]
AS StrToSet(
"[日期].[日期层次].[年].&["
+
Format(DateAdd('yyyy',-3,CDate([TimeNow])
),"yyyy-01-01")
+"T00:00:00]:[日期].[日期层次].[年].&["
+
Format(DateAdd('yyyy',0,CDate([TimeNow])
),"yyyy-01-01")
+"T00:00:00]"
), DISPLAY_FOLDER = '时间段'
附上 SQL Server 里，常见时间计算 Sql 语句
Year to Date
CASE WHEN Date BETWEEN DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AND DATEADD(dd, - 1, GETDATE()) THEN 'YTD' ELSE 'N' END AS YTD
Month to Date
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, (DATEADD(dd, - 1, GETDATE()))), 0) AND DATEADD(dd, DATEDIFF(dd, 1, getdate()), 0) THEN 'MTD' ELSE 'N' END AS MTD
Prior Year to Date
CASE WHEN Date BETWEEN DATEADD(yy, DATEDIFF(yy, 0, (DATEADD(yy, DATEDIFF(yy, 0, getdate()), - 1))), 0) AND DATEADD(yy, - 1, (DATEADD(dd, - 1, GETDATE())))
THEN 'Prior YTD' ELSE 'N' END AS [Prior YTD]
Yesterday
CASE WHEN DATEADD(dd, DATEDIFF(dd, 1, getdate()), 0) = Date THEN 'Yesterday' ELSE 'N' END AS Yesterday,
Week to Date
CASE WHEN Date BETWEEN DATEADD(wk, DATEDIFF(wk, 0, getdate() - 1), - 1) AND DATEADD(dd, DATEDIFF(dd, 1, getdate()), 0) THEN 'WTD' ELSE 'N' END AS WTD,
Prior Month
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 1, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Prior Month' ELSE 'N' END AS [Prior Month]
Prior Month -1
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 2, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 1, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Prior Month -1' ELSE 'N' END AS [Prior Month -1]
Prior Month -2
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 3, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 2, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Prior Month -2' ELSE 'N' END AS [Prior Month -2]
Last 12 Months
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 12, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Last 12 Months' ELSE 'N' END AS [Last 12 Months]
Last Year MTD
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 12, (DATEADD(dd, - 1, GETDATE())))), 0) AND DATEADD(yy, - 1, (DATEADD(dd, DATEDIFF(dd, 1, getdate()), 0))) THEN 'Last Year MTD' ELSE 'N' END AS LastYearMTD
Prior Quarter
CASE WHEN Date BETWEEN DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 1, 0) AND DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)), - 1) THEN 'Prior Quarter' ELSE 'N' END AS PriorQuarter
Prior Quarter -1
CASE WHEN Date BETWEEN DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 2, 0) AND DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 1, - 1) THEN 'Prior Quarter -1' ELSE 'N' END AS [PriorQuarter -1]
Prior Quarter -2
CASE WHEN Date BETWEEN DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 3, 0) AND DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 2, - 1) THEN 'Prior Quarter -2' ELSE 'N' END AS [PriorQuarter -2]
Prior Quarter -3
CASE WHEN Date BETWEEN DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 4, 0) AND DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 3, - 1) THEN 'Prior Quarter -3' ELSE 'N' END AS [PriorQuarter -3]
Last 9 Months
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 9, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Last 9 Months' ELSE 'N' END AS [Last 9 Months]
Last 6 Months
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 6, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Last 6 Months' ELSE 'N' END AS [Last 6 Months]
Last 3 Months
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 3, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Last 3 Months' ELSE 'N' END AS [Last 3 Months]
Prior Quarter LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 1, 0))) AND DATEADD(yy, - 1, (DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)), - 1))) THEN 'Prior Quarter LY' ELSE 'N' END AS [PriorQuarter LY]
Prior Quarter -1 LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 2, 0))) AND DATEADD(yy, - 1, DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 1, - 1))) THEN 'Prior Quarter -1 LY' ELSE 'N' END AS [PriorQuarter -1 LY]
Prior Quarter -2 LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 3, 0))) AND DATEADD(yy, - 1, (DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 2, - 1))) THEN 'Prior Quarter -2 LY' ELSE 'N' END AS [PriorQuarter -2 LY]
Prior Quarter -3 LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 4, 0))) AND DATEADD(yy, - 1, (DATEADD(qq, DATEDIFF(qq, 0, DATEADD(dd, DATEDIFF(dd, - 1, getdate()) - 1, 0)) - 3, - 1))) THEN 'Prior Quarter -3 LY' ELSE 'N' END AS [PriorQuarter -3 LY]
Last 12 Months LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 12, (DATEADD(dd, 0, GETDATE())))), 0))) AND DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1))) THEN 'Last 12 Months LY' ELSE 'N' END AS [Last 12 Months LY]
Last 9 Months LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 9, (DATEADD(dd, 0, GETDATE())))), 0))) AND DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1))) THEN 'Last 9 Months LY' ELSE 'N' END AS [Last 9 Months LY]
Last 6 Months LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 6, (DATEADD(dd, 0, GETDATE())))), 0))) AND DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1))) THEN 'Last 6 Months LY' ELSE 'N' END AS [Last 6 Months LY]
Last 3 Months LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 3, (DATEADD(dd, 0, GETDATE())))), 0))) AND DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1))) THEN 'Last 3 Months LY' ELSE 'N' END AS [Last 3 Months LY]
Prior Month -2 LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 3, (DATEADD(dd, 0, GETDATE())))), 0))) AND DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 2, (DATEADD(dd, 0, GETDATE())))), - 1))) THEN 'Prior Month -2 LY' ELSE 'N' END AS [Prior Month -2 LY]
Prior Month -1 LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 2, (DATEADD(dd, 0, GETDATE())))), 0))) AND DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 1, (DATEADD(dd, 0, GETDATE())))), - 1))) THEN 'Prior Month -1 LY' ELSE 'N' END AS [Prior Month -1 LY]
Prior Month LY
CASE WHEN Date BETWEEN DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 1, (DATEADD(dd, 0, GETDATE())))), 0))) AND DATEADD(yy, - 1, (DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 0, (DATEADD(dd, 0, GETDATE())))), - 1))) THEN 'Prior Month LY' ELSE 'N' END AS [Prior Month LY]
Last Year
CASE WHEN Date BETWEEN DATEADD(yy, DATEDIFF(yy, 0, getdate() - 1) - 1, 0) AND DATEADD(yy, DATEDIFF(yy, 0, getdate() - 1), - 1) THEN 'Last Year' ELSE 'N' END AS [Last Year]
2 Years Ago
CASE WHEN Date BETWEEN DATEADD(yy, DATEDIFF(yy, 0, getdate() - 1) - 2, 0) AND DATEADD(yy, DATEDIFF(yy, 0, getdate() - 1) - 1, - 1) THEN '2 Years Ago' ELSE 'N' END AS [2 Years Ago]
Last 12 Months -1
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 13, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, - 1, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Last 12 Months -1' ELSE 'N' END AS [Last 12 Months -1]
Current Month +1
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 1, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 2, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Current Month +1' ELSE 'N' END AS [Current Month +1]
Current Month +2
CASE WHEN Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 2, (DATEADD(dd, 0, GETDATE())))), 0) AND DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 3, (DATEADD(dd, 0, GETDATE())))), - 1) THEN 'Current Month +2' ELSE 'N' END AS [Current Month +2]
- 用 MDX 创建时间命名集总结 SSAS
- ssas mdx 成员属性
- SSAS中MDX语法之一
- SSAS中命名集(SET)的使用
- SSAS中MDX语言之二
- SSAS表格命名计算语法
- 创建SSAS项目
- SSAS项目创建
- 一些常用的MDX查询语句（基于SSAS 2008）
- 一些常用的MDX查询语句（基于SSAS 2008）
- 浅谈SSAS计算中MDX性能改进的几个技巧
- 浅谈SSAS计算中MDX性能改进的几个技巧
- c++ 创建用当前时间命名的文件夹
- SSAS : 从现有多维数据集创建挖掘结构
- SSAS 2012实战示例一：创建多维数据集
- SSAS 2012实战示例一：创建多维数据集
- 创建和使用SQL Server SSAS本地多维数据集
- SSAS 2012实战示例一：创建多维数据集
- 网管工具之cacti
- linux双向链表分析之list_del中的技巧
- Flex命名空间解说
- C#设计模式之17——迭代器模式
- MySQL 修改AUTO_INCREMENT的值
- 用 MDX 创建时间命名集总结 SSAS
- NIO应用
- 有关使用 GPS Intermediate Driver 无法获取到GPS数据的一个问题
- cassandra学习笔记一
- db2命令行远程连接数据库
- 文件流示例
- chroot环境安装Qt 4.7.2
- 多线程实现Galgame的字幕显示
- FLex资源
