Lấy data từ Analysis Services trong Power Pivot (Phần I)

>>Lấy data từ Analysis Services trong Power Pivot (Phần II)

Ở phần bài trước, tôi đã giới thiệu với các bạn cách lấy data từ một data feed trong Power Pivot. Phần tiếp theo sau đây sẽ là cách import data từ nhiều data source Analysis Services. Analysis Services cung cấp dữ liệu có chiều/định lượng (dimensional data), rất phù hợp cho việc khai phá dữ liệu trong PowerTables và Power View. Bạn có thể lấy data Analysis Services từ:

–       OLAP cubes trên một server Analysis Services đa chiều.

–       Tabular models trên Analysis Services tabular server.

–       Excel 2013 workbook trên SharePoint 2013, nếu workbook chứa một data model.

–       Power Pivot workbooks trên SharePoint 2010.

Bạn có thể phân tích data từ data source Analysis Service bên ngoài sử dụng một connect trực tuyến. Như bạn pivot, slice và filter một report, Excel truy vấn từ Analysis Services các data mà bạn yêu cầu. Phân tích data bằng cách này yêu cầu phải có kết nối oline tới data source.

Yêu cầu môi trường khi sử dụng Analysis Services

Analysis Services cubes: SQL Server 2005, SQL Server 2008, SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012.

Analysis Services tabular model databases: chỉ có trong SQL 2012.

Bạn cũng phải biết cách kết nối đến một database Analysis Services. Kiểm tra với Analysis Services database administrator để xác định tên server và tên database mà bạn được phép sử dụng.

Để import data vào một data model, bạn phải có add-in Power Pivot trong Office Professional Plus. Bạn cũng phải biết cách viết câu query MDX để lấy dữ liệu bạn muốn sử dụng. Excel kết nối đến một OLAP cube hoặc tubular model.

Kích thước file sẽ lớn hơn so với những gì bạn thường làm. Bằng cách so sánh một workbook sử dụng một data kết nối có thể dưới 100 KB, trong khi một workbook tương tự được import data có thể lớn gấp 10 lần. Nên dung lượng ổ đĩa là cần thiết để chứa những file lớn như vậy.

Power Pivot workbook mà bạn sử dụng như data source có thể publish trên SharePoint 2010 hoặc SharePoint 2013. Trang SharePoint có thể chạy trên một máy tính khác, ngoài máy tính bạn đang sử dụng để tạo report. Bạn phải phân quyền xem trên trang SharePoint cho report với workbook trên SharePoint.

Import data từ cube

Mọi data trong database SQL Server Analysis Services có thể được copy vào một data model trong Excel. Bạn có thể lấy tất cả hoặc một phần thông số dimension, hoặc lấy slice và aggregate từ cube, như tổng số sales, theo từng tháng, năm.

Cách làm dưới đây sẽ chỉ ra cách sử dụng add-in và MDX để lấy tập hợp data từ một traditional cube trên một instance Analysis Service. MDX luôn được sử dụng khi build query sử dụng add-in Power Pivot. Tôi sẽ sử dụng database mẫu Adventure Works DW Multidimensional 2012 để giới thiệu với các bạn cách import data từ Analysis Services. Nếu bạn access vào Analysis Services server có database Adventure Works DW Multidimensional 2012, bạn có thể làm các bước sau để import data từ Analysis Services.

Bước 1: Mở cửa sổ Power Pivot.

Bước 2: Click Get External Data > From Database > From Analysis Services or Power Pivot.

Bước 3: Trong hộp thoại Connect to Microsoft SQL Server Analysis Services, phần Server or File Name, nhập tên máy tính có Analysis Services.

Bước 4: Click vào mũi tên bên phải Database name, và chọn database Analysis Services.

 

Bước 5: Click Test Connection để kiểm tra server có thể sử dụng.

Bước 6: Click Next.

Bước 7: Ở trang Specify a MDX Query, click Design để mở MDX query builder lên.

Bước 8: Kéo vào phần query design tất cả measures, dimension attributes, hierarchies, và calculated members mà bạn muốn import.

 

Chọn ít nhất một measure và một hoặc nhiều dimensions.

Nếu bạn có sẵn một câu lệnh MDX mà bạn muốn sử dụng, paste câu lệnh đó vào text box và click Validate để chắc chắn câu lệnh hoạt động.

Trong bài này, tôi sử dụng Adventure Works cube với các bước sau:

–       Trong phần Metadata pane, mở rộng Measure và sau đó mở rộng Sales Summary.

–       Kéo Average Sales Amount vào phần design pane nằm ở bên phải màn hình.

 

–       Trong pane Metadata, mở rộng Product dimension.

–       Kéo Product Categories vào bên trái Average Sales Amount trong phần design.

–       Trong Metadata pane, mở rộng Date dimension, sau đó mở rộng Calendar.

–       Kéo Date.Calendar Year vào bên trái Category trong phần design.

 

–       Ngoài ra, thêm một filter để import một tập con data. Trong pane ở bên phải trên cùng của designer, kéo Date vào field Dimension. Trong Hierarchy, chọn Date.Calendar Year; chọn Not Equal cho Operator; và click mũi tên chọn CY 2009CY 2010 cho phần Filter Expression.

 

Bước 9: Click OK và xem lại câu query MDX vừa được tạo bởi query designer.

 

Bước 10: Nhấn Finish.

Bước 11: Khi data load xong, click Close.

 

Tất cả data mà bạn import từ cube database là phẳng. Trong model, data hiện lên như một table đơn lẻ chứa tất cả column bạn query được. Nếu bạn xác định câu query để lấy measure theo nhiều dimensions, data sẽ được imported với mỗi dimension trong từng column riêng biệt.

Nếu Power Pivot có giá trị empty trong column, nó chuyển data type thành Text. Bạn có thể verify và thay đổi data type bằng cách chọn từng column và xem Data Type trong Formatting trên thành ribbon. Bạn có thể sử dụng Data Type option để sửa lại data type nếu không đúng.

Để sử dụng data trong PivotTable, quay trở lại Excel:

Bước 1: Click vào Enable Content để enable Data Connection.

Bước 2: Click Insert > Table > PivotTable.

Bước 3: Click Use an external data sources, và click Choose Connections.

Bước 4: Click Tables.

Bước 5: Trong This Workbook Data Model, chọn table bạn vừa import.

 

Bước 6: Nhấn Open.

One thought on “Lấy data từ Analysis Services trong Power Pivot (Phần I)

  • 26/02/2016 at 5:55 am
    Permalink

    Cảm ơn Anh Dũng về loạt bài viết chi tiết và thiết thực. ANh cho Như hỏi về import và export từ excel 365 và bảng của Foxpro (dbf) có được không ANh?

    Mong tin ANh!

    Reply

Gửi bình luận tại đây