이 블로그에서는, [Start_Date] column 과 [End_Date] column 이 존재하는 table을 분석할 때, 나는 어떤 생각을 가지고 Power BI analysis를 시작하는지에 대해서 공유하고자 한다.
DAX measures를 작성해서 시작하는 방법과, 새로운 테이블(Calculated table)을 만들어서 시작하는 방법, 이렇게 총 두가지 방법 중에서 하나를 선택한다.
시작날짜와 종료날짜가 있는 다음과 같은 프로젝트 테이블을 예로 들어서 설명을 시작하겠다. 아래의 형태를 가지고 있는 것들이, 호텔 check-in & check-out, 또는 인사정보 (입사일/퇴사일) 등등의 경우가 있으며, 이 블로그에서 사용한 방법들이 응용 가능하다.
Project table:
Calendar table:
Data model:
위의 그림 Data model에서 보이듯이 relationship을 만들이 않았다.
1. 새로운 테이블을 작성하여 분석하기
새로운 calculated table을 작성할때, 각각의 프로젝트별로 Start_date to End_date 까지의 모든 날짜가, 새로운 table 안에서 하나의 [Project date] column에 존재하고, 이것이 Calendar table [Date] column과 연결이 된다면, 아주 간단한 Data model이 만들어진다.
이렇게 하기 위해서 사용게 될 DAX function은 GENERATE이며, 다음과 같이 작성하면 원하는 결과가 만들어진다.
SUMMARIZE DAX function을 사용해서 필요한 columns만을 새로운 table에 남게한다.
Project_NewTable = SUMMARIZE ( GENERATE ( Project, SELECTCOLUMNS ( FILTER ( DISTINCT ( 'Calendar'[Date] ), 'Calendar'[Date] >= Project[Start date] && IF ( NOT ISBLANK ( Project[End date] ), 'Calendar'[Date] <= Project[End date], 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) ), "Project date", 'Calendar'[Date] ) ), Project[Project ID], Project[Daily cost], [Project date] )
아래와 같이 relationship을 작성한다.
[Daily cost sum calc table] measure를 아래와 같이 작성하여서 Matrix visualization을 만들 수 있다.
Daily cost sum calc table measure: =
SUM ( Project_NewTable[Daily cost] )
위에서 보듯이, [Daily cost sum calc table] measure는 아주 간단하게 작성되었다. 이유는, 새로운 table을 만들었기 때문이다.
여기서 한가지 생각이 들게 된다. 고작 2년 동안의 information을 분석하기 위해서, 기존에 존재하고있던 9 rows X 4 columns 의 아주 작은 size의 table을 기어이 1069 rows X 3 columns의 보다 더 큰 size의 테이블로 새롭게 만들 필요가 있을까 하는 의문이다. 이 부분에 대해서 내가 어떻게 생각하는지에 대해서는 블로그 마지막에 표현해 보기로 하고, 다음은 두번째 방법인 DAX measure를 사용해서 같은 결과를 만들어 보도록 하겠다.
2. DAX measure를 작성하여 분석하기
새로운 calculated table이 작성되기 이전상태로 돌아가서 아래의 data model안에서만 원하는 결과를 만들어보겠다.
Project cost sum measure: = SUMX ( ADDCOLUMNS ( DISTINCT('Calendar'[Date]), "@projectcost", CALCULATE ( SUMX ( FILTER ( Project, IF ( NOT ISBLANK ( Project[End date] ), Project[Start date] <= MAX ( 'Calendar'[Date] ) && Project[End date] >= MIN ( 'Calendar'[Date] ), Project[Start date] <= MAX ( 'Calendar'[Date] ) ) ), Project[Daily cost] ) ) ), [@projectcost] )
위에 나와있는 두가지 방법을 모두 사용해서 분석을 시작한다. 두 가지 방향으로 각각을 끝까지 진행하지는 않는다. 시작을 하고나서 딱 여기까지만 두가지 방법으로 한다. 그리고나서 바로 질문을 던진다. 혹시 어떻게 진행하는게 좋은지...
이런 질문을 하는 대상자는 end user일수도 있고, data warehouse admin일수도 있고, product manager일수도 있다.
특히, data warehouse admin한테 질문을 할 경우에는, 반드시 물어봐야 할 것이 있다. 첫번째의 경우에, 내가 새롭게 만들어낸 Project_NewTable을 본적이 있느냐고...본적이 있다고 말하는 경우가 대부분이다. 대부분의 경우에는, 1069 rows X 3 columns 형태의 구조로 source system에 기록이 되고, 이 형태 그대로 data warehouse에 저장이 된다. 이것을 예전에 누군가의 요청으로 인하여 9 rows X 4 columns 의 형태로 변형하여서 가지고 있는 경우가 대부분이다. 1069 rows X 3 columns의 구조가 가지고 있는 모든 정보가, 9 rows X 4 columns의 구조로도 모두 표현이 되기 때문에, 어느 순간부터는 source system에서 가지고 온 그대로의 형태는 보관만 하고, Power BI에서, 또는 다른 analysis tools 에서, extract 시점부터는 당연하다는듯이 9 rows X 4 columns의 구조로 공유하고 있는 것이다. 나의 첫번째 방법은, source system이 가지고 있는 그대로의 구조를 사용하는 것이 data model 관점에서 볼 때 아주 간단한 DAX measure 작성만으로도 원하는 분석자료를 작성할 수 있음을 보여줌으로서, 원래 가지고 있는 1069 rows X 3 columns의 table을 나에게 내놓으라는 의미이다.
만일, 어느 누구도 source system에서 어떻게 기록하고 있는지 본 적이 없어서 1069 rows X 3 columns 형태의 구조를 알 지 못한다면, 그래서 오로지 9 rows X 4 columns의 구조만을 나에게 보여줄 수 있다면, 어쩔 수 없이, 내가 새롭게 만들어 내서 (첫번째 방법) 간단한 DAX measure 작성하는 방법, 또는 DAX measure가 조금 복잡해 지더라도 추가로 calculated table을 만들지 않고 같은 결과를 보여주는 방법, 이 두가지 중에서 하나만 선택하면 된다.
혹시라도 많은 량의 data 때문에 performance가 문제가 된다면, DAX measure를 수정해 가면서 보완하면 된다.
각각의 pbix file은 아래에 첨부하였다.
Start date column 과 End date column이 있는 table을 분석할 때에 내가 주로 사용하는 두가지 방법이며, 이것이 Power BI를 보다 더 재미있게 사용하는데 도움이 되기를 바란다.
Comments