MDX export Result in Excel 2007

How to fetching result from MDX Command to Excel , MDX Export to Excel
วิธีการรัน MDX Query ผ่าน Excel โดยใช้ odc connection


มีหลายครั้งที่เราต้องการข้อมูลจากการ Execute MDX Query ผ่าน Microsoft SQL Server Management Studio แล้วต้องการ copy ข้อมูลนั้นไปยัง Excel แต่ในบางกรณีที่ข้อมูลมีปริมาณมาก ทำให้ไม่สามารถ Copy ออกมาไ้ด้  จึงได้ลอง Search หาวิธีจนพบว่าสามารถแก้ไข MDX Query ที่อยู่ใน odc File ที่ Excel generate ขึ้นมาได้ ทำให้สามารถ Execute MDX ที่ต้องการผ่าน Excel ได้เลย

C:\Documents and Settings\username\My Documents\My Data Sources\xxxx.odc

ขั้นตอนการ Setting
1  Add new connection ที่ connect ไปยัง OLAP CUBE ที่ต้องการ (ไฟล์นามสกุล odc)
2  เปิดไฟล์ connection ที่ได้ เพื่อแก้ไข Script  ดังนี้

จากเดิม

   <odc:CommandType>Cube</odc:CommandType>
   <odc:CommandText>Cube_TXNs</odc:CommandText>

แก้ไขเป็นดังนี้  

   <odc:CommandType>MDX</odc:CommandType>
   <odc:CommandText>Your MDX Query to be execute </odc:CommandText>

   


ทำการ Save file
ใน MDX Query นี้ห้ามมีการเว้นบรรทัด และเครื่องหมาย comment ใดๆทั้งสิ้นไม่เช่นนั้นตอน execute จะ error


3 เปิด excel ขึ้นมาเพื่อกำการ execute script โดยเลือกที่เมนู
   Data --> Existing connection
   เลือกไฟล์ connection ที่เราต้องการ จะมี popup เพื่อให้เืลือกรูปแบบ และ Destination ของขอ้มูลที่จะวางลงไป  ตอบ OK ได้เลย   แล้วรอผลจากการ Execute ได้เรยครับ

MDX Query : Getdate()

บทความนี้จะอธิบายถึงวิธีการใช้งาน Function Getdate() ใน MDX Query
ปกติหากจะหาวันที่ปัจจุบันใน SQL Server จะใช้ Function Getdate() และจัด Format ตามต้องการ
เช่น  SELECT Convert(char(8),Getdate(),112)
แต่หากเป็นใน MDX Quey แล้ว จะแตกต่างกันไปนิดหน่อยโดยใช้ Function NOW() แทนครับ
ดูตามตัวอย่าง และนำไปประยุกต์ใช้เอาตามชอบใจ


WITH
MEMBER [Measures].[CurYear] as -- Current Year (Full)
Format(NOW(), "yyyy" )
MEMBER [Measures].[CurYearShot] as -- Current Year (With 2 digits)
Format(NOW(), "yy" )
MEMBER [Measures].[CurMonthFull] as -- Current Month (Full)
Format(NOW(),"MMMM")
MEMBER [Measures].[CurMonthNum] as -- Current Month (With 2 digits)
Format(NOW(),"MM")
MEMBER [Measures].[CurDateNum] as -- Current Date (With 2 digits)
Format(NOW(),"dd")
MEMBER [Measures].[CurDateFull] as -- Current Date
Format(NOW(),"d")
MEMBER [Measures].[CurDateFullFormat] as -- Current Date (With custom format)
Format(NOW(),"dd/MM/yyyy")
MEMBER [Measures].[PrvYear] as -- Previous Year
Format(DATEADD("yyyy",-1,NOW()),"yyyy")
MEMBER [Measures].[NxtYear] as -- Next Year
Format(DATEADD("yyyy",1,NOW()),"yyyy")
MEMBER [Measures].[PrvMonth] as -- Previous Month
Format(DATEADD("m",-1,NOW()),"MMMM")
MEMBER [Measures].[NxtMonth] as -- Next Month
Format(DATEADD("m",1,NOW()),"MMMM")
MEMBER [Measures].[PrvDate] as -- Previous Day
Format(DATEADD("d",-1,NOW()),"dd")
MEMBER [Measures].[NxtDate] as -- Next Day
Format(DATEADD("d",1,NOW()),"dd")
MEMBER [Measures].[YearDiff] as -- Difference between 2 years
DATEDIFF("yyyy",DATEADD("yyyy",-1,NOW()),DATEADD("yyyy",3,NOW()))
MEMBER [Measures].[ConvertStrToDate] as -- Convert String to Date
Cdate("1/28/2010")
select {[Measures].[CurYear],[Measures].[CurYearShot],[Measures].[CurMonthFull],[Measures].[CurMonthNum],
[Measures].[CurDateNum],[Measures].[CurDateFull],[Measures].[CurDateFullFormat],
[Measures].[PrvYear],[Measures].[NxtYear],[Measures].[PrvMonth],[Measures].[NxtMonth],
[Measures].[PrvDate],[Measures].[NxtDate], [Measures].[YearDiff],
[Measures].[ConvertStrToDate]} ON 0
FROM [Adventure Works]

MDX Query : Select Except members

MDX Query syntax for "NOT IN" or "Except" condition
มีทริ๊กเล็ก เกี่ยวกับการใช้ MDX ในการ Selelect ข้อมูลให้ได้ตามต้องการ  ในที่นี้หากเทียบกับใน SQL
แล้วก็คือการใช้ IN หรือ NOT IN นั่นเอง 
ตัวอย่าง MDX สำหรับ Condition NOT IN : Using AdventureWork

---------------Normal condition -------------------
ตัวอย่าง Query สำหรับการเรียกดูยอดขายของสินค้าทุกประเภท

SELECT [Measures].[Sales Amount] on 0,
[Product].[Category].[Category] ON 1
FROM [Adventure Works]

Result

              Sales Amount

Accessories   $1,272,057.89
Bikes         $94,620,526.21
Clothing      $2,117,613.45
Components    $11,799,076.66

-------- EXCEPT or NOT IN condition  ---------

SELECT [Measures].[Sales Amount] on 0,
-{[Product].[Category].&[1],[Product].[Category].&[4]} on 1
FROM [Adventure Works]

Result
             Sales Amount
Clothing     $2,117,613.45
Components   $11,799,076.66

จากตัวอย่างจะเห็นว่าผลลัพธ์เหลือแค่ 2 rows คือ  Clothing และ Component
Categories ที่หายไปคือ Accessories และ Bikes ซึ่งเกิดจากการที่เราได้ใส่ Except / Not in
เข้าไปใน Query  นั่นเอง ซึ่งรูปแบบคือ   -{member set}

MDX Query : Dimension Usage with(out) summary row

MDX Query Exclude summary row.
ปกติเวลาเราสร้าง MDX Query ขึ้นมา คงไม่มีใครจะพิมพ์เองทั้งหมดนะครับ เนื่องจากมี Tools ช่วยอยู่แล้ว
ทำให้เราสามารถ Drag & Drop Object ได้โดยสะดวก
บทความนี้จะมี Trick เล็ก ๆ น้อย ๆ เกี่ยวกับการใช้งาน Dimension ใน MDX Query ครับ
โดยจะขอยกตัวอย่างจาก Adventure Work อีกเช่นเคย
ซึ่งตามหัวข้อของบทความก็คือ การ Select ข้อมูลโดยมีและไม่มี Summary record ครับ
ให้สังเกตส่วนที่ Hiligh นะครับเพราะเป็น Level ของ Dimension ส่วนที่แตกต่างกัน
เมื่อนำมาใช้ก็จะได้ผลตามข้อ 1 และ 2 ซึ่งจะเป็นประโยชน์ในการทำ Report ต่อไป

1 MDX Query with summary record
SELECT {[Measures].[Internet Order Quantity],[Measures].[Internet Sales Amount]} on 0
,[Sales Territory].[Sales Territory Country].MEMBERS on 1
FROM [Adventure Works]
WHERE
[Sales Territory].[Sales Territory Group].&[Europe]

2 MDX Query without summary record
SELECT {[Measures].[Internet Order Quantity],[Measures].[Internet Sales Amount]} on 0
,[Sales Territory].[Sales Territory Country].[Sales Terriotory Country].MEMBERS on 1
FROM [Adventure Works]
WHERE
[Sales Territory].[Sales Territory Group].&[Europe]

MDX Query : Where clause with dimensions slice & dice

บทความนี้จะกล่าวถึง Basic MDX Query ที่อาจทำให้เรา Query ข้อมูลผิดพลาด
ในความเคยชินของผู้ใช้งาน RDBMS ทั่วไปนั้นแน่นอนว่าการ Filter ข้อมูลก็คือการใส่ เงื่อนไขหลัง WHERE
ใน SQL Statement ยกตัวอย่างง่าย ๆ ของการหายอดขายของ ภูมิภาคยุโรป ที่มีชื่อประเทศตั้งแต่ฝรั่งเศษ - เยอรมันนี

SELECT sum(sales_amount)
FROM sales
WHERE Region ='EUROPE'
AND Country Between 'France' AND 'Germany'

ซึ่งก็จะได้ข้อมูลถูกต้องแน่นอนครับ แต่หากเราจะประยุกต์ Query นี้เพื่อใช้กับ MDX แล้วผลลัพธ์จะไม่ได้ตามต้องการ
ในที่นี้ผมขอยกตัวอย่างจาก Adventure Works ของ Microsoft นะครับ
Query ในแบบที่น่าจะเป็น


SELECT {[Measures].[Internet Order Quantity],[Measures].[Internet Sales Amount]}
on 0
FROM [Adventure Works]
WHERE
(
[Sales Territory].[Sales Territory Group].&[Europe]
,{[Sales Territory].[Sales Territory Country].&[France]: [Sales Territory].[Sales Territory Country].&[Germany]}
)


ผลลัพธ์ที่ได้คือ ให้สังเกตตรงผลรวมด้านบนไว้นะครับ
จะเห็นว่า Internet Order Quantity = 18,089

ส่วนอันนี้เป็น Query ที่ให้ผลลัพธ์ถูกต้อง (มีหลายรูปแบบนะครับ)
SELECT {[Measures].[Internet Order Quantity],[Measures].[Internet Sales Amount]}
on 0
,[Sales Territory].[Sales Territory Country].members on 1
FROM
(SELECT
{[Sales Territory].[Sales Territory Country].&[France]:
[Sales Territory].[Sales Territory Country].&[Germany]} ON 0
FROM [Adventure Works] WHERE
[Sales Territory].[Sales Territory Group].&[Europe])


ผลลัพธ์ที่ได้ จะเห็นว่ายอดลดลงเหลือ 11,183 เท่านั้น อาจสงสัยว่าแล้วยอด 18,089 มาจากไหน
คำตอบคือเป็นยอดทั้งหมดของยุโรปครับ ซึ่งใน Database มีอยู่ 3 ประเทศด้วยกันตามนี้

SELECT {[Measures].[Internet Order Quantity],[Measures].[Internet Sales Amount]}
on 0
,
[Sales Territory].[Sales Territory Country].MEMBERS
on 1
FROM [Adventure Works]
WHERE
[Sales Territory].[Sales Territory Group].&[Europe]




เหตุผลที่ Query ในรูปแบบของ RDBMS เกิดข้อผิดพลาดขึ้นในกรณีนี้ก็คือเรื่อง Heirachy ของ Dimension ครับ
กล่าวคือใน Level ที่อยู่สูงกว่าจะมียอด Summary ของ Measurement เก็บไว้
ไม่ว่าเราจะใช้ Where ในการ Filter ข้อมูลใน Level ที่ต่ำลงมาก็จะไม่ทำให้ข้อมูลใน Parent level เปลี่ยนไปตามเงื่อนไข (ยกเว้นการทำ Sub Selection)
ดังนั้นต้องระวังในข้อนี้ให้ดีครับ เพราะสิ่งเหล่านี้จะพื้นฐานของการนำไปสร้างรายงานสำหรับ User ต่อไป
ในเรื่องของการรับ Parameter