MS Power BI Cube Access - XMLA over HTTP

[ptrex]

MS Power BI

MS Power BI is hot for the last several years ... it is part of the Office 365 Suite.

To access the data you need an MS Power BI Pro License minimal to start with, which is cheap about 9 €  month / per User

The limitations is that you can't access the data unless you use the Power Bi Desktop tool (free to download) or Power BI Online Service;

 

XML-A over HTTP

This XML-A technique is +20 years old and was developed and used in MS SSAS services.

But is still available to access Power BI 🙂 

The requirement is that you have a Premium Per User License (PPU) the cost is abaout double per user compared to the PRO license.

 

Use Case

If you need to extract data from your Online Data Model to be used for other application you can do this using  the MSOLAP COM library

 

Example :

expandcollapse popup

#AutoIt3Wrapper_UseX64=y

Local $Cube
Local $Localention
Local $CubeName
Local $SourceQry
Local $strColumnHeader

; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

Const $User = "Your.User@Company.com"
Const $Pswd = "P@ssW0rd"
Const $Endpoint = "powerbi://api.powerbi.com/v1.0/myorg/YOUR Dataset NAME"
Const $Catalog = "Your Data Model Name"

Local $cn
$cn = ObjCreate("ADODB.Connection")
$cat = ObjCreate("ADOMD.Catalog")
$cst = ObjCreate("ADOMD.Cellset")

;Over HTTP
$cn.open ("Provider=MSOLAP;Data Source="&$Endpoint&";initial catalog="&$Catalog&";User Id="&$User&";Password="&$Pswd&";")

; This connects via a connection string to an MD Cube
$cat.ActiveConnection = $cn

ConsoleWrite("Cube Names : " & @CRLF)
; Get the Cube Name
Local $c = 0
For $oCubes in $cat.CubeDefs
        ConsoleWrite("Cube Name = " & $cat.CubeDefs($c).Name & @CRLF)
        $c += 1
Next

; Set the Cube Name
$CubeName=$cat.CubeDefs(2).Name

$Cube = $cat.CubeDefs($CubeName)

ConsoleWrite(@CRLF& @CRLF)

ConsoleWrite("Dimensions : " & @CRLF)
For $Dimention In $Cube.Dimensions
    ConsoleWrite($Dimention.Name & @CRLF)
Next
ConsoleWrite(@CRLF& @CRLF)


ConsoleWrite("Hierarchies : " & @CRLF)
$oHrch = $Cube.Dimensions("Dim Customer").Hierarchies(0)

For $oLevel in $oHRCH.Levels
    ConsoleWrite($oLevel.Name&@CRLF)
Next
ConsoleWrite(@CRLF& @CRLF)

$SourceQry = "SELECT {} ON 0, {[ENTITY].Members} ON 1 FROM [SALES]"

$cst.Source = $SourceQry

; Set Cell / Sets active connection to current connection
$cst.ActiveConnection = $cat.ActiveConnection

$cst.open

ConsoleWrite("Dim Axis 0 Count : " & $cst.Axes(0).DimensionCount-1 & @CRLF)
ConsoleWrite("Dim Axis 1 Count : " & $cst.Axes(1).DimensionCount-1 & @CRLF)

ConsoleWrite("Pos Axis 0 Count : " & $cst.Axes(0).Positions.Count - 1 & @CRLF)
ConsoleWrite("Pos Axis 1 Count : " & $cst.Axes(1).Positions.Count - 1 & @CRLF)
ConsoleWrite(@CRLF& @CRLF)

ConsoleWrite("Column Header : " & @CRLF)

; Loop through Column Headers
Local $i, $j, $k, $l
For $i = 0 To $cst.Axes(0).Positions.Count - 1
    $strColumnHeader = $strColumnHeader & $cst.Axes(0).Positions($i).Members(0).Caption & @TAB
    ConsoleWrite("Headers : " & $strColumnHeader & @CRLF)
Next
ConsoleWrite(@CRLF& @CRLF)

; How to https://docs.microsoft.com/en-us/office/vba/access/concepts/miscellaneous/members-example-vbscript
; Loop through Row Headers and Provide data for each row

Local $strRowText = "", $strRowText1 = ""
For $j = 0 To $cst.Axes(1).Positions.Count - 1
    For $l = 0 To $cst.Axes(1).DimensionCount - 1
        $strRowText = $strRowText & $cst.Axes(1).Positions($j).Members($l).Caption & @TAB & @TAB & @TAB & @TAB
    Next
    ConsoleWrite("Rows : " & $strRowText & @CRLF)
$strRowText = ""
Next
ConsoleWrite(@CRLF)

$cst.close
$cn.close

$cat = ""
$cn = ""

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

 

Change the user and password and your XML-A  Endpoint Connection string and the Catalog and the Query string.

Keep in mind that XML-A uses MDX as a query string and not DAX !!

See here for more info : 

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools

 

Enjoy! 

 

 

 

 

 

 

Edited July 26, 2021 by ptrex