Archive for the ‘MDX’ Category

DAX QuerieTables in Excel via VBA

September 16th, 2011 admin No comments

I came upon the this great article DAX Table Queries in Excel from Gobán Saor.
This got me thinking about automating this via VBA.
The first try immediatly led to this macro

Sub Create_QT_Test()

    Dim qtTable As QueryTable
    Dim sConn As String
    Dim sMDX As String
    Dim ws As Worksheet

    sConn = "OLEDB;Provider=MSOLAP.5;" & _
            "Persist Security Info=True;" & _
            "Initial Catalog=Microsoft_SQLServer_AnalysisServices;" & _
            "Data Source=$Embedded$;" & _
            "MDX Compatibility=1;" & _
            "Safety Options=2;" & _
            "MDX Missing Member Mode=Error;" & _
            "Optimize Response=3;" & _
            "Cell Error Mode=TextValue"

    sMDX = "DRILLTHROUGH SELECT FROM [Model] WHERE ([Measures].[Sum of trades])"

    Set ws = ActiveSheet

    Set qtTable = ws.ListObjects.Add(3, sConn, Destination:=Range("B2")).QueryTable

    With qtTable

        .CommandText = sMDX
        .CommandType = xlCmdDefault

    End With

End Sub

I’m going to work on this futher and post better and more code…



Categories: Denali, Excel, MDX, PowerPivot, VBA Tags: , , ,

Great article about using MDX in OLAP Pivottables (PowerPivot)

July 12th, 2011 admin No comments


I found this great article Quartile, Percentile and Median in PowerPivot. It’s about using MDX in OLAP Pivottables and the ability to perform COM interop calls through the ExcelMDX assembly.
It’s not to long but I’m sure I will spend a lot of time putting it to use…

thnx Javier Guillen

Using MDX for ranking in a PowerPivot solution

June 29th, 2011 admin No comments


I found this great article about using a combination of MDX and DAX in a pivottable based on a PowerPivot model. It explains a real world Ranking problem using this approach. It is definitely worth reading…

PowerPivot is ‘Rank’