Bài đăng trên blog này nhằm mục đích giúp bạn bắt đầu sử dụng API REST của Phân tích lập kế hoạch. Excel sẽ được sử dụng làm giao diện người dùng với VBA để điều khiển truy vấn xử lý kết quả
Excel không phải là giao diện lý tưởng để làm việc với JSON nhưng thông qua việc sử dụng các mô-đun của bên thứ 3, JSON có thể được phân tích cú pháp thành các đối tượng có thể được xử lý trong Excel VBA
Trong bài đăng này, chúng tôi xem xét việc xây dựng các quy trình để truy xuất Phiên và Chủ đề được liên kết cũng như yêu cầu hủy Chủ đề
Có nhiều phương thức tương tác với API REST
- Một dịch vụ sử dụng MSXML2. XMLHTTP60
- Thông qua Đối tượng tự động hóa văn phòng Cognos bằng cách sử dụng đối tượng Báo cáo
Bài đăng này sẽ xem xét việc sử dụng MSXML2. Tùy chọn XMLHTTP60 không yêu cầu bổ trợ Lập kế hoạch Analytics cho Excel. Điểm khác biệt chính mà tôi nhận thấy khi làm việc với Lập kế hoạch Phân tích trên Đám mây [PAOC] là bạn chỉ có thể truy cập API REST thông qua người dùng tự động hóa được cung cấp [tài khoản không tương tác]. Sử dụng đối tượng Báo cáo, bạn có thể tương tác với API REST thông qua người dùng được xác thực
Ví dụ của tôi, tôi sẽ kết nối với PAOC thông qua người dùng tự động hóa. Bạn có thể thay đổi điểm cuối cho máy chủ cục bộ của mình. g. http. // tm1server. /api/v1/
Trên trang tính đầu tiên của chúng tôi trong sổ làm việc, chúng tôi sẽ tạo chi tiết truy cập máy chủ
Chúng tôi sẽ sử dụng các tham số sau khi bắt đầu
Sự liên quan. Tên để xác định kết nối
Người phục vụ. Điểm cuối của máy chủ của tôi
cơ sở dữ liệu. Tên cơ sở dữ liệu TM1
tên tài khoản. Người dùng tự động hóa
Mật khẩu. Mật khẩu
Không gian tên CAM. Đối với PAOC, điều này được liên kết với LDAP
Đặt tên cho trang tính của bạn là Cấu hình và thêm từng tham số vào trang tính bắt đầu từ ô B3
Đối với mỗi tham số, hãy thêm một phạm vi đã đặt tên vào sổ làm việc như sau
Máy chủ, Cơ sở dữ liệu, Tên người dùng, mật khẩu, pCAMNamespace
Bạn nên có một trang tính trông giống như bên dưới
Trên trang tính của tôi, tôi có nhiều kết nối và đã liên kết kết nối của mình với danh sách các máy chủ để chọn và chuyển đổi theo yêu cầu. Bạn có thể thêm phần này vào giải pháp của mình sau để mở rộng nó hơn nữa
Chúng tôi sẽ cần 4 thành phần trước khi có thể kiểm tra kết nối;
- Base64Encoder để mã hóa thông tin đăng nhập của chúng tôi
- Trình chuyển đổi JSON để phân tích cú pháp JSON thành các đối tượng VBA mà chúng ta có thể xử lý
- Một hàm để thực hiện truy vấn và trả về đối tượng JSON được phân tích cú pháp
- Một quy trình để kiểm tra kết nối của chúng tôi với máy chủ
3. 1. Bộ mã hóa Base64
Có nhiều chức năng khác nhau có sẵn trên web nhưng tôi đã sử dụng các chức năng sau
Option Explicit
Function Base64Encode[text As String] As String
Dim arrData[] As Byte
arrData = StrConv[text, vbFromUnicode]
Dim objXML As Variant
Dim objNode As Variant
Set objXML = CreateObject["MSXML2.DOMDocument"]
Set objNode = objXML.createElement["b64"]
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
Base64Encode = objNode.text
Set objNode = Nothing
Set objXML = Nothing
End Function
Thêm một mô-đun mới vào dự án VBA của bạn và gọi nó là modRESTAPI hoặc tương tự. Dán chức năng trên vào mô-đun. Tôi muốn thêm Tùy chọn rõ ràng để đảm bảo các biến được xác định chính xác và chúng tôi không gặp bất ngờ
3. 2. Trình chuyển đổi JSONTải xuống mô-đun của Tim Hall từ https. //github. com/VBA-tools/VBA-JSON
Nhập mô-đun vào dự án VBA của bạn để cung cấp mô-đun cho các cuộc gọi của chúng tôi
Điều này sử dụng các loại dữ liệu Từ điển và yêu cầu tham chiếu Thư viện Microsoft Scripting được chọn trong Công cụ, Tài liệu tham khảo
3. 3. Hàm truy vấn API REST
Sao chép chức năng bên dưới và thêm vào dự án VBA của bạn trong mô-đun bạn đã tạo trước đó
Function ExecuteQuery[pAction As String, pQueryString As String, Optional strPayload As String] As Object
Dim TM1Service As New MSXML2.XMLHTTP60
Dim pServer As String
Dim pDatabase As String
Dim pUsername As String
Dim pPassword As String
Dim pCAMNamespace As String
Dim sBase64Credentials As String
Dim sQueryString As String
Dim sQueryResult As String
Dim bAsynch As Boolean
Sheets["Config"].Calculate
pServer = ThisWorkbook.Names["pServer"].RefersToRange
pDatabase = ThisWorkbook.Names["pDatabase"].RefersToRange
pUsername = ThisWorkbook.Names["pUsername"].RefersToRange
pPassword = ThisWorkbook.Names["pPassword"].RefersToRange
pCAMNamespace = ThisWorkbook.Names["pCAMNamespace"].RefersToRange
sBase64Credentials = Base64Encode[pUsername & ":" & pPassword & ":" & pCAMNamespace]
With TM1Service
'Query design for PAOC but could add in some code to look for ibmcloud.com and use the below or a local endpoint
sQueryString = pServer & "tm1/api/" & pDatabase & "/api/v1/" + pQueryString
If UCase[pAction] = "POST" Then
bAsynch = True
Else
bAsynch = False
End If
.Open pAction, sQueryString, bAsynch
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json;odata.metadata=none"
.setRequestHeader "TM1-SessionContext", "TM1 REST API tool"
.setRequestHeader "Authorization", "CAMNamespace " & sBase64Credentials
.Send strPayload
While .readyState 4
DoEvents
Wend
If .Status >= 400 And .Status 0 Then
For Each oThread In oSession["Threads"]
iCol = 3
For Each oThreadDetail In oThread
Range[sResultRange].Offset[iRow, iCol].Value2 = oThread[oThreadDetail]
iCol = iCol + 1
Next
If oThread["ID"] oSession["Threads"][oSession["Threads"].Count]["ID"] Then
iRow = iRow + 1
End If
Next
End If
End If
Next
iRow = iRow + 1
Next
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Với đoạn mã trên, có thể hữu ích khi thêm một số đồng hồ để hiểu rõ hơn từng đối tượng chứa gì
Theo đoạn mã JSON ở trên, có các thuộc tính liên quan đến Phiên và sau đó là vùng chứa không có nhiều Chủ đề hơn
Bảng Phiên của tôi trông giống như đoạn mã bên dưới sau khi chạy macro
Bạn có thể xem chi tiết Phiên với chi tiết Chủ đề bên cạnh. Khi có nhiều Chủ đề, chúng sẽ được lặp lại bắt đầu từ cột D của các hàng tiếp theo
5. Hủy bỏ một chủ đềVề cơ bản, chúng tôi muốn chọn một ID chủ đề và chuyển nó tới một hành động API REST sẽ yêu cầu hủy bỏ
Thêm đoạn mã sau vào mô-đun của bạn
Sub ThreadCancel[]
Dim oJSON As Variant
Dim pQueryString As String
Dim sThread As String
'Thread ID is in column D
sThread = ActiveCell.EntireRow.Columns[4].Value2
If sThread = "" Then GoTo Cleanup:
pQueryString = "Threads['" & sThread & "']/tm1.CancelOperation"
Set oJSON = ExecuteQuery["Post", pQueryString]
Cleanup:
End Sub
Quá trình sẽ đọc ID luồng từ cột D cho hàng của ActiveCell. Giá trị này sau đó được gửi đến CancelOperation
Bạn có thể thêm một nút khác vào trang tính của mình để hủy Chủ đề bằng cách gán quy trình Hủy Chủ đề cho nó
Để kiểm tra chức năng này, bạn sẽ cần chạy một quy trình từ PAW hoặc có thể tạo một quy trình đơn giản với chức năng Ngủ để giữ cho quy trình chạy trong vài giây
Ảnh chụp màn hình bên dưới hiển thị Phiên và Chủ đề đang hoạt động cùng với quá trình đang chạy
Để hủy Chủ đề, hãy chọn một ô trong hàng có liên quan, sau đó nhấp vào nút Hủy Chủ đề hoặc chạy quy trình của bạn.
Sau đó, PAW sẽ hiển thị cho bạn một hộp thông báo rằng quy trình đã bị hủy [có thể không hiển thị cho người dùng không phải quản trị viên]
Bây giờ bạn có một cách để kiểm tra Phiên và Chủ đề đang hoạt động đang chạy trên một máy chủ được chỉ định
Bạn cũng có cách để hủy Chủ đề nếu có sự cố với quy trình
Bạn có thể mở rộng ví dụ này bằng cách thêm vào bảng cấu hình của mình, phục vụ cho nhiều kết nối. Bạn cũng có thể thay đổi quy trình Hủy Chủ đề của mình để phục vụ cho nhiều Chủ đề đã chọn
Những ví dụ này đã đặt nền tảng cho nhiều dự án truy vấn API REST khác và sẽ cho phép bạn dễ dàng mở rộng để truy vấn khối, kích thước, tập hợp con, v.v.
Vui lòng để lại nhận xét nếu bạn thấy điều này hữu ích hoặc có điều gì khác muốn nói. Hãy cho tôi biết bất kỳ lỗi nào để tôi có thể giải quyết chúng