QlikView – How to run MDX queries against OLAP Cubes

September 29, 2014

QlikView does not support natively accessing OLAP Cubes running MDX Queries.

But with a little creativity you can get it going.

1. First of all I setup an IcCube OLAP In Memory Cube to run the MDX against in the IDE.


IcCube supports the XML/A protocol natively Smile

Of course you can use your existing SSAS cube(s) as well as a data source, if you have setup an XML/A endpoint config.

I just used IcCube to get a quick testing environment.

2. Next I have used MS SQL as a middleware to grab the OLAP Cube data.

This MS SQL Express (Free) Edition is just there to connect to from the QV client.

3. On the QV client I made an ODBC connection to the MS SQL Express.

Next I run the OPENROWSET Query against the OLAP Cube using XML/A protocol.


As you can see the OPENROWSET command is executed locally on the MS SQL Server.

See : DATA SOURCE=http://localhost:8282/icCube/xmla?msrs

This makes it super easy to access the CUBE data.

Put in there your MDX Query and go.

4. When finished, reload and and look at the result.


This is realy fun stuff you don’t learn at school Winking smile

Enjoy !

QlikView – Creating Macro’s using VBScript

September 29, 2014

Qlikview is heaven for scripters like us Smile

It has an API that can be accessed by any COM compatible client.

And on the other hand it supports the VBScript language for writing Macro’s.

This will get you started.

First add the Macro to QV => Tools -> EDIT Module


Go to => Settings -> Document Properties -> Triggers


Add your Macro Function


Open the document again and test it.


See help file to know where you can trigger macro’s. There are several objects to which you can attach Macros

This is a Button example



Now it’s up to your imagination what you are going to automate in QlikView.

We could also call PowerShell scripts if you like.

If you are interested stay tuned Winking smile

Windows – Offline Synchronization not available outside of the Domain

September 23, 2014

Some user forgot the select the Make Offline Available option when he left the office using a VPN connection.


1. Make sure you have connected to the domain using a VPN client.

2. Once Connected right click the folder that you want to sync. And choose Always Available Offline.


But when the option is NOT Available in the shortcut menu.

3. Just press the button Work Offline.


And the option will come available, that’s it.

Sometimes the solution is right in front of your eyes Winking smile

PS :  See here for the Windows 10 Solution

Outlook – Migrating AutoComplete cache from Outlook 2010 to 2007

September 19, 2014

I seen many blog on the net about how to migrate the Email AutoComplete list for 2007 to 2010.

I will not repeat that here there’s already enough on the net to find for yourself.

But let’s presume the following. A PC crashes and you got a spare on around. But the user was using OL 2010 and now you handed over to him an OL 2007 ?

And he want to have all his email addresses migrated because does not want to type it all over. Sad smile

Solution :

In OL 2010 there is something that is called “Suggested Contacts”


Since the AutoComplete file format changed between the 2 Versions of OL. You just can’t backport it.

OL 2007 and before used a ProfileName.NK2 file


OL 2010 uses a Stream_Autocomplete_*.DAT file

But wait, we could pick up the “Suggested Contacts” and Export them to a PST file and then import them again in OL 2007 as contacts ?


Just go to OL 2010 File –> Options –> Advanced –> IMPORT (for Export)

Or go to File –> Open –> Import ( For Export )


Select Export to a File –> PST file –> Suggested Contacts


And so on, at the end you will have a PST file with all you Autocomplete E-mail Addresses in it as Contacts.

Now we can IMPORT it again in OL 2007. It’s not quite the same as autocomplete.

But the users can at least find back all his email addresses he did not have before.

1. Composing a new E-mail

2. types a part of the name, or email address

2. Next Press Ctrl + K and it will bring up the list of available email addresses.

You just made a user happy Smile


PowerPivot – Connect to an SSRS Report

September 17, 2014

SSRS is a very nice reporting platform that comes along with MS SQL.

Using the free Report Builder client you can make some very nice reports and publish or schedule them to run on your intranet.

But what if you want to reuse valuable information in the reports ?

Well we can you use MS PowerPivot to access the data over the internet on you client.

Open PP and choose the in the menu From Report.

Next point to the URL on you Reporting Intranet or SharePoint if you are running in mixed mode.


When you click next it will grab the TABLIX data grid in your report.


As easy as that.

This is how the bits and pieces fall together in your MS BI environment. Smile

SSRS – Connect to an OLAP cube using XML/A

September 16, 2014

While there is native way in Report Builder 3.0 to connect to an MS SQL OLAP Cube.

I wan’t to demonstrate an alternative way to connect to ANY kind of OLAP cube that supports XML/A.

For MS SQL you first need to configure your OLAP Analisys Services to be able to connect using XML/A protocol.

For Windows Server 2003 look here for Windows Server 2008 look here

Make sure you have the IIS Application Pool settings set to be 32Bit compatible.


Once you have it running you can connect SSRS using the msmdpump.dll

Like this :


You can use multiple XML/A compatible clients like for example Kettle Spoon.


Where you can enter your MDX query and test it.

As well as just plain MS Excel to connect to the OLAP Cube using XML/A connection


Or even PowerPivot as a client to connect.


But back to SSRS, it goes like this. Choose SQL Analysis Server, and point the to the XML/A URL as connection string.


And you can access the Dimensions using regular MDX Queries.



Apart from the fact that you access multiple OLAP Cube brands like for example the In-Memory IcCube



And using Multiple Clients – see above.

It comes also is also is not blocked by the firewall since XML/A uses the HTML (80) or SHTML (443) port.

Enjoy !

SSRS – Using SharePoint List Views to Filter & Sort

September 16, 2014

By default when reporting against a SP List it will use the DEFAULT View.



When you want to access the data using a Specific VIEW you need to add the following to the data source XML :


Create a View and pass Parameters of the View

<rssharepointlist xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <parameter name="viewName">
 <defaultvalue>All Issues</defaultvalue>

You need to add –> query –> parameters –> parameter name –> BEFORE the viewfields xml node.

The properties of this view are applicable to the dataset.

Also the sorting will be applicable to that dataset. 🙂

Reinstalling Windows 7 – SATA driver issues

September 16, 2014

I had to reinstall an HP Probook laptop on a brand new hard drive – but I could not get it to work ?

Whenever loading Windows 7 the disk would hang while installation.

After some re-trails I got windows installed but I got a Blue Screen BAD_SYSTEM_CONFIG_INFO.

It seems that that standard Windows SATA drivers where not stable enough to load the full CD ? Even it the drives where properly listed it did not work. Sad smile

So I had to go back the look for the appropriate drivers on the net. And load them BEFORE Windows started.

Get the drivers from the HP site

Preparing the SATA Drivers

The problem these days is that most PC’s have USB 3.0 ports, which means you can’t copy the drivers to a USB drive, because windows does not recognize the USB 3.0 port Sad smile

So next step is to burn a CD or DVD. But when downloading the Drivers from the HP site. These are EXE files, so while in the middle of the installation process you can run the EXE file to install the drivers Sad smile

Solution :

Download the EXE files on your drive.


Use 7-Zip to extract the Drivers.



Save it and burn a DVD for later use.

Ones all is done you can insert the driver DVD before Windows starts loading.



What about if you PC does not have a DVD drive ? Well then hopefully you can copy the drivers on a Network Share and get you NIC driver to work to grab the drivers from there !!

How hard can it be ?

If you don’t find the drivers at the Hardware reseller site, you can get the AHCI drivers from the Intel Site


Or for AMD look here on the AMD Site


Other option (not tested) is to disable the AHCI in the BIOS. So it will use the IDE or ATA to access the drive.

Needless to tell that these drivers don’t apply for all situations, you need to apply the drivers in combination of your specific hardware.

SharePoint -Build a Table of Content (TOC)

September 12, 2014

The thing I was always missing in SharePoint is how we could provide the users a nice (dynamic) TOC per Site or Site Collection.

Well this is possible using the SOAP connector (Web Services) against SharePoint itself.

Major bonus that comes along using the SOAP data source is, that is can access any data within SharePoint, across all Site Collections. Smile

Let’s go

Add a SOAP connection to a Site Collection


Use the proper WDSL syntax !


Add in a new Web Part Page.


Insert the Web Part.


Get the data using the SOAP Connector you have created above, using the Insert –> Data View


Browse down to the SOAP Services to find it.


It will list too much columns and rows, so we can apply a filter. Using the Add / Remove Columns button. And Filter Button if needed.


First remove most columns, keep only Title and add Description.


Now we are going to make I a bit more dynamic, by making the Title data a Hyperlink so you can jump to the respective page.

click on the Title columns and choose INSERT –> Hyperlink. And choose the Formula button.


And now we go and look at the result so far.

Go to you Site Pages where you saved the page and check it.


Looking good !

A bit more salt and pepper will make it perfect. So we are going to add the appropriate ICONS the belong by each type of list.

Go back to the SP Designer and add a space before Title the table rows.


Go to the Data Source Details on the right side pane. Select ImageURL


Choose on top Insert Selected Fields as … Formatted –> Picture

Save it and go back to see the end result !


This will dynamically keep track of you TOC. Smile

Enjoy !

SSRS – Debug Connection Problems

September 12, 2014

Sometimes you get an error like this.


For more information about this error navigate to the report server on the local server machine, or enable remote errors

So how to enable Remote Errors for SSRS

Go to Central Admin.

  1. In Site Actions click Site Settings for the site you want to modify.
  2. Click Reporting Services Site Settings in the Reporting Services group.
  3. Click Enable remote errors in local mode.



Bu keep in mind that SSRS will not pass most errors forward for the WFE to display. Sad smile

You can set the Option as well using SSMS

Connect to Reporting Services


Go to Server Properties –> Advanced –> Remote Errors



Since the BCS data resides in an external data source (like MS SQL database).

Just don’t make the round trip using the SharePoint External Content Types (BCS). Because it will overcomplicate things security wise. It’s more then a double hop to jump over.

Just use SSRS to go directly to the external data source. It will save you a trip and give you a better performanceSmile