So no I need to set-up a 32 bit installation and go back in time to test this…

Does anybody have any experience with Vizubi please let me know…

regards

Jelle-Jeroen

A man was driving along a freeway when he noticed a chicken running along side his car.
He was amazed to see the chicken keeping up with him because he was doing 50 MPH.
He accelerated to 60 and the chicken stayed right next to him. He speeded up to 75 MPH and the chicken passed him up.
The man noticed the chicken had three legs. So, he followed to chicken down a road and ended up at a farm.
He got out of his car and saw that all the chickens had three legs.
He asked the farmer What’s up with these chickens?
The farmer said Well, everybody likes chicken legs. I bred a three legged bird. I’m going to be a millionaire.
The man asked him how they tasted.
The farmer said Don’t know, haven’t caught one yet.

A physicist, a theoretical economist and an econometrician are asked to find a black cat (who doesn’t really exist) in a closed room with the lights off:
The physicist gets crazy trying to find a black cat that doesn’t exist inside the darkened room and ends up in a psychiatric hospital.
The theoretical economist is unable to catch the black cat that doesn’t exist inside the darkened room,
but exits the room proudly proclaiming that he can construct a model to describe all his movements with extreme accuracy.
The econometrician walks securely into the darkened room, spend one hour looking for the black cat
that doesn’t exits and shouts from inside the room that he has it catched by the neck.

A user (manager) came to me with a PivotTable his predecessor created that produced random results after refreshing.
The PivotTable was based on a PivotCache connected to a query on a SQL Server.
When the query was run in Management Studio it was complaining about a conversion and returned a error.
This query didn’t produce a error in excel but just returned the records upuntil the error.
As the query wasn’t sorted the number of records returned to excel was random as so the results in the PivotTable.

If after the first refresh the data was almost complete nobody would have noticed the corrupt results

Hope I this helps anybody having the same issue….

p.s. Self Service BI is not for everybody …

I could not resist making a screenshot with the 10 post from my Blog …

This is the script I came up with and to be fair it works like a charm.
Some minor tweaks need to be done but I couldn’t resist the post because it has been a while.

$files = get-childitem 'D:\Data\'

$datafiles = $files | where {$_.extension -eq ".csv"}

$Excel = New-Object -Com Excel.Application

$Excel.ScreenUpdating = $false
$Excel.DisplayAlerts = $false

$WorkBook = $Excel.Workbooks.Open('D:\Data\'+$datafiles[0].name)

$mv = [System.Reflection.Missing]::Value

for ($index = 1; $index -lt $datafiles.Count; $index++)
{
 $WorkSheet = $WorkBook.Sheets.Add($mv,$mv,$mv,'D:\Data\'+$datafiles[$index].name)
 $WorkSheet.Cells.EntireColumn.AutoFit()
}

$Excel.Visible = $True
$Excel.DisplayAlerts = $True
$Excel.ScreenUpdating = $True

He needed to parse a function like (3^[x1])+([x2]^2)+[x1]+[x3]+3*[x4] into a function
And needed to parse the values for the variables via a range.

I thought I would spread the wealth and share my code with the world….

voila..

 'Set reference to: Microsoft Scripting Runtime

Public Function EVAL(sFormula As String, rVarValues As Range)

    Dim tmpDict As Dictionary
    Dim Cell As Range
    Dim i As Long

    Set tmpDict = VariableDictionary(sFormula, "[", "]")

    i = 0

    For Each Cell In rVarValues

        sFormula = Replace(sFormula, tmpDict.Items(i), Cell.Value): i = i + 1

    Next tmpCell

    Set tmpDict = Nothing

    EVAL = EVALUATE(sFormula)

End Function

Private Function VariableDictionary(sString, sStart, sEnd) As Dictionary

    Dim tmpDict As Dictionary
    Dim strTemp As String
    Dim i As Long

    strTemp = ""

    Set tmpDict = New Dictionary

    On Error Resume Next

    For i = 1 To Len(sString)

        If Mid(sString, i, 1) = sStart Then strTemp = ""

        strTemp = strTemp & Mid(sString, i, 1)

        If Mid(sString, i, 1) = sEnd Then tmpDict.Add strTemp, strTemp

    Next i

    On Error GoTo 0

    Set VariableDictionary = tmpDict

    Set tmpDict = Nothing

End Function

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
        .Refresh

    End With

End Sub

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

regards

Jelle-jeroen