GENERATE(): Is there an alternative?

For the following fact table (3 keys, 2 measures)

Assessment_Date_PK

Machine_PK

Patch_PK

PatchApplied (tinyint 0 or 1)

PatchNotApplied (tinyint 0 or 1)

the following Named Set has been created in the cube script (SSAS 2005) utilizing the GENERATE() function:

CREATE HIDDEN SET CURRENTCUBE.[Last Patched]

AS GENERATE(

{NONEMPTY(

([Machine].[Machine Name].[Machine Name],

[Patch].[Patch Name].[Patch Name])

)},

CROSSJOIN(

{

([Machine].[Machine Name].CurrentMember,

[Patch].[Patch Name].CurrentMember)

},

{(NONEMPTY([Date].[Date].Members)).Item(COUNT(NONEMPTY([Date].[Date].Members))-1).Item(0)}

)

);

Introduction of this MDX has adversely impacted performance both 1) when the cube is processed and 2) when the cube is browsed (ie in XL07). Are there any alternatives to GENERATE that we might be able to try?

The purpose of the Named Set [Last Patched] is to provide, for each Machine/Patch combination, the last time it was assessed. With this Named Set, other calculations can be applied to answer questions regarding the last know patch status of the machines being assessed.

Thanks

+ Ron

Hi Ron,

Looks like you could try the approach discussed here in Chris Webb's blog:

Optimising GENERATE() type operations

...

Take the following Adventure Works query, which is an approximation of the one in the thread:

WITH SET MYROWS AS

GENERATE(

NONEMPTY([Customer].[Customer Geography].[Full Name].MEMBERS, [Measures].[Internet Sales Amount])

,TAIL(

NONEMPTY([Customer].[Customer Geography].CURRENTMEMBER * [Date].[Date].[Date].MEMBERS, [Measures].[Internet Sales Amount])

,1)

)

SELECT [Measures].[Internet Sales Amount] ON 0,

MYROWS ON 1

FROM

[Adventure Works]

What we're doing here is finding the last date that each customer bought something. Using the TAIL function within a GENERATE might be the obvious thing to do here, but in fact it isn't the most efficient way of solving the problem: on my machine, with a warm cache, it runs in 16 seconds whereas the query below which does the same thing only takes 6 seconds:

WITH SET MYROWS AS

FILTER(

NONEMPTY(

[Customer].[Customer Geography].[Full Name].MEMBERS

* [Date].[Date].[Date].MEMBERS

, [Measures].[Internet Sales Amount])

AS MYSET,

NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(RANK(MYSET.CURRENT, MYSET)).ITEM(0))

)

SELECT [Measures].[Internet Sales Amount] ON 0,

MYROWS ON 1

FROM

[Adventure Works]

What I'm doing differently here is rather than iterating through each Customer finding the set of dates when each Customer bought something and then finding the last one, I'm saying give me a set of tuples containing all Customers and the Dates they bought stuff on and then using a FILTER to go through and find the last Date for each Customer by checking to see if the Customer mentioned in the current tuple is the same as the Customer in the next tuple in the set - if it isn't, then we've got the last Date a Customer bought something. Obviously operations like this within a GENERATE are something to be avoided if you can.

|||

I have the following MDX using the above example as a template

WITH SET MYROWS AS

FILTER(

NONEMPTY(

([Machine].[Machine Name].[Machine Name],

[Patch].[Patch Name].[Patch Name])

* [Date].[Date].MEMBERS

) AS MYSET,

NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(RANK(MYSET.CURRENT, MYSET)).ITEM(0))

)

SELECT

[Measures].[Patch Applied] ON 0,

MYROWS ON 1

FROM [MyCube]

Notice the tuple (Machine Name, Patch Name)

The results of the above query are quite different from the similar query below where the tuple order is reversed.

WITH SET MYROWS AS

FILTER(

NONEMPTY(

([Patch].[Patch Name].[Patch Name],

[Machine].[Machine Name].[Machine Name])

* [Date].[Date].MEMBERS

) AS MYSET,

NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(RANK(MYSET.CURRENT, MYSET)).ITEM(0))

)

SELECT

[Measures].[Patch Applied] ON 0,

MYROWS ON 1

FROM [MyCube]

Unfortunately, neither result from the above queries is desired.

Anyone have a suggestion on how to modify the FILTER's search condition to work with a tuple?

|||

WITH SET MYROWS AS

FILTER( NONEMPTY([Machine].[Machine Name].MEMBERS * [Date].[Date].MEMBERS * [Patch].[Patch Name].MEMBERS, [Measures].[Patch Applied]) AS MYSET,

NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(RANK(MYSET.CURRENT, MYSET)).ITEM(0))

)

SELECT MYROWS ON 0

FROM [MyCube]

Try the above it should list the last patch applied per Machine. Just with the Machine and Patch if you and the patch per Machine, which is not the same thing J.

I run this same sort of quest on my cubes and verified the result in my DW.I would recommend you do the same.

|||

CaveM,

Thanks for the reply but this isn't what I'm looking for.

As you mentioned, the MDX you provided will list the last patch applied per Machine. What I need answered is when a machine was last assessed and the status for each patch in order to answer questions such as "Are my machines 100% patched?".

|||

WITH SET MYROWS AS

FILTER( NONEMPTY([Machine].[Machine Name].MEMBERS * [Patch].[Patch Name].MEMBERS * [Date].[Date].MEMBERS, [Measures].[Patch Applied]) AS MYSET,

NOT(MYSET.CURRENT.ITEM(1) IS MYSET.ITEM(RANK(MYSET.CURRENT, MYSET)).ITEM(1))

)

SELECT MYROWS ON 0

FROM [MyCube]

The above should tell you what you have installed per Machine. If not I'll just have to shut up I tested it in my CUBE and verified it to DW, please do the same. I am still learning MDX so don't take my word for it, test it

|||

Thanks Deepak and CaveM,

I still need to test the performance against a production system but with the combination of your answers I have an alternative.

CaveM,

No need to shut up. Keep answering these!