Trong Excel, bạn có thể tạo các mô hình dữ liệu có chứa hàng triệu hàng, rồi 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. Có thể tạo mô hình dữ liệu có hoặc không có 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 vài lý do để không xây dựng. Đầu tiên, các mô hình lớn chứa nhiều bảng và cột là quá mức cần thiết đối với hầu hết các phân tích và tạo nên một Danh sách Trường cồng kềnh. 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ó chung 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 thước của tệp Excel ở 10 MB. Đối với các mô hình dữ liệu sổ làm việc có chứa hàng triệu hàng, bạn sẽ gặp phải giới hạn 10 MB khá nhanh. 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ễ làm việc hơn và sử dụng ít bộ nhớ hơn. Dành thời gian để tìm hiểu các biện pháp tốt nhất trong việc thiết kế mô hình hiệu quả sẽ mang lại lợi ích cho 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 máy chủ Office Web Apps 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
Không gì tốt hơn cột không tồn tại vì mức sử dụng bộ nhớ thấp
Đ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ó chứ?
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ụ triển khai các kỹ thuật nén mạnh mẽ để giảm yêu cầu lưu trữ, thu nhỏ tập hợp kết quả cho đến khi chỉ còn một phần nhỏ so với kích thước ban đầu.
Trung bình, bạn có thể kỳ vọng một mô hình dữ liệu nhỏ hơn 7 đến 10 lần so với cùng dữ liệu đó tại điểm xuất phát của nó. 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 có kích cỡ từ 1 MB trở xuống. Mức độ nén thực tế phụ thuộc chủ yếu vào số lượng các giá trị duy nhất trong mỗi cột. Càng nhiều giá trị duy nhất, càng cần nhiều bộ nhớ để lưu trữ chúng.
Tại sao chúng ta nói về 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ả nhằm giảm thiểu việc sử dụng bộ nhớ là về 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ỏ mọi cột mà bạn không thực sự cần, đặc biệt nếu các cột đó chứa số lượng lớn các giá trị duy nhất.
Lưu ý
Sự khác biệt trong yêu cầu lưu trữ cho các cột riêng lẻ có thể là rất lớn. Trong một số trường hợp, bạn nên có nhiều cột có số lượng giá trị duy nhất thấp hơn là nên có một cột có số lượng giá trị duy nhất nhiều. Phần về tối ưu hóa ngày giờ đề cập chi tiết về kỹ thuật này.
Không gì tốt hơn cột không tồn tại vì mức sử dụng bộ nhớ thấp
Cột sử dụng bộ nhớ hiệu quả nhất là cột mà bạn chưa bao giờ nhập ngay từ đầu. Nếu bạn muốn xây dựng một mô hình hiệu quả, hãy xem xét từng cột và tự hỏi liệu nó có đóng góp vào phân tích bạn muốn thực hiện hay không. Nếu không tìm hiểu 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ày nếu cần.
Hai ví dụ về các cột nên luôn được loại trừ
Ví dụ đầu tiên liên quan đến dữ liệu bắt nguồn từ kho dữ liệu. Trong kho dữ liệu, việc tìm thấy các thành phần lạ của các quy trình ETL tải và làm mới dữ liệu trong kho là rất phổ biến. Các cột như "ngày tạo", "ngày cập nhật" và "chạy ETL" được tạo khi dữ liệu được tải. Không cần cột nào trong số này trong mô hình và nên bỏ chọn 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 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ư những bảng chứa dữ liệu khách hàng, nhân viên hoặc bán hàng, bạn sẽ muốn có khóa chính của bảng để có thể sử dụng khóa đó nhằm tạo mối quan hệ trong mô hình.
Bảng dữ kiện thì khác. Trong bảng dữ kiện, khóa chính được sử dụng để xác định duy nhất mỗi hàng. Mặc dù cần thiết cho mục đích chuẩn hóa, nhưng hàm này ít hữu ích hơn trong mô hình dữ liệu nơi bạn chỉ muốn các cột được dùng để phân tích hoặc thiết lập các mối quan hệ bảng. Vì lý do này, khi nhập từ bảng dữ kiện, đừng bao gồm khóa chính của bảng dữ kiện. Các khóa chính trong bảng dữ kiện sử dụng một lượng lớn dung lượng trong mô hình, nhưng không mang lại lợi ích gì, vì chúng không thể được dùng để tạo 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 dạng số thường được gọi là "bảng dữ kiện". Bảng dữ kiện thường bao gồm hiệu suất kinh doanh hoặc dữ liệu giao dịch, chẳng hạn như các điểm dữ liệu doanh thu và chi phí được tổng hợp và căn chỉnh theo 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ữ kiện có chứa dữ liệu nghiệp vụ hoặc có thể được sử 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 mà 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. Do bảng dữ kiện rất lớn, nên một số lợi ích lớn nhất trong hiệu quả của mô hình bắt nguồn từ việc loại trừ hàng hoặc cột khỏi 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 sẽ 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 nào được đưa vào 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 thay vì 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.
Với từng bảng, bạn có thể bấm 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 nên bỏ chọn tất cả các cột, sau đó tiếp tục kiểm tra các cột bạn muốn, sau khi xem xét liệu chúng có cần thiết cho phân tích hay không.
Còn việc chỉ lọc các hàng cần thiết thì sao?
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 một 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 đang quan tâm chứa thông tin về các lĩnh vực kinh doanh không bắt buộc cho phân tích cụ thể của bạn.
Dùng trình hướng dẫn Nhập Bảng, bạn có thể lọc các dữ liệu lịch sử hoặc không liên quan, do đó tiết kiệm rất nhiều không gian trong mô hình. Trong hình dưới đây, bộ lọc ngày được sử dụng để chỉ truy xuất các hàng có chứa dữ liệu cho năm hiện tại, không bao gồm 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ó chứ?
Có một vài kỹ thuật bổ sung mà bạn có thể áp dụng để làm cho một cột trở thành một ứng viên tốt hơn để nén. Hãy nhớ rằng đặc điểm duy nhất của cột ảnh hưởng đến nén là số lượng giá trị duy nhất. Trong mục 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 ngày giờ
Trong nhiều trường hợp, cột Ngày giờ chiếm nhiều không gian. May mắn là có một số cách để giảm yêu cầu lưu trữ cho loại 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 khi xây dựng truy vấn SQL.
Cột ngày giờ bao gồm phần ngày và thời gian. Khi bạn tự hỏi 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 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? , phút? , Giây? , mili giây?
- Tôi có nhiều cột Ngày giờ vì muốn tính toán chênh lệch 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ẽ xác định các tùy chọn bạn để xử lý 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 ra í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 viết tắt (SELECT *) thành câu lệnh SELECT bao gồm tên cột đủ điều kiện, giúp sửa đổi dễ dàng hơn nhiều.
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 sử dụng để nhập bảng. Nếu bạn đã lọc bất kỳ cột nào trong khi nhập, truy vấn của bạn sẽ bao gồm các tên cột đầy đủ tiêu chuẩn:
Ngược lại, nếu bạn đã nhập 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 "Select * from ", khiến 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ỡ mô hình của bạn.
- Đố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 sau để loại bỏ số thập phân:
"SELECT ROUND([Decimal_column_name],0)... .”
Nếu bạn cần số tiền xu nhưng không cần số xu, hãy thay thế số 0 bằng 2. Nếu bạn sử dụng số âm, bạn có thể làm tròn tới đơn vị, hàng chục, hàng trăm, v.v. - Nếu bạn có cột Datetime tên là dbo. Bảng lớn. [Ngày giờ] và bạn không cần phần Thời gian, hãy sử dụng cú pháp để xóa thời gian:
"CHỌN CAST (dbo. Bảng lớn. [Date, time] as date), AS [Date, time]) " - Nếu bạn có cột Datetime tên là dbo. Bảng lớn. [Ngày giờ] và bạn cần cả phần Ngày và Giờ, hãy sử dụng nhiều cột trong truy vấn SQL thay vì cột Ngày giờ duy nhất:
"CHỌN CAST (dbo. Bảng lớn. [Ngày Thời gian] AS [Ngày Giờ ]) AS [Ngày Giờ ])
DatePart(hh, dbo. Bảng lớn. [Date, Time]) dưới dạng [ngày, giờ, giờ],
DatePart(mi, dbo. Bảng lớn. [Date, Time]) dưới dạng [ngày, thời gian, phút],
DatePart(ss, dbo. Bảng lớn. [Date, Time]) dưới dạng [ngày, thời gian, giây],
DatePart(MS, DBO. Bảng lớn. [Date, Time]) dưới dạng [Ngày, Thời gian, Mili giây]"
Sử dụng số lượng cột tùy ý để lưu trữ từng phần vào các cột riêng biệt. - Nếu bạn cần giờ và phút, đồng thời muốn chúng kết hợp như một cột thời gian, bạn có thể sử dụng cú pháp:
Timefromparts(datepart(hh, dbo. Bảng lớn. [Date, Time]), datepart(mm, dbo. Bảng lớn. [Date, Time])) dưới dạng [Ngày, Thời gian, Giờ, Phút] - 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à điều bạn thực sự cần là sự chênh lệch về thời gian giữa chúng tính bằng giây dưới dạng một cột có tên là [Khoảng 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]) dưới dạng [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 tính theo mili giây
Dùng thước đ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, bạn có thể đã biết rằng các cột được tính toán được sử dụng để rút ra 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 thước đo được tính đượ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ác cột thường hoặc được tính bằng các số đo được 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 gói thứ ba bằng DAX.
Bạn nên giữ 2 cột nào?
Trong ví dụ trên, hãy 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 thước đo được tính toán như:
"TotalSales:=sumx('Bảng Doanh số','Bảng Bán hàng'[Đơn Giá]*'Bảng Bán hàng'[Số lượng])"
Cột được tính giống như cột thông thường, trong đó cả hai cột đều chiếm không gian trong mô hình. Ngược lại, các thước đo được tính toán nhanh và không lấy 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ố phương pháp có thể giúp bạn xây dựng một mô hình tiết kiệm bộ nhớ hơn. Cách để giảm yêu cầu về kích cỡ tệp 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 các 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 tất 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ỏ một cột và thay thế bằng một 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 các hàng trong Trình hướng dẫn Nhập Bảng.
- Nói chung, tách một cột đơn 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 cộng sẽ nhỏ hơn cột thống 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 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.
- Trong nhiều trường hợp, các cột còn chứa nhiều thông tin hơn mức bạn cần. Ví dụ: giả sử một cột lưu trữ cá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. Làm tròn có thể rất hiệu quả trong việc giảm kích cỡ cột số.
Giờ thì 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 xem xét chạy cả 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