SQL多维数据集ISO 8601日历插入445个月

Question

我是SSAS的新手，目前正在构建一个多维数据集。除了我需要使用ISO 8601日历之外，其他事情都可以，但不幸的是，内置的日历不包含月份，只包含年、周、日。我想要实现的是添加具有445种模式的月份，如报告月份，但使用ISO 8601日历的周、天、年初/年末。

是否可以在不手动编辑日历表的情况下实现这一点？

感谢

John

Answer 1

没错！我终于用这种逻辑做了一些事情：

WHEN [ISO_8601_Week_Of_Year]>=1 AND [ISO_8601_Week_Of_Year]< 5 THEN 'January,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','') 
  WHEN [ISO_8601_Week_Of_Year]>4 AND [ISO_8601_Week_Of_Year]< 9 THEN 'February,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>8 AND [ISO_8601_Week_Of_Year]< 14 THEN 'March,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>13 AND [ISO_8601_Week_Of_Year]< 18 THEN 'April,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>17 AND [ISO_8601_Week_Of_Year]< 22 THEN 'May,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>21 AND [ISO_8601_Week_Of_Year]< 27 THEN 'June,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>26 AND [ISO_8601_Week_Of_Year]< 31 THEN 'July,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>30 AND [ISO_8601_Week_Of_Year]< 35 THEN 'August,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>34 AND [ISO_8601_Week_Of_Year]< 40 THEN 'September,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>39 AND [ISO_8601_Week_Of_Year]< 44 THEN 'October,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>43 AND [ISO_8601_Week_Of_Year]< 48 THEN 'November,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')
  WHEN [ISO_8601_Week_Of_Year]>47 AND [ISO_8601_Week_Of_Year]<= 52 THEN 'December,'+REPLACE(ISO_8601_Year_Name,'ISO8601 Calendar','')

然后使用了另一个类似的查询：

SELECT MIN(ISO_8601_Week) AS 'Month_date',Month_Name
FROM [Reporting].[dbo].[SSAS_Calendar]
GROUP BY Month_Name
ORDER BY Month_date

Month_Name是创建的列，Month_Date返回每个月的开始。

Answer 2

您可以在Visual Studio中添加"报告日历"。它可以有445个图案。但对我来说，它给周的数字是错误的，即周是星期一，而不是星期四（根据ISO应该是这样）。

选择"新建维度…"，然后选择一个时间维度选项。