補足のレッスン: 不規則階層

適用対象:SQL Server 2017 以降の Analysis Services Azure Analysis Services Fabric/Power BI Premium

この補足のレッスンでは、さまざまなレベルに空白の値 (メンバー) が含まれている階層でピボットするときの一般的な問題を解決します。 たとえば、上級管理者が、部門管理者と管理者以外の両方を直属の部下として持つ組織などがあります。 また、国 - リージョン - 市からなる地理的階層では、ワシントン D.C. やバチカン市など、都市によっては親となる州や県がありません。 階層に空のメンバーがあると、多くの場合、レベル数が異なったり不規則になったりします。

1400 互換性レベルの表形式モデルには、階層用の [メンバーを隠す] プロパティがあります。 [既定] 設定では、どのレベルにも空のメンバーがないと想定されています。 [空白のメンバーを隠す] 設定では、ピボットテーブルやレポートに追加した空のメンバーが階層から除外されます。

このレッスンの推定所要時間: 20 分

前提条件

この補足のレッスン記事は、表形式モデリングチュートリアルの一部です。 この補足のレッスンの作業を実行する前に、前のレッスンをすべて完了しているか、または Adventure Works Internet Sales のサンプル モデル プロジェクトを完了している必要があります。

チュートリアルの一環として AW Internet Sales プロジェクトを作成した場合、モデルには不規則なデータや階層がまだ含まれていません。 この補足のレッスンを実行するには、まず最初に、いくつかのテーブルを追加することによって問題を生じさせ、リレーションシップ、計算列、メジャー、および新しい組織階層を作成する必要があります。 その作業に 15 分程かかります。 その後、ほんの数分でそれを解決します。

テーブルとオブジェクトの追加

新しいテーブルをモデルに追加するには

1. [表形式モデル] エクスプローラーで、[データ ソース] を展開し、接続 > [新しいテーブルのインポート] を右クリックします。

2. ナビゲーターで、DimEmployee と FactResellerSales を選択し、[OK] をクリックします。

3. クエリ エディターで、[インポート] をクリックします。

4. 次のリレーションシップを作成します。

   テーブル 1	列	フィルターの方向	テーブル 2	列	アクティブ
   FactResellerSales	OrderDateKey	Default	DimDate	Date	はい
   FactResellerSales	DueDate	Default	DimDate	Date	いいえ
   FactResellerSales	ShipDateKey	Default	DimDate	Date	いいえ
   FactResellerSales	ProductKey	Default	DimProduct	ProductKey	Yes
   FactResellerSales	EmployeeKey	両方のテーブル	DimEmployee	EmployeeKey	Yes

5. DimEmployee テーブルで、次の集計列を作成します。

   パス

   =PATH([EmployeeKey],[ParentEmployeeKey])
   

   FullName

   =[FirstName] & " " & [MiddleName] & " " & [LastName]
   

   Level1

   =LOOKUPVALUE(DimEmployee[FullName],DimEmployee[EmployeeKey],PATHITEM([Path],1,1)) 
   

   Level2

   =LOOKUPVALUE(DimEmployee[FullName],DimEmployee[EmployeeKey],PATHITEM([Path],2,1)) 
   

   Level3

   =LOOKUPVALUE(DimEmployee[FullName],DimEmployee[EmployeeKey],PATHITEM([Path],3,1)) 
   

   Level4

   =LOOKUPVALUE(DimEmployee[FullName],DimEmployee[EmployeeKey],PATHITEM([Path],4,1)) 
   

   Level5

   =LOOKUPVALUE(DimEmployee[FullName],DimEmployee[EmployeeKey],PATHITEM([Path],5,1)) 
   

6. DimEmployee テーブルで、Organization (組織) という名前の階層を作成します。 次の列を順番通りに追加します。Level1、Level2、Level3、Level4、Level5。

7. FactResellerSales テーブルで、次のメジャーを作成します。

   ResellerTotalSales:=SUM([SalesAmount])
   

8. [Excel で分析] を使用して Excel を開き、ピボットテーブルを自動的に作成します。

9. ピボットテーブル フィールドで、DimEmployee テーブルの Organization 階層を Rows に、ResellerTotalSales メジャーを FactResellerSales テーブルから Values に追加します。

   

   ピボットテーブルから分かるとおり、階層には不規則な行が表示されます。 空のメンバーが表示される行が多数あります。

[メンバーを隠す] プロパティを設定して不規則階層を修正するには

1. 表形式モデル エクスプ ローラーで、[テーブル]>[DimEmployee]>[階層]>[Organization] を展開します。

2. [ プロパティ]>[メンバーの非表示] で、[ 空白のメンバーを非表示にする] を選択します。

   

3. Excel に戻り、ピボットテーブルを更新します。

   

   これで、大幅に改善されました。

参照

レッスン 9: 階層の作成
補足レッスン - 動的セキュリティ
補足レッスン - 詳細行