BI4 RESTful SDK / Excel : Content of an Universe Sample
Synopsis: In this series of Blogs, I’ll explore how we can use RESTful Raylight Web Services in Excel with VBA.
Previous Entry: List universes sample
We’ll use the previously list of the universes ; we’ll enter an Universe’s id and get the content with a new macro.
It’s not user-friendly, but remember, it’s just a sample !
Sample result with efashion universe :
1) Workflow
The workflow is :
- get universe’s id from excel worksheet
- logon, get the token
- create an object for HTTP request
- define the URL, set parameters of the HTTP request
- put the token in header of the http request
- call the URL
- check return code, stop and logoff it an error occured
- create an object to analyze the XML of the response
- load XML of the response
- get data
- paste these in the worksheet
- logoff
2) Code
We call the REST url to get content of an universe, the url is like GET <servername>:6405/biprws/sl/v1/universes/?aggregated=false, where universeID is the universe’s ID.
The aggregated parameter is useful for unx. If it is set to true, then you get all the objects of all the business views. If it is set to false you only get the objects of the master view, or the default view if the master view is denied. Default value is false. I let it to false in the sample.
The XML of response body is like :
<universe>
<id>5200</id>
<cuid>AZtmGeZqCllIugfputlCuho</cuid>
<name>eFashion</name>
<description>eFashion retail Data Warehouse dated 14 Oct 2007. 89,000+ row fact table.
Version 13</description>
<type>unv</type>
<folderId>5199</folderId>
<maxRowsRetrieved>90000</maxRowsRetrieved>
<maxRetrievalTime>300</maxRetrievalTime>
<outline aggregated="false">
<folder>
<name>Time period</name>
<description>Time hierarchy</description>
<item hasLov="true" dataType="String" type="Dimension">
<id>DObc</id>
<name>Year</name>
<description>Year 2003 - 2006.</description>
<item hasLov="true" dataType="String" type="Attribute">
<id>DObb</id>
<name>Fiscal Period</name>
<description>Year FY99 - FY01</description>
<path>Time period|folder\Year|dimension\Fiscal Period|attribute</path>
</item>
<path>Time period|folder\Year|dimension</path>
</item>
<item hasLov="true" dataType="String" type="Dimension">
<id>DOba</id>
<name>Quarter</name>
<description>Quarter number: Q1, Q2, Q3, Q4.</description>
<path>Time period|folder\Quarter|dimension</path>
</item>
<item hasLov="true" dataType="Numeric" type="Dimension">
<id>DOb9</id>
<name>Month</name>
<description>Month number in year, 1-12.</description>
<item hasLov="true" dataType="String" type="Attribute">
<id>DOc4</id>
<name>Month Name</name>
<description>Month name, January-December.</description>
<path>Time period|folder\Month|dimension\Month Name|attribute</path>
</item>
<path>Time period|folder\Month|dimension</path>
</item>
... </folder>
<folder>
<name>Measures</name>
<description>3 years historical view showing measures in both USD and Euros.</description>
<item hasLov="true" dataType="Numeric" type="Measure">
<id>DO93</id>
<name>Sales revenue</name>
<description>Sales revenue $ - $ revenue of SKU sold</description>
<path>Measures|folder\Sales revenue|measure</path>
<aggregationFunction>Sum</aggregationFunction>
</item>
<item hasLov="true" dataType="Numeric" type="Measure">
<id>DO94</id>
<name>Quantity sold</name>
<description>Quantity sold - number of SKU sold</description>
<path>Measures|folder\Quantity sold|measure</path>
<aggregationFunction>Sum</aggregationFunction>
</item>
<item hasLov="true" dataType="Numeric" type="Measure">
<id>DO92</id>
<name>Margin</name>
<description>Margin $ = Revenue - Cost of sales</description>
<path>Measures|folder\Margin|measure</path>
<aggregationFunction>Sum</aggregationFunction>
</item>
<item hasLov="true" dataType="Numeric" type="Measure">
<id>DObf</id>
<name>Discount</name>
<description>Total discount of a SKU. Discount= Qty * Unit Price - Revenue. Negative sums indicate the product was marked up (increased margin). Note discount is a calculated object (it does not exist in the fact table).</description>
<path>Measures|folder\Discount|measure</path>
<aggregationFunction>Sum</aggregationFunction>
</item>
</folder>
</outline>
</universe>
We parse all nodes from XML and paste values in excel worksheet. We start at root node and browse recursively (because we have folders and subfolders) the childs with procedure parcourirNodeUniv :
Private Sub refreshContenuUniversObjets()
...
numLigne = 4
Set oNodeXML = objXML.SelectSingleNode("/universe/outline")
Call parcourirNodeUniv(oNodeXML, "", f)
End Sub
' Browse the XML tree of universe recursively and paste values in worksheet
' Parameters :
' - node : element of the tree, the proc scans only the childs
' - path : path of the node ( "" at start)
' - f : excel worksheet
Private Sub parcourirNodeUniv(ByVal node As MSXML2.IXMLDOMNode, path As String, f As Worksheet)
Dim enf, att As MSXML2.IXMLDOMNode
Dim subpath, fullpath As String
For Each enf In node.ChildNodes
If enf.BaseName = "folder" Then
subpath = enf.SelectSingleNode("name").Text
If path = "" Then fullpath = subpath Else fullpath = path & "/" & subpath
Call parcourirNodeUniv(enf, fullpath, f) 'recursive : childs
ElseIf enf.BaseName = "item" Then
On Error Resume Next ' we cheat if we don't have the node or the attribute
f.Cells(numLigne, 1) = Decode_UTF8(path)
f.Cells(numLigne, 2) = Decode_UTF8(enf.SelectSingleNode("name").Text)
f.Cells(numLigne, 3) = enf.Attributes.getNamedItem("type").Text
f.Cells(numLigne, 4) = enf.Attributes.getNamedItem("dataType").Text
f.Cells(numLigne, 5) = enf.Attributes.getNamedItem("hasLov").Text
f.Cells(numLigne, 6) = enf.SelectSingleNode("aggregationFunction").Text
f.Cells(numLigne, 7) = Decode_UTF8(enf.SelectSingleNode("description").Text)
On Error GoTo 0 ' don't cheat
numLigne = numLigne + 1
' childs for some objects like dimension/attribute
subpath = enf.SelectSingleNode("name").Text
If path = "" Then fullpath = subpath Else fullpath = path & "/" & subpath
Call parcourirNodeUniv(enf, fullpath, f)
End If
Next
End Sub
3) new RESTful VBA vs old VBA libraries
With RESTful SDK you have some limitations, you can’t do an excel file like this : http://www.forumtopics.com/busobj/viewtopic.php?t=59650&sid=b305c734bdad68677c04a5344a56acf9 Document a universe using Excel and the Designer SDK
RESTful SDK + :
- get content for unv or unx
- get details for business views
- get query capabilities(example : The maximum number of items that can be set in a filter based on INLIST or NOT_INLIST operators)
- get link groups
RESTful SDK – :
- no sql select
- no sql from
- no sgbd tables
- no sgbd columns
4) Howto test
Open the document, set the bi4 servername, login, password in the first sheet, and click the button refresh list or call the macro refreshListUnivers().
Select the universe in sheet liste univers, note the ID (first column).
Enter this ID in sheet config and click the button get universe content.
See the objects sheet.
5) TODO List :
- Get data from other urls : Business Layers, Parameters …
- Get other informations, like SQL, Tables, columns ?
Next step : List documents using an universe
Attached file : liste univers contenu_1.0.xlsm.zip
As i read in this topic https://archive.sap.com/discussions/thread/3667565
Maybe since December 11, 2014 something changes.