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.

image

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.

image

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.

image

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

image

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

clip_image002

Add your Macro Function

clip_image004

Open the document again and test it.

clip_image006

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

clip_image008

clip_image010

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.

Solution:

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.

image

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

3. Just press the button Work Offline.

image

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”

image

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

image

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 ?

image

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

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

image

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

image

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

Enjoy!


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.

image

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

image

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.

image

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

Like this :

http://YourServer/olap/msmdpump.dll

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

image

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

image

Or even PowerPivot as a client to connect.

image

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

image

And you can access the Dimensions using regular MDX Queries.

image

Bonus:

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

image

image

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.

clip_image002

clip_image004

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

clip_image006

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">
 <listname>HelpDesk</listname>
 <query>
 <parameters>
 <parameter name="viewName">
 <defaultvalue>All Issues</defaultvalue>
 </parameter>
 </parameters>
 </query>
 <viewfields>
 </viewfields>
 </rssharepointlist>

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.

image

Use 7-Zip to extract the Drivers.

image

image

Save it and burn a DVD for later use.

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

image

image

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

image

Or for AMD look here on the AMD Site

image

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

image

Use the proper WDSL syntax !

image

Add in a new Web Part Page.

image

Insert the Web Part.

image

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

image

Browse down to the SOAP Services to find it.

image

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.

image

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

image

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.

image

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

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

image

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.

image

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

image

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

Save it and go back to see the end result !

image

This will dynamically keep track of you TOC. Smile

Enjoy !


SSRS – Debug Connection Problems

September 12, 2014

Sometimes you get an error like this.

image

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.

image

image

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

image

Go to Server Properties –> Advanced –> Remote Errors

image

Solution:

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