Trong Excel, bạn có thể tạo mô hình dữ liệu có chứa hàng triệu hàng, sau đó thực hiện phân tích dữ liệu mạnh mẽ dựa trên các mô hình này. Mô hình dữ liệu có thể được tạo có hoặc không có phần bổ trợ Power Pivot hỗ trợ bất kỳ số lượng PivotTable, biểu đồ và trực quan hóa Power View nào trong cùng một sổ làm việc.
Mặc dù bạn có thể dễ dàng xây dựng các mô hình dữ liệu khổng lồ trong Excel, nhưng có một số lý do không nên làm. Trước tiên, các mô hình lớn chứa nhiều bảng và cột bị quá tải đối với hầu hết các phân tích và tạo nên Danh sách Trường rườm rà. Thứ hai, các mô hình lớn sử dụng hết bộ nhớ có giá trị, ảnh hưởng tiêu cực đến các ứng dụng và báo cáo khác có cùng tài nguyên hệ thống. Cuối cùng, trong Microsoft 365, cả SharePoint Online và Excel Web App đều giới hạn kích cỡ tệp Excel là 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 nhanh đến giới hạn 10 MB. 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ô hình được xây dựng chặt chẽ, dễ làm việc hơn và sử dụng ít bộ nhớ hơn. Việc dành thời gian để tìm hiểu các biện pháp tốt nhất trong thiết kế mô hình hiệu quả sẽ giúp bạn phát triển bất kỳ mô hình nào bạn tạo và sử dụng, cho dù bạn đang xem mô hình đó trong Excel, Microsoft 365 SharePoint Online, trên Office Online Server hay trong SharePoint.
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 hóa 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ớ
Các 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 mẽ để giảm yêu cầu lưu trữ, thu hẹp một tập kết quả cho đến khi nó là một phần của kích thước ban đầu của nó.
Trung bình, bạn có thể mong đợi mô hình dữ liệu nhỏ hơn từ 7 đến 10 lần so với cùng một dữ liệu tại thời điểm ban đầu. Ví dụ: nếu bạn đang nhập 7 MB dữ liệu từ cơ sở dữ liệu SQL Server, thì mô hình dữ liệu trong Excel có thể dễ dàng có dung lượng từ 1 MB trở xuống. Mức độ nén thực sự đạt được phụ thuộc chủ yếu vào số lượng các giá trị duy nhất trong mỗi cột. Các giá trị càng duy nhất thì càng cần nhiều bộ nhớ để lưu trữ chúng.
Tại sao chúng ta lại nói về việc nén và các giá trị duy nhất? Vì việc xây dựng một mô hình hiệu quả giúp giảm thiểu mức sử dụng bộ nhớ là hoàn toàn thông qua việc tối đa hóa nén và cách dễ nhất để thực hiện điều đó là loại bỏ bất kỳ cột nào bạn không thực sự cần, đặc biệt là nếu các cột đó chứa một số lượng lớn các giá trị duy nhất.
: Sự khác biệt về yêu cầu lưu trữ đối với 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 bạn nên có nhiều cột với số lượng giá trị duy nhất thấp hơn là một cột có số lượng giá trị duy nhất cao. Mục về tối ưu hóa Datetime bao gồm kỹ thuật này một cách chi tiết.
Không có gì đánh bại một cột không tồn tại cho mức sử dụng bộ nhớ thấp
Cột hiệu quả nhất với bộ nhớ là cột mà bạn không bao giờ nhập vào lần đầu tiên. Nếu bạn muốn xây dựng một mô hình hiệu quả, hãy nhìn vào từng cột và tự hỏi liệu mô hình đó có đóng góp vào phân tích mà bạn muốn thực hiện hay không. Nếu thiết bị không hoặc bạn không chắc chắn, hãy bỏ qua. Bạn luôn có thể thêm cột mới sau nếu cần.
Hai ví dụ về cột luôn cầ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 kho dữ liệu. Trong một nhà kho dữ liệu, việc tìm các thành phần của các quy trình ETL tải và làm mới dữ liệu trong nhà kho là điều phổ biến. Các cột như "tạo ngày", "ngày cập nhật" và "Chạy ETL" được tạo khi dữ liệu được tải. Mô hình không cần có cột nào trong số này và nên được bỏ chọn khi bạn nhập dữ liệu.
Ví dụ thứ hai liên quan đến việc bỏ qua cột khóa chính khi nhập bảng dữ kiện.
Nhiều bảng, bao gồm cả bảng dữ kiện, có khóa chính. Đối với hầu hết các bảng, chẳng hạn như bảng có chứa dữ liệu khách hàng, nhân viên hoặc doanh số, bạn sẽ muốn có khóa chính của bảng để bạn có thể sử dụng khóa đó để tạo mối quan hệ trong mô hình.
Bảng dữ kiện khác nhau. Trong bảng dữ kiện, khóa chính được sử dụng để nhận dạng duy nhất từng hàng. Mặc dù cần thiết cho mục đích chuẩn hóa, nhưng mô hình dữ liệu ít hữu ích hơn khi bạn chỉ muốn các cột được dùng cho 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ừ bảng dữ kiện, không bao gồm khóa chính. Các khóa chính trong bảng dữ kiện tiêu thụ một lượng lớn không gian trong mô hình nhưng lại không mang lại lợi ích gì, vì chúng không thể được sử dụng để tạo mối quan hệ.
: 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 dữ kiện". Bảng dữ kiện thường bao gồm dữ liệu hiệu suất kinh doanh hoặc giao dịch, chẳng hạn như các điểm dữ liệu doanh số và chi phí được tổng hợp và liên kết với các đơn vị tổ chức, sản phẩm, phân khúc thị trường, khu vực địa lý, v.v. Tất cả các cột trong bảng dữ liệu có chứa dữ liệu nghiệp vụ hoặc có thể được dùng để tham chiếu chéo dữ liệu được lưu trữ trong các bảng khác nên đượ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 dữ kiện, bao gồm các giá trị duy nhất chỉ tồn tại trong bảng dữ kiện và không tồn tại ở nơi nào khác. Bởi vì bảng dữ kiện rất lớn, một số lợi ích lớn nhất trong hiệu quả mô hình được lấy từ việc loại trừ các hàng hoặc cột từ bảng dữ kiện.
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 những cột mà bạn thực sự cần trong sổ làm việc của mình. Nếu bạn muốn kiểm soát những cột đượ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 bổ trợ Power Pivot để nhập dữ liệu chứ không phải hộp thoại "Nhập Dữ liệu" trong Excel.
Khi khởi động Trình hướng dẫn nhập bảng, bạn chọn bảng cần nhập.
Đối với mỗi bảng, bạn có thể bấm vào nút Xem trước & 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 nê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 cân nhắc xem chúng có cần thiết cho phân tích hay không.
Điều gì sẽ xảy ra nếu chỉ lọc 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 chứa dữ liệu lịch sử được tích lũy trong 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 lĩnh vực kinh doanh 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, do đó tiết kiệm nhiều dung lượng trong mô hình. Trong hình ảnh sau đây, bộ lọc ngày được dùng để chỉ truy xuất các hàng 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.
Điều gì sẽ xảy ra nếu chúng ta cần cột; chúng ta vẫn có thể giảm chi phí không gian của nó?
Có một số kỹ thuật bổ sung mà bạn có thể áp dụng để đặt cột làm đề xuất nén tốt hơ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 cách sửa đổi một số cột để giảm số lượng giá trị duy nhất.
Sửa đổi cột Datetime
Trong nhiều trường hợp, cột Datetime chiếm nhiều dung lượng. May mắn là 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 trong việc xây dựng truy vấn SQL.
Cột Ngày giờ 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 cột hay không, hãy đặt câu hỏi tương tự nhiều lần cho cột Ngày giờ:
-
Tôi có cần phần thời gian không?
-
Tôi có cần phần thời gian ở cấp độ giờ không? biên bản? Giây? , 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 hay 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ẽ quyết định các tùy chọ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 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 cấu tạo truy vấn từ định dạng viết tắt (SELECT *) thành câu lệnh SELECT bao gồm tên cột đầy đủ tiêu chuẩn, vốn dễ sửa đổi hơn rất nhiều.
Chúng ta hãy xem các truy vấn được tạo 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 từng bảng.
Từ Thuộc tính Bảng, chọn Trình soạn thảo Truy vấn.
Trình soạn thảo Truy vấn hiển thị truy vấn SQL được dùng để điền bảng. Nếu bạn đã lọc ra bất kỳ cột nào trong quá trình nhập, truy vấn của bạn sẽ bao gồm tên cột đầy đủ tiêu chuẩn:
|
Ngược lại, nếu bạn đã nhập một bảng trong toàn bộ bảng mà không 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 dưới dạng "Chọn * từ ", sẽ khó sửa đổi 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 truy vấn để giảm thêm kích cỡ của mô hình.
-
Đối với các cột 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 để loại bỏ số thập phân:
"SELECT ROUND([Decimal_column_name],0)... .”
Nếu bạn cần xu nhưng không phải phân số của xu, hãy thay thế 0 bằng 2. Nếu bạn sử dụng số âm, bạn có thể làm tròn tới số đơn vị, hàng chục, hàng trăm v.v.
-
Nếu bạn có cột Datetime có tên là dbo. Bàn lớn. [Ngày Giờ] và bạn không cần phần Thời gian, hãy dùng cú pháp để loại bỏ thời gian:
"SELECT CAST (dbo. Bàn lớn. [Date time] as date) AS [Date time]) "
-
Nếu bạn có cột Datetime có tên là dbo. Bàn lớn. [Ngày Giờ] và bạn cần cả hai 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:
"SELECT CAST (dbo. Bàn lớn. [Date Time] as date ) AS [Date Time],
datepart(hh, dbo. Bàn lớn. [Ngày Giờ]) dưới dạng [Ngày Giờ],
datepart(mi, dbo. Bàn lớn. [Ngày Giờ]) dưới dạng [Phút Thời gian Ngày],
datepart(ss, dbo. Bàn lớn. [Ngày Giờ]) dưới dạng [Date Time Seconds],
datepart(ms, dbo. Bàn lớn. [Ngày Giờ]) dưới dạng [Date Time Milliseconds]"
Sử dụng số cột tùy theo nhu cầu để lưu trữ từng phần trong các cột riêng biệt.
-
Nếu bạn cần nhiều giờ và phút, và bạn thích chúng lại với nhau như một cột thời gian, bạn có thể sử dụng cú pháp:
Timefromparts(datepart(hh, dbo. Bàn lớn. [Date Time]), datepart(mm, dbo. Bàn lớn. [Ngày Giờ])) dưới dạng [Date Time HourMinute]
-
Nếu bạn có hai cột ngày giờ, chẳng hạn như [Thời gian Bắt đầu] và [Thời gian Kết thúc], và sự khác biệt thời gian giữa chúng tính bằng giây dưới dạng một cột gọi là [Thời lượng], hãy loại bỏ cả hai cột khỏi danh sách và thêm:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
Nếu bạn sử dụng từ khóa ms thay vì ss, bạn sẽ nhận được thời lượng tính theo mili giây
Sử dụng số đo được tính TOÁN DAX thay vì cột
Nếu trước đây bạn đã làm việc với ngôn ngữ biểu thức DAX, có thể bạn đã 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 số đ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 được sử dụng trong PivotTable hoặc báo cáo khác.
Một kỹ thuật tiết kiệm bộ nhớ là để thay thế cột thường xuyên hoặc tính toán bằng các biện pháp tính toán. Ví dụ cổ điển là Đơn Giá, Số lượng và Tổng. 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 lần thứ ba bằng DAX.
Bạn nên giữ lại 2 cột nào?
Trong ví dụ ở trên, giữ Số lượng và Đơn Giá. Hai giá trị này có ít giá trị hơn Tổng. Để tính Tổng, hãy thêm một số đo được tính toán như:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
Cột được tính toán giống như các cột thông thường, trong đó cả hai đều chiếm không gian trong mô hình. Ngược lại, các số đo được tính toán được tính toán khi bay và không chiếm không gian.
Kết luận
Trong bài viết này, chúng tôi đã trao đổi về một số phương pháp có thể giúp bạn xây dựng mô hình hiệu quả bộ nhớ hơn. Cách giảm kích cỡ tệp và yêu cầu về bộ nhớ của mô hình dữ liệu là giảm tổng số cột và hàng cũng như số lượng giá trị duy nhất xuất hiện trong mỗi cột. Dưới đây là một số kỹ thuật mà chúng tôi đã đề cập:
-
Loại bỏ cột dĩ nhiên là cách tốt nhất để tiết kiệm không gian. Quyết định xem bạn thực sự cần cột nào.
-
Đôi khi, bạn có thể loại bỏ cột và thay thế bằng số đo được tính trong bảng.
-
Bạn có thể không cần tất cả các hàng trong 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, tách riêng một cột thành nhiều phần riêng biệt là một cách hay để giảm số lượng các giá trị duy nhất trong một cột. Mỗi phần sẽ có một số lượng nhỏ các giá trị duy nhất và tổng kết hợp 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 để sử dụng làm 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ột chứa nhiều thông tin hơn mức bạn cũng cần. Ví dụ: giả sử một cột lưu trữ 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. Làm tròn có thể rất hiệu quả trong việc giảm kích thước của một cột số.
Bây giờ bạn đã làm những gì có thể để giảm kích cỡ sổ làm việc của mình, hãy cân nhắc việc 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 hóa 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
Trình tối ưu hóa 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
