analysis service 2005 使用中的一点记录()

 

问题记录

 

1.       Analysis Serive远程访问:

 目前只支持windows认证,如访问的程序与analysis service在同一台机器上,则不存在访问的问题,如分布在不同机器上,而必须保证两台机器都加入同一域,并且访问程序必须在机器为域登陆状态下才可以连接上.

 几种情况: 域用户登陆系统 , .Net开发环境下运行web程序,可以访问到 远程analysis服务. 将web程序发布到IIS上,访问不了,提示用户访问失败(用户提示为机器名, 分析WEB发布后运行的身份不是域用户身份)

           

             本机问题同上,发布后的就无法问题analysis service

 

             不用域用户登陆系统,任何程序无法访问远程analysis 服务.

 

  

2.       部署提示用户无法登陆错误,数据源连接不上，处理方法，修改数据源设计器中的模拟信息，改为使用服务帐户

 

4 . 维度表、或是维度用的查询，必须要有主键,不然生成不了多维数据

 

5. 直接生成的父子维度，父（非叶子结点）会生成一个自己的子成员，可去掉之（将memberswithdate 设这 NonLeafDataHidden ）

 

5．直接生成的父子维度，会有一个all成员，可去掉之（将isaggregatable 设为 false）

 去掉后,其它非层次结构的维度会有问题(无统计数据了,…….建议不这样处理,因为多维数据的其它维度会有影响,可在查询时过滤到all的成员,后面有讲)

 

6.将Analysis服务作为作业中的一个步骤加入时,选择的是Analysis 命令,不是Analysis 查询

 

 

8.去掉ALL 例子

 

 

set GGG as

 filter(   [SMC Organise4].[Org Parent ID].allmembers ,

   [SMC Organise4].[Org Parent ID].currentmember.name <>"all")

 

 

 

 

 

 

 

 

9.当前时间

 

WITH  

MEMBER [Measures].[Full Date] as 'NOW()'

-- The Second Calculated Member is the Day part of the first calculated member.

MEMBER [Measures].[What Day] as 'DAY([Full Date])'

-- The Third Calculated Member is the Month part of the first calculated member.

MEMBER [Measures].[What Month] as 'MONTH([Full Date])'

-- The Fourth Calculated Member is the Year part of the first calculated member.

Member [Measures].[What Year] as 'YEAR([Full Date])'

 

 

 

11. 只显示昨天的时间

 

with

MEMBER today as 'NOW()'

member yesterday as Format(dateadd('d',-1,now()),"yyyy-MM-dd")

member ssad as '1'

 

set sent_col as

filter (

crossjoin ([Time].[Hour Of Day].allmembers,

[Measures].[sent]

),

([Time].[Hour Of Day].currentmember.name<>"all")

)

set recvd_col as

filter (

crossjoin ([Time].[Hour Of Day].allmembers,

[Measures].[recvd]

),

([Time].[Hour Of Day].currentmember.name<>"all")

)

set viewday_col as

crossjoin ([Time].[Hour Of Day], --.allmembers,

yesterday)

 

set ssad_col as

crossjoin ([Time].[Hour Of Day], --.allmembers,

ssad)

 

 

 

set org_row as

filter (  

[SMC Organise].[Org Parent ID].allmembers,

[SMC Organise].[Org Parent ID].currentmember.name <>"all"

)

 

 

select

{ssad_col, viewday_col ,sent_col ,recvd_col } On 0, //subset (DDD,1,24)

org_row On 1

from [SMC_C2]

where {strtomember(

          "[Time].[TimeTree].&["

           +Format(cdate(yesterday),"yyyy") //cdate(yesterday)

           +"].&["+

           +Format(cdate(yesterday),"MM")+

           +"].&["+

           +Format(cdate(yesterday),"dd")+"]"

            )

        }

 

 

13. 对维度中不存在的值的处理 , 完整性的问题 , 注意设定的是 CUBE 的相关 ErrorConfig

   个般做试验时经常碰到 .

 

 

 

14. 在上面查询上 , 精确的对不同的 mimetype 进行分类统计 , 注意 WHERE 部分的设计

with

MEMBER today as 'NOW()'

member yesterday as Format(dateadd('d',-1,now()),"yyyy-MM-dd")

member ssad as '1'

 

set sent_col as

filter (

crossjoin ([Time].[Hour Of Day].allmembers,

[Measures].[sent]

),

([Time].[Hour Of Day].currentmember.name<>"all")

)

set recvd_col as

filter (

crossjoin ([Time].[Hour Of Day].allmembers,

[Measures].[recvd]

),

([Time].[Hour Of Day].currentmember.name<>"all")

)

set viewday_col as

crossjoin ([Time].[Hour Of Day], --.allmembers,

yesterday)

 

set ssad_col as

crossjoin ([Time].[Hour Of Day], --.allmembers,

ssad)

set org_row as

filter (  

[SMC Organise].[Org Parent ID].allmembers,

[SMC Organise].[Org Parent ID].currentmember.name <>"all"

)

select

{ssad_col, viewday_col ,sent_col ,recvd_col } On 0, //subset (DDD,1,24)

org_row On 1

from [SMC_C2]

where {

crossjoin (

strtomember (

          "[Time].[TimeTree].&["

           +Format(cdate(yesterday),"yyyy") //cdate(yesterday)

           +"].&["+

           +Format(cdate(yesterday),"MM")+

           +"].&["+

           +Format(cdate(yesterday),"dd")+"]"

            )

            ,

          {[SMC mime].[Mimesort].&[media],[SMC mime].[Mimesort].&[application]}

            )

           

        }

 

 

 

 

15. 每天的数据加入每月 , 但不可能对在事实数据中保存一个月的数据 , 这时可使用 处理中的

“ 增量处理 ” , 在查询中指定范围 , 这时 , 以前的数据还会有 , 来达到 .

 

 

 

16. 单个维度与事实表之类的关系 , 好象还与其它维度有关系

 ( 比如和其它维度匹配不上的项 , 本维度同样不匹配 , 因为是多维数据集 , 处理办法 , 转为 unkonwn 项 )

 

 

 

17. 对度量值做 DistinctCount 时 , 对于非整型的列 , 要设定属性中的 DataType 为整型