Using MDX queries in SSIS without OpenQuery

September 6, 2009

Recently i needed to use data from an SSAS2005 cube as a source within an SSIS data flow. I thought this was going to be pretty simple but only managed to do it nicely after a bit of searching around.

Firstly i tried the obvious way i.e. create a new OLE DB connection by right-clicking from the connections pane, and then choosing the “Native OLEDB\Microsoft OLE DB Provider for Analysis Services 9.0” Provider. In the data flow i used an OLEDB source and types in my MDX query. Preview worked fine but executing the package kept giving me an error (80004005). Darren Gosbell covers this briefly here, and also looks at the linked server with OpenQuery method which i have used elsewhere.

While the linked server solution does work it certainly isnt elegant and does require server settings to be changed. This is not always feasible in reality.. or at least not without due process being followed in production environments. Luckily i came across another solution. Instead of using the OLE DB connection, we instead use an ADO.Net connection, and then use “.Net Providers for OleDb\Microsoft OLE DB Provider for Analysis Services 9.0” provider and configure the connection to your cube. From there use a Datareader source and configure it as per normal, substituting your MDX query instead of T-SQL. The slight downside is that all fields are returned as DT_NTEXT. You will need to convert to DT_WSTR first and then perform a second conversion to the required types. Still better than openquery!


Handling Divide by null in Cubes for PPS, a better way than IIF()

August 13, 2009

Today i needed to get rid of the “1.#INF” error you get from SSAS when there is a divide by zero or null. The reason for this was that i was building a scorecard in PPS that had a “% change from previous month” calculated member as one metric in the KPI. This was specified as (Current-Previous)/Previous with the appropriate format string. When there was no activity in the previous month Previous would be null and this would give the error. I wanted to produce null instead so i could use the PPS scorecard option to replace null with a custom string.. in my case just a hyphen (i.e. “-“)

I initially though of using IIF() to solve my problem but i have read many times how it should be used thoughtfully as it can affect performance in certain scenarios. I have also experienced this in practice. Unfortunately ive lost the original post i read but i will reproduce the method i found here.

The SSAS formula parser tries to performs certain optimizations and we can take advantage of that! ill simplify the example by using just two values A and B.

If our calculated member originally took the form:

A/B

we replace it with:

(B * (A/B)) / B

What does this do? Well if you do the simple maths, effectively it just multiplies the calc by 1.. but the advantage is that the parser will not evaluate the rest of the expression if B is null (N.B this does not work if B = 0) . I tested this behaviour with the following query:

with member a as 55

member b as null
member c as 0

member c1 as a/b
member c2 as a/c

member c3 as (b * (a/b))/b
member c4 as (c * (a/c))/c

select  {c1, c2, c3, c4} on 0

from

[AdventureWorks]

Result:
divByNull

Its interesting that c4 returns “-1.#IND”. This (according to the IEEE 754 definition) means “Negative Indefinite”. Basically 0/0 is special case and is not the same as any other divide by zero.

Here is a related post on this topic.