字符串度量与CUBE设计

参考资源:

http://www.sqlserveranalysisservices.com/OLAPPapers/Handling%20Comments%20in%20Yukon.htm

http://www.sqlserveranalysisservices.com/OLAPPapers/Loading%20Aggregate%20Data%20in%20AS2005v2.htm

1.       新增一个维度表Dim_Annotation, 表结构如下

AnnotationKey, AnnotationString, ParentKey

ParentKey的目的是为了构造一个假的父子维度

新增一个记录 0,All Annotation,0

 

2.       在事实表中新增字段AnnotationKey

3.       新增视图Fact_AvailabilityAnnotation

只取Fact_Availability中的PK和AnnotationKey字段

4.       打开BIDS, 将Dim_Annotation和Fact_AvailabilityAnnotation拉入DSV, 新增一个维度表Annotation, 它有两个属性Annotation, Annotations

Annotation.Key= AnnotationKey, Annotation.Name= AnnotationString

5.       将Annotation维度拉入Cube中

6.       增加一个度量CommentIndex和维度组Annotation,  聚合方式为SUM, 在Dimension Usage中将Annotation 的关联关系清空

7.       打开Calculation页新增一个计算成员

CREATE MEMBER CURRENTCUBE.[MEASURES].[Availability Annotation]

 AS IiF  (Measures.[Comment Index] = 0,"-" ,StrToMember("[Annotation].[Annotation].&[" + VBA!CStr(measures.[Comment Index]) + "]").Properties("Caption")),

 

8.  处理Cube

9.  测试更新与查询备注, MDX脚本如下:

 

/* Step 1: Add a new annotation */

alter cube [Availability]

create dimension member [Annotation].[Annotations].&[0].[testAnnotation6],

KEY=6

 

;

/* alter annotation value */

alter cube [Availability]

update dimension member [Annotation].[Annotations].&[1]

,[Annotation String]='testAnnotation6++'

/* Step 2: Assign the new annotation Id to a cell */

ALTER CUBE [Availability]

UPDATE DIMENSION MEMBER [Annotation].[Annotations].&[3]

,[Annotation String]='DEMO3'

 

 

/* Step 3: Change annotation associated with cell */

update cube [Availability]

set ([Time].[Date].&[20080502]

,[Runner].[Runner].&[3]

,[Measures].[Comment Index])=1

 

/* Step 4: View Changes*/

select

([Time].[Date].&[20080502]

,[Runner].[Runner].&[3]

) on 0

,{[Measures].[Availability],[Measures].[Availability Goal],[Measures].[Availability Annotation]} ON 1

from [Availability]