วันศุกร์ที่ 27 พฤศจิกายน พ.ศ. 2552

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

วันพฤหัสบดีที่ 26 พฤศจิกายน พ.ศ. 2552

Copy Reporting Service objects , with RSScripter

การ Copy Reporting service objects ด้วย Tool RSScripter
ในกรณีที่ต้องการจะ Duplicate Report folder เพื่อทดสอบ หรือ เพื่อจุดประสงค์ใดก็ตาม
เช่นอาจจะ Copy report จาก Server01 ---> Server02
เครื่องมือนี้ถือว่ามีประสิทธิภาพยอดเยี่ยมเลยทีเดียวครับ โดยวิธีการก็คือ การ ดึง File Reports ที่อยู่บน Server
ที่ต้องการมาเก็บไว้ และ Generate Script ในการ Deploy ไปยัง Destination Server อีกทีครับ

มาถึงขั้นตอนกันดีกว่า ซึ่งก่อนที่จะใช้งาน Tool ตัวนี้ได้จะต้องลง RS.Exe ก่อนนะครับ (Microsoft System Center)
1 Download file ConfigMgr2007SDKv40.zip จาก Microsoft Download Center แล้วติดตั้ง
2 Download file RSScripter.zip จาก sqldbatips.com ครับ
3 Run RSScripter.exe แล้วทำทำการ Set ค่า Options ดังนี้ครับ
- Report server เลือก Version ให้ตรงกับ Server ที่ต้องการ (2000,2005,2008)
- Default script directory คือ Path ที่ต้องการให้เก็บ Script ไว้
- Server ให้ Set URL ให้ถูกต้อง เช่น http://MyReportServer/ReportServer/ReportService2005.asmx
- SQL2005 RS.EXE Location คือ Path ของ RS.EXE ว่าเราได้ Install ลงที่ ไหนขึ้นอยู่กับ Version SQL ที่เรา Install ด้วย ตัวอย่างเช่น C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RS.EXE
4 ทำการ GET Catalogs จะได้ผลลัพธ์ทำนองเดียวกับในรูปนะครับ


5 ทำการ Get script ได้เลยครับ
6 เมื่อได้ Script เรียบร้อยแล้ว ให้แก้ Batch file ตามต้องการคือ Destination Server ที่ต้องการ Deploy รายงานขึ้นครับ โดยแก้ในส่วนของ SET REPORTSERVER
เช่น SET REPORTSERVER=http://MyReportServer02/ReportServer
7 Run Batch ไฟล์ได้เลยครับ โดยจะมี Log file เพื่อใช้ตรวจสอบสถานะการทำงาน ก็เป็นอันสิ้นสุดกระบวนการ

Site Reference : http://www.sqldbatips.com/showarticle.asp?ID=62

วันพุธที่ 25 พฤศจิกายน พ.ศ. 2552

Analysis Services Stored Procedure (ASSP) 2

การเรียกใช้งาน ASSP
1 เปิด Microsoft SQL Server Management Studio ขึ้นมาก่อนเลย
2 New SQL ได้ทุกรูปแบไม่ว่าจะเป็น XMLA , MDX หรือ MDX Query

ตัวอย่างของการเรียกใช้งานเพื่อ Get information จาก SSAS ครับ


***GET ROLES FROM SSAS Database***List Roles ที่มีทั้งหมดใน Analysis Service
CALL ASSP.DiscoverXmlMetadata("\Database\Roles\Role");

ตัวอย่างผลลัพธ์ของ Query ครับ (จาก Adventure works)


***GET ROLES MEMBER***สำหรับดูสมาชิก(Windows user)ใน Roles คือ
call assp.discoverxmlmetadatafull("\Database\Roles\Role|Name\Members\Member")



*** GET DIMENSION ALLOWED SET  ***
ในกรณีที่ต้องการหาว่า  Role ต่าง ๆ มี Allowed/Denied set ใช้ Query นี้ครับ
ซึ่งจะนำไช้ประโยชน์ได้เยอะเลยครับ


call assp.discoverxmlmetadatafull("\Database\Dimensions\Dimension\DimensionPermissions\DimensionPermission
RoleID\AttributePermissions\AttributePermission
AllowedSet")




ซึ่งจริง ๆ แล้วอาจจะต้องเข้าใจพื้นฐานเรื่องของ XML บ้างสักเล็กน้อยนะครับ แนะนำให้ลอง Search หาด้วย Key word "ASSP" ดูครับ แล้วก็ "discoverxmlmetadatafull"
Site Reference : http://www.codeplex.com/wikipage?ProjectName=ASStoredProcedures&title=DiscoverXmlMetadata

Analysis Services Stored Procedure (ASSP) 1

การติดตั้ง ASSP ใน SSAS Server
หลายคนอาจจะยังเป็นมือใหม่ในการใช้งาน XMLA ซึ่งผมเองก็เช่นเดียวกันครับ
จริง ๆ แล้วที่ทำให้ได้รู้จักกับ ASSP ก็เนื่องมาการที่ต้องดำเนินการ Copy Roles Permission ของ Dimension ใน OLAP
ไปยัง CUBE Version ใหม่นั่นเอง ซึ่ง หากต้องมานั่งทำใหม่ทีละอย่างก็จะเป็นการยากมากมายครับ
ขั้นตอนก่อนที่เราจะสามารถใช้งานเจ้า ASSP ได้
1 Download ASSP.dll จาก Codeplex กันก่อนเลยครับ (อันนี้ขึ้นอยู่กับ Version ของ MSSQL 2005/2008)
  ASSP.dll New version has release 1.3.5 แก้ BUG  indicates that either the object has no columns
 
2 ทำการ Install ให้ Server สามารถเรียกใช้งานได้
โดยเมื่อ Download file เสร็จเรียบร้อยแล้ว ใน zip file จะมีไฟล์ชื่อ ASSP.dll อยู่
- เปิด Microsoft Sql Management Studio ขึ้นมา
- Connect ไปยัง Analysis service ที่ต้องการ
- Expand ในส่วนของ Assemblies คลิ๊กขาว เลือก New Assemblies


Set ค่าต่าง ๆ ดังนี้ครับ
- Type เลือกเป็น .NET Assembly
- Assembly Name = "ASSP"
- Permission เลือก Unrestricted
- Impersionation เลือก Default



Reference Site : http://www.codeplex.com/wikipage?ProjectName=ASStoredProcedures&title=DiscoverXmlMetadata