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 :

    <description>eFashion retail Data Warehouse dated 14 Oct 2007. 89,000+ row fact table.
Version 13</description>
    <outline aggregated="false">
            <name>Time period</name>
            <description>Time hierarchy</description>
            <item hasLov="true" dataType="String" type="Dimension">
                <description>Year 2003 - 2006.</description>
                <item hasLov="true" dataType="String" type="Attribute">
                    <name>Fiscal Period</name>
                    <description>Year FY99 - FY01</description>
                    <path>Time period|folder\Year|dimension\Fiscal Period|attribute</path>
                <path>Time period|folder\Year|dimension</path>
            <item hasLov="true" dataType="String" type="Dimension">
                <description>Quarter number: Q1, Q2, Q3, Q4.</description>
                <path>Time period|folder\Quarter|dimension</path>
            <item hasLov="true" dataType="Numeric" type="Dimension">
                <description>Month number in year, 1-12.</description>
                <item hasLov="true" dataType="String" type="Attribute">
                    <name>Month Name</name>
                    <description>Month name, January-December.</description>
                    <path>Time period|folder\Month|dimension\Month Name|attribute</path>
                <path>Time period|folder\Month|dimension</path>
...        </folder>
            <description>3 years historical view showing measures in both USD and Euros.</description>
            <item hasLov="true" dataType="Numeric" type="Measure">
                <name>Sales revenue</name>
                <description>Sales revenue $ - $ revenue of SKU sold</description>
                <path>Measures|folder\Sales revenue|measure</path>
            <item hasLov="true" dataType="Numeric" type="Measure">
                <name>Quantity sold</name>
                <description>Quantity sold - number of SKU sold</description>
                <path>Measures|folder\Quantity sold|measure</path>
            <item hasLov="true" dataType="Numeric" type="Measure">
                <description>Margin $ = Revenue - Cost of sales</description>
            <item hasLov="true" dataType="Numeric" type="Measure">
                <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>

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

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 : 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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Andrey Kozachek

    As i read in this topic

    with the current Rest SDKs, you cannot get the SQL of an individual object.

    Maybe since December 11, 2014 something changes.


Leave a Reply