規律、精度、細部へのこだわり、パート2（OLAP、SSAS）

はじめに

この記事では、Microsoft Analysis Servicesでの私の経験の話を続けます。 前の記事に加えて、最後のプロジェクトで行われた非標準の決定について書きたいと思います。 これらのソリューションにより、Microsoft Analysis Servicesに近づくことができ、以前よりも彼を尊敬し、以前は信じられなかったようになりました。

期間の平均の合計

次の図に示すように、顧客は期間の各要素の平均値の合計を要求しました。

$$

$$（2.5 + 3.5 + 3）= 9$$

つまり、要素elem1、elem2、...、elemNのコンテキストでは、キューブは選択された期間の各要素の平均をカウントし、他のすべての場合は合計が考慮されるという考え方です。 1つのディメンションのキューブの動作を変更する方法、および他のすべてのディメンションが標準から逸脱する方法が必要です（説明した場合、標準の動作はSUMです）。

ソリューションオプション：

1. 最初のオプション：
   
   
   
   このオプションでは、[ELEM]のコピーを作成し、VisibleプロパティをFalseに設定するだけで、非表示のディメンション[ELEM COPY]を作成する必要があります。 次に、図に示すように、「計算」キューブのセクションで「新しい計算メンバー」を選択します。
   
   
   
   
   
   
   
   
   
   
   
   式を入力するために表示されるウィンドウに次のように記述します。
   
   
   
   

   iif ( not isleaf([ELEM].[ELEM SK].currentmember), sum(EXISTING [ELEM COPY].[ELEM SK].currentmember.Children,[Measures].[FCT VAL]), [Measures].[FCT VAL] )
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   ここで、[ELEM COPY]。[ELEM SK]は非表示のディメンションのキー属性です。
   
   
   
   [対策]。[FCT VAL]はAvarageOfChildren集計、つまり キューブを作成するときに、AvarageOfChildren型のディメンションを作成し、自動的に計算しました。 AvarageOfChildren集計を使用してディメンションを作成する方法の例を次の図に示します。
   
   
   
   
   
   
   
   
   
   
   
   
2. SCOPEを使用したソリューション：
   
   この決定は同僚によって私に示されました。私の意見では、理解するのは簡単ですが、実装はもう少し難しいです。
   
   
   
   対処方法：
   
   ファクトテーブルの選択を書き換えます。
   
   
   
   代わりに：
   
   
   
   

    SELECT [DATE] ,ID_CO ,ID_CUST ,ID_SYS ,ID_VOL ,ID_QUAL ,GB_used_DATA FROM [000_REP].NAS_FACTS
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   書く：
   
   
   
   

    SELECT [DATE] ,ID_CO ,ID_CUST ,ID_SYS ,ID_VOL ,ID_QUAL ,GB_used_DATA ,CONVERT(VARCHAR(10), [DATE], 120) + '|' + CAST(ID_VOL AS VARCHAR(MAX)) AS VolDate ,NULL AS Avg_GB_used_DATA FROM [000_REP].NAS_FACTS
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   GB_used_DATAは、キューブに追加したい事実です。 ID_VOLキーを使用した測定に対して非標準の動作を実行します。ID_CO、ID_CUST、ID_SYS、およびID_QUALキーを使用した残りの測定については、動作が標準であり、すべてをタスクにまとめる必要があります。 ID_VOLの場合のみ、各要素の期間の平均を考慮し、ID_VOL要素の合計も合計する必要があり、ID_VOLの平均値の合計にする必要があります。
   
   
   
   2番目のクエリでは、2つの列が追加されました。
   
   
   
   
   • 最初の列は、平均値の合計を計算するID要素と日付のリンクを一意に決定します。 キューブでは、DistinctCount集計関数を持つディメンションがこの列に追加されます。 下図の例：
     
     
     
     
     
     
     
     
   • 2番目の列には、テーブルのすべての行に常にNULL値が格納されます。 2番目の列の名前は重要です。SCOPE関数を使用してバインドできるディメンションをキューブに作成できるようにするために必要です。 また、この測定にSum集計関数を使用することも重要です。 以下の例：
     
     
     
     
     
     
     
     次に、「計算」キューブのセクションで「スクリプトビュー」を選択し、スクリプトを編集するためのウィンドウで次のコードを挿入します。
     
     
     
     

      SCOPE([Measures].[Avg GB Used DATA]); SCOPE([ID_VOL Items].[ID VOL].[ID VOL].MEMBERS); THIS = [Measures].[Sum GB Used DATA]/[Measures].[Vol Date Distinct Count]; END SCOPE; END SCOPE;
           
           
     
             
             
             
           
     
         
             
             
             
           
           
     
             
             
             
           
     
         
          

     
     
     [ID_VOL Items]は、キーがID_VOLであるディメンションです。
     
     
     
     図は、このステップの一連のステップを示しています。
     
     
     
     
     
     
     
     このソリューションでは、SCOPEに式が記述されていないため、SCOPEに式がない場合、データベースへのクエリからのNULLをそこに格納します。
     
     
   
   
   

どちらのソリューションでも同じ結果が得られ、出力では、タスクで必要な平均の合計が考慮されました。

統計平均

しばらくして、顧客は平均値の計算のトピックに戻りました。 今回、彼は平均の合計ではなく、古典的な平均、つまりExcelのAVERAGE関数と同じ機能を要求しました。 顧客は常に「統計平均」の概念を使用していたため、この章のタイトルになりました。











範囲全体の平均値を計算する必要がありました。 各日のすべての要素の平均が取得され、合計され、期間内の日数で除算されます。 結果は、期間ごとの1つの要素の平均値です。 次の解決策が提案されました。

 CREATE MEMBER CURRENTCUBE.[Measures].[Avg GB Used DATA (AvgAll Only valid days)] as [Measures].[Sum GB Used DATA]/[Measures].[Count VCMDB Only valid days], VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB Only valid days] as Count(NonEmpty({crossjoin([DIM Business Time HD].[DAY].currentmember.Children,[DIM NAS Provider Configuration Item HD].[NAS Volume CMDBID].currentmember.Children)}, [Measures].[Sum GB Free Data] )), VISIBLE = 1 ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この決定では、要素の値がある日のみが取られました。 隠された次元のトリックも使用されました（これらは[DIM Business Time HD]、[DAY]および[DIM NAS Provider Configuration Item HD]、[NAS Volume CMDBID]次元）。 値があった日数、私はcrossjoinを使用しました。



すべての日のすべての値の平均を取得する必要がある場合、任意の日の値の不在は0に等しいので、次の式を使用しました。

 CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB All days] as [Measures].[NAS Volume CMDBID Distinct Count] * [Measures].[NAS BTIME Count], VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB All days] as [Measures].[NAS Volume CMDBID Distinct Count] * [Measures].[NAS BTIME Count], VISIBLE = 1 ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ここで、[Measures]。[NAS Volume CMDBID Distinct Count]および[Measures]。[NAS BTIME Count]は、測定（一時測定および要素測定）用のテーブルから構築されたキューブ測定です。

別の便利な機能

キューブを操作するプロセスでは、値の計算が見たい階層のレベルとは異なる要件が提示されました。 つまり、日が選択された場合、期間の平均が表示され、月が選択された場合、金額が表示されます。 これは、レベル関数を使用して行われました。

 CREATE MEMBER CURRENTCUBE.[Measures].[ML] as case when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[YEAR] then 3 when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[PERIOD KAL] then 2 when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[DAY] then 1 else 4 end, VISIBLE = 1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

まとめ

正直なところ、平均値を計算するための要件を見たとき、私は笑いながら泣きたいと思いました。 時々、平均値が、選択した範囲内のすべての値よりも大きい（平均値の合計の例のように）か、それ以下であったという事実から笑います。 この記事では、意味を理解できなかったため、ケースを説明しませんでした。顧客は、1日の値を月の日数で割る必要がありました。 そして、これらの要件を何らかの形で実装する必要があったため、泣かなければなりませんでした。



試行は、標準のAvarageOfChildrenに限定されます。これは、その日のすべてのインジケーターを要約し、これらの金額を合計して、期間内の日数で割ったもので、成功しませんでした。 顧客は、現実は彼の世界の写真と一致すると主張しました。 私たちのすべての決定は、厳格なテストにさらされました。 相互作用の初期段階で、私たちとクライアントの間の信頼の問題はなくなりました。 彼は常にシンプルで直感的なものではなく、奇妙で怪しいものに変わったトリックと解決策を探していました。 しかし、すべてには独自の利点があります。 このような要件のため、OLAPキューブをより詳細に処理し、任意の集計を操作する方法を学習する必要がありました。 この経験が無駄ではなく、将来私だけでなく同様の問題を解決するのに役立つように、私はそれをあなたと共有することにしました。 一般的に、配管工に関するジョークのように：

研修生が配管に取り付けられました。 彼らは去るように呼びかけます。 到着。 下水道マンホール。 それからくそが流れます。 配管工はハッチに来て、飛び込みます。

1分後、彼は叫びながら現れました。

-キーは19です。

再び潜る。 半分後に現れます：

-ガスケットNo.6。

再び潜る。 出現：

-キーは26です。

ダイブ。 1分後、彼は現れます。 それは判明し、揺れ、点灯します。 彼は座って息をし、研修生に言いました。

-このように！..学ぶ、学生！ そして、あなたは一生キーを与えます...