ความสัมพันธ์ระหว่างตารางในแบบจำลองข้อมูล

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

ในฐานข้อมูลเชิงสัมพันธ์มีคีย์หลายชนิด โดยทั่วไปแล้วคีย์จะเป็นคอลัมน์ที่มีคุณสมบัติพิเศษ การทำความเข้าใจเกี่ยวกับวัตถุประสงค์ของแต่ละคีย์จะช่วยให้คุณจัดการตัวแบบข้อมูลแบบหลายตารางที่มีข้อมูลไปยังรายงาน PivotTable, PivotChart หรือ Power View

แม้ว่าจะมีหลายชนิดของคีย์เหล่านี้เป็นสิ่งสำคัญที่สุดสำหรับวัตถุประสงค์ของเราที่นี่:

• คีย์หลัก:ระบุแถวในตารางโดยไม่ซ้ำกันเช่นCustomerIDในตารางลูกค้า

• แป้นสลับ (หรือคีย์ผู้สมัคร):คอลัมน์อื่นที่ไม่ใช่คีย์หลักที่ไม่ซ้ำกัน ตัวอย่างเช่นตารางพนักงานอาจจัดเก็บ ID พนักงานและหมายเลขประกันสังคมซึ่งจะไม่ซ้ำกัน

• Foreign key:คอลัมน์ที่อ้างอิงไปยังคอลัมน์ที่ไม่ซ้ำกันในตารางอื่นเช่นcustomeridในตารางใบสั่งซื้อซึ่งอ้างอิงไปยังCustomerIDในตารางลูกค้า

ในตัวแบบข้อมูลคีย์หลักหรือคีย์อื่นจะถูกเรียกว่าคอลัมน์ที่เกี่ยวข้อง ถ้าตารางมีทั้งคีย์หลักและคีย์อื่นคุณสามารถใช้ได้อย่างใดอย่างหนึ่งเป็นพื้นฐานของความสัมพันธ์ของตาราง แป้นต่างประเทศจะถูกเรียกว่าคอลัมน์ต้นฉบับหรือคอลัมน์เพียงคอลัมน์เดียว ในตัวอย่างของเราความสัมพันธ์จะถูกกำหนดระหว่างCustomerIDในตารางใบสั่งซื้อ(คอลัมน์) และCustomerIDในตารางลูกค้า(คอลัมน์การค้นหา) ถ้าคุณนำเข้าข้อมูลจากฐานข้อมูลเชิงสัมพันธ์ตามค่าเริ่มต้น Excel จะเลือก foreign key จากตารางหนึ่งและคีย์หลักที่สอดคล้องกันจากตารางอื่น อย่างไรก็ตามคุณสามารถใช้คอลัมน์ใดก็ได้ที่มีค่าที่ไม่ซ้ำกันสำหรับคอลัมน์การค้นหา

ความสัมพันธ์ระหว่างลูกค้าและใบสั่งซื้อเป็นความสัมพันธ์แบบหนึ่ง-ต่อ-กลุ่ม ลูกค้าทุกคนสามารถมีหลายใบสั่งซื้อได้แต่การสั่งซื้อไม่สามารถมีลูกค้าได้หลายคน ความสัมพันธ์ของตารางที่สำคัญอื่นเป็นแบบหนึ่ง-ต่อ-หนึ่ง ในตัวอย่างของเราที่นี่ตารางCustomerDiscountsที่กำหนดอัตราส่วนลดเดียวสำหรับลูกค้าแต่ละรายมีความสัมพันธ์แบบหนึ่ง-ต่อ-หนึ่งกับตารางลูกค้า

ตารางนี้แสดงความสัมพันธ์ระหว่างตารางทั้งสามตาราง (ลูกค้า, CustomerDiscountsและOrders):

ความสัมพันธ์	ประเภท	คอลัมน์การค้นหา	คอลัมน์
Customers-CustomerDiscounts	หนึ่งต่อหนึ่ง	Customers.CustomerID	CustomerDiscounts.CustomerID
Customers-Orders	หนึ่งถึงจำนวนมาก	Customers.CustomerID	Orders.CustomerID

หลังจากที่มีการสร้างความสัมพันธ์แล้ว Excel จะต้องคำนวณสูตรใดๆที่ใช้คอลัมน์จากตารางในความสัมพันธ์ที่สร้างขึ้นใหม่ การประมวลผลอาจใช้เวลาสักครู่ทั้งนี้ขึ้นอยู่กับปริมาณของข้อมูลและความซับซ้อนของความสัมพันธ์ สำหรับรายละเอียดเพิ่มเติมให้ดูที่การคำนวณสูตรใหม่

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

ในมุมมองไดอะแกรมความสัมพันธ์ที่ใช้งานอยู่คือเส้นทึบและการใช้งานที่ไม่ได้ใช้งานจะเป็นเส้นประ ตัวอย่างเช่นใน AdventureWorksDW2012 ตารางDimDateมีคอลัมน์DateKeyที่เกี่ยวข้องกับคอลัมน์ที่แตกต่างกันสามคอลัมน์ในตารางFactInternetSales: OrderDate, iifและShipDate ถ้าความสัมพันธ์ที่ใช้งานอยู่อยู่ระหว่าง DateKey และ OrderDate นั่นคือความสัมพันธ์เริ่มต้นในสูตรเว้นแต่ว่าคุณจะระบุไว้เป็นอย่างอื่น

คุณสามารถสร้างความสัมพันธ์ได้เมื่อเป็นไปตามข้อกำหนดต่อไปนี้:

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

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

คีย์ผสมและคอลัมน์การค้นหา

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

เมื่อต้องการสร้างความสัมพันธ์ระหว่างตารางสองตารางที่มีหลายคอลัมน์ที่กำหนดคีย์หลักและ foreign ให้รวมค่าเพื่อสร้างคอลัมน์คีย์เดียวก่อนที่จะสร้างความสัมพันธ์ คุณสามารถทำสิ่งนี้ได้ก่อนที่คุณจะนำเข้าข้อมูลหรือโดยการสร้างคอลัมน์จากการคำนวณในตัวแบบข้อมูลโดยใช้ add-in Power Pivot

ความสัมพันธ์แบบกลุ่มต่อกลุ่ม

ตัวแบบข้อมูลไม่สามารถมีความสัมพันธ์แบบหลาย-ต่อ-กลุ่มได้ คุณไม่สามารถเพิ่มตารางตัวเชื่อมต่อในตัวแบบได้ อย่างไรก็ตามคุณสามารถใช้ฟังก์ชัน DAX เพื่อสร้างความสัมพันธ์แบบหลาย-ต่อ-กลุ่มได้

การรวมกับตัวเองและการวนรอบ

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

Excel จะไม่อนุญาตให้มีการสร้างการวนรอบระหว่างความสัมพันธ์ใน เวิร์กบุ๊ก ในอีกนัยหนึ่ง ห้ามใช้ชุดของความสัมพันธ์ต่อไปนี้

ตาราง 1, คอลัมน์ a ถึง ตาราง 2, คอลัมน์ f    

ตาราง 2, คอลัมน์ f ถึง ตาราง 3, คอลัมน์ n    

ตาราง 3, คอลัมน์ n ถึง ตาราง 1, คอลัมน์ a    

ถ้าคุณพยายามสร้างความสัมพันธ์ที่ทำให้มีการสร้างการวนรอบ อาจทำให้เกิดข้อผิดพลาดได้

หนึ่งในข้อดีของการนำเข้าข้อมูลโดยใช้ Add-in ของ Power Pivot ก็คือ ในบางครั้ง Power Pivot สามารถตรวจหาความสัมพันธ์และสร้างความสัมพันธ์ใหม่ในตัวแบบข้อมูลที่สร้างใน Excel ได้

เมื่อคุณนำเข้าหลายตาราง Power Pivot จะตรวจหาความสัมพันธ์ที่มีอยู่ระหว่างตารางโดยอัตโนมัติ และเมื่อคุณสร้าง PivotTable นั้น Power Pivot จะวิเคราะห์ข้อมูลในตาราง โดยโปรแกรมจะตรวจหาความสัมพันธ์ที่เป็นไปได้ซึ่งยังไม่ระบุ และแนะนำคอลัมน์ที่เหมาะสมเพื่อรวมไว้ในความสัมพันธ์เหล่านั้น

อัลกอริทึมการตรวจหาจะใช้ข้อมูลเชิงสถิติเกี่ยวกับค่าและ Metadata ของคอลัมน์เพื่อทำการอนุมานเกี่ยวกับความน่าจะเป็นของความสัมพันธ์

• ชนิดข้อมูลในคอลัมน์ที่สัมพันธ์กันทั้งหมดควรเข้ากันได้ สำหรับการตรวจหาโดยอัตโนมัติ เฉพาะชนิดข้อมูลจำนวนเต็มและข้อความเท่านั้นที่จะได้รับการสนับสนุน สำหรับข้อมูลเพิ่มเติมเกี่ยวกับชนิดข้อมูล ให้ดูที่ ชนิดข้อมูลในตัวแบบข้อมูล

• ในการที่ความสัมพันธ์จะถูกตรวจพบได้ จำนวนคีย์ที่ไม่ซ้ำกันในคอลัมน์การค้นหาจะต้องมากกว่าค่าในตารางในด้านกลุ่ม หรืออีกนัยหนึ่ง คอลัมน์หลักบนความสัมพันธ์ด้านกลุ่มต้องไม่มีค่าใดๆ ที่ไม่ได้อยู่ในคอลัมน์หลักของตารางการค้นหา ตัวอย่างเช่น สมมติว่าคุณมีตารางที่แสดงรายการผลิตภัณฑ์พร้อมกับรหัส (ตารางการค้นหา) และตารางการขายที่แสดงรายการยอดขายสำหรับแต่ละผลิตภัณฑ์ (ด้านกลุ่มของความสัมพันธ์) ถ้าระเบียนการขายมีรหัสของผลิตภัณฑ์ที่ไม่สอดคล้องกับรหัสในตาราง ผลิตภัณฑ์ ความสัมพันธ์นั้นจะไม่สามารถสร้างขึ้นได้โดยอัตโนมัติ แต่คุณอาจสร้างความสัมพันธ์นั้นได้ด้วยตนเอง เมื่อต้องการให้ Excel ตรวจหาความสัมพันธ์นั้น ก่อนอื่นคุณต้องอัปเดตตารางการค้นหาด้วยรหัสของผลิตภัณฑ์ที่ขาดหายไป

• ตรวจสอบให้แน่ใจว่าชื่อของคอลัมน์คีย์ที่อยู่ด้านบนหลายด้านคล้ายกับชื่อของคอลัมน์คีย์ในตารางการค้นหา ชื่อไม่จำเป็นต้องตรงกัน ตัวอย่างเช่นในการตั้งค่าธุรกิจคุณมักจะมีการเปลี่ยนแปลงชื่อของคอลัมน์ที่มีข้อมูลที่เหมือนกันเป็นหลัก: Emp id, EmployeeID, ID พนักงาน, EMP_IDและอื่นๆ อัลกอริทึมตรวจพบชื่อที่คล้ายกันและกำหนดความน่าจะเป็นให้กับคอลัมน์ที่มีชื่อเหมือนกันหรือมีชื่อตรงกัน ดังนั้นเมื่อต้องการเพิ่มความน่าจะเป็นในการสร้างความสัมพันธ์คุณสามารถลองเปลี่ยนชื่อคอลัมน์ในข้อมูลที่คุณนำเข้าไปยังบางสิ่งที่คล้ายกับคอลัมน์ในตารางที่มีอยู่ของคุณได้ ถ้า Excel ค้นหาความสัมพันธ์ที่เป็นไปได้หลายความสัมพันธ์จะไม่สร้างความสัมพันธ์

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

การตรวจหาอัตโนมัติสำหรับชุดที่มีชื่อ

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

การอนุมานของความสัมพันธ์

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

ผลิตภัณฑ์ และ ประเภท -- สร้างด้วยตนเอง

ประเภท และ ประเภทย่อย -- สร้างด้วยตนเอง

ผลิตภัณฑ์ และ ประเภทย่อย -- ความสัมพันธ์ถูกอ้างถึง

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