basquang™ on clouds

May 10, 2019

Power BI: Row Level Security cấu trúc tổ chức

Filed under: Business Intelligence — basquang @ 9:44 AM
Tags: ,

Một bài toán rất hay gặp khi phân quyền dữ liệu đó là cấu trúc tổ chức Organization Hierachy. Với cấu trúc này, mỗi nhân viên chỉ được xem dữ liệu của chính họ, trong khi quản lý sẽ được xem dữ liệu của toàn bộ nhân viên dưới quyền. Để tổ chức phân quyền dữ liệu dạng này trên Power BI chúng ta xét ví dụ dưới đây:

Giả thiết rằng ta có cấu trúc tổ chức và báo cáo bán hàng như hình trên. Khi đó Cuong Do và Jeff Bezos chỉ được xem dữ liệu của chính mình. Bill Gates sẽ được xem dữ liệu của Bill, Cuong Do và Jeff Bezos. Quang Nguyen sẽ được xem dữ liệu của Quang, Bill, Cuong và Jeff.

Tổ chức dữ liệu

Ta có bảng dữ liệu SalePerson như sau:

Cột Manager ID sẽ chứa ID của SalePerson đóng vai trò là quản lý tương ứng.

Bảng SaleTransaction chứa số liệu kinh doanh của mỗi người

Hai bảng SalePersonSaleTransaction quan hệ 1:n với nhau thông qua IDSale ID

Thiết lập bộ lọc Hierachy Slicer cho cấu trúc tổ chức

Thiết lập báo cáo như hình 1, tuy nhiên Slicer mặc định của Power BI có cấu trúc phẳng. Để hiển thị Slicer cấu trúc dạng tổ chức ta tiến hành như sau:

Trong mục VISUALIZATIONS chọn Import from marketplace

Trong cửa sổ Power BI Visuals tìm đến HierachySlicer và chọn Add vào Power BI

HierachySlicer sẽ được thêm vào VISUALS

Tuy nhiên để HierachySlicer hiển thị theo đúng cấu trúc mong muốn, chúng ta cần tổ chức lại dữ liệu bảng SalePerson như sau

Sử dụng hàm PATH trong DAX

PATH(<ID_columnName>, <parent_columnName>)

Hàm này trả về chuỗi ký tự chứa ID của tất cả các cha theo nhiều mức cho đến cùng cho đối tượng tìm kiếm

Ta thiết lập một Calculated Column có tên là Path như sau:

Tiếp đến kết hợp hàm LOOKUPVALUEPATHITEM để tìm ra tên của các Organization Level 1 đến Level 3 như sau

Org Level1 = LOOKUPVALUE(SalePerson[Name],SalePerson[ID], PATHITEM(SalePerson[Path],1,1))

Org Level2 = LOOKUPVALUE(SalePerson[Name],SalePerson[ID], PATHITEM(SalePerson[Path],2,1))

Org Level3 = LOOKUPVALUE(SalePerson[Name],SalePerson[ID], PATHITEM(SalePerson[Path],3,INTEGER))

Khi đó sử dụng HierachySlicer ta có cấu trúc tổ chức như sau

Kết quả lọc dữ liệu theo cấu trúc

Thiết lập Role để lọc theo người dùng đăng nhập

Bây giờ chúng ta sẽ tạo mới Role để Power BI sẽ tự động lọc dữ liệu theo tài khoản người dùng đăng nhập. Ta thiết lập Role có tên là Sale với cấu trúc lọc DAX expression cho bảng SalePerson như sau

PATHCONTAINS(SalePerson[Path],MaxX(Filter(SalePerson,[email]=USERPRINCIPALNAME()),SalePerson[ID]))

  • Hàm Filter sẽ lọc ra tất cả các dữ liệu trong bảng SalePerson có email trùng với email tài khoản đăng nhập
  • Hàm MaxX sẽ lấy ra ID của người dùng từ dữ liệu lọc Filter ở trên
  • Hàm PATHCONTAINS lọc ra các ID có trong dữ liệu cột Path của bảng SalePerson

Kiểm ta kết quả

Publish báo cáo này lên Power BI Service, thêm người dùng vào Role và kiểm tra kết quả với Bill Gates ta được như sau.

Báo cáo đã lọc ra số liệu của riêng Bill và nhân viên dưới quyền, Cuong và Jeff.

Advertisements

May 3, 2019

Power BI: Row Level Security phân quyền động theo dữ liệu

Filed under: Business Intelligence — basquang @ 2:57 PM
Tags: ,

Trong bài viết trước, chúng ta đã đề cập cách thức phân quyền dữ liệu theo vai trò và gán người dùng vào từng vai trò. Bài viết dưới đây sẽ mô tả cách thức thực hiện phân quyền động, linh hoạt hơn dựa trên giá trị của từng bản ghi dữ liệu. Giả thiết rằng bài toán yêu cầu dữ liệu bán hàng của cá nhân nào chỉ được xem bởi cá nhân đó, chỉ quản lý mới có thể xem dữ liệu của toàn bộ nhân viên bán hàng dưới quyền. Chúng ta thiết lập như sau:

Bảng dữ liệu SalePerson chứa thông tin nhân viên kinh doanh

Bảng dữ liệu SaleTransaction chứa số liệu bán hàng như sau

Hai bảng này liên kết với nhau thông qua ID của SalePerson

Thiết lập Report với 2 mẫu Visual như sau

Tại FIELDS SaleTransaction thực hiện thêm mới 1 New measure để hiển thị tên người đăng nhập giá trị như sau:

User = USERNAME();

Thêm mới 1 New measure để hiển thị thời gian xem report.

Now = NOW();

Thêm 2 giá trị này vào Report để hiển thị như sau

Thực hiện tạo mới Sales role với bộ lọc [UserName] = USERNAME() để lọc ra dữ liệu dựa theo tài khoản người dùng đăng nhập như sau:

Chúng ta tiến hành Publish báo cáo này lên Power BI Service và cấu hình tài khoản tương ứng như bài trước ta có kết quả xem với tài khoản Bill Gates như sau

Như vậy chúng ta có thể lọc dữ liệu hiển thị dựa trên tài khoản đăng nhập của từng người dùng. Hay nói cách khác dữ liệu của người nào chỉ có thể xem được của người đó. Trong trường hợp vai trò quản lý có thể xem toàn bộ dữ liệu bán hàng của nhân viên dưới quyền, chúng ta chỉ việc thêm mới 1 vài trò và gán người dùng tương ứng vào vai trò.

Giả thiết rằng người dùng Quang là quản lý của Bill và Jeff, có nhu cầu xem toàn bộ dữ liệu sale của cả 3 chúng ta thiết lập như sau

  • Tạo mới 1 role với tên là Manager trong DAX expression để trống
  • Publish report này lên Power BI Service
  • Thêm Quang vào vai trò Manager rồi kiểm tra hiển thị ta được như sau

Dữ liệu đã hiển thị đầy đủ cho toàn bộ sale person với vai trò Manager

Tuy nhiên với cách này thì chúng ta đang gán chặt từng người vào vai trò Manager trên Power BI Service. Chúng ta có thể tổ chức dữ liệu theo cách dưới đây để Power BI tự động phân loại vai trò Manager theo dữ liệu phía dưới như sau:

Giả thiết bảng SalePerson có cột IsManager để xác định ai là quản lý như sau. Trong trường hợp này ta thiết lập Quang là quản lý của Bill và Jeff trong dữ liệu

Cập nhật role Sales với DAX expression như sau:

If(MaxX(Filter(SalePerson,[UserName] = USERNAME()),[IsManager])=0,[UserName]=USERNAME(),1=1)

  • Filter(SalePerson,[UserName] = USERNAME()): Lấy ra tất cả các bản ghi dữ liệu của người đang đăng nhập
  • MaxX(Filter(SalePerson,[UserName] = USERNAME()),[IsManager]): Xác định xem trong các bản ghi lấy ra ở trên người dùng đang đăng nhập đó có giá trị IsManager là 1 hay không (có phải là Manager hay không)

Ở đây chú ý rằng không có định nghĩa role là Manager.

Thực hiện Publish báo cáo này lên Power BI Service và gán toàn bộ Quang, Bill và Jeff vào vai trò Sales rồi kiểm tra dữ liệu ta được kết quả tương tự như bước định nghĩa Role Manager ở trên

Power BI: Phân quyền dữ liệu Row Level Security (RLS)

Filed under: Business Intelligence — basquang @ 11:02 AM
Tags: , ,

Row Level Security (RLS) được hiểu là phân quyền mức bản ghi dữ liệu. Lấy ví dụ Sale Manager của Hà Nội chỉ có thể xem được dữ liệu kinh doanh của Hà Nội, trong khi Sale Manager của TP HCM chỉ có thể xem được dữ liệu của TP HCM mà không thể xem được số liệu kinh doanh của các địa phương khác. Tuy nhiên CEO thuộc hội đồng quản trị có thể xem được số liệu kinh doanh của tất cả các địa phương trên cả nước.

Bài viết dưới đây sẽ trình bày cách thực hiện phân quyền dữ liệu theo vai trò trong Power BI

Đầu tiên chúng ta thực hiện lấy dữ liệu DimSalesTerritoryFactResellerSales từ nguồn AdventureWorksDW như sau:

Thiết lập Report với các Visual như sau:

  • Stacked column chart (SalesAmount by SalesTerritoryCountry): Biều đồ thể hiện doanh số bán hàng theo Country
  • Card (SalesAmount): Thể hiển tổng doanh số bán hàng cho toàn bộ
  • 2 Slicer (SalesTerritoryGroup và SalesTerritoryRegion): 2 bộ lọc theo khu vực (Group) và vùng (Region)

Thiết lập các Roles như sau:

  • Chọn Modeling | Manage Roles
  • Trong cửa sổ Manage roles thực hiện tạo các roles như sau
Roles Tables Table filter DAX expression
Europe Sales Manager DimSalesTerritory [SalesTerritoryGroup] = “Europe”
USA Sales Manager DimSalesTerritory [SalesTerritoryCountry] = “United States”

Thực hiện Test thử các Roles trên Power PI Desktop như sau:

  • Chọn Modeling | View as Roles
  • Trong cửa sổ View as roles, chọn Roles cần kiểm tra

  • Ta được kết quả hiển thị số liệu bán hàng của Châu Âu như sau:

Cấu hình Row Level Security trên Power BI Service

Sau khi đã thiết lập và kiểm tra hiển thị dữ liệu theo Roles trên Power BI Desktop. Chúng ta thực hiện Publish report lên Power BI Service để chia sẻ và cấu hình theo từng user thuộc role tương ứng

  • Mở Power BI Service, chọn tới DATASETS tương ứng, ở đây tôi đặt tên Power BI Report là AdventureWorks_RowLevel do đó tên DATASETS tương ứng sẽ là tên report.
  • Chọn SECURITY để phân quyền cho dataset này

  • Trong trang Row-Level Security thực hiện gán user vào các roles tương ứng

  • Thực hiện chức năng Test as role để kiểm tra hiển thị

Kết quả hiển thị số liệu bán hàng tại USA cho user Jeff Bezos như sau

Như vậy sau khi tạo các roles, thực hiện lọc data set tương ứng với các role trên Power BI Desktop, tiến hành publish report lên Power BI Service và thực hiện gán user cho các roles tương ứng. Khi đó với mỗi user truy cập vào report trên Power BI Service dữ liệu sẽ được lọc tương ứng theo từng vai trò đã được chỉ định.

April 19, 2019

E27: Một số Trendline khác trong Excel

Filed under: Excel — basquang @ 3:46 PM
Tags: , , , ,

Mô hình hóa tăng trưởng cấp số nhân (Exponential)

Công thức của mô hình tăng trưởng cấp số nhân . Trong đó

  • x: là biến độc lập
  • y: là biến phụ thuộc (dự đoán)
  • e: số logarit tự nhiên e = 2.718
  • a và b là các hằng số.

Mô hình này thường được sử dụng để dự đoán tăng trưởng doanh thu của doanh nghiệp theo thời gian khi biết số liệu về doanh thu hàng năm. Khi đó x là số năm, và y sẽ là doanh thu của doanh nghiệp. Lấy ví dụ dữ liệu sau:

Đây là số liệu doanh thu của Cisco từ năm 1990 đến năm 1999. Sử dụng Scatter Chart cho dữ liệu A3:B3 với Trendline là Exponential ta ra được đồ thị như hình dưới và 2 biểu thức tính toán:

Áp dụng công thức này vào cột C14 =58.552664*EXP(0.569367*A14) ta dự đoán được doanh thu của Cisco năm 2000 là $30,729.0453 (triệu đô) với trị số R² = 0.982833 có nghĩa là mô hình này phù hợp 98,28%. Ở cột D ta tính được tỉ lệ tăng trưởng hàng năm của công ty rơi vào khoảng 76.7%.

Mô hình hóa theo đường lũy thừa (Power)

Đường cong lũy thừa (power curve) được tính bởi phương trình . Trong đó a và b là các hằng số. Mô hình này thường được dùng để ước lượng mối tương quan giữa số lượng tích lũy sản phẩm làm ra và chi phí sản xuất hoặc là thời gian cần thiết để tạo ra một sản phẩm. Đôi khi chúng ta gọi đường này là đường cong cải tiến (learning curve) hay đường cong tích lũy (experience curve) vì đồ thị của đường cong này thể hiện khả năng tối ưu (thời gian hoặc chi phí) sản xuất theo thời gian.

Xét ví dụ dưới đây, là dữ liệu về số máy Fax được sản xuất ra từ năm 1982 tới năm 1988. Lấy ví dụ vào năm 1983 có 70000 đơn vị máy Fax được sản xuất ra và tích lũy từ năm 1982 có 13400 số máy Fax đã được sản xuất. Với chi phí sản xuất cho chiếc cuối cùng của năm 1983 là $3,416.00. Chúng ta thấy rằng số lượng máy Fax sản xuất được tăng lên theo thời gian và chi phí để làm ra 1 sản phẩm thì giảm theo thời gian. Tới năm 1988 sản xuất được 78500 chiếc với chi phí sản xuất chiếc cuối cùng là $1,788.

Sử dụng Scatter Chart cho dữ liệu từ C3:D10 với Trendline là Power ta được đồ thị như hình dưới với 2 phương trình

Áp dụng công thức này vào cột E11 = 65259*C11^-0.253 = $1,533.66 ta dự đoán được năm 1989 nếu sản xuất 1 triệu máy Fax thì chi phí sản xuất đơn vị cho chiếc cuối cùng là $1,533.66.

(còn tiếp)

April 18, 2019

E26: Ước lượng tương quan tuyến tính trong Excel

Ước lượng tương quan tuyến tính trong hay một cái tên toán học đó là hồi quy tuyến tính (linear regression) là một phương pháp để mối tương quan giữa 2 biến số trong phân tích kinh doanh. Biến số cần tìm được gọi là biến phụ thuộc (dependent variable) và biến số được dùng để dự đoán trong mô hình được gọi là biến độc lập (independent variable). Trong toán học hàm tuyến tính được biểu diễn dưới dạng

y = a*x + b

Hay được biểu diễn dưới dạng đồ thị đường thẳng

Khi đó đầu ra y là biến phụ thuộc còn x là biến độc lập đóng vai trò đầu vào.

Trong thực tế có rất nhiều bài yêu cầu ước lượng đầu ra dựa trên tham số đầu vào kể trên

Biến độc lập (đầu vào) Biến phụ thuộc (đầu ra)
Số sản phẩm làm ra hàng tháng Chi phí hàng tháng
Chi phí quảng cáo hàng tháng Doanh thu bán hàng
Số nhân công Chi phí đi lại
Lợi nhuận doanh nghiệp Số nhân viên
Giá sản phẩm Doanh số bán hàng

Để xác định mối tương quan tuyến tính kể trên, chúng ta có thể sử dụng tính năng trend curve trong Excel thông qua Scatter Chart. Khi đó biến độc lập nằm trên trục x còn biến phụ thuộc nằm trên trục y.

Lấy ví dụ, bạn muốn mở một nhà máy sản xuất gót giầy, và bạn cần xác định xem số lượng gót giầy sản xuất hàng tháng sao cho phù hợp với chi phí vận hành hàng tháng tương ứng. Giả thiết chúng ta có số liệu thống kê như sau:

  • Month: tháng
  • Units Producced: Đơn vị sản phẩm làm ra trong tháng
  • Monthly cost: Chi phí vận hành trong tháng

Câu hỏi đặt ra là tháng 15 nếu sản xuất 1100 đơn vị thì chi phí vận hành trong tháng đó ước chừng bao nhiêu?

Trong trường hợp này, ta thiết lập đồ thị Scatter như sau, cột bên trái Units Produced sẽ thuộc về trục x và trục tiếp theo cần tính toán Monthly cost sẽ thuộc về trục y.

  • Chọn vùng dữ liệu C2:D16. Vùng dữ liệu này bao gồm cả nhãn của dữ liệu trên mỗi cột
  • Chọn Insert | Insert Scatter (X, Y) or Buddle Chart. Ta có một đồ thị dưới dạng những điểm trên mặt phẳng sẽ xuất hiện
  • Chọn một điểm xanh trên đồ thị, rồi chuột phải chọn Add Trendline…
  • Excel sẽ tự động thiết lập format cho Trendline với Trendline OptionsLinear. Có nghĩa rằng dữ liệu này phù hợp với hàm tuyến tính Linear.

  • Tích chọn vào lựa chọn Display Equation on chart để hiển thị hàm tuyến tính trên đồ thị
  • Tích chọn vào lựa chọn Display R-squared value on chart để hiển thị trị số
  • Ta có được đồ thị dữ liệu như sau:

  • Ở đây các hằng số trong công thức đã được làm tròn có thể phát sinh nhiều sai số. Chúng ta format giá trị hằng số trong công thức bằng cách chọn công thức trên đồ thị rồi format Category dạng Number với Decimal places là 4 như sau

Như vậy Excel xác định được hàm tuyến tính để ước lượng chi phí vận hành hàng dựa trên số đơn vị sản phẩm sản xuất như sau:

Y = 64.2687x + 37,894.0956 hay Monthy cost = 64.2687(Units produced) + 37,894.0956

Đây là một đường thẳng mà tổng khoảng cách theo phương thẳng đứng từ các điểm nằm bên trên đường thẳng (>0) và các điểm nằm bên dưới đường thẳng (<0) là nhỏ nhất. Khoảng cách theo phương thẳng đứng từ một điểm tới đường thẳng trên trục được gọi là độ lệch (error hay là residual). Đường thẳng này được gọi là đường bình phương nhỏ nhất least-squares line.

Như vậy ta xác định được giá trị các ô E3:E17 dựa vào công thức trên. Khi đó tháng 15 nếu sản xuất 1100 đơn vị thì dự kiến chi phí vận hành tháng đó là $108589.6665. Ngoài ra ta tính được độ lệch trên các ô F3:F17 và tổng độ lệch F1 = -0.043.

Chúng ta nên sử dụng hàm tuyến tính này để ước lượng nếu đầu ra trong phép toán nằm trong khoảng dữ liệu phân tích.

Trong công thức này, giá trị chặn y-intercept = 37,894.0956 có thể hiểu là chi phí cố định hàng tháng (monthly fixed cost). Độ dốc (slope) của đường thẳng slope = 64.2687 chỉ ra rằng mỗi đơn vị sản phẩm sản xuất ra phát sinh thêm chi phí hàng tháng là $64.2687 hay đây chính là chi phí biến đổi đơn vị trên mỗi sản phẩm.

Ở đây trị số R² = 0.6882 = 68,82% hay còn gọi là hệ số xác định bội (goodness of fit statistics). Giá trị này thể hiện mối liên hệ giữa biến độc lập x và biến phụ thuộc y. Hay trong bài toán này là mối liên hệ giữa số đơn vị sản phẩm sản xuất ra và chi phí vận hành hàng tháng. Từ mô hình này ta có thể nhận xét rằng số đơn vị sản phẩm sản xuất ra ảnh hưởng 68,82% tới chi phí vận hành hàng tháng. Còn lại 31,18% phụ thuộc vào các nhân tố khác. Nếu giá trị R² càng cao thì mối liên hệ giữa biến phụ thuộc và biến độc lập càng chặt chẽ.

April 11, 2019

E25: Các hàm thống kê cấu trúc CSDL trên Excel

Filed under: Excel — basquang @ 3:26 PM
Tags: , , , , , ,

Các hàm thống kê cấu trúc CSDL trên Excel bao gồm DSUM, DAVERAGE, DCOUNT, DMAX và DMIN. Những hàm này mục đích tương tự như các hàm thông thường SUM, AVERAGE, COUNT, MAX, MIN, tuy nhiên sự khác biệt của các hàm tiền tố D này cho phép thêm vào các tiêu chí lọc tại các rows của vùng dữ liệu tính toán.

Cú pháp ví dụ như sau:

DSUM(database, field, criterial) với các tham số

  • Database: là vùng dữ liệu được chọn hoặc đặt tên. Trong đó hàng đầu tiên là tiêu đề cho mỗi cột dự liệu
  • Field: là cột chứa dữ liệu cần tính toán. Field có thể định dạng dưới dạng “column label” hoặc chỉ số của cột trong vùng dữ liệu
  • Criterial: là vùng dữ liệu để thực hiện lọc. Trong đó hàng đầu tiên cần chứa 1 hoặc nhiều tiêu đề của cột. Trong trường hợp có nhiều cột, tương đương với nhiều tiêu chí thì những giá trị dữ liệu trên cùng 1 hàng sẽ thực hiện theo phép toán AND và khác hàng sẽ thực hiện theo phép toán OR

Ví dụ:

Xét bài toán với dữ liệu bán hàng của một cửa hàng bán đồ mỹ phẩm chứa các thông tin như hình dưới:

  • Name: tên người bán
  • Date: ngày bán
  • Product: sản phẩm được bán
  • Units: số sản phẩm bán được
  • Dollars: Số tiền bán được
  • Location: vị trí cửa hàng bán

Bằng việc sử dụng các hàm Dfunction ta có thể trả lời các câu hỏi sau một cách dễ dàng

Trước hết ta đặt tên cho các vùng dữ liệu như sau:

  • Data: vùng dữ liệu bán hàng từ C4:H1895
  • Date: cột dữ liệu ngày bán D5:D1895
  • Dollars: cột dữ liệu số tiền bán được G5:G1895
  • Location: cột dữ liệu vị trí cửa hàng bán H5:H1895
  • Name: cột dữ liệu tên người bán C5:C1895
  • Product: cột dữ liệu sản phẩm bán được E5:E1895
  • Trans_Numbers: cột thứ tự B5:B1895
  • Units: cột số sản phẩm bán được F5:F1895

Doanh thu của Jen cho sản phẩm lip gloss$5,461.61 được tính theo công thức DSUM với bộ lọc 2 tiêu chí với dữ liệu lọc cho phép AND từ K4:L5

Trung số lipstickJen bán được trung bình ở vùng east42.25 được tính theo công thức DAVERAGE với bộ lọc 3 tiêu chí với dữ liệu lọc trên 1 hàng cho phép AND K7:M8

Doanh số của Emilee hoặc của vùng east76,156.48 được tính trong công thức DSUM với bộ lọc 2 tiêu chí và dữ liệu lọc nằm trên 2 dòng khác nhau cho phép OR K10:L12

Doanh số bán lipstick của Colleen hoặc Zaret bán vùng east1,073.20 được tính theo công thức DSUM với bộ lọc 3 tiêu chí và dữ liệu lọc nằm trên 2 dòng khác nhau cho phép OR K13:M15

Tổng số giao dịch lipstick không thuộc vùng east164 được tính trong công thức DCOUNT

Tổng số doanh thu sản phẩm lipstickJen bán được trong năm 2014 được tính trong công thức DSUM với 4 tiêu chí lọc K18:N19

Ngoài ra sử dụng công cụ Data Table 2 biến ta có thể thiết lập bảng tổng hợp doanh thu 2 chiều số liệu người bán và sản phẩm như sau. Trong đó

  • Vùng dữ liệu Data Table được xác định từ M25:R33
  • Giá trị ô đầu tiên M25 được tính theo công thức =DSUM(data,5,T25:U26)
  • Thiết lập Data Table với 2 biến đầu vào
    • Row input cell: U$26 chứa biến sản phẩm
    • Column input cell: T$26 chứa biến người bán

April 9, 2019

E24: Power Pivot

Filed under: Excel — basquang @ 3:28 PM
Tags: ,

Thiết lập Power Pivot trong Excel

Power Pivot là một tính năng mở rộng trong Excel, mặc định không có sẵn, để thiết lập tính năng này lựa chọn theo các bước sau

  1. Chọn File | Options | Add-ins.
  2. Trong cửa sổ Excel Options Add-ins chọn Microsoft Power Pivot for Excel, mục Manage chọn COM Add-ins rồi bấm Go.

  3. Trong cửa sổ COM Add-ins chọn Microsoft Power Pivot for Excel và bấm OK

  4. Tab Power Pivot sẽ được thêm vào Excel như hình dưới

Với Power Pivot chúng ta có thể thực hiện các công việc như:

  • Thực hiện truy vấn hàng trăm triệu dữ liệu từ một hoặc nhiều nguồn khác nhau trong và ngoài Excel
  • Power Pivot bao gồm Data Analysis Expressions (DAX) cho phép tạo các cột tính toán một cách dễ dàng
  • Có thể tạo các ô tính toán tổng hợp từ nhiều rows khác nhau
  • Có thể thiết lập KPI để xác định hiệu quả trên mục tiêu của tổ chức
  • Power Pivot còn được sử dụng như là nguồn dữ liệu cho các báo cáo dưới dạng PivotTables, Charts, Power View…
  • Power Pivot cũng có thể được xuất bản lên Microsoft SharePoint để chia sẻ

Kết nối dữ liệu với Power Pivot

Để thực hiện kết nối dữ liệu với Power Pivot thực hiện các bước sau:

  1. Chọn Power Pivot | Data Model | Manage
  2. Một cửa sổ Power Pivot for Excel sẽ xuất hiện với giao diện như hình

  3. Tại đây chúng ta có rất nhiều lựa chọn để kết nối dữ liệu

  4. Nếu chúng ta chọn Get External Data | From Other Sources ta sẽ thấy một cửa sổ Table Import Wizard với rất nhiều lựa chọn. Từ Microsoft SQL Server tới Oracle, Teradata, Azure…

Một ví dụ về Power Pivot

Giả sử chúng ta có 2 file

  • Text file chứa dữ liệu sale

  • Excel file chứa dữ liệu các store

Bài toán đặt ra cần tổng hợp số liệu sale theo state.

Ta thực hiện yêu cầu trên theo các bước sau sử dụng Power Pivot

  1. Trong cửa sổ Power Pivot for Excel chọn Get External Data | From Other Sources
  2. Trong cửa sổ Table Import Wizard chọn Text File rồi bấm Next
  3. Trong cửa sổ tiếp theo thiết lập như dưới đây rồi bấm Finish
  • File Path: chọn tới vị trí text file
  • Friendly connection name: Excel sẽ tự gợi ý tên cho connection
  • Column Separator: ở đây text file sử dụng Tab để phân cách dữ liệu
  • Use first row as column headers: trong trường hợp này text file có header là row đầu tiên

  1. Sau khi kết thúc quá trình Import dữ liệu được import dưới dạng Data View sẽ được hiển thị như hình dưới

  2. Trong cửa sổ Home nếu ta chuyển đổi từ Data View sang Diagram View thì Power Pivot sẽ cho ta thấy cấu trúc như một bảng định nghĩa dữ liệu

  3. Bước tiếp theo ta tiến hành import file excel chứa dữ liệu state, chúng ta có thể tiến hành theo cách tương tự các bước trên với lựa chọn nguồn dữ liệu là Excel File
  4. Sau khi Import ta có 2 Sheet Storesales và States. Tuy nhiên 2 sheet này không có liên kết với nhau. Để thực hiện liên kết dữ liệu giữa 2 sheet thông qua giá trị của cột store ta thực hiện tạo liên kết bằng cách sau:
  5. Chọn Design | Create Relationship
  6. Trong cửa sổ Create Relationship lựa chọn mối quan hệ tương ứng giữa 2 bảng như hình. Ở đây chúng ta kết nối Store trong bảng States tương ứng với Store trong bảng Storesales rồi bấm OK

  7. Sau khi kết thúc chọn Diagram View chúng ta sẽ thấy mối quan hệ ở dạng 1:*

Như vậy chúng ta đã hoàn thành việc thiết lập và kết nối dữ liệu trong Power Pivot.

Để tổng hợp dữ liệu sales theo state ta lựa chọn Home | Pivot Table

Khi đó các bước thực hiện với Pivot Table tương tự như các bài trước.

E23: Data Model

Filed under: Excel — basquang @ 10:38 AM
Tags: ,

Excel 2016 hỗ trợ 1,048,576 rows dữ liệu, trong đó Data Model là một tính năng được thêm vào từ Excel 2013 để tận dụng khả năng đó thiết lập PivotTables. Data Model cũng cho phép kết hợp dữ liệu từ nhiều nguồn ngoài Excel khác nhau như Access hay SQL Server.

Thiết lập table vào Data Model

Để thực hiện thêm một bảng dữ liệu vào Data Table tiến hành theo các bước sau

  1. Chọn vùng dữ liệu
  2. Data | Get & Transform Data | From Table/Range hoặc (Ctrl + T)

  3. Sau khi bấm OK chúng ta có thể đặt tên cho Table vừa tạo

  4. Chọn Insert | PivotTable, trong cửa sổ Create PivotTable tích chọn Add this data to the Data Model

  5. Thực hiện tương tự cho vùng dữ liệu liên quan tới Sales. Sau khi kết thúc ta được PivotTable Fields với 2 bảng như hình

Thiết lập mối quan hệ giữa các bảng trong Data Model

Ở trên chúng ta có 2 bảng Reps chứa 2 cột ID và State, bảng Sales chứa 2 cột ID và Sales. Để liên kết giá trị ID giữa 2 bảng để kết nối giá trị State và Sales ta thực hiện như sau

  1. Chọn Analyze | Relationships
  2. Trong cửa sổ Manage Relationships chọn New
  3. Trong cửa sổ Create Relationship. Ở đây bảng Reps chứa thống tin sale person do đó là bảng chính, bảng Sales là bảng quan hệ

Sử dụng Data Model để tạo PivotTables

Sau khi khởi tạo các tables vào Data Model và thiết lập relationships, chúng ta có thể tổng hợp số sales theo state như sau:

Sử dụng Quick Explorer

Sử dụng chức năng này để xem chi tiết Drill To cho một dữ liệu tổng hợp. Giả sử ta muốn xem chi tiết Giá trị sale cho từng Person của bang AK ở trên. Lựa chọn biểu tưởng Explorer đi kèm tương ứng với row dữ liệu

Kết quả như sau. Với State = AK

April 4, 2019

E22: PivotTable

Filed under: Excel — basquang @ 11:11 AM
Tags:

Một mẫu dữ liệu về doanh số bán hàng tạp phẩm như sau

Để trỏ chuột vào vị trí bất kỳ trong vùng dữ liệu và chọn Chọn Insert | PivotTable excel sẽ tự động xác định chính xác Table/Range: từ A1:G923

Sau khi bấm OK, một cửa sổ PivotTable Fields với các nội dung sau

Trong panel này chúng ta thấy danh sách các fields có thể thêm vào Report bằng cách kéo vào 4 vùng dữ liệu dưới đây

  • Rows: Những fields được kéo vào vùng này sẽ được sắp xếp theo thứ tự kéo và được nhóm lại (group by) theo thứ tự tương ứng. Chúng ta có thể thay đổi thứ tự này bất kỳ nếu muốn. Giả sử ở đây ta kéo Year, Group, Product và Store. Thì kết quả sẽ như sau:

  • Columns: Những fields được kéo vào vùng này sẽ hiển thị giá trị được nhóm theo cột. Ví dụ nếu ta kéo field Year từ vùng Rows sang vùng Columns thì kết quả sẽ như sau:

  • Values: Những fields được kéo vào vùng này là kiểu số để có thể tính toán và tổng hợp bằng những công thức toán học. Thông thường trong Business Intelligence những fields này là các giá trị measure trong các bảng Fact. Còn các fields nằm trong vùng dữ liệu Rows và Columns là các chiều dữ liệu trong các bảng Dimensions. Ví dụ ở đây ta kéo Units và Revenue sẽ ra kết quả như sau:

    Ở đây excel tự động tính tổng SUM các giá trị trong vùng dữ liệu Values. Chúng ta có thể thay đổi sang AVERAGE hoặc công thức khác bằng lựa chọn Value Fields Settings tại mỗi ô dữ liệu

  • Filters: Những fields được kéo vào vùng này sẽ được dùng để lọc giá trị hiển thị trong báo cáo PivotTables. Những fields này là những chiều dữ liệu dimension. Trong ví dụ này ta kéo field còn lại là Month. Kết quả ta sẽ có bộ lọc theo tháng như sau:

Nếu ta đổi lại vị trí Rows: Year, Group, Product, StoreColums:để trống ta sẽ có kết quả như sau:

Bố cục hiển thị báo cáo

Hình ảnh trên thể hiện báo cáo PivotTable dưới dạng Compact Form. Trong compact form các fields trong vùng Rows cùng ở 1 cột chồng lên nhau. Để các Rows fields thể hiện ở các cột khác nhau chúng ta sử dụng Outline Form bằng cách Design | Report Layout | Show in Outline Form như sau

Collapse/Expand các chiều dữ liệu

Để collapsed/expand từng field sử dụng các icon +/- trên mỗi field, để thực hiện collapsed/expand toàn bộ các fields sử dụng chức năng Analyze | Active Field | Expand Field/Collapse Field sau khi click chọn 1 field mà có thể collapse/expand bất kỳ

Lọc dữ liệu Filter

Ngoài việc lọc dữ liệu dựa trên các chiều dữ liệu trong vùng Filters, chúng ta có thể sử dụng chức năng Analyze | Filter | Insert Slicer để thực hiện thêm trực tiếp các chiều dữ liệu vào bộ lọc. Ví dụ ở đây ta thêm mới Slicer để lọc theo Product

April 3, 2019

E21: Pareto chart

Filed under: Excel — basquang @ 4:38 PM

Next Page »

Blog at WordPress.com.