Power Platform Day Summer '20 イベント レポート

Power BI 班として ログとかデータ回りをまとめて締めくくるべきかと。
2020-07-04(Sat) に イベントがありまして利用したサービスから得られたログを集計するなど。

- [Power Platform Day Summer '20 - connpass](https://power-platform.connpass.com/event/178495/)


# connpass
告知や募集などには connpass を使っています。コミュニティのグループを作ったりして便利なので使っている。イベント管理者になっているとイベントサイトの関する集計されたログなどをみて、告知がもっと必要だなとかできるわけです。connpass じゃなきゃダメってことではないんだけどね。
既定の集計を見ているだけではちょっと物足りないときは集計しなおしてみたりするのだけど、データのエクスポートをしてからちょちょっとやることはある。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/1571de60-8f6b-8d86-a7e6-2731fcec8cfe.png)
こっちのほうはおまけ的な感じで。

# Teams ライブイベント
どんなログが得られるのかなというところから。イベント終了後 "出席者の活動レポート" (AttendeeReport.csv) っていうのが入手できるんですね。これを整理しておきたいなと。そもそも、どのようなデータが含まれるのか初見だったので具合を見ながらで。

|ヘッダ|たぶんこれ|
|:--|:--|
|Session Id|通信セッションGUID|
|Participant Id|組織アカウントとか匿名など混在|
|Full Name|AAD に依存するものとか匿名など混在|
|UserAgent|UA|
|UTC Event Timestamp|M/d/yyyy hh:&#xFFF0;mm:ss tt|
|Action|"Joined" "Left"|
|Role|"Attendee" " Event Team Member"|

まぁ大したものは見れないだろうなと思ったのと個人情報には興味がないな。でも、参加者がどんな感じ参加しサヨナラしていったのかわかりそうな感じかなと。ちょっとフォーマットがよろしくないかもだ。Timezone 付きの方が好みである。また、文字列はダブルクォーテーションでくくってないから調子悪い行とかもありましたよ。

## どんなログだ
Power BI 班なので あえて DAX クエリで見てみたなど。

```text:DAXクエリ
EVALUATE
SUMMARIZE( AttendeeReport, AttendeeReport[Action], AttendeeReport[Role] )

----------------
Action	Role
"Joined"	"Attendee"
"Left"	"Attendee"
"Joined"	"Event Team Member"
"Left"	"Event Team Member"
```

該当 Teams ライブイベントに "Joined", "Left"
該当 Teams ライブイベントの "Attendee", "Event Team Member"
なんだろうなということが判った。

```text:DAXクエリ
EVALUATE
SUMMARIZE(
	ADDCOLUMNS(
	    VALUES( AttendeeReport[Session Id] ),
	    "@Count", CALCULATE( COUNTROWS( AttendeeReport) )
	),
	[@Count]
)

------------
@Count
2
1
```
列 "Session Id" (GUID) ごとでカウントしてみた。どうやら、"Session Id" ごとで "Joined", "Left" ということっぽい。

```text:DAXクエリ
EVALUATE
CALCULATETABLE(
	AttendeeReport,
	FILTER(
		ADDCOLUMNS(
		    VALUES( AttendeeReport[Session Id] ),
		    "@Count", CALCULATE( COUNTROWS( AttendeeReport) )
		),
		[@Count] = 1
	)
)

ORDER BY AttendeeReport[JST Event Timestamp]
```
カウントが 1 になってしまうものの詳細を確認した "Joined" のみなので、"Left" となる前に レポートを出力したのではないかと思う。
後日レポートを再出力したら かなり後から "Left" なってるセッションもあるみたいなのでさほど気にすることでもないなと。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/4e9a21b4-0088-9e7f-e86d-19272f712188.png)


## 集計してみる(仮)

```text:PowerQuery
// AttendeeReport
let
    Source = Csv.Document(
        File.Contents(
            "C:\hogehoge\AttendeeReport.csv"
        ),
        [Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]
    ),
    ChangedType1 = Table.TransformColumnTypes(
        Source,
        {
            {"Column1", type text}, {"Column2", type text},
            {"Column3", type text}, {"Column4", type text},
            {"Column5", type text}, {"Column6", type text},
            {"Column7", type text}
        }
    ),
    TrimmedText = Table.TransformColumns(
        ChangedType1,
        {
            {"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text},
            {"Column3", Text.Trim, type text}, {"Column4", Text.Trim, type text},
            {"Column5", Text.Trim, type text}, {"Column6", Text.Trim, type text},
            {"Column7", Text.Trim, type text}
        }
    ),
    PromotedHeaders = Table.PromoteHeaders( TrimmedText, [PromoteAllScalars=true] ),
    ChangedType2 = Table.TransformColumnTypes(
        PromotedHeaders,
        {"UTC Event Timestamp", type datetime}
    ),
    RemovedErrors = Table.RemoveRowsWithErrors(ChangedType2),
    ToJST = Table.TransformColumns(
        RemovedErrors,
        {"UTC Event Timestamp", each _ + #duration( 0,9,0,0 ), type datetime}
    ),
    RenamedColumns = Table.RenameColumns(
        ToJST,{"UTC Event Timestamp", "JST Event Timestamp"}
    ),
    RemovedOtherColumns = Table.SelectColumns(
        RenamedColumns,
        {"Session Id", "UserAgent", "JST Event Timestamp", "Action", "Role"}
    )
in
    RemovedOtherColumns
```

```text:DAX
# Attendee = 
VAR CurrentDateTime = MAX( AttendeeReport[JST Event Timestamp] )
RETURN
CALCULATE(
    CALCULATE(
        COUNTROWS( AttendeeReport ),
        AttendeeReport[Action] = "Joined"
    ) -
    CALCULATE(
        COUNTROWS( AttendeeReport ),
        AttendeeReport[Action] <> "Joined"
    ),
    AttendeeReport[JST Event Timestamp] <= CurrentDateTime,
    AttendeeReport[Role] = "Attendee"
)
```
きちんと整理してないから正確ではない可能性があるけれども、なんとなく雰囲気はつかめる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/7b9cd570-49ad-63b5-bf51-d6f33853cf84.png)

# 集計してみる
Teams ライブイベント は 計 6 件、それぞれに "出席者の活動レポート" (AttendeeReport.csv) がありまして Combine してあげるとよいわけです。おまとめしなくてもできるといえばできるけど後からの手間の方が問題だ。 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/cfd66b6a-1b65-a13d-768a-560fc5085eca.png)
なんだかわからないくてもよいのだけど、わかる人に伝わればそれで充分である。
## モデリング
テーブルひとつ 絶対ダメと言ったので、ジャンクディメンジョン上等でスタースキーマを構成したなど。まぁ、検算したいと思わないし。あとから別の集計をするかもしれないし。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/422b8e43-b66b-0803-d816-52d35c9e5c3f.png)

```text:DAX
# Atenndees = 
VAR CurrentTimeStamp = MAX( TimeStamp[JST Event Timestamp] )
RETURN
CALCULATE(
    CALCULATE(
        COUNTROWS( Attendees ),
        'Action'[Action] = "Joined"
    ) -
    CALCULATE(
        COUNTROWS( Attendees ),
        'Action'[Action] <> "Joined"
    ),
    Role[Role] = "Attendee",
    TimeStamp[JST Event Timestamp] <= CurrentTimeStamp
)
```
多分大丈夫だろうと思うメジャーを定義して。できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/0cafdb2c-cdc2-5808-3821-28ffa1bb827e.png)
これが見たかったし、出席者の活動レポート からこれくらいはできるだろうと思っていたし。
ただ、解決すべき問題があって、ビジュアルが表示されるまで 8 s とかかかっちゃうんですよね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/337ef57a-194c-41a9-1298-fe77b856a331.png)
秒単位のログなんでそのまま集計したら遅いわけです。秒単位で参加者の動向を見たいということではないのだから調整を試みる。

**すべての時間を分単位にまるめる。**

```text:PowerQuery
// Attendees
let
    Source = Table.Combine(
        { Track1, Track2, Track3, Track4, はじまりの会, おわりの会 }
    ),
    RemovedOtherColumns = Table.SelectColumns(
        Source,
        {"Session Id", "JST Event Timestamp", "Action", "Role", "Track"}
    ),
    InsertedJST2 = Table.AddColumn(
        RemovedOtherColumns,
        "JST Event Timestamp 2",
        each
            [JST Event Timestamp]
                - #duration( 0,0,0, Time.Second( [JST Event Timestamp] ) ),
            type datetime
    )
in
    InsertedJST2
```

```text:DAX
# Atenndees = 
VAR CurrentTimeStamp = MAX( 'TimeStamp 2'[JST Event Timestamp 2] )
RETURN
CALCULATE(
    CALCULATE(
        COUNTROWS( Attendees ),
        'Action'[Action] = "Joined"
    ) -
    CALCULATE(
        COUNTROWS( Attendees ),
        'Action'[Action] <> "Joined"
    ),
    Role[Role] = "Attendee",
    'TimeStamp 2'[JST Event Timestamp 2] <= CurrentTimeStamp
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/3298240f-2b47-ba29-732e-4d018308ba93.png)
見た目はほぼ変わらないけど、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/6b51fa12-543b-52cc-0a81-0cf7d245225f.png)
ほぼ気にならないパフォーマンスを得た。

# 思ったこと🙄
ログを見始めて仮であっても結果を目にしたときはウホウホするのに、すこし経つと興味が薄れていくのは普通かな？と思うようにしている。
**どんなモデリングしたら素早く集計されるかな? と当初考えていたのだけど、そんなことより効果的なことはたくさんあるんだろうね。**
# その他
- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)



```text
Measure = 
VAR SummarizedTable =
    ADDCOLUMNS(
        CALCULATETABLE(
            VALUES( Attendees[JST Event Timestamp] ),
            Attendees[Role] = "Attendee",
            ALL( Attendees )
        ),
        "@Attendees", 
        CALCULATE( COUNTROWS( Attendees ), Attendees[Action] = "Joined" )
        - CALCULATE( COUNTROWS( Attendees ), Attendees[Action] <> "Joined" )
    )
RETURN
    SUMX(
        FILTER(
            SummarizedTable,
            Attendees[JST Event Timestamp] <= SELECTEDVALUE( Attendees[JST Event Timestamp] )
        ),
        [@Attendees]
    )
```