Analytics for directors

When developing dashboards with managers and directors, one learns that good directors never stops. They are always in action: coordinating, traveling, in meetings, talking or presenting. They are clever and brilliant, but they have no time at all.  They have almost no time for looking graphics, but when they need it, it should be quick and clear.

That working experience lead us to following principles for building successful analytics:

  • One picture is better than 1000 words. Everybody knows the difference of looking at a table with numbers and seeing them on a graph. Examining a table requires scrolling down the records, memorizing data and comparing them, and this takes long time. But our eyes can grasp a graph at once. Where is the minimum? Where is the maximum? How is the data distributed? All that happens in fractions of seconds. And that’s the power of information visualization. An enormous power.
  • Intuitive design. No help page! If you need an help page you have already lost. Use intuitive symbols for controls and steering.
  • Develop for smartphones. Develop 1st for smartphones. Our directors need the graphics, now, while traveling! Remember, if you can develop for smartphones you can develop for big screens as well.
  • Touch screen is “touch” screen. And it is about 12-14 mm radius. Don’t develop for children.
  • Every Pixel Counts!  Minimize your elements. Don’t distract the user.  Each screen elements should be either for steering or to carry data information.  Before a release, take your time and make a clean-up round. Remove or simplify your graphics. Minimize text usage.
  • Taylor your graphs to your client. Qlikview and Tableau are not written for directors. They are written for DevOps. The assumption there is that the user (the DevOp), will be finally able, with the given tools, to produce a graphic for the real  business user, who will finally use this graphics.  We use a different approach: we know our director and his needs, and we want to make a taylored graph for him. And we don’t want third party software between the data and the screen. We want full control of the screen, because we want to give our users excellent results!
  • One graph at a time.  Or: less is more. Each screen page should present one result and no more. Graphs should be clear and easy to understand. Don’t be scared to take responsability and  ownership of your graphs.  Guide the users through your analysis, instead of leaving them alone with lot of numbers.

 

How to drive IE with Powershell to Automatic Delete unused QlikView CALs

Zürich, 30-03-2017

Well, that wasn’t easy… I wanted to help my client to manage his server CALs . So I tried to install some open source Qlikview management utility for this purpose.

Unfortunately, due to lack of time, administration rights … and knowledge… I wasn’t  very successful in my intent.

So, more as a fun then seriously, I started to build a  iE “robot” script for that.

I tried 1st with perl, but the WIn32::OLE module wasn’t very promising: after logging  in the QV server console, it was just … stacked.

So I don’t even know how, I had the idea to try with Powershell, ok but I never programmed power shell before, so after looking on the net I tried this 1st script:

$ie = new-object -ComObject "InternetExplorer.Application"
$ie.Width=1400
$requestUri = 'http://localhost:4780/QMC/Licenses.htm#'
$ie.Visible = $true
$ie.navigate($requestUri)
do {sleep 1} until (-not $ie.busy) 
$doc = $ie.document

and voilà my IE opened and showed me the QV server, as I would be myself doing it manually.

So, next step, open the dev-tools, and try to identify the QlikView server Element in the License page, which by clicking, gets you to the CAL section.

In this server, this element is called: ‘EntLicenses.Services.List.1.Name’

So I tried:

$QVServerLine = $doc.getElementById('EntLicenses.Services.List.1.Name')

But nothing worked, why? After some hours of investigation it turned round that for Windows Server 2012 with IE 10 and Powershell 3.0  preinstalled (which is quite old…) you have to do like that:

$QVServerLine = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc, 'EntLicenses.Services.List.1.Name')

….Sigh…

Nevertheless…, who cares?  I’m not easy to surrender…, so finally I got the script:

$ie = new-object -ComObject "InternetExplorer.Application"
$ie.Width=1400
$requestUri = 'http://localhost:4780/QMC/Licenses.htm#'
$ie.Visible = $false
$ie.navigate($requestUri)
do {sleep 1} until (-not $ie.busy) 
$doc = $ie.document
$QVServerLine = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc, 'EntLicenses.Services.List.1.Name')
$QVServerLine.click()
do {sleep 1} until (-not $ie.busy) 
$CalTab = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'CALsContainer.AssignedTab')
do {sleep 1} until (-not $ie.busy) 
$CalTab.click()
do {sleep 1} until (-not $ie.busy) 
$TodayDate=(GET-DATE)
Write-Output "-- Today Date for checking licenses  $TodayDate  --"
$LicenseContainer = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'pagingpEntLicenses.Properties.CalInfo.AssignedList.List')
$LicensePages =@([System.__ComObject].InvokeMember(“getElementsByClassName”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $LicenseContainer, 'searchClear'))
for($pg=0; $pg -lt $LicensePages.length; $pg++) {
    $LicensePages[$pg].click()
    do {sleep 1} until (-not $ie.busy)
    Write-Output "--------------------------------------"
    Write-Output "-- select page $pg  --"
    for($i=0; $i -le 100; $i++) {
        $FullName = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'EntLicenses.Properties.CalInfo.AssignedList.List.'+$i+'.FullName')
        if (!$FullName.textContent){continue};
        Write-Output "-- examine $($FullName.textContent)-"
        $LastUsed = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'EntLicenses.Properties.CalInfo.AssignedList.List.'+$i+'.LastUsed')
             if (!$LastUsed.textContent){continue};
             Write-Output "-- Last Used $($LastUsed.textContent)  --"
        $LastDate=[datetime]::parseexact($LastUsed.textContent,"dd.MM.yyyy HH:mm:ss",$null)
        $delta=NEW-TIMESPAN  $LastDate  $TodayDate
        if ($delta.Days -le 15.01) {continue};
        Write-Output "-- delta Days  $delta.Days  --"
        Write-Output " ----->  delete lease for $($FullName.textContent) --"
        $DeleteCal = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'EntLicenses.Properties.CalInfo.AssignedList.List.'+$i+'.DeleteCal')
             if (!$DeleteCal){continue};
        $DeleteCal.click();
        do {sleep 1} until (-not $ie.busy)
    }
    $apply = [System.__ComObject].InvokeMember(“getElementById”,[System.Reflection.BindingFlags]::InvokeMethod, $null, $doc,  'apply')
       do {sleep 1} until (-not $ie.busy)
       Write-Output "apply for page $pg "
       $apply.click()
       do {sleep 1} until (-not $ie.busy)
}
$ie.Quit()
$ie=$null
Write-Output "End  "

 

Isn’t ugly?  But it’s working! And it’s very useful! I installed the script on the server and let the script run every 12 hours.

Ciao!