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, rồi 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 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.
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ó rất ít khác biệt giữa các phiên bản này của Excel.
Mặc dù bạn có thể dễ dàng xây dựng các mô hình dữ liệu lớn Excel nhưng có một số lý do không thể giải quyết. Trước tiên, các mô hình lớn có chứa nhiều bảng và cột sẽ được dùng quá nhiều đối với hầu hết các phân tích và tạo thành một Danh sách Trường phức t00. Thứ hai, mô hình lớn sử dụng 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 dùng chung các 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 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 khá nhanh 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 thực hành tốt nhất trong thiết kế mô hình hiệu quả sẽ giúp tiết kiệm chi phí cho mọi mô hình bạn tạo và sử dụng, cho dù bạn đang xem mô hình đó trong Excel 2013, Microsoft 365 SharePoint Online, trên Office Online Server hay 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 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ớ
Mô hình dữ liệu trong Excel 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 về dung lượng lưu trữ, thu nhỏ tập kết quả cho đến khi thu nhỏ một phần của kích cỡ ban đầu.
Trung bình, bạn có thể mong đợi mô hình dữ liệu nhỏ hơn 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ừ một cơ sở dữ liệu SQL Server, mô hình dữ liệu trong Excel có thể dễ dàng có 1 MB trở xuống. Mức độ nén thực sự có đượ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àng duy nhất thì càng cần có nhiều bộ nhớ để lưu trữ.
Tại sao chúng ta đang 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 việc sử dụng bộ nhớ là thực hiện tối đa hóa quá trình nén, và cách dễ nhất để làm điều này là loại bỏ bất kỳ cột nào mà 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.
Lưu ý: 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ể là 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 thay vì một cột chứa số lượng giá trị duy nhất cao. Mục về tối ưu hóa Datetime bao gồm chi tiết về kỹ thuật này.
Không có gì xảy ra với cột không tồn tại vì mức sử dụng bộ nhớ thấp
Cột hiệu quả nhất về bộ nhớ là cột mà bạn chưa bao giờ nhập vào đầ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 thư không chắc chắn hoặc không chắc chắn, hãy bỏ qua. Sau này, bạn luôn có thể thêm các cột mới 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 xuất phát từ nhà kho dữ liệu. Trong nhà kho dữ liệu, thông thường sẽ có một số thành phần khác nhau của quy trình ETL tải và làm mới dữ liệu trong nhà kho. Các cột như "ngày tạo", "ngày cập nhật" và "Chạy ETL" được tạo ra khi dữ liệu được tải. Mô hình không cần cột nào trong số này và sẽ đượ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 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 bán hàng, bạn sẽ muốn 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 dùng để nhận dạng riêng từng hàng. Trong khi cần thiết cho mục đích chuẩn hóa, nó sẽ ít hữu ích hơn trong mô hình dữ liệu mà 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, bạn đừng đưa khóa chính vào. Các khóa chính trong bảng dữ kiện sử dụng một lượng không gian vô cùng lớn trong mô hình, nhưng không mang lại lợi ích gì, vì không thể dùng chúng để tạo mối quan hệ.
Lưu ý: Trong các nhà 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 về 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à căn chỉnh theo đơ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 một bảng dữ kiện 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 bao gồm trong 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 ở nơi nào khác. Vì bảng dữ kiện quá lớn nên một số lợi ích lớn nhất trong hiệu quả mô hình đều xuất phát 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 có cột nào 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 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 sẽ 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 & Lọc và chọn các phần của bảng 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 xem xét liệu có bắt buộc đối với phân tích hay không.
Lọc hàng cần thiết thì sao?
Nhiều bảng trong cơ sở dữ liệu của công ty và các nhà kho dữ liệu chứa dữ liệu lịch sử được tích lũy trong những 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 về các lĩnh vực của doanh nghiệp không cần thiết cho 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 đó lưu rất nhiều khoảng trố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 tôi vẫn có thể giảm chi phí không gian của chúng?
Có một số kỹ thuật bổ sung mà bạn có thể áp dụng để giúp cột trở thành ứng viên 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 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ờ sẽ mất 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 của bạn trong việc xây SQL vấn.
Cột Ngày giờ bao gồm 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 nhiều lần cùng một câu hỏi 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 ở mức giờ không? , phút? , Giây? , mili giây?
-
Tôi có nhiều cột Datetime vì tôi muốn tính toán chênh lệch giữa các cột đó hay chỉ để tổng hợp dữ liệu theo năm, tháng, quý, v.v..
Cách thức bạn trả lời từng câu hỏi này sẽ xác định tùy chọn để xử lý cột Ngày giờ.
Tất cả các giải pháp này yêu cầu sửa đổi truy SQL kế. Để 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 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, dễ sửa đổi hơn rất nhiều.
Hãy cùng 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 hiện SQL bảng 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 SQL truy vấn được sử dụng để tạo bảng. Nếu bạn đã lọc bất kỳ cột nào trong quá trình 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 một bảng vào 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 SQL trang
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 tiền tệ hoặc dữ liệu 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 các cent nhưng không cần phân số của xu, hãy thay thế 0 bằng 2. Nếu bạn 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. BigTable. [Ngày Giờ] và bạn không cần phần Thời gian, hãy sử dụng cú pháp để loại bỏ thời gian:
"SELECT CAST (dbo. BigTable. [Date time] as date) AS [Date time]) "
-
Nếu bạn có cột Datetime có tên là dbo. BigTable. [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ì một cột Ngày giờ:
"SELECT CAST (dbo. BigTable. [Date Time] as date ) AS [Date Time],
datepart(hh, dbo. BigTable. [Date Time]) có dạng [Ngày Giờ],
datepart(mi, dbo. BigTable. [Date Time]) dưới dạng [Phút Thời gian ngày],
datepart(ss, dbo. BigTable. [Date Time]) dưới dạng [Date Time Seconds] (Giây thời gian cho ngày],
datepart(ms, dbo. BigTable. [Date Time]) dưới dạng [Date Time Milliseconds]"
Sử dụng số cột cần thiết để lưu trữ từng phần trong các cột riêng biệt.
-
Nếu bạn cần giờ và phút, đồng thời muốn các giờ và cột đó kết hợp lại với nhau, bạn có thể dùng cú pháp:
Timefromparts(datepart(hh, dbo. BigTable. [Date Time]), datepart(mm, dbo. BigTable. [Date Time])) dưới dạng [Date Time HourMinute]
-
Nếu bạn có hai cột ngày giờ, 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à khoảng chênh lệch thời gian tính bằng giây dưới dạng cột có tên [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ẽ tính theo mili giây
Sử dụng các số đo được dax tính toán 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 để 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ỉ đánh giá khi được sử dụng trong PivotTable hoặc báo cáo khác.
Một kỹ thuật lưu bộ nhớ là thay thế cột thông thường hoặc cột được tính bằng 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 dung lượng bằng cách chỉ duy trì hai và tính toán khoảng trắng thứ ba bằng cách sử dụng DAX.
Bạn nên giữ lại 2 cột nào?
Trong ví dụ ở trên, giữ lại 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('Bảng Doanh số','Bảng Doanh số'[Đơn Giá]*'Bảng Doanh số'[Số lượng])"
Cột được tính toán giống như cột thông thường, ở chỗ cả hai đều lấy 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 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ô hình sử dụng bộ nhớ hiệu quả hơn. 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 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 tất nhiên là cách tốt nhất để tiết kiệm không gian. Quyết định những cột bạn thực sự cần.
-
Đôi khi, bạn có thể loại bỏ cột và thay thế bằng giá trị đ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 bỏ 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 trong số các 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á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ộ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ả 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ố.
Bây giờ bạn đã làm được 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 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
