SQL Server 脳で学ぶ DAX

14644 ワード

SQLServer PowerBI PowerBIDesktop SQLServer テキストリンク

Power BI や Analysis Services 等で使用される DAX ですが、SQL Server の Transact-SQL (T-SQL) の考え方で操作をしようとして、いつもちんぷんかんぷんになるので、情報をまとめておこうかと。

DAX の公式の情報は次のリファレンスになるかと思います。
- Data Analysis Expressions (DAX) リファレンス
DAX を使いだした際には、Power BI 勉強会で紹介されていた次の情報も参考になるのではないでしょうか。
(私は表面的に触っているレベルなので、まだガイドを使いこなしてクエリはかけていません…。)
- DAX Guide
- DAX Patterns

DAX についての基本情報

基本的な情報は DAX の概要から確認を行うことができます。
Power BI Desktop で操作を行う場合、「モデリング」のタブで操作をする内容が DAX の操作が必要になる箇所かと。

Power Query M 式言語 (M Language : M 言語) によって、取り込まれたデータに対して、モデリングを行う際に DAX を使用することになります。

DAX は、Data Analysis Expressions の略ですのでデータを分析するための強力な式が含まれており、取り込まれたデータの分析を行う際に活用ができる言語 (式) となります。

DAX を操作するためのツール

DAX を操作するためのツールにはいくつかのものがあるかと思いますが、代表的なものは次の 2 種類ではないでしょうか。
- SQL Server Management Studio (SSMS)
- DAX Studio

これらのツールについては DAX クエリでも紹介が行われています。

DAX クエリを使用すると、テーブル式によって定義されたデータのクエリを実行して取得できます。レポートクライアントでは、フィールドがレポート画面に配置されるとき、またはフィルターや計算が適用されるときは常に、DAX クエリを作成します。また、DAX クエリは、SQL Server Management Studio (SSMS) や、DAX Studio などのオープンソースツールで作成して、実行することもできます。 DAX クエリを SSMS や DAX Studio で実行すると、結果はテーブルとして返されます。

DAX によりデータを操作するための DAX クエリを記述する際には、これらのツールを活用するとインテリセンスを効かせながらクエリを記載することができます。

DAX クエリの基本構文

T-SQL の基本構文と同じように、DAX クエリにも基本構文があります。
この基本構文は、SSMS / DAX Studio からクエリを記載する際には、この構文に準拠する必要があります。

オブジェクトの修飾子 (SQL Server でいう 3 パート名や [] によるオブジェクト名の修飾のような考え方) については、オブジェクト名の例に記載されています。
テーブル名を修飾する際には '' で修飾 (T-SQL : [Test Table] -> DAX : 'Test Table) し、列名の指定は [] で指定する (T-SQL : [Test Table].[C1] -> DAX : 'Test Table'[C1]) というような記述方法となります。

構文については DAX クエリに記載されていいる次の構文となります。

Power BI Desktop のモデリングで記述する DAX は、次の機能を使用する際に記載するケースが多いのではないでしょうか。

これらの機能で DAX を記述する場合、列名 / テーブル名に対して DAX を記述する (<列名> = DAX 数式) ことになるかと思いますが「DAX クエリ」を記述する場合には、前述の構文に合わせて記載する必要があります。

カレンダーテーブルを例にしてみます。
Power BI Desktop で、単純なカレンダーテーブルを作成する場合、「新しいテーブル」に対して、次のような式を記述します。

カレンダーテーブル = CALENDAR("2020-01-01", TODAY())

それでは、この式を DAX Studio にそのまま貼り付けて実行するとどうなるかというと、エラーとなります。

DAX Studio で記載しているのは「DAX クエリ」(DAX ステートメント) となり、Power BI Desktop で使用しているのは「DAX 式」となります。

そのため、DAX Studio では、DAX クエリの構文に即した形でクエリを記述する必要があります。
DAX クエリは、DEFINE / EVALUATE / ORDER BY / START AT (EVALUATE 以外は省略可能) の DAX ステートメントで構成されます。
先ほどのカレンダーのデータを DAX クエリで取得する場合は、次のようなクエリとなります。

DEFINE
    VAR startDate = "2020-01-01"
    VAR endDate = TODAY()
EVALUATE
(
    CALENDAR(startDate, endDate)
)
ORDER BY FORMAT([Date], "YYYY-MM-DD") ASC
START AT "2020-01-03"

DAX クエリで必須となるのは、EVALUATE だけですのでシンプルなものであれば、次の 1 文でも実行することはできます。

EVALUATE CALENDAR("2020-01-01", TODAY())

EVALUATE のリファレンスのパラメーターには次のように記載されています。

パラメーターにはテーブルを設定する必要があります。SQL Server で考える場合には、テーブル名やテーブル関数の結果を設定するようなイメージでしょうか。
テーブルをパラメーターとして指定するためには、テーブル名の指定やテーブル操作関数の結果を指定する必要が出てくるかと思いますので、EVALUATE にはテーブルを渡しているかを意識すると良いのかもしれませんね。

T-SQL の場合は、記述するのはクエリのみとなりますが、DAX の場合は、自分が記述しているのが「数式」なのか「クエリ」なのかは、意識してみてもよいのではないでしょうか。

RETRUN て何だろう

DAX で新しいテーブルからカレンダーを作成する場合Date calendar dax code の記事がヒットします。
この中では「RETURN」が使用されていますが、DAX リファレンスで RETURN を検索してもヒットしません。

カレンダーの作成を列の付与を一部追加して DAX クエリで書くと次のようになります。

DEFINE 
     VAR startday = "2020-01-01"
     VAR endday = "2020-12-31"
     VAR t_calender = CALENDAR(startday, endday)
EVALUATE
(
    GENERATE(
        t_calender, 
        var c1 = YEAR([date])
        RETURN 
            ROW( 
                "Year", c1
            )
    )
)

この中でも RETURN と書いているのですが、RETURN は関数ではなくステートメントのため、DAX リファレンスでタイトルでフィルターしてもヒットしません。
RETURN については、Expression に記載されています。

評価され、結果を返す DAX ロジックの単位。式では変数を宣言できますが、その場合、サブ式が割り当てられ、最終的な式を出力する RETURN ステートメントを含める必要があります。式は、モデルオブジェクト (テーブル、列、またはメジャー)、関数、演算子、または定数を使用して構成されます。

最終的な式については RETURN ステートメントを記述することになるようです。
上記のクエリであれば、GENERATE 関数に対しては、2 つのテーブルを渡す必要があります。

一つ目のテーブルについては「t_calender」を渡しています。
二つ目のテーブルについては、GENERATE 内に記載した数式の中化で、ROW に記述した内容をテーブルとして返すような意味合いとなるのかと。

Power BI Desktop から実行される DAX のクエリトレース

こちらは、SQL Server の DBA 的な要素が含まれる内容です。

Power BI Desktop では、SQL Serve Analysis Services (SSAS) のエンジンが使用されています。
Power BI Desktop を起動すると「msmdsrv.exe (Microsoft SQL Server Analysis Services)」が自動的に起動されます。
起動した SSAS が「%USERPROFILE%\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces」配下のファイルを使用したインスタンスを起動して、データの分析を行う際にのクエリエンジンとして動作しています。

SSAS が起動していますので、Power BI Desktop を起動しているローカル環境からであれば、SSMS でも接続することができます。
Power BI Dekstop を起動した状態で、次の PowerShell のコマンドレットを実行すると、Power BI Desktop で起動している、SSAS のポート番号を取得できます。

Get-NetTCPConnection | ? OwningProcess -eq (Get-Process -Name msmdsrv).Id

次のような結果であれば、TCP 61327 で起動していることになります。

この情報がわかれば、SSMS から接続することができます。SSMS で Analysis Services を選択して、次のように「127.0.0.1:<ポート番号>」を指定すると、接続をすることができます。

SQL Server のトレースを取得する SQL Server Profiler は、SSAS のクエリトレースも対応しており、「Query Begin」のような情報をトレースするように設定することが可能です。
これにより、Power BI Desktop から実行されている DAX クエリや、M 言語により実行されているクエリをトレースすることができます。

Power BI Desktop でレポートにデータを表示する際には、DAX により情報を取得している個所が多々あります。
SQL Server で「どのようなクエリが実行されているか」を確認する際には、SQL Server Profiler や拡張イベントを使用して取得を行うことがありますが、この考え方は Power BI Desktop で実行されている DAX にも活用できます。

クエリの書き方のヒントとして、Power BI から実行されている DAX を取得して眺めてみることで、参考になることもあるのではないでしょうか。

SSAS と Power BI の情報を組み合わせて確認することは、クエリのトレースだけでなく、ドキュメントでも役に立つかと。

Power BI の DirectQuery の制限については、DirectQuery の制限に記載されており、SSAS の DirectQuery の制限についてはテーブルモデルでの DirectQuery モードに記載されています。
Power BI は SSAS を分析エンジンとして使用していますので、DirectQuery の制限については、両方のドキュメントを組み合わせて確認することで理解度が増すこともあるのではないでしょうか。

実行プランの取得

DAX にも実行プランがあり、拡張イベントを使用した取得方法についてはExtracting a DAX Query Plan With Analysis Services 2016 Extended Events で解説が行われています。

DAX Studio からクエリを実行する際に「Queyr Plan」を有効にしてクエリを実行することでも、クエリプランが取得できますので、実行されているクエリの効率を確認する場合には、DAX Studio のほうがお手軽なのかもしれませんね。

基本的なクエリの書き方

T-SQL と比較しながら。DAX をぺちぺち書いてみます。効率的な処理になっているかはアレです。。。

TOP x

T-SQL

SELECT TOP 10 * FROM frontdoor.accesslog

DAX

EVALUATE
(
    TOPN(10, 'frontdoor accesslog')
)

WHERE

T-SQL

SELECT TOP 10 * FROM frontdoor.accesslog WHERE httpMethod = 'GET'

DAX

EVALUATE
(   
    TOPN(10,
        FILTER('frontdoor accesslog', 'frontdoor accesslog'[httpMethod] = "GET")
    )       
)

COUNT

T-SQL

SELECT COUNT(*) FROM frontdoor.accesslog WHERE time >= '2020-03-19 00:00:00' AND time < '2020-03-20 00:00:00'

DAX

EVALUATE
(   
    ROW(
        "c1", COUNTROWS(FILTER('frontdoor accesslog', AND('frontdoor accesslog'[time] >= DATEVALUE("2020-03-19 00:00:00") ,'frontdoor accesslog'[time] < DATEVALUE("2020-03-20 00:00:00"))))
    )
)

一時テーブルを使用したクエリ

T-SQL

DECLARE @T TABLE(C1 int, C2 int)
INSERT INTO @T VALUES(1,1), (2,2)

SELECT * FROM @T WHERE C1 = 1

DAX

DEFINE
    TABLE T = DATATABLE(
        "C1", INTEGER,
        "C2", INTEGER,
        {
            {1,1},
            {2,2}
        }
    )
EVALUATE(
    FILTER(T, T[C1] = 1)
)

JOIN

T-SQL

DECLARE @T1 TABLE(C1 int, C2 int)
INSERT INTO @T1 VALUES(1,1), (2,2)

DECLARE @T2 TABLE(C1 int, C2 int)
INSERT INTO @T2 VALUES(1,1), (1,2)

SELECT * FROM @T1
LEFT JOIN @T2 ON [@T2].C1 = [@T1].C1

DAX

EVALUATE(
    NATURALLEFTOUTERJOIN(T1,T2)
)

DEFINE で定義したテーブルでは、正常に動作させることができなかったので、T1 / T2 を新しいテーブルとして作成して、リレーションシップを Power BI 側で設定

GROUP BY SUM

T-SQL

SELECT
    httpStatusCode,
    SUM(requestBytes)
FROM frontdoor.accesslog
GROUP BY httpStatusCode

DAX

EVALUATE
(
    SUMMARIZE('frontdoor accesslog', 
        'frontdoor accesslog'[httpStatusCode], 
        "ReqBytesTotal", SUM('frontdoor accesslog'[requestBytes])
    )
)

Author And Source

この問題について(SQL Server 脳で学ぶ DAX), 我々は、より多くの情報をここで見つけました https://qiita.com/Masayuki_Ozawa/items/3d11291f0dc4b9185b7c

著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。

Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .