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 !

Advertisements

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

image

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

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


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