พารามิเตอร์คิวรี M แบบไดนามิกใน Power BI Desktop

บทความนี้อธิบายวิธีการสร้างและทํางานกับพารามิเตอร์คิวรี M แบบไดนามิกใน Power BI Desktop ด้วยพารามิเตอร์คิวรี M แบบไดนามิก ผู้สร้างแบบจําลองสามารถกําหนดค่าตัวกรองหรือตัวแบ่งส่วนข้อมูลที่ผู้ชมรายงานสามารถใช้สําหรับพารามิเตอร์คิวรี M ได้ พารามิเตอร์คิวรี M แบบไดนามิกช่วยให้ผู้สร้างแบบจําลองสามารถควบคุมการเลือกตัวกรองได้มากขึ้นเพื่อรวมเข้ากับคิวรีแหล่งข้อมูล DirectQuery

ผู้สร้างแบบจําลองเข้าใจความหมายที่ตั้งใจไว้ของตัวกรอง และบ่อยครั้งที่ทราบวิธีการเขียนคิวรีที่มีประสิทธิภาพกับแหล่งข้อมูล ด้วยพารามิเตอร์คิวรี M แบบไดนามิก ผู้สร้างแบบจําลองสามารถมั่นใจได้ว่าการเลือกตัวกรองจะรวมเข้ากับคิวรีต้นทางที่จุดที่เหมาะสมเพื่อให้ได้ผลลัพธ์ที่ตั้งใจไว้พร้อมประสิทธิภาพสูงสุด พารามิเตอร์คิวรี M แบบไดนามิกอาจมีประโยชน์อย่างยิ่งสําหรับการเพิ่มประสิทธิภาพการทํางานของคิวรี

[! หมายเหตุ] ตัวอย่างในบทความนี้ใช้ทั้ง Kusto และ T-SQL เพื่อสาธิตพารามิเตอร์คิวรี M แบบไดนามิก แนวคิดนี้นําไปใช้กับแหล่งข้อมูล DirectQuery ที่ได้รับการสนับสนุน เมื่อเชื่อมต่อกับฐานข้อมูล KQL (Real-Time Intelligence) Fabric ให้ใช้ตัวเชื่อมต่อ Azure Data Explorer (Kusto) — ใช้รูปแบบเดียวกัน

ดู Sujata อธิบายและใช้พารามิเตอร์คิวรี M แบบไดนามิกในวิดีโอต่อไปนี้ แล้วลองใช้ด้วยตนเอง

ข้อกำหนดเบื้องต้น

ในการทํางานผ่านขั้นตอนเหล่านี้ คุณต้องมีคิวรี M ที่ถูกต้องที่ใช้ตาราง DirectQuery อย่างน้อยหนึ่งตาราง

สร้างและใช้พารามิเตอร์แบบไดนามิก

ตัวอย่างต่อไปนี้ส่งผ่านค่าเดียวผ่านไปยังพารามิเตอร์แบบไดนามิก

เพิ่มพารามิเตอร์

1. ใน Power BI เดสก์ท็อป ให้เลือก Home>แปลงข้อมูล>แปลงข้อมูล เพื่อเปิด ตัวแก้ไข Power Query

2. ใน ตัวแก้ไข Power Query ให้เลือก พารามิเตอร์ใหม่ ภายใต้ จัดการพารามิเตอร์ ใน Ribbon

   

3. ในหน้าต่าง จัดการพารามิเตอร์ ให้กรอกข้อมูลเกี่ยวกับพารามิเตอร์ สําหรับข้อมูลเพิ่มเติม ดูการสร้างพารามิเตอร์

   

4. เลือก ใหม่ เพื่อเพิ่มพารามิเตอร์เพิ่มเติม

   

5. เมื่อคุณเพิ่มพารามิเตอร์เสร็จแล้ว เลือกตกลง

อ้างอิงพารามิเตอร์ในคิวรี M

1. เมื่อคุณสร้างพารามิเตอร์แล้ว คุณสามารถอ้างอิงข้อมูลเหล่านั้นในคิวรี M ได้ เมื่อต้องการปรับเปลี่ยนแบบสอบถาม M ในขณะที่คุณเลือกแบบสอบถามไว้ ให้เปิด เครื่องมือแก้ไขขั้นสูง

   

2. อ้างอิงพารามิเตอร์ในคิวรี M ตามที่ไฮไลต์เป็นสีเหลืองในรูปต่อไปนี้:

   

   เคล็ดลับ

   หากต้องการอ้างอิงพารามิเตอร์ในโค้ด M ให้ใช้ชื่อของพารามิเตอร์โดยตรง (เช่น CountryParameter) สําหรับพารามิเตอร์ข้อความ คุณสามารถเชื่อมต่อได้ดังที่แสดงด้านบน สําหรับพารามิเตอร์วันที่หรือตัวเลข ให้ตรวจสอบให้แน่ใจว่าชนิดข้อมูลตรงกัน

   ตัวอย่าง T-SQL (พารามิเตอร์เดียว)

   let
         Source = Sql.Database("server", "database", [Query="SELECT * FROM Sales WHERE Country = '" & CountryParameter & "'"])
   in
         Source
   

3. เมื่อคุณแก้ไขคิวรีเสร็จแล้ว ให้เลือก เสร็จสิ้น

สร้างตารางของค่า

สร้างตารางสําหรับแต่ละพารามิเตอร์ด้วยคอลัมน์ที่มีค่าที่เป็นไปได้ที่สามารถตั้งค่าได้แบบไดนามิกตามการเลือกตัวกรอง ในตัวอย่างนี้ คุณต้องการ StartTime ให้พารามิเตอร์ และ EndTime เป็นแบบไดนามิก เนื่องจากพารามิเตอร์เหล่านี้จําเป็นต้องมี Date/Time พารามิเตอร์ คุณจึงสร้างอินพุตที่เป็นไปได้เพื่อตั้งค่าวันที่สําหรับพารามิเตอร์แบบไดนามิก

1. ใน Ribbon Power BI เดสก์ท็อป ภายใต้ Modeling ให้เลือก New table

   

2. สร้างตารางสําหรับค่าของ StartTime พารามิเตอร์ ตัวอย่างเช่น:

   StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

   

3. สร้างตารางที่สองสําหรับค่าของ EndTime พารามิเตอร์ ตัวอย่างเช่น:

   EndDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

   

   หมายเหตุ

   ใช้ชื่อคอลัมน์ที่ไม่ได้อยู่ในตารางจริง ถ้าคุณใช้ชื่อเดียวกันกับคอลัมน์ตารางจริง ค่าที่เลือกจะใช้เป็นตัวกรองในคิวรี

ผูกเขตข้อมูลกับพารามิเตอร์

หลังจากที่คุณสร้างตารางที่มี Date เขตข้อมูลแล้ว คุณสามารถผูกเขตข้อมูลแต่ละรายการกับพารามิเตอร์ได้ การผูกเขตข้อมูลกับพารามิเตอร์หมายความว่าเมื่อค่าเขตข้อมูลที่เลือกเปลี่ยนแปลง ค่าจะส่งผ่านไปยังพารามิเตอร์และอัปเดตคิวรีที่อ้างอิงพารามิเตอร์

1. เมื่อต้องการผูกฟิลด์ ในมุมมอง Power BI เดสก์ท็อป โมเดล ให้เลือกฟิลด์ที่สร้างขึ้นใหม่ และในบานหน้าต่าง Properties ให้เลือก Advanced

   หมายเหตุ

   ชนิดข้อมูลของคอลัมน์ควรตรงกับชนิดข้อมูลพารามิเตอร์ M

   

2. เลือกรายการแบบเลื่อนลงภายใต้ ผูกกับพารามิเตอร์ และเลือกพารามิเตอร์ที่คุณต้องการผูกกับเขตข้อมูล:

   

   เนื่องจากตัวอย่างนี้สําหรับการตั้งค่าพารามิเตอร์เป็นค่าเดียว ให้เก็บ การตั้งค่าแบบเลือก ได้หลายรายการเป็น ไม่ ซึ่งเป็นค่าเริ่มต้น:

   

   ถ้าคุณตั้งค่าคอลัมน์ที่ถูกแมปเป็น ไม่ สําหรับ การเลือกหลายรายการ คุณต้องใช้โหมดเลือกเดียวในตัวแบ่งส่วนข้อมูล หรือจําเป็นต้องเลือกเพียงครั้งเดียวในการ์ดตัวกรอง

   ถ้ากรณีการใช้งานของคุณจําเป็นต้องมีการส่งผ่านค่าหลายค่าไปยังพารามิเตอร์เดียว ให้ตั้งค่าตัวควบคุมเป็น ใช่ และตรวจสอบให้แน่ใจว่าคิวรี M ของคุณได้รับการตั้งค่าให้ยอมรับหลายค่า นี่คือตัวอย่างสําหรับ RepoNameParameterซึ่งอนุญาตให้มีหลายค่า:

   

3. ทําซ้ําขั้นตอนเหล่านี้ถ้าคุณมีเขตข้อมูลอื่นๆ ที่จะผูกกับพารามิเตอร์อื่นๆ

   

ขณะนี้คุณสามารถอ้างอิงเขตข้อมูลนี้ในตัวแบ่งส่วนข้อมูลหรือเป็นตัวกรอง:

เปิดใช้งานเลือกทั้งหมด

ในตัวอย่างนี้ แบบจําลองเดสก์ท็อป Power BI มีเขตข้อมูลที่เรียกว่า Country ซึ่งเป็นรายการของประเทศ/ภูมิภาคที่ผูกไว้กับพารามิเตอร์ M ที่เรียกว่า countryNameMParameter พารามิเตอร์นี้เปิดใช้งานสําหรับการเลือกหลายรายการ แต่ไม่ได้เปิดใช้งานสําหรับ เลือกทั้งหมด เพื่อให้สามารถใช้ ตัวเลือก เลือกทั้งหมด ในตัวแบ่งส่วนข้อมูลหรือบัตรตัวกรอง ให้ทําตามขั้นตอนที่เพิ่มเข้ามาต่อไปนี้:

เมื่อต้องการเปิดใช้งาน เลือกทั้งหมด สําหรับ ประเทศ:

1. ใน คุณสมบัติขั้นสูง สําหรับ ประเทศ ให้เปิดใช้งานการ สลับเลือกทั้งหมด ซึ่งเปิดใช้งานการป้อนข้อมูลเลือก ค่า ทั้งหมด แก้ไขค่า เลือกทั้งหมด หรือจดค่าเริ่มต้น

   

   ค่า เลือกทั้งหมด จะส่งผ่านไปยังพารามิเตอร์เป็นรายการที่ประกอบด้วยค่าที่คุณกําหนดไว้ ดังนั้น เมื่อคุณกําหนดค่านี้หรือใช้ค่าเริ่มต้น ตรวจสอบให้แน่ใจว่าค่าไม่ซ้ํากัน และไม่มีอยู่ในเขตข้อมูลที่ผูกไว้กับพารามิเตอร์

2. เปิดใช้ ตัวแก้ไข Power Query เลือกแบบสอบถาม แล้วเลือก เครื่องมือแก้ไขขั้นสูง แก้ไขคิวรี M เพื่อใช้ค่า เลือกทั้งหมด เพื่ออ้างอิงถึงตัวเลือก เลือกทั้งหมด

   

3. ใน เครื่องมือแก้ไขขั้นสูง ให้เพิ่มนิพจน์บูลีนที่ประเมินเป็น true ถ้าพารามิเตอร์ถูกเปิดใช้งานสําหรับ Multi-select และประกอบด้วย เลือกค่าทั้งหมด และส่งกลับ false:

   

4. รวมผลลัพธ์ของเลือก นิพจน์บูลีนทั้งหมด ลงในคิวรีแหล่งข้อมูล ตัวอย่างมีพารามิเตอร์คิวรีบูลีนในคิวรีต้นทางที่เรียกว่า includeAllCountries ที่ถูกตั้งค่าเป็นผลลัพธ์ของนิพจน์บูลีนจากขั้นตอนก่อนหน้า คุณสามารถใช้พารามิเตอร์นี้ในส่วนคําสั่งตัวกรองในคิวรี ดังกล่าว false สําหรับตัวกรองบูลีนกับชื่อประเทศหรือภูมิภาคที่เลือก และ true ไม่มีตัวกรองได้อย่างมีประสิทธิภาพ

   

5. เมื่อคุณอัปเดตคิวรี M ของคุณเป็นบัญชีสําหรับค่า เลือกทั้งหมด ใหม่ คุณสามารถใช้ฟังก์ชัน เลือกทั้งหมด ในตัวแบ่งส่วนข้อมูลหรือตัวกรองได้

   

สําหรับการอ้างอิง นี่คือคิวรีแบบเต็มสําหรับตัวอย่างก่อนหน้า:

let
    selectedcountryNames = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      Text.Combine({"'", Text.Combine(countryNameMParameter, "','") , "'"})
    else
      Text.Combine({"'" , countryNameMParameter , "'"}),

    selectAllCountries = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      List.Contains(countryNameMParameter, "__SelectAll__")
    else
      false,

    KustoParametersDeclareQuery = Text.Combine({"declare query_parameters(", 
                                 "startTimep:datetime = datetime(", DateTime.ToText(StartTimeMParameter, "yyyy-MM-dd hh:mm"), "), " , 
                                 "endTimep:datetime = datetime(", DateTime.ToText(EndTimeMParameter, "yyyy-MM-dd hh:mm:ss"), "), ",   
                                 "includeAllCountries: bool = ", Logical.ToText(selectAllCountries) ,",",
                                 "countryNames: dynamic = dynamic([", selectedcountryNames, "]));" }),

   ActualQueryWithKustoParameters = 
                                "Covid19
                                | where includeAllCountries or Country in(countryNames)
                                | where Timestamp > startTimep and Timestamp < endTimep
                                | summarize sum(Confirmed) by Country, bin(Timestamp, 30d)",

    finalQuery = Text.Combine({KustoParametersDeclareQuery, ActualQueryWithKustoParameters}),

    Source = AzureDataExplorer.Contents("help", "samples", finalQuery, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Timestamp", "Date"}, {"sum_Confirmed", "Confirmed Cases"}})
in
    #"Renamed Columns"

ความเสี่ยงด้านความปลอดภัยที่อาจเกิดขึ้น

ผู้อ่านรายงานที่สามารถตั้งค่าแบบไดนามิกสําหรับพารามิเตอร์คิวรี M อาจสามารถเข้าถึงข้อมูลเพิ่มเติมหรือทริกเกอร์การปรับเปลี่ยนไปยังระบบต้นทางโดยใช้ การโจมตีการฉีด ความเป็นไปได้นี้ขึ้นอยู่กับวิธีที่คุณอ้างอิงพารามิเตอร์ในคิวรี M และค่าที่คุณส่งผ่านไปยังพารามิเตอร์

ตัวอย่างเช่น คุณมีคิวรี Kusto ที่มีการกําหนดพารามิเตอร์ที่สร้างขึ้นดังนี้:

Products
| where Category == [Parameter inserted here] & HasReleased == 'True'
 | project ReleaseDate, Name, Category, Region

ไม่มีปัญหากับผู้ใช้ที่เป็นมิตรที่ส่งผ่านค่าที่เหมาะสมสําหรับพารามิเตอร์ ตัวอย่างเช่น Games:

| where Category == 'Games' & HasReleased == 'True'

อย่างไรก็ตาม ผู้โจมตีอาจสามารถส่งผ่านค่าที่ปรับเปลี่ยนคิวรีเพื่อเข้าถึงข้อมูลเพิ่มเติม ตัวอย่างเช่น 'Games'//:

Products
| where Category == 'Games'// & HasReleased == 'True'
| project ReleaseDate, Name, Category, Region

ในตัวอย่างนี้ ผู้โจมตีสามารถเข้าถึงข้อมูลเกี่ยวกับเกมที่ยังไม่ได้เผยแพร่ โดยการเปลี่ยนแปลงส่วนของแบบสอบถามลงในข้อคิดเห็น

ลดความเสี่ยง

เมื่อต้องการลดความเสี่ยงด้านความปลอดภัย ให้หลีกเลี่ยงการเรียงต่อกันของค่าพารามิเตอร์ M ภายในคิวรี แทนที่จะใช้ค่าพารามิเตอร์เหล่านั้นในการดําเนินการ M ที่พับไปยังคิวรีแหล่งข้อมูลเพื่อให้กลไกจัดการ M และตัวเชื่อมต่อสร้างคิวรีสุดท้าย

ถ้าแหล่งข้อมูลสนับสนุนการนําเข้ากระบวนงานที่เก็บไว้ ให้พิจารณาการจัดเก็บตรรกะคิวรีของคุณที่นั่น และเรียกใช้ในคิวรี M อีกวิธีหนึ่งคือ ถ้ามี ให้ใช้กลไกการส่งผ่านพารามิเตอร์ที่สร้างขึ้นในภาษาและตัวเชื่อมต่อคิวรีต้นทาง ตัวอย่างเช่น Azure Data Explorer มีความสามารถพารามิเตอร์คิวรีในตัวที่ออกแบบมาเพื่อป้องกันการโจมตีแบบแทรก

นี่คือตัวอย่างบางส่วนของการลดเหล่านี้:

• ตัวอย่างที่ใช้การดําเนินการกรองของคิวรี M:

  Table.SelectRows(Source, (r) => r[Columns] = Parameter)
  

• ตัวอย่างที่ตรวจนับพารามิเตอร์ในคิวรีต้นทาง หรือส่งผ่านค่าพารามิเตอร์เป็นข้อมูลป้อนเข้าไปยังฟังก์ชันคิวรีต้นทาง:

  declare query_parameters (Name of Parameter : Type of Parameter);
  

• ตัวอย่างของการเรียกขั้นตอนการจัดเก็บโดยตรง:

  let CustomerByProductFn = AzureDataExplorer.Contents("Help", "ContosoSales"){[Name="CustomerByProduct"]}[Data] in
  CustomerByProductFn({1, 3, 5})
  

ข้อควรพิจารณาและข้อจำกัด

มีข้อควรพิจารณาและข้อจํากัดบางอย่างที่ต้องคํานึงถึงเมื่อคุณใช้พารามิเตอร์คิวรี M แบบไดนามิก:

• พารามิเตอร์เดียวไม่สามารถผูกกับหลายเขตข้อมูลหรือในทางกลับกันได้
• พารามิเตอร์คิวรี M แบบไดนามิกไม่สนับสนุนการรวม
• พารามิเตอร์คิวรี M แบบไดนามิกไม่สนับสนุนการรักษาความปลอดภัยระดับแถว (RLS)
• ชื่อพารามิเตอร์ไม่สามารถเป็นคําที่สงวนไว้ของนิพจน์การวิเคราะห์ข้อมูล (DAX) หรือมีช่องว่างได้ คุณสามารถผนวก Parameter เข้ากับส่วนท้ายของชื่อพารามิเตอร์เพื่อช่วยหลีกเลี่ยงข้อจํากัดนี้
• ชื่อตารางต้องไม่ประกอบด้วยช่องว่างหรืออักขระพิเศษ
• ถ้าพารามิเตอร์ของคุณเป็นDate/Timeชนิดข้อมูล คุณจะต้องแคสต์พารามิเตอร์ภายในคิวรี M เป็นDateTime.Date(<YourDateParameter>)
• ถ้าคุณใช้แหล่งข้อมูล SQL คุณอาจได้รับกล่องโต้ตอบการยืนยันทุกครั้งที่ค่าพารามิเตอร์เปลี่ยนแปลง กล่องโต้ตอบนี้เกิดเนื่องจากการตั้งค่าความปลอดภัย: จําเป็นต้องมีการอนุมัติจากผู้ใช้สําหรับคิวรีฐานข้อมูลดั้งเดิมใหม่ คุณสามารถค้นหาและปิดการตั้งค่านี้ได้ในส่วน Security ของ Power BI Desktop Options
• พารามิเตอร์คิวรี M แบบไดนามิกอาจไม่ทํางานเมื่อเข้าถึงแบบจําลองความหมายใน Excel
• พารามิเตอร์คิวรี M แบบไดนามิกไม่ได้รับการสนับสนุนบนเซิร์ฟเวอร์รายงาน เซิร์ฟเวอร์รายงาน Power BI
• การสลับแหล่งข้อมูลโดยใช้พารามิเตอร์คิวรี M แบบไดนามิกไม่ได้รับการสนับสนุนในบริการของ บริการของ Power BI ดู รีเฟรชและแหล่งข้อมูล แบบไดนามิกสําหรับข้อมูลเพิ่มเติม

ชนิดพารามิเตอร์ที่ไม่ได้รับการสนับสนุนในกล่อง

• ใด
• ระยะเวลา
• จริง/เท็จ
• ไบนารี

ตัวกรองที่ไม่สนับสนุน

• ตัวแบ่งส่วนหรือตัวกรองเวลาที่สัมพันธ์กัน
• วันที่สัมพัทธ์
• ตัวแบ่งส่วนลําดับชั้น
• หลายเขตข้อมูลรวมตัวกรอง
• ไม่รวมตัวกรอง/ไม่กรอง
• การไฮไลต์แบบเชื่อมโยง
• ตัวกรองการดูรายละเอียดแนวลึก
• ตัวกรองแบบเจาะข้าม
• ตัวกรอง N อันดับแรก

การดําเนินการที่ไม่สนับสนุน

• และ
• ประกอบด้วย
• น้อยกว่า
• มากกว่า
• เริ่มต้นด้วย
• ไม่ได้เริ่มต้นด้วย
• ไม่ใช่
• ไม่ประกอบด้วย
• ว่างเปล่า
• ไม่ว่างเปล่า

เนื้อหาที่เกี่ยวข้อง

สําหรับข้อมูลเพิ่มเติมเกี่ยวกับความสามารถของ Power BI Desktop โปรดดูแหล่งข้อมูลต่อไปนี้:

• DirectQuery ใน Power BI
• Power BI คืออะไร?
• ภาพรวม Query ใน Power BI Desktop
• ชนิดข้อมูลใน Power BI Desktop
• บทช่วยสอน: จัดรูปร่างและรวมข้อมูลใน Power BI Desktop
• ดําเนินการคิวรีทั่วไปใน Power BI Desktop