SSAS Tabular关系设计--时间最小粒度维度

这两天设计的T2模型，涉及日期维度表中粒度的选择，因为一般实际销售达成ACT表是按天为标准记录的，而销售目标表TGT一般是按月记录，特此总结一下建模技巧。

数据仓库：

时间维度表，最小粒度为DATE。建立视图，表格模型中Times维度表：select * from vw_CB_DATE

CREATE view [dbo].[vw_CB_DATE]

AS

WITH mycte AS(
     SELECT a.[DATE]
     ,CONVERT(nvarchar(8),a.[DATE],112) as DAY
     ,a.[MONTH_KEY],a.[QUARTER_KEY],a.[CURRENT_YEAR],a.[MONTH],a.[QUARTER] 
     ,LEFT(CURRENT_YEAR, 4) + ' ' + QUARTER AS YEAR_QUARER
     ,LEFT(CURRENT_YEAR, 4) + '/' + LEFT(MONTH, 3) AS YEAR_MONTH
     ,left(a.YEAR_WEEK,4)+'W'+RIGHT(a.YEAR_WEEK,2) as YEAR_WEEK
     ,b.YEAR_WEEK_SEQ
    FROM [dbo].[CB_DATE] a
    INNER JOIN (
    SELECT DISTINCT YEAR_WEEK,Row_number() over (order by YEAR_WEEK ASC) as YEAR_WEEK_SEQ FROM [dbo].[CB_DATE]
    WHERE YEAR_WEEK<>'190001' group by YEAR_WEEK
    ) b ON a.YEAR_WEEK=b.YEAR_WEEK
)

SELECT a.* FROM mycte a

 

事实表1：实际销售达成ACT表，源表字段即属性包括[YYYYMMDD]（day）--与Times维度表关联,[YYYYMM](Month) [QTY_BASE_TARGET]--销售实际，用于度量值计算。

度量值组：ACT SO(QTY):=SUM([EO_QTY])，以日期（day）粒度聚合。

事实表2：销售目标表TGT，源表属性[YYYYMM](Month)--直接则无法与Times维度表关联，因为要求关联具有唯一性的键值。即度量值组粒度为月份。

解决方案：

创建视图，为销售目标表TGT添加YYYYMMDD字段，与Times维度表关联。

因为用户在使用时只会拉取到月份透视，所以只要每月份数据存在即可，即便拉取到DAY,只有每月1号存在TGT值组，不显示空行即可。这样就避免了再建立一个亿月份为最小粒度的TIME维度表。

SELECT CAST([YYYYMM] AS NVARCHAR(6))+'01' AS YYYYMMDD
   --...others
  FROM [DM_ACCN_T1].[dbo].[FS_SELLOUT_SALES_TGT] 

 

度量值组： TGT Company SO QTY:=sum([QTY_BASE_TARGET])，以日期（day）粒度聚合。

结果：<注：在表格中分析透视表时，一定要进行数据处理，处理操作可以从数据源获取更新的数据，这样在才有与sql查询比对的意义>

拉取DATE到行标签：

设置显示行中的空数据项：

sql查询验证：

  SELECT sum(REV_BASE_TARGET),sum(QTY_BASE_TARGET) FROM dbo.FS_SELLOUT_DEALER_TGT
  where YYYYMM='201204'  --956217978.0300   312772.9900

  SELECT sum(REV_BASE_TARGET),sum(QTY_BASE_TARGET) FROM dbo.FS_SELLOUT_DEALER_TGT A
  LEFT JOIN vw_CB_DATE B ON A.YYYYMM=B.MONTH_KEY
  where B.DAY='20120401'   --956217978.0300   312772.9900

  SELECT sum(REV_BASE_TARGET),sum(QTY_BASE_TARGET) FROM dbo.FS_SELLOUT_DEALER_TGT A
  LEFT JOIN vw_CB_DATE B ON A.YYYYMM=B.MONTH_KEY
  where B.DAY='20120410'   --956217978.0300   312772.9900

 

但是要是这样查询：

  SELECT sum(REV_BASE_TARGET),sum(QTY_BASE_TARGET) FROM dbo.vw_FS_SELLOUT_DEALER_TGT A
  LEFT JOIN vw_CB_DATE B ON A.YYYYMMDD=B.DAY
  where B.DAY='20120410'   --NULL   NULL

 

参看MSDN关于：定义度量值组中的维度粒度

https://msdn.microsoft.com/zh-cn/library/ms166573%28v=sql.120%29.aspx

粒度：

粒度将直接决定所构建仓库系统能够提供决策支持的细节级别。粒度越高表示仓库中的数据较粗，反之，较细。粒度是与具体指标相关的，具体表现在描述此指标的某些可分层次维的维值上。例如，时间维度，时间可以分成年、季、月、周、日等。

数据仓库模型中所存储的数据的粒度将对信息系统的多方面产生影响。事实表中以各种维度的什么层次作为最细粒度，将决定存储的数据能否满足信息分析的功能需求，而粒度的层次划分、以及聚合表中粒度的选择将直接影响查询的响应时间。