AnalysisSerive精選-或許可以體會更多

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.單個維度與事實表之類的關係,好象還與其它維度有關係
 (比如和其它維度匹配不上的項,本維度同樣不匹配 ,因為是Cube,處理辦法,轉為unkonwn項 )
 
 
 
17.對度量值做DistinctCount時,對於非整型的列,要設定屬性中的DataType為整型