PerformancePoint Server 2007 RTM has been up on MSDN subscriber downloads for a while now, well several days anyway, so I would imagine there aren't too many people who haven't got hold of it who are doing some serious mucking around in PPS.
If you're one of those people who are on the CTP but don't have access to the RTM then this may be of some interest.
As some of you may be aware one of the report types you can embed into a PerformancePoint dashboard is a Proclarity Analytics Server Page. This effectively provides the user with a Proclarity briefing book allowing some interactivity and as with almost all report types within PPS filters can be applied externally by PPS allowing say a PPS analytics grid, PAS page or reporting services report to all be displayed with the same parameters / dimension members being passed through.
The particular filter type that this post is concerned with are multi-selects. This is a typical dimension selection technique you've seen in plenty of tools before so I won't teach you to suck eggs on that one. Anyway, to cut a long story short after the dashboard has been published it's possible to preview the dashboard with your embedded PAS based report and filters.
Select a single member from your filter and apply and the report return pretty quickly, not lightening fast but remember this a CTP and debugging is switched on etc. so that has to be taken into account. Select 2 members from the filter and when the report is say performing a non empty on a dimension with members numbering in the thousands, it takes a little longer than it should, in fact uncomfortably longer.
Profile the poor performing MDX and here's what you get....
WITH
SET [<##<SET!smchugh!{E3EF8F62-CD2B-422D-881F-ABAFB4AA3E16}>##>]
AS '{[Product].[Product Categories].[Subcategory].&[31]}'
SET [<##<SET!smchugh!{844FB37C-1DA6-4536-B7A4-297650572576}>##>]
AS '{[Product].[Product Categories].[Subcategory].&[1]}'
MEMBER [Product].[Product Categories].[All Products].[ Aggregation]
AS ' AGGREGATE( EXISTING { [<##<SET!smchugh!{E3EF8F62-CD2B-422D-881F-ABAFB4AA3E16}>##>],
[<##<SET!smchugh!{844FB37C-1DA6-4536-B7A4-297650572576}>##>] }) ',
SOLVE_ORDER = 0
SELECT
NON EMPTY { [Measures].[Internet Tax Amount], [Measures].[Internet Order Quantity] }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS ,
NON EMPTY { [Customer].[Customer].[All Customers].CHILDREN}
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS
FROM
[Adventure Works]
WHERE
( [Product].[Product Categories].[All Products].[ Aggregation] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
Take particular notice of the new member called [ Aggregation] that uses the 2 sets. If they are stripped out then and the MDX run in a much more sensible manner such as ....
WITH
MEMBER [Product].[Product Categories].[All Products].[ Aggregation]
AS ' AGGREGATE( EXISTING {
[Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[1]}) ',
SOLVE_ORDER = 0
SELECT
NON EMPTY { [Measures].[Internet Tax Amount], [Measures].[Internet Order Quantity] }
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS ,
NON EMPTY { [Customer].[Customer].[All Customers].CHILDREN}
PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS
FROM
[Adventure Works]
WHERE
( [Product].[Product Categories].[All Products].[ Aggregation] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
...same data returned but with a massive time difference. It's worth actually running both these queries on [Adventure Works] as it's a good indication as to what a difference little changes in your MDX can make.
Suffice to say the second query is now how it's structured in PerformancePoint Server 2007 RTM so it's well worth getting this installed as soon as possible as not only will queries return faster but some nasty timeouts on your dashboard will be avoided.
Posts
No comments:
Post a Comment