Tạo mô hình dữ liệu bộ nhớ có hiệu quả bằng cách dùng Excel và bổ trợ Power Pivot

Trong Excel 2013 trở lên, bạn có thể tạo mô hình dữ liệu có chứa hàng triệu hàng và sau đó thực hiện phân tích dữ liệu mạnh mẽ đối với các mô hình này. Mô hình dữ liệu có thể được tạo ra có hoặc không có phần bổ trợ Power Pivot để hỗ trợ bất kỳ số lượng của Pivottable, biểu đồ và Power View trực quan hóa trong cùng một sổ làm việc.

Lưu ý: Bài viết này mô tả các mô hình dữ liệu trong Excel 2013. Tuy nhiên, cách tạo mô hình dữ liệu và các tính năng Power Pivot tương tự được giới thiệu trong Excel 2013 cũng được áp dụng cho Excel 2016. Có sự khác biệt có hiệu quả giữa các phiên bản Excel này.

Mặc dù bạn có thể dễ dàng tạo mô hình dữ liệu rất lớn trong Excel, có nhiều lý do không. Trước tiên, các mô hình lớn có chứa nhiều bảng và cột đều vượt quá nhiều nhất để phân tích và tạo cho một danh sách trường rườm rà. Thứ hai, mô hình lớn sử dụng bộ nhớ có giá trị, tiêu cực ảnh hưởng đến các ứng dụng và báo cáo khác mà chia sẻ các tài nguyên hệ thống. Cuối cùng, trong Microsoft 365, cả SharePoint Online và Excel Web App giới hạn kích cỡ tệp Excel thành 10 MB. Đối với mô hình dữ liệu sổ làm việc có chứa hàng triệu hàng, bạn sẽ chạy vào giới hạn 10 MB khá nhanh chóng. Xem đặc tả và giới hạn của mô hình dữ liệu.

Trong bài viết này, bạn sẽ tìm hiểu cách xây dựng một mô hình được xây dựng chặt chẽ dễ dàng hơn để làm việc và sử dụng bộ nhớ ít hơn. Dành thời gian để tìm hiểu các cách thực hành tốt nhất trong thiết kế mô hình hiệu quả sẽ thanh toán giảm xuống đường cho bất kỳ mô hình nào bạn tạo và sử dụng, cho dù bạn đang xem nó trong Excel 2013, Microsoft 365 SharePoint Online, trên máy chủ Office Online hoặc trong SharePoint 2013.

Ngoài ra hãy xem xét chạy Trình tối ưu hóa Kích cỡ Sổ làm việc. Trình tối ưu hóa đó sẽ phân tích sổ làm việc Excel của bạn và nếu có thể sẽ nén sổ đó thêm. Tải xuống trình tối ưu kích cỡ sổlàm việc.

Trong bài viết này

Tỷ lệ nén và công cụ phân tích trong bộ nhớ

Không có gì nhịp đập một cột không tồn tại cho việc sử dụng bộ nhớ thấp

Hai ví dụ về các cột luôn được loại trừ

Cách loại trừ các cột không cần thiết

Điều gì về việc lọc chỉ các hàng cần thiết?

Điều gì sẽ xảy ra nếu chúng ta cần cột; chúng tôi vẫn có thể giảm chi phí không gian của nó?

Sửa đổi cột DateTime

Sửa đổi truy vấn SQL

Sử dụng các số đo được tính DAX thay vì cột

Bạn nên giữ lại 2 cột nào?

Kết luận

Các nối kết liên quan

Tỷ lệ nén và công cụ phân tích trong bộ nhớ

Mô hình dữ liệu trong Excel sử dụng công cụ phân tích trong bộ nhớ để lưu trữ dữ liệu trong bộ nhớ. Công cụ này thực hiện các kỹ thuật nén mạnh để giảm các yêu cầu lưu trữ, thu nhỏ tập kết quả cho đến khi nó là một phần của kích cỡ ban đầu của nó.

Trung bình, bạn có thể trông đợi một mô hình dữ liệu sẽ nhỏ hơn 7 đến 10 lần so với cùng một dữ liệu tại điểm gốc. Ví dụ, nếu bạn đang nhập 7 MB dữ liệu từ cơ sở dữ liệu SQL Server, mô hình dữ liệu trong Excel có thể dễ dàng là 1 MB trở lên. Mức độ nén thực sự đạt được phụ thuộc chủ yếu vào số lượng giá trị duy nhất trong mỗi cột. Các giá trị độc đáo hơn, bộ nhớ khác là cần thiết để lưu trữ chúng.

Tại sao chúng ta lại nói về các giá trị nén và duy nhất? Vì việc xây dựng một mô hình hiệu quả giúp giảm thiểu việc sử dụng bộ nhớ là tất cả thông tin về sự phóng đại và cách dễ dàng nhất để thực hiện điều đó là để thoát khỏi bất kỳ cột nào mà bạn không cần, đặc biệt là nếu những cột này bao gồm một số lượng lớn các giá trị duy nhất.

Lưu ý:  Sự khác nhau trong yêu cầu lưu trữ cho các cột riêng lẻ có thể rất lớn. Trong một số trường hợp, tốt hơn nên có nhiều cột với số lượng giá trị duy nhất thấp chứ không phải là một cột có số lượng lớn các giá trị duy nhất. Mục trên DateTime tối ưu hóa bao gồm kỹ thuật này chi tiết.

Không có gì nhịp đập một cột không tồn tại cho việc sử dụng bộ nhớ thấp

Cột hiệu quả nhất trong bộ nhớ là tài bạn không bao giờ được nhập vào vị trí đầu tiên. Nếu bạn muốn xây dựng một mô hình hiệu quả, hãy xem từng cột và tự hỏi liệu nó có đóng góp vào phân tích mà bạn muốn thực hiện. Nếu không hoặc bạn không chắc chắn, hãy để nó thoát ra. Bạn luôn có thể thêm các cột mới sau nếu cần thiết.

Hai ví dụ về các cột luôn được loại trừ

Ví dụ đầu tiên liên quan đến dữ liệu có nguồn gốc từ một nhà kho dữ liệu. Trong một nhà kho dữ liệu, nó phổ biến để tìm kiếm các quy trình ETL tải và làm mới dữ liệu trong kho. Các cột như "tạo ngày", "Cập Nhật ngày", và "chạy ETL" được tạo ra khi dữ liệu được tải. Không có các cột nào cần thiết trong mô hình và cần được bỏ chọn khi bạn nhập dữ liệu.

Ví dụ thứ hai liên quan đến việc bỏ sót cột khóa chính khi nhập một bảng thực tế.

Nhiều bảng, bao gồm các bảng thực tế, có khóa chính. Đối với hầu hết các bảng, chẳng hạn như những người có chứa dữ liệu khách hàng, nhân viên hoặc doanh số, bạn sẽ muốn khóa chính của bảng để bạn có thể dùng nó để tạo mối quan hệ trong mô hình.

Bảng thực tế khác nhau. Trong bảng thực tế, khóa chính được dùng để xác định duy nhất mỗi hàng. Trong khi cần thiết cho các mục đích chuẩn hóa, nó ít hữu ích hơn trong một mô hình dữ liệu mà bạn chỉ muốn các cột được sử dụng để phân tích hoặc thiết lập mối quan hệ bảng. Vì lý do này, khi nhập từ một bảng thực tế, không bao gồm khóa chính của nó. Các phím chính trong bảng thực tế tiêu thụ số lượng lớn không gian trong mô hình, nhưng không cung cấp lợi ích, vì chúng không thể được sử dụng để tạo mối quan hệ.

Lưu ý:  Trong kho dữ liệu và cơ sở dữ liệu đa chiều, các bảng lớn bao gồm hầu hết là dữ liệu số thường được gọi là "bảng thực tế". Bảng Fact thường bao gồm hiệu suất kinh doanh hoặc dữ liệu giao dịch, chẳng hạn như bán hàng và các điểm dữ liệu chi phí được tổng hợp và căn chỉnh cho đơn vị tổ chức, sản phẩm, phân đoạn thị trường, khu vực địa lý, v.v. Tất cả các cột trong bảng thực tế có chứa dữ liệu doanh nghiệp hoặc có thể được sử dụng để dữ liệu tham chiếu chéo được lưu trữ trong các bảng khác sẽ được đưa vào mô hình để hỗ trợ phân tích dữ liệu. Cột bạn muốn loại trừ là cột khóa chính của bảng thực tế, bao gồm các giá trị duy nhất chỉ tồn tại trong bảng thực tế và không có chỗ nào khác. Vì các bảng thực tế rất lớn, một số lợi ích lớn nhất trong hiệu suất mô hình có nguồn gốc từ việc ngoại trừ các hàng hoặc cột từ các bảng thực tế.

Cách loại trừ các cột không cần thiết

Các mô hình hiệu quả chỉ chứa các cột mà bạn thực sự cần trong sổ làm việc của bạn. Nếu bạn muốn kiểm soát các cột nào được bao gồm trong mô hình, bạn sẽ phải sử dụng trình hướng dẫn nhập bảng trong phần bổ trợ Power Pivot để nhập dữ liệu chứ không phải là hộp thoại "nhập dữ liệu" trong Excel.

Khi bạn khởi động trình hướng dẫn nhập bảng, bạn chọn bảng cần nhập.

Trình hướng dẫn Nhập Bảng trong bổ trợ PowerPivot

Đối với mỗi bảng, bạn có thể bấm vào nút Xem trước bộ lọc & và chọn các phần của bảng mà bạn thực sự cần. Chúng tôi khuyên bạn trước tiên bỏ chọn tất cả các cột, rồi tiếp tục kiểm tra các cột bạn muốn, sau khi xem xét xem họ có cần thiết để phân tích không.

Ngăn Xem trước trong Trình hướng dẫn Nhập Bảng

Điều gì về việc lọc chỉ các hàng cần thiết?

Nhiều bảng trong cơ sở dữ liệu công ty và kho dữ liệu có chứa dữ liệu lịch sử được tích lũy trong khoảng thời gian dài. Ngoài ra, bạn có thể thấy rằng các bảng mà bạn quan tâm có chứa thông tin cho các khu vực của doanh nghiệp không bắt buộc đối với phân tích cụ thể của bạn.

Sử dụng trình hướng dẫn nhập bảng, bạn có thể lọc dữ liệu lịch sử hoặc không liên quan, và do đó lưu nhiều không gian trong mô hình. Trong hình ảnh sau đây, một bộ lọc ngày được dùng để chỉ truy xuất các hàng có chứa dữ liệu cho năm hiện tại, ngoại trừ dữ liệu lịch sử không cần thiết.

Ngăn Lọc trong Trình hướng dẫn Nhập Bảng

Điều gì sẽ xảy ra nếu chúng ta cần cột; chúng tôi vẫn có thể giảm chi phí không gian của nó?

Có một vài kỹ thuật bổ sung mà bạn có thể áp dụng để tạo một cột một ứng viên tốt hơn để nén. Hãy nhớ rằng đặc tính duy nhất của cột ảnh hưởng đến việc nén là số lượng giá trị duy nhất. Trong phần này, bạn sẽ tìm hiểu về cách một số cột có thể được sửa đổi để giảm số lượng các giá trị duy nhất.

Sửa đổi cột DateTime

Trong nhiều trường hợp, các cột DateTime mất nhiều dung lượng. May mắn thay, có một số cách để giảm yêu cầu lưu trữ cho kiểu dữ liệu này. Các kỹ thuật sẽ khác nhau tùy thuộc vào cách bạn sử dụng cột và mức độ thoải mái của bạn trong việc xây dựng truy vấn SQL.

Các cột DateTime bao gồm một phần ngày và thời gian. Khi bạn tự hỏi liệu mình có cần một cột hay không, hãy hỏi cùng một câu hỏi nhiều lần cho một cột DateTime:

  • Tôi có cần phần thời gian không?

  • Tôi có cần phần thời gian ở mức giờ không? giờ? Seconds? mili giây?

  • Tôi có nhiều cột DateTime vì tôi muốn tính toán sự khác biệt giữa chúng hoặc chỉ để tổng hợp dữ liệu theo năm, tháng, quý, v.v.

Cách bạn trả lời từng câu hỏi này sẽ xác định các tùy chọn của bạn để xử lý với cột DateTime.

Tất cả các giải pháp này yêu cầu sửa đổi một truy vấn SQL. Để sửa đổi truy vấn dễ dàng hơn, bạn nên lọc ít nhất một cột trong mỗi bảng. Bằng cách lọc một cột, bạn thay đổi việc xây dựng truy vấn từ định dạng được viết tắt (chọn *) vào một câu lệnh SELECT có các tên cột đầy đủ điều kiện, cách dễ dàng hơn để sửa đổi.

Chúng ta hãy xem các truy vấn được tạo ra cho bạn. Từ hộp thoại thuộc tính bảng, bạn có thể chuyển sang trình soạn thảo truy vấn và xem truy vấn SQL hiện tại cho mỗi bảng.

Ruy-băng trong cửa sổ PowerPivot hiển thị lệnh Thuộc tính Bảng

Từ thuộc tính của bảng, hãy chọn trình soạn thảo truy vấn.

Mở Trình soạn thảo Truy vấn từ hộp thoại Thuộc tính Bảng.

Trình soạn thảo truy vấn Hiển thị truy vấn SQL được dùng để định cư bảng. Nếu bạn đã lọc ra bất kỳ cột nào trong khi nhập, truy vấn của bạn bao gồm các tên cột đầy đủ điều kiện:

Truy vấn SQL được dùng để truy xuất dữ liệu

Ngược lại, nếu bạn đã nhập một bảng trong toàn bộ, mà không cần bỏ chọn bất kỳ cột nào hoặc áp dụng bất kỳ bộ lọc nào, bạn sẽ thấy truy vấn là "chọn * từ", sẽ có nhiều khó khăn hơn để sửa đổi:

Truy vấn SQL dùng cú pháp mặc định, ngắn hơn

Sửa đổi truy vấn SQL

Bây giờ bạn đã biết cách tìm truy vấn, bạn có thể sửa đổi để giảm kích cỡ của mô hình của mình.

  1. Đối với các cột có chứa dữ liệu tiền tệ hoặc thập phân, nếu bạn không cần số thập phân, hãy dùng cú pháp này để thoát khỏi số thập phân:

    "Chọn ROUND ([Decimal_column_name], 0)... .”

    Nếu bạn cần các xu nhưng không phân số của xu, hãy thay thế 0 bằng 2. Nếu bạn sử dụng các số âm, bạn có thể làm tròn thành đơn vị, hàng chục, v.v...

  2. Nếu bạn có một cột DateTime có tên là dbo. Bigtable. [Thời gian ngày] và bạn không cần phần thời gian, hãy dùng cú pháp để thoát khỏi thời gian:

    "Chọn CAST (dbo. Bigtable. [Thời gian date] as date) là [giờ ngày]) "

  3. Nếu bạn có một cột DateTime có tên là dbo. Bigtable. [Thời gian hẹn hò] và bạn cần cả phần ngày và thời gian, sử dụng nhiều cột trong truy vấn SQL thay vì cột DateTime duy nhất:

    "Chọn CAST (dbo. Bigtable. [Thời gian hẹn hò] theo ngày) là [giờ ngày],

    datePart (HH, dbo. Bigtable. [Giờ ngày]) như [giờ thời gian],

    datePart (mi, dbo. Bigtable. [Giờ ngày]) như [giờ ngày phút],

    datePart (SS, dbo. Bigtable. [Giờ ngày]) như [giây thời gian],

    datePart (MS, dbo. Bigtable. [Giờ ngày]) như [giờ ngày tháng mili giây] "

    Sử dụng nhiều cột như bạn cần lưu trữ từng phần trong các cột riêng biệt.

  4. Nếu bạn cần giờ và phút, và bạn thích chúng cùng nhau như một cột thời gian, bạn có thể sử dụng cú pháp:

    Các phần timefrom(datePart (HH, dbo. Bigtable. [Giờ ngày]), datePart (mm, dbo. Bigtable. [Thời gian date])) như [phút thời gian giờ]

  5. Nếu bạn có hai cột DateTime, chẳng hạn như [thời gian bắt đầu] và [thời gian kết thúc], và những gì bạn thực sự cần là sự khác biệt thời gian giữa chúng trong vài giây dưới dạng cột có tên [thời gian], hãy loại bỏ cả hai cột khỏi danh sách và thêm:

    "datediff (SS, [ngày bắt đầu], [ngày kết thúc]) là [thời lượng]"

    Nếu bạn sử dụng từ khóa MS thay vì SS, bạn sẽ nhận được thời lượng trong mili giây

Sử dụng các số đo được tính DAX thay vì cột

Nếu bạn đã làm việc với ngôn ngữ biểu thức DAX trước đây, bạn có thể biết rằng các cột được tính toán được sử dụng để lấy các cột mới dựa trên một số cột khác trong mô hình, trong khi các đo được tính toán được xác định một lần trong mô hình nhưng chỉ được đánh giá khi dùng trong một PivotTable hoặc báo cáo khác.

Một kỹ thuật lưu bộ nhớ là thay thế các cột thường hoặc được tính toán bằng các thước đo được tính. Ví dụ cổ điển là đơn vị giá, số lượng và tổng số. Nếu bạn có cả ba, bạn có thể tiết kiệm không gian bằng cách duy trì chỉ hai và tính toán phần thứ ba bằng DAX.

Bạn nên giữ lại 2 cột nào?

Trong ví dụ ở trên, hãy giữ lại số lượng và giá đơn vị. Hai giá trị này có ít giá trị hơn tổng. Để tính tổng, hãy thêm một thước đo được tính toán như sau:

"TotalSales: = sumx (' bảng bán hàng ', ' bảng bán hàng ' [đơn giá] * ' Sales Table ' [số lượng])"

Các cột được tính toán đều giống như các cột thông thường trong đó đều chiếm khoảng cách trong mô hình. Ngược lại, các đo được tính được tính toán trên bay và không có khoảng trống.

Kết luận

Trong bài viết này, chúng tôi đã nói về một số cách tiếp cận có thể giúp bạn xây dựng mô hình hiệu quả hơn một bộ nhớ. Cách để giảm kích cỡ tệp và yêu cầu bộ nhớ của mô hình dữ liệu là giảm số lượng cột và hàng tổng thể và số lượng giá trị duy nhất xuất hiện trong mỗi cột. Sau đây là một số kỹ thuật mà chúng tôi đã bảo hiểm:

  • Loại bỏ cột là cách tốt nhất để tiết kiệm không gian. Quyết định cột nào bạn thực sự cần.

  • Đôi khi bạn có thể loại bỏ một cột và thay thế bằng một thước đo được tính trong bảng.

  • Bạn có thể không cần phải có tất cả các hàng trong một bảng. Bạn có thể lọc ra các hàng trong trình hướng dẫn nhập bảng.

  • Nói chung, hãy ngắt một cột duy nhất thành nhiều phần khác biệt là cách tốt nhất để giảm số lượng giá trị duy nhất trong một cột. Mỗi một trong các phần sẽ có một số lượng nhỏ các giá trị duy nhất và tổng cộng sẽ nhỏ hơn cột hợp nhất ban đầu.

  • Trong nhiều trường hợp, bạn cũng cần các phần riêng biệt để dùng như slicer trong báo cáo của mình. Khi thích hợp, bạn có thể tạo cấu trúc phân cấp từ các phần như giờ, phút và giây.

  • Nhiều lần, các cột có chứa nhiều thông tin hơn bạn cần chúng. Ví dụ, giả sử các cửa hàng cột có số thập phân, nhưng bạn đã áp dụng định dạng để ẩn tất cả các số thập phân. Có thể làm tròn có thể rất hiệu quả trong việc giảm kích cỡ của cột số.

Bây giờ bạn đã thực hiện những gì bạn có thể giảm kích cỡ sổ làm việc của mình, hãy xem xét cũng đang chạy trình tối ưu hóa kích cỡ sổ làm việc. Trình tối ưu hóa đó sẽ phân tích sổ làm việc Excel của bạn và nếu có thể sẽ nén sổ đó thêm. Tải xuống trình tối ưu kích cỡ sổlàm việc.

Các nối kết liên quan

Đặc tả và giới hạn của Mô hình Dữ liệu

Tải xuống trình tối ưu kích cỡ sổ làm việc

PowerPivot: Phân tích dữ liệu và lập mô hình dữ liệu mạnh mẽ trong Excel

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×