Clever Geek Handbook

規律、精度、細部へのこだわり

はじめに：



この記事では、 Microsoft Analysis Servicesとの連携について説明し、SSASが動作するMicrosoft SQL Serverのストレージについて少し説明します。 私はささいなことではないことに直面しなければならず、時には彼らが私に望んだことをするために「頭を飛び越える」必要がありました。 会議の合間に作業する必要がありました。 時々、新しい機能が開発されたよりも長く議論されました。 多くの場合、会議で何度か、同じことを言わなければなりませんでした。 私が1時間以上会議をするのは難しいと言ったとき、彼らは驚きと誤解で私を見ました。 多くの点で、このような雰囲気のおかげで、これらの非常に些細なことは私が書くことを決めたように見えました。



期間平均



期間の平均値を計算する必要がありました。 MS Analysis Servicesには、この関数に子の平均があり、選択した期間のすべての空でない値の平均を計算します。



画像



しかし、結果を綿密に研究した後、顧客に質問がありましたか？ 彼は私に次のサンプルを見せて、それが正しくないと言った：



画像






彼の意見では、これは次のようになるはずだからです。



画像






私の質問に：なぜですか？ 彼は、期間の平均値ではなく、期間の各要素の平均値の合計が必要だと答えました。



そうではない：



画像






（5 + 6 + 7）/ 3 = 6



ここに：



画像






（2.5 + 3.5 + 3）= 9



この欲求により、私は避けられないものを受け入れるすべての段階を経ました。



  1. 拒否（これは任意ですが、期間の平均ではありません）;
  2. 怒り（誰が彼に数学を教えたのですか？）;
  3. 交渉（このままにしておき、それを使う人に聞いてみましょう）;
  4. うつ病（そして、彼らはここの誰もがとても親切で良いと言いました...）
  5. 受け入れ（うさぎに喫煙を教えることもできます。そうする必要があります。私は尋ねられたとおりに行います）。


決定は完全に明白ではありませんでした。 [計算]セクションで、計算メンバーを作成し、iif、isleaf、およびsum関数を使用して式を作成しました。



最初のオプション： 



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


ここで、[ELEM]は次元で、[ELEM SK]はそのキーです。



結果は予想外でした：



画像






結果の奇妙な数字は、フィルターで選択した値に関係なく、ディメンション内の要素のすべての値の合計であることが判明しました。 つまり、最終的には、選択された要素だけでなく、常にすべての要素の合計がありました。



問題はすぐには解決しませんでした。 最初の解決策は、追加の非表示ディメンションを持つオプションでした。 [ELEM]ディメンションのコピーを作成し、VisibleプロパティをFalseに変更して、次のように書きました。 



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


顧客の望みどおりになりました。



画像






少し後に「より正確な」解決策を見つけました。 名前付きセットを作成しました。 



 CREATE DYNAMIC SET CURRENTCUBE.[Controller Set] AS { EXISTING [ELEM].[ELEM SK].currentmember.Children };


計算メンバーはこれに書き直しました： 



 iif ( not isleaf([ELEM].[ELEM SK].currentmember), sum([Controller Set],[Measures].[FCT VAL]), [Measures].[FCT VAL] )


したがって、[ELEM COPY]ディメンションのコピーはもう必要ありません。



このソリューションを顧客に提示し、他のタスクを取り上げました。 しばらくして、「統計的平均」の概念が現れる新しい技術タスクを受け取りました。 私の質問に-それは何ですか？ 彼は、ユーザーは期間の各要素の平均値の合計ではなく、期間の平均、つまり通常の子供の平均を必要とすると言いましたが、彼は私の決定を保存するよう求めました。



また、価値のある日だけでなく、すべての日を考慮して、期間の平均を追加するように求めました。



画像






（5 + 6）/ 3 = 3.666667



時間ディメンションのテーブルの行数を単純にカウントする追加の事実を使用して問題を解決しました（このソリューションでは、このテーブルには毎日1つのレコードが常にあります）。 さらに、彼は集計付きのファクト-合計（AggregateFunction = Sum）を追加し、計算メンバーを追加しました。計算メンバーでは、合計を日数で除算しました。 



 [Measures].[Sum DATA]/[Measures].[TIME Count]


保管



私たちが働いているクライアントはそれほど単純ではありませんでした。 彼について私たちは言うことができます-「ムッシュは倒錯について多くを知っています。」 キューバに対処した後、よりグローバルな課題に直面しました。 Data Vaultを設計しますが、簡単ではありません。 最初に言われたのは、ダニエルリンシュテットによって書かれた「データボールト2.0でスケーラブルなデータウェアハウスを構築する」聖書であり、さらに「バイテムポラルデータベーステーブルデザイン」を導入することを主張したことです。 構築されたストレージに基づいて、歴史的なキューブを構築する必要がありました。 このようなキューブには2つの時間ディメンションがあり、1つは営業日を示し、もう1つは取引日を示します。 開発プロセスについて話すと、痛みがあり、今でも痛いですが、何かが起こりました。



「Bitemporal Database Table Design」の本質は、各レコードに4つの追加フィールドがあることです。



  1. Business_date_ from
  2. Business_date_to
  3. Transaction_date_from
  4. Transaction_date_to


最初の2つは、営業日の間隔を含みます-値が変更されなかった日付と日付。 2番目の2つのフィールドには、取引日の間隔が含まれています。 この間隔には、誰も値を調整しなかった期間が保存されます（オプションとして遡及的に）。



この問題を解決する際の問題の1つは、ソースデータ、またはそれを受け取った形式です。 データは、毎日のスライスの形で提供されました。 つまり、データが取得された日付が保存された日付フィールドがあり、日付フィールドの翌日には新しい値があり、データはこの日に変更されたかどうかにかかわらず。 期間ごとにデータを結合する必要がありました。 つまり たとえば、値が3日間連続して変更されていない場合、3行の代わりに、DATA列の代わりにBEGINとENDの2つの列がある行を保存する必要があります。



変換前のデータ：



画像






変換後のデータ：



画像






LAGおよびLEAD関数を使用してこの問題を解決しました。 ソリューションの本質は、値が連続して3日間変わらない場合、現在の行の日付から前の日付の日付を引いたものが1になることです。



画像








ここから： 



  1.   SELECT * FROM ( SELECT Volume ,[Date] dt ,DATEDIFF(day, LAG([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date]), [Date]) difLag ,DATEDIFF(day, [Date], LEAD([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date])) difLead FROM dbo.Test n ) m WHERE ( difLag > 1 OR difLag IS NULL ) OR ( difLead > 1 OR difLead IS NULL )


  2. 次に、この結果を何らかの方法でグループ化し、1日以上続いた期間のペアを結合して、期間が1日続いた期間のペアを残す必要があります。 行に番号を付け、偶数と奇数を組み合わせてグループ化を行いました。



    クエリ全体： 



     WITH se AS ( SELECT * FROM ( SELECT Volume ,[Date] dt ,DATEDIFF(day, LAG([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date]), [Date]) difLag ,DATEDIFF(day, [Date], LEAD([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date])) difLead FROM Test n ) m WHERE (difLag > 1 OR difLag IS NULL) OR (difLead > 1 OR difLead IS NULL) ) ,p1 AS --choose only one day periods (be careful) ( SELECT Volume ,dt AS VT_BEG ,dt AS VT_END FROM se WHERE NOT (isnull(difLag, - 1) = 1 OR isnull(difLead, - 1) = 1) ) ,p2 AS ( SELECT Volume ,CASE WHEN difLead IS NOT NULL AND (difLag IS NULL OR difLag > 1) THEN dt END AS VT_BEG ,CASE WHEN difLag IS NOT NULL AND (difLead IS NULL OR difLead > 1) THEN dt END AS VT_END ,row_number() OVER (ORDER BY Volume,dt) AS rn FROM se WHERE isnull(difLag, - 1) = 1 OR isnull(difLead, - 1) = 1 ) SELECT * FROM ( SELECT min(Volume) AS data ,min(VT_BEG) AS VT_BEG ,min(VT_END) AS VT_END FROM p2 GROUP BY (CASE WHEN rn % 2 = 0 THEN rn ELSE rn + 1 END) UNION ALL SELECT Volume,VT_BEG,VT_END FROM p1 ) g ORDER BY VT_BEG ,data


結果：



私が働いているクライアントのオフィスには、スローガン付きのポスターがあります。 私の意見では、対処しなければならない困難の理由を部分的に説明しているため、この記事をこの記事と呼ぶことにしました。 このプロジェクトはまだ完了しておらず、最も興味深いのはこれからだと思います。 私はすでに会議に賛成しており、彼らが私に何かを尋ねると、KVNのフレーズが頭に浮かぶことがあります：「よくやった、スマートな質問をして、愚かな答えを得る...」



この記事では、私が思うに、プロジェクトにあった最も興味深いことについてだけ話しました。 それらに加えて、多くの定型的、討論的、その他の、それほど独創的ではない解決策がありました。 私が書いたことが興味深く、役に立つことを願っています。


More articles:


All Articles