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 :

1. get universe’s id from excel worksheet
2. logon, get the token
3. create an object for HTTP request
4. define the URL, set parameters of the HTTP request
5. put the token in header of the http request
6. call the URL
7. check return code, stop and logoff it an error occured
8. create an object to analyze the XML of the response
9. load XML of the response
10. get data
11. paste these in the worksheet
12. 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 + :

1. get content for unv or unx
2. get details for business views
3. get query capabilities(example : The maximum number of items that can be set in a filter based on INLIST or NOT_INLIST operators)
4. get link groups

RESTful SDK – :

1. no sql select
2. no sql from
3. no sgbd tables
4. 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