Load the data nguồn và create the model Load the date nguồn
Một trong ba công cụ giúp phân tích dữ liệu excel là Power Pivot có thể cho phép người
sử dụng làm việc với các dữ liệu có dung lượng lớn, tạo mô hình dữ liệu, thiệt lập quan
hệ và tạo lập các phép toán có độ khó cao nhưng nó chỉ cso thể hổ trợ với phiên bản Excel 2016 trở lên.
Các nguồn dữ liệu có thể kết nối là Microsoft Access, Excel, SQL Server… Từ đó sẽ thực
hiện được các mô hình dữ liệu, phân tích dữ liệu hiệu quả với Pivot Table.
Bước 1: Đăng nhập vào mlearning môn học Tin Học Ứng Dụng => chọn file có tên Data
Báo cáo môn học để tải về máy thực hiện đề tại báo cáo nhóm, khi tải xong file sẽ có tên
SOURCE và được lưu ở dạng nén.
Bước 2: Với tập tin đang ở dạng nén WinRAR ta phải giải nén tệp tin để lấy dữ liệu trong SOURCE => Extract Here
Bước 3: Sau khi thực hiện viêc giải nén, ta bắt đầu thực hiện các yêu cầu của báo cáo.
Mở ứng dụng Excel của Microsoft để tải dữ liệu nguồn cần cho việc thực hiện báo cáo
lên ứng dụng và thao tác tạo phân tích và báo cáo số liệu của doanh nghiệp => khi giao diện Excel hiện ra.
Cách kích hoạt Power Pivot trong Excel
Bước 4: Để có thể sử dụng chức năng của Power Pivot, vì chức năng không có sẳn nên ta
cần vào thanh công cụ của Excel bằng các bước:
Bấm vào File trên thanh công cụ => chọn Options
Lưu ý: Như đã nói ở trên chúng ta chỉ có thể thực hiện tính năng Power Pivot chỉ xuất
hiện trên phần mềm Excel từ năm 2016 trở lên.
Bảng Excel Option hiện ra chọn Add in => ở phần Manage chọn COM Add-ins => click Go để thêm tính năng
Bảng COM Ađ-ins xuất hiện ta chọn Microsoft Power Pivot for Excel => OK
Bước 5: Ta sẽ load dữ liệu từ nguồn dữ liệu SOURCE vừa lưu vào máy lên Power Pivot
bằng cách: Ta nhấn vào mục Power Pivot trên thanh công cụ mới thêm, Ấn chọn mục Manage
Chọn From Database (tùy vào nguồn dữ liệu muốn tải lên mà ta chọn lệnh cho phù hợp)
=> Vì nguồn dữ liệu của ta ở Access nên ta sẽ chọn From Access
Bảng Table Import Wizard xuất hiện Ta click vào Browse
Chọn file nguồn StoreSales => Open
Chọn mặc định trong Table Import Wizard => chọn Next
Bước 6: Các thao tác để chọn nguồn dữ liệu để tiến hành thực hiện báo cáo, phân tích đã
được tải lên Power Pivot xong ta bấm chọn vào Next 2 lần để hoàn thành => Các bảng dữ
liệu hiện ra, ta muốn chọn dữ liệu nào để thao tác thì nhấn chọn vào dữ liệu đó. Trường
hợp này ta chọn tất cả các dữ liệu => Và chọn Finish
Nhấn chọn hết tất cả cột dữ liệu (ta cũng có thể muốn tải dữ liệu nào thì chọn dữ liệu đó
để tải lên) => Finish để hoàn thành tải lên
Chờ đợi trong 1-2s, dữ liệu sẽ được tải lên và bảng hiện như hình là thành công => ta
bấm Close và tất cả dữ liệu đã được tải lên hoàn tất. Thiết lập Create the model
Model ở dạng bảng table để đảm bảo tính ràng buộc và toàn vẹn của dữ liệu, từ đó chúng
ta phải tạo các mối quan hệ mang tính Relationship giữa các bảng trên các khóa liên kết.
Khóa chính (Primary Key) không được phép là các field rỗng và không được trùng lập.
Mỗi quan hệ chỉ được khai báo một khóa chính.
Khóa ngoại (Foreign Key) là ràng buộc giá trị dữ liệu nằm trong bảng này phải xuất hiện
ở bảng khác, phải có kiểu dữ liệu, số lượng có sắp xếp tương ứng với khóa chính.
Bảng trong model sẽ có 3 mối quan hệ Mối quan hệ 1-1:
Ví dụ: sinh viên nộp bài tập và bài tập của sinh viên Mối quan hệ 1-N
Ví dụ: Một công ty có nhiều phòng ban, mỗi phòng ban sẽ có nhiều nhân viên khác nhau.
Những mỗi nhân viên chỉ làm cho một phòng ban Mối quan hệ N-N
Ví dụ: một sinh viên có thể học nhiều môn và một môn học cũng có thể được học bởi nhiều sinh viên khác nhau Mô tả dữ liệu
Bảng 1. Dữ liệu dbo_DimDate STT Tên thuộc tính Diễn dãi Kiểu dữ liệu Ghi chú 1 FullDate Ngày tháng năm General Date Khóa chính 2 MonthName Tên Tháng Text 3 Month Number of year Số tháng Number 4 CalenderQuarter Lịch quý Number 5 CalenderYear Lịch năm Number 6 CalenderSemester Lịch định kỳ Number 7 FiscalQuarter Quý tài chính Number 8 FiscalYear Năm tài chính Number 9 FiscalSemes Kỳ tài chính Number
Bảng 2. Dữ liệu dbo_DimEmployee STT Tên thuộc tính Diễn dãi Kiểu dữ liệu Ghi chú 1 EmloyeeKey Nhân viên Number Khóa chính 2 ParentEmployeeKey Cha mẹ của nhân Number Khóa viên chính 3 EmployeeNationalID
Quốc tịch của nhân Number Khóa AlternateKey viên chính 4 ParentEmployeeNatio Quốc tịch của cha Text Khóa nalIDAlternateKey mẹ nhân viên chính 5 SalesTerritoryKey Lãnh thổ bán hàng Number Khóa chính 6 FirstName Tên Text 7 LastName Họ Text 8 MiddleName Tên đệm Text 9 NameStyle Phong cách tên True/false 10 Title Tiêu đề Text 11 HireDate Ngày cho thuê General Date 12 BirthDate Ngày sinh General Date 13 LoginID Số chứng minhnhân Text dân 14 EmailAddress Địa chỉ email Text 15 Phone Số điện thoại Text 16 MaritalStatus Tình trạng hôn nhân Text 17 Emergency Contact Người liên hệ Text Name 18 Emergency
Contact Số điện thoại của Text Phone người liên hệ 19 Salaried Flag Mức lương True/false 20 Gender Giới tính Text 21 Pay Frequency Tần suất thanh toán Whole number 22 BaseRate Lãi suất gốc Currency 23 Vacation Hours Số giờ nghỉ Whole number 24 Sick Leave Hours Số giờ nghỉ ốm Whole number 25 Current Flag Tỉ lệ True/false 26 SalesPersonFlag Người bán hàng True/false 27 DepartmentName Tên bộ phận 28 StartDate Ngày bắt đầu General Date 29 EndDate Ngày kết thúc General Date 30 Status Tình trạng Text 31 EmployeePhoto
Hình ảnh của nhân OLE Object viên
Bảng 3. Dữ liệu dbo_DimGeography STT Tên thuộc tính Diễn dãi Kiểu dữ liệu Ghi chú 1 GeographyKey Điạ lý Whole Khóa number chính 2 City Thành phố Text 3 StateProvinceCode Mã tỉnh Text 4 StateProvinceName Tên tỉnh Text 5 CountryRegionCode Quốc tịch Text 6 EnglishCountryRegion
Quốc tịch bằng tiếng Text Name Anh 7 SpanishCountryRegio
Quốc tịch bằng tiếng Text nName Tây Ban Nha 8 FrenchCountryRegion Quốc tịch bằng tiếng Name Pháp 9 PostalCode Mã bưu chính Text 10 SalesTerritoryKey Lãnh thổ bán hàng Whole number 11 IpAddressLocator
Vị trí địa lý thông Text qua IP
Bảng 4. Dữ liệu dbo_DimProduct STT Tên thuộc tính Diễn dãi Kiểu dữ liệu Ghi chú 1 ProductKey Sản phẩm Whole Khóa number chính 2 ProductAlternateKey Sản phẩm thay thế Text Khóa chính 3 ProductSubcategoryKey Danh mục sản phẩm Whole Khóa phụ number ngoại 4 WeightUnitMeasureCode
Mã đơn vị đo trọng Text lượng 5 SizeUnitMeasureCode Mã đơn vị đo kích Text cỡ 6 EnglishProductName Tên Tiếng Anh của Text sản phẩm 7 SpanishProductName Tên Tây Ban Nha Text của sản phẩm 8 FrenchProductName Tên Pháp của sản Text phẩm 9 StandardCost Chi phí tiêu chuẩn Currency 10 FinishedGoodsFlag Thành phẩm True/false 11 Color Màu sắc Text 12 SafetyStockLevel Mức tồn kho an toàn Whole number 13 ReorderPoint Điểm đặt hàng lại Whole number 14 ListPrice Giá niêm yết Whole number 15 Size Kích cỡ Text 16 SizeRange Các loại kích cỡ Text 17 Weight Trọng lượng General 18 DaysToManufacture
Ngày tháng sản xuất Whole number 19 ProductLine Dòng sản phẩm Text 20 DealerPrice Giá thị trường General 21 Class Loại Text 22 Style Kiểu dáng Text 23 ModelName Tênthươnghiệu Text 24 LargePhoto Hình ảnh Text 25 EnglishDescription Miêu tả bằng Tiếng Text Anh 26 FrenchDescription Miêu tả bằng tiếng Text Pháp 27 ChineseDescription Miêu tả bằng tiếng Text Trung 28 ArabicDescription Miêu tả bằng tiếng Text Arập 29 HebrewDescription Miêu tả bằng tiếng Text Do Thái 30 ThaiDescription Miêu tả bằng tiếng Text Thái 31 GermanDescription Miêu tả bằng tiếng Text Đức 32 JapaneseDescription Miêu tả bằng tiếng Text Nhật 33 TurkishDescription Miêu tả bằng tiếng Text Thổ Nhĩ Kỳ 34 StartDate Ngày sản xuất General Date 35 EndDate Thời hạn General Date 36 Status Tình trạng Text
Bảng 5. Dữ liệu dbo_DimProductCategory STT Tên thuộc tính Diễn dãi Kiểu dữ Ghi chú liệu 1 ProductCategoryKey
Khóa chính chủng loại Whole Khóa sản phẩm number chính 2
ProductCategoryAlternateKey Danh mục sản phẩm Whole thay thế number 3
EnglishProductCategoryName Tên loại sản phẩm bằng Text tiếng Anh 4 SpanishProductCategoryNam
Tên loại sản phẩm bằng Text e tiếng Tây Ban Nha 5 FrenchProductCategoryName
Tên loại sản phẩm bằng Text tiếng Pháp
Bảng 6. Dữ liệu dbo_DimProductSubcategory ST Tên thuộc tính Diễn dãi Kiểu dữ Ghi chú T liệu 1 ProductSubcategory Key Khóa chính danh Whole Khóa
mục sản phẩm phụ number chính 2
ProductSubcategoryAlternateKey Danh mục sản Whole phẩm phụ thay thế number 3
EnglishProductSubcategoryName Danh mục sản Text phẩm phụ bằng tiếng Anh 4 SpanishProductSubcategoryNam Danh mục sản Text e phẩm phụ bằng tiếng Tây Ban Nha 5 FrenchProductSubcategoryName Danh mục sản Text phẩm phụ bằng tiếng Pháp 6 ProductCategoryKey Danh mục sản Whole Khóa phẩm number ngoại
Bảng 7. Dữ liệu dbo_DimReseller STT Tên thuộc tính Diễn dãi Kiểu dữ liệu Ghi chú 1 ResellerKey Người bán Whole Khóa number ngoại 2 GeographyKey Địa lý Whole Khóa number ngoại 3 ResellerAlternateKey Người bán thay Text thế 4 Phone Điện thoại Text 5 BusinessType Loại hình kinh Text doanh 6 ResellerName Tên người bán Text 7 NumberEmployees Số lượng nhân Whole viên number 8 OrderFrequency Tần suất đặt Text hàng 9 OrderMonth Tháng đặt hàng Whole number 10 FirstOrderYear Năm đầu tiên Whole đặt hàng number 11 LastOrderYear Năm cuối đặt Whole hàng number 12 ProductLine Dòng sản Text phẩm 13 AddressLine1 Địa chỉ cơ sở Text 14 AddressLine2 Địa chỉ Text 15 AnnualSales Mức tiêu thụ Currency hàng năm 16 BankName Tên ngân hàng Text 17 MinPaymentType Loại thanh Whole toán tối thiểu number 18 MinPaymentAmount Khoản thanh Currency toán tối thiểu 19 AnnualRevenue Thu thập hàng Currency năm 20 YearOpened Năm thành lập Whole number
Bảng 8. Dữ liệu dbo_DimFactResellerSales STT Tên thuộc tính Diễn dãi Kiểu dữ liệu Ghi chú 1 ProductKey Sản phẩm Whole number Khóa Ngoại 2 ResellerKey Người bán Whole number Khóa Ngoại 3 EmployeeKey Nhân viên Whole number Khóa Ngoại 4 PromotionKey Quảng cáo Whole number 5 CurrencyKey Tiền tệ Whole number 6 SalesTerritoryKey Thiết lập Whole number lãnh thổ bán hàng 7 SalesOrderNumber Số đơn hàng Text người mua 8 SalesOrderLineNumber Số dòng Whole number đơn hàng người mua 9 RevisionNumber Số lần sửa Whole number đổi 10 OrderQuantity Số lượng Whole number đặt hàng 11 UnitPrice Giá đơn vị Currency 12 ExtendedAmount Số tiền mở Currency rộng 13 UnitPriceDiscountPct Đơn giá General chiết khấu 14 DiscountAmount Số tiền chiết General khấu 15 ProductStandardCost Chi phí định Currency mức của sản phẩm 16 TotalProductCost Chi phí sản Currency xuất sản phẩm 17 SalesAmount Sản lượng Currency bán ra 18 TaxAmt Thuế tối Currency thiểu thay thế 19 Freight Chi phí vận Currency chuyển 20 CarrierTrackingNumber Mã số vận Text chuyển hàng 21 CustomerPONumber Mã số đơn Text đặt hàng 22 OrderDate Ngày đặt General Date Khóa ngoại hàng 23 DueDate Ngày trả General Date tiền 24 ShipDate Ngày General Date chuyển hàng
Thực hiện Create the Model nhằm tạo mối liên kết giữa các bảng dữ liệu mới tải lên để
các bảng dữ liệu có quan hệ với nhau khi thực hiện báo cáo sẽ có tính thống nhất hơn:
Bước 1: Chọn Home => chọn thanh công cụ Diagram View trong view để có được giao
diện các kết nối bảng.
Bước 2: Data view hiện dưới dạng nội dung bên trong các bảng dữ liệu Diagram View sẽ
hiện dưới dạng các bảng và tạo mối quan hệ giữa các bảng để xem nội dung trong Data view
Khi chọn Diagram View thì màn hình sẽ hiện những bảng dữ liệu như sau, bên trong các
bảng là tên của các thông tin, các cột dữ liệu.
Liên kết 2 bảng dữ liệu dbo_DimDate và bảng dbo_FactResellerSales với 2 khóa FullDate và OrderDate:
Liên kết 2 bảng dữ liệu dbo_Employee và bảng dbo_FactResellerSales với khóa Employeekey
Liên kết 2 bảng dữ liệu dbo_DimReseller và bảng dbo_FactResellerSales với khóa Resellerkey
Liên kết 2 bảng dữ liệu dbo_DimReseller và bảng dbo_Geography với khóa Geographykey
Liên kết 2 bảng dữ liệu dbo_DimProduct và bảng dbo_DimProductSubcategory với khóa ProductSubcategoryKey
Liên kết 2 bảng dữ liệu dbo_DimProductCategory và bảng dbo_DimProductSubcategory với khóa ProductCategoryKey
Hoàn thành Create the Model để tạo các mối quan hệ liên kế cho các bảng dữ liệu
Bước 3: Tương tự ta sẽ tải thêm nguồn dữ liệu Excel SalesQuotas lên Power Pivot để
khai thác nó làm báo cáo về doanh thu của năm 2013 để so sanh doanh thu hạn ngạch và
doanh thu thực mà công ty đã đạt được