สร้าง Calculations YTD QTD และ MTD บน OLAP Cubes

การทำ BI Report ที่หลายหน่วยงานมักถามถึงกันบ่อยเช่น การคำนวณ Measurement ที่สามารถดูเปรียบเทียบในรูปแบบ YTD, QTD, และ MTD กับมุมมอง (Dimension) ต่างๆที่เราสนใจ สมมุติว่าเราต้องการดู Report ยอดขาย (Sales Amount) ที่เป็น YTD โดยเปรียบเทียบตาม Product Category ในปี 2013 เปรียบเทียบกัน ดังตัวอย่างในรูป สามารถสร้าง cube ได้อย่างไร

การสร้าง Cube ให้รองรับ Measurement YTD, QTD, MTD (ผมถือว่าเพื่อนๆมีพื้นฐานอยู่แล้ว) จึงอธิบายเฉพาะจุดที่สำคัญ

จุดที่ 1 ต้องทำ Dimension Date ให้มี Attribute Relationships ดังนี้
New Dimension จากนั้นเลือก Generate a time table in the data source เนื่องจากว่าเราจะให้โปรแกรม generate date ให้เรา First calendar day : เลือกปีเริ่มต้นที่จะทำการ Generate ข้อมูล Dimension Date ในที่นี้ผมเริ่มต้นที่ปี 2010
Last calendar day : เลือก Generate Date ถึงปี 2016
Time Periods : ในที่นี้ผมเลือก Year -> Quarter -> Month -> Date
ทำการคลิก Next …
คลิกเลือก Fiscal calendar แล้วทำการคลิก next
ตั้งชื่อ Dimension และทำการติ๊กเครื่องหมายถูก Generate schema now ซึ่งจะเป็นการสร้าง table บน database (table จะมีชื่อตาม Name ที่เราตั้งตามรูปด้านล่าง) และสร้างใน data source views จากนั้นทำการคลิก Finish
ทำการคลิก Next
ณ จุดนี้ ผมเลือกที่จะสร้าง Dimension Table “Dim DateTime” บน data source view ที่ผมมีอยู่ โดยเลือก Use existing data source view จากนั้นทำการคลิก Next
ถ้ามี popup ถาม user และ password ก็ให้ใส่ user password ที่ใช้ connect database ของเราแล้วคลิก OK
ทำการคลิก Next
โปรแกรมก็จะทำการ Generate Table “Dim DateTime” ลงไปที่ Database และ Data source views ดังรูปด้านล่าง
หลังจากที่สร้างเสร็จเราลองมา View ที่ Tab Attribute Relationships จะเห็นความสัมพันธ์ดังรูป
ส่วนใน Data source view ก็จะมี table เพิ่มเข้ามา จากนั้นเราก็ทำการลาก Relational กับ Fact table ดังรูปจะใช้ฟิลล์ order date ลิ้งไปยัง pk_date
ทำการ Process Dimension Date Time ที่เรา Generate ขึ้น
ทำการคลิกขวา Add Cube Dimension เลือก Dimension “Dim DateTime”

จุดที่ 2 การสร้าง Measurement ที่ใช้คำนวณ YTD โดยไปที่ Tab Calculations -> คลิกขวา New Calculated Member
ตั้งชื่อ Measurement Name : [Sales Amount YTD]
ที่ช่อง Expression ใส่ชุดคำสั่ง MDX ซึ่งจุดนี้สำคัญเพราะจะเป็นการ calculate measure ด้วย function YTD ดังนี้

SUM(YTD([Dim DateTime].[Year - Quarter - Month - Date].currentmember), [Measures].[Sales Amount])

จากนั้นทำการ Process Cube
ต่อไปเราลองมาทำการ View ข้อมูลดูโดยไปที่ Tab Browser แล้วทำการลาก Dim DateTime และ Measurement ของ [Sales Amount], [Sales Amount YTD] มาเปรียบเทียบดู จากรูปด้านล่างจะเห็นว่า เมื่อเรา drill down ไประดับ Quarter การคำนวณของ Measure ของ [Sales Amount YTD] ก็จะแสดงผลคำนวณยอดขาย incremental ไปในระดับ Quarter

และเมื่อทำการ drill down ลงไปถึงระดับ Month ยอดขายที่แสดงก็จะเป็นแบบ incremental ของยอดขายทีละเดือนไปเรื่อยๆ ซึ่งจะทำให้เราเห็น YTD ในระดับเดือนได้ดังรูป

ดังนั้น หากเราจะลองใช้ Excel เชื่อมต่อไปยัง Cube และลองดูยอดขายที่เป็น [Sales Amount] และ [Sales Amount YTD] ในมุมมอง Product Category แสดงได้ดังรูป หรือจะ Pivot ดูในแกนอื่นๆ ยอด [Sales Amount YTD] ก็จะแปรผันไปตามแต่ละมุมมอง(Dimension)