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

Quan trọng:  Bài viết này là dịch máy, hãy xem tuyên bố miễn trừ trách nhiệm. Bạn hãy tìm phiên bản tiếng Anh của bài viết này tại đây để tham khảo.

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

Ghi chú: Bài viết này mô tả mô hình dữ liệu trong Excel 2013. Tuy nhiên, cùng một dữ liệu lập mô hình và các tính năng Power Pivot giới thiệu trong Excel 2013 cũng áp dụng cho Excel 2016. Không có hiệu quả nhỏ điểm khác biệt giữa các phiên bản Excel trước.

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

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 thiết kế chặt chẽ để dễ dàng làm việc hơn và dùng ít bộ nhớ hơn. Dành thời gian để tìm hiểu những cách thực hành tốt nhất trong thiết kế mô hình hiệu quả sẽ giúp rút ngắn quá trình đối với bất kỳ mô hình nào bạn tạo ra và sử dụng, cho dù bạn đang xem trong Excel 2013, Office 365 SharePoint Online, trên Office Online Server, hoặc trong SharePoint 2013.

Cân nhắc cũng chạy trình tối ưu hóa kích cỡ sổ làm việc. Nó phân tích sổ làm việc Excel của bạn và nếu có thể nén thêm nó. 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ớ

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

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

Làm thế nào để loại trừ các cột

Thế còn việc chỉ lọc các cần thiết hàng?

Nếu cần có cột; chúng ta có thể giảm bớt khoảng cách mà chúng chiếm?

Sửa đổi cột Datetime

Sửa đổi truy vấn SQL

Sử dụng DAX thước đo thay vì dùng cột tính

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

Kết luận

Nối kết liên quan

Tỷ lệ nén và bộ máy phân tích trong bộ nhớ

Mô hình dữ liệu trong Excel dùng bộ máy 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 hiệu quả để giảm các yêu cầu lưu trữ, thu nhỏ bộ kết quả cho đến khi nó chỉ bằng một phần nhỏ của kích cỡ ban đầu.

Trung bình, bạn có thể mong đợi một mô hình dữ liệu có kích cỡ nhỏ hơn dữ liệu tại thời điểm ban đầu từ 7 đến 10 lần. Ví dụ, nếu bạn đang nhập 7 MB dữ liệu từ một cơ sở dữ liệu SQL Server, thì mô hình dữ liệu trong Excel có thể là 1 MB hoặc ít hơn. Mức độ nén thực tế đạ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àng có nhiều giá trị duy nhất, thì càng cần thêm 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ì xây dựng một mô hình hiệu quả để giảm thiểu sử dụng bộ nhớ thực chất là việc tối đa hóa sức nén, và cách đơn giản nhất để làm điều này là tránh dùng bất kỳ cột nào không thực sự cần thiết, đặ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.

Ghi chú: Sự khác biệt 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 là nên có nhiều cột với một số ít các giá trị duy nhất, thay vì có một cột với một số lượng lớn các giá trị duy nhất. Phần tối ưu hóa Datetime sẽ giới thiệu chi tiết về kỹ thuật này.

Cột không tồn tại sẽ có mức độ sử dụng bộ nhớ thấp nhất

Cột đạt hiệu quả sử dụng bộ nhớ cao nhất là cột mà bạn chưa từng nhập dữ liệu lúc đầu. 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 cột đó có đóng góp vào phân tích mà bạn muốn thực hiện hay không. Nếu câu trả lời là không hoặc bạn không chắc chắn, hãy loại bỏ cột đó. Sau này, bạn luôn có thể thêm các cột này nếu cần.

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

Ví dụ thứ nhất liên quan đến dữ liệu có nguồn gốc từ một kho dữ liệu. Trong một kho dữ liệu, việc tìm các đồ tạo tác của quá trình ETL tải và làm mới dữ liệu trong kho thường 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 ra khi tải dữ liệu. Không cột nào trong số các cột này là cần thiết trong mô hình và nên được loại bỏ khi bạn nhập dữ liệu.

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

Nhiều bảng, bao gồm cả bảng sự kiện, có các khóa chính. Đối với hầu hết các bảng, chẳng hạn như các 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 để bạn có thể sử dụng để tạo ra các mối quan hệ trong mô hình.

Các bảng sự kiện khác nhau. Trong bảng sự kiện, các khóa chính được dùng để xác định duy nhất mỗi hàng. Tuy cần thiết cho các mục đích chuẩn hóa nhưng bảng sự kiện không mấy hữu dụng trong mô hình dữ liệu mà bạn muốn chỉ có 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ừ một bảng sự kiện, bạn đừng bao gồm khóa chính. Các khóa chính trong một bảng sự kiện chiếm dung lượng lớn trong mô hình và không mang lại lợi ích gì bởi không thể sử dụng chúng để tạo ra các quan hệ.

Ghi chú: Trong kho dữ liệu và cơ sở dữ liệu đa chiều, các bảng lớn bao gồm chủ yếu là dữ liệu số thường được gọi là "bảng sự kiện". Bảng sự kiện thường bao gồm các hoạt động kinh doanh hoặc các dữ liệu giao dịch, chẳng hạn như doanh số bán hàng và các điểm dữ liệu 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 một bảng sự kiện có chứa dữ liệu kinh doanh 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 trong mô hình để hỗ trợ phân tích dữ liệu. Cột bạn muốn loại bỏ là cột khóa chính của bảng sự kiện, bao gồm các giá trị duy nhất chỉ tồn tại trong bảng sự kiện và không nơi nào khác. Vì bảng sự kiện rất lớn nên một vài trong số các lợi ích lớn nhất của hiệu quả mô hình sẽ phát sinh từ việc loại trừ các hàng hoặc các cột ra khỏi bảng sự kiện.

Làm thế nào để loại trừ các cột không cần thiết

Mô hình có hiệu quả chứa chỉ những cột mà bạn cần thực sự trong sổ làm việc của bạn. Nếu bạn muốn để điều khiển cột nào được bao gồm trong mô hình, bạn sẽ phải sử dụng hướng dẫn nhập bảng trong Power Pivot bổ trợ để 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 hãy chọn bảng để 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 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 chọn những cột mà bạn muốn sau khi đã xem xét liệu các cột đó có cần cho phân tích hay không.

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

Thế còn việc chỉ lọc các hàng cần thiết thì sao?

Nhiều bảng trong kho dữ liệu và cơ sở dữ liệu của doanh nghiệp chứa những dữ liệu lịch sử dồn tích 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 quan tâm có chứa thông tin dành cho các lĩnh vực kinh doanh không cần thiết đối với 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 bỏ các dữ liệu lịch sử hoặc không liên quan và như vậy tiết kiệm được nhiều dung lượng trong mô hình. Trong hình dưới đây, bộ lọc ngày được sử dụng để truy xuất chỉ những hàng có chứa dữ liệu cho năm hiện tại, không bao gồm các 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

Nếu cần có cột, liệu chúng ta có thể giảm bớt dung lượng bộ nhớ mà chúng chiếm hay không?

Có thêm một vài kỹ thuật khác mà bạn có thể áp dụng để làm cho cột hiệu quả hơn khi nén. Hãy nhớ rằng đặc tính duy nhất của cột gây ảnh hưởng đến việc nén là số lượng các 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 các giá trị duy nhất.

Sửa đổi cột Datetime

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

Cột datetime bao gồm một phần ngày và thời gian. Khi bạn tự hỏi liệu bạn 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 ở cấp độ giờ không? , phút? , Giây? , phần nghìn giây?

  • Tôi có nhiều cột Datetime có phải vì tôi muốn tính toán sự khác biệt 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 bạn trả lời từng câu hỏi này sẽ quyết đị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 đều yêu cầu sửa đổi truy vấn SQL. Để dễ dàng hơn trong việc thực hiện sửa đổi truy vấ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 ra một cột, bạn sẽ thay đổi kết cấu của truy vấn từ một định dạng viết tắt (CHỌN *) sang một câu lệnh CHỌN có chứa các tên cột đầy đủ điều kiện mà như vậy sẽ dễ dàng sửa đổi hơn rất nhiều.

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 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 cho thấy truy vấn SQL được dùng để nhập 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 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 toàn bộ nội dung của một bảng mà không bỏ chọn bất kỳ cột hoặc áp dụng bất kỳ bộ lọc nào, bạn sẽ thấy truy vấn ở dạng "Chọn* từ" gây 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

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

  1. Đối với các cột có chứa dữ liệu tiền tệ hoặc số thập phân, nếu bạn không cần phần thập phân, hãy dùng cú pháp này để bỏ phần 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 phần lẻ của các 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 đến hàng đơn vị, hàng chục, hàng trăm ...

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

    “SELECT CAST (dbo.Bigtable.[Date Time] as date) AS [Date Time]) “

  3. Nếu bạn có một cột Datetime tên là dbo.Bigtable [Date Time] và bạn cần cả phần Ngày và Thời gian, hãy dùng nhiều cột trong truy vấn SQL thay vì cột một Datetime đơn lẻ:

    “SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    Bạn có thể dùng bao nhiêu cột tùy ý để 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 muốn chúng tập hợp lại thành một cột thời gian, bạn có thể dùng cú pháp:

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Nếu bạn có hai cột datetime, như [Start Time] và [End Time] và điều bạn thực sự cần là sự chênh lệch thời gian giữa hai cột theo hàng giây như là một cột được gọi là [Duration], hãy loại bỏ cả hai cột ra khỏi danh sách và thêm:

    “datediff(ss,[Start Date],[End Date]) as [Duration]”

    Nếu bạn dùng từ khóa ms thay vì ss, bạn sẽ có được khoảng thời gian tính bằng phần nghìn giây

Dùng các thước đo được tính DAX thay vì dùng 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 tính toán được dùng để có được các cột mới dựa trên một số cột khác trong mô hình, còn các thước đo được tính sẽ được xác định một lần trong mô hình, nhưng chỉ được đánh giá khi dùng trong PivotTable hay 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 thường hoặc cột được tính toán bằng các thước đo được tính. Ví dụ cổ điển là Đơn Giá, Số lượng và Tổng. Nếu bạn có cả ba cột, bạn có thể tiết kiệm bộ nhớ bằng cách chỉ duy trì chỉ hai cột và tính toán cột thứ ba bằng cách dùng DAX.

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

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

“TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])

Các cột được tính toán giống như các cột thông thường ở chỗ cả hai đều chiếm bộ nhớ trong mô hình. Ngược lại, các thước đo được tính sẽ được tính nhanh và không chiếm bộ nhớ.

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 tiếp cận có thể giúp bạn xây dựng một mô hình có hiệu quả sử dụng bộ nhớ cao hơn. Cách để giảm kích cỡ tệp và yêu cầu bộ nhớ của một mô hình dữ liệu là làm 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ố phương pháp chúng tôi đã giới thiệu:

  • Loại bỏ cột đương nhiên là cách tốt nhất để tiết kiệm bộ nhớ. Quyết định xem bạn thực sự cần những cột nào.

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

  • Có thể bạn 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 một cột đơn thành nhiều phần riêng biệt là một cách hữu hiệu để 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à giá trị tổng cộng sẽ nhỏ hơn so với 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ư các slicer trong các báo cáo của bạn. Bạn có thể tạo ra cấu trúc phân cấp từ các phần như Giờ, Phút và Giây khi thích hợp.

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

Bây giờ bạn đã làm những gì bạn có thể giảm kích cỡ sổ làm việc của bạn, hãy xem xét cũng chạy trình tối ưu hóa kích cỡ sổ làm việc. Nó phân tích sổ làm việc Excel của bạn và nếu có thể nén thêm nó. 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ính và giới hạn của Mô hình Dữ liệu

Tải xuống 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 trong Excel

Ghi chú: Tuyên bố miễn trừ trách nhiệm Dịch Máy: Bài viết này do một hệ thống máy tính dịch mà không có sự can thiệp của con người. Microsoft cung cấp những bản dịch máy này để giúp người dùng không nói tiếng Anh hiểu nội dung về các sản phẩm, dịch vụ và công nghệ của Microsoft. Do bài viết này được dịch máy nên có thể có các lỗi về từ vựng, cú pháp hoặc ngữ pháp.

Phát triển các kỹ năng 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.

×