I am using PDP 6.3. When I try to put a total on my grid, it basically kills ProClarity. I know there was a problem with Totals in older versions of PDP, but I thought it would have been fixed by now. Is there some workaround or hotfix?
Thanks,
Linda Fleming
Hello Linda! I have not seen performance issues with ProClarity totals but can you send some more information regarding your problem?
Can you recreate the problem on the Adventure Works sample cube or can you tell us more about the structure of your cube?
Regards
Thomas Ivarsson
|||Thomas,
(Note: I tried using a partition but did not make any difference because I think the problem is with the large number of dimension rows and not the fact table)
factTable: 200,000 rows.
I want subtotals in my view by Stage, but the query never returns.
All Companies has 1614 rows
All Opportunities has 406 rows
Here is my MDX:
SELECT NON EMPTY { [Next 3 Months Anticipated Close] } ON COLUMNS ,
NON EMPTY { { { [Stage].[Stage].&[10 - Confirmed], [Stage].[Stage].&[20 - Qualified], [Stage].[Stage].&[30 - Proposed] } * { DESCENDANTS( [Company].[Company].[All Companies], [Company].[Company].[Company] ) } * { DESCENDANTS( [Opportunity].[Opportunity].[All Opportunities], , LEAVES ) } } } ON ROWS
FROM [Daily Pipeline Snapshot]
WHERE ( [Measures].[Opportunity Value], [Sales Rep].[Sales Rep].&[TAYLOR], [Opportunity Status].[Opportunity Status].[Op Status].&[Open], [Probability].[Probability Description].[All Probability] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
I can send you my cube if this would expedite things.
|||Hello Linda. I think that the problem relates to that you crossjoin several dimensions, one of them at the leaf level.
I do not know how many members you have in each dimensions but if you can do subtotals on the Adventure Works cube it is probably the design of the cube that decides this.
If you remove some dimensions in the crossjoin and only start with the first two? Will it work?
HTH
Thomas Ivarsson
|||Hi,
There isn't a work around or hotfix for the problem, we had the same problem and users have been banded from including them in their queries.
Most of the time you don't need to use ProClarity's Total options, if you are selecting every child of an attribute, you can just include the all level as well and that will give you a total e.g.
All Opps
-opp1
-opp2
-opp3
SELECT { [Measures].[Measure1] } ON COLUMNS ,
{ [Dim].[Opps].[All Opps], [Dim].[Opps].&[Opp1], [Dim].[Opps].&[Opp2],[Dim].[Opps].&[Opp3] } ON ROWS
FROM [fact]
would give you an all level (total)
Measure
All 20
opp1 5
opp2 5
opp3 10
If you're not using all the members in the attribute, I wouldn't let ProClarity build your MDX statement with totals. It ends up writing the most inefficient code ever, hence why proclarity dies. Perhaps write your own MDX and paste it in the MDX viewer.
HTH
Matt
|||Good point Mark but if the subtotal feature is usable or useless depends on how many cells that you call with several dimensions on an axis and on the leaf level.
No tool can manage that!
Regards
Thomas Ivarsson
|||I wasn't really saying it was, I was just impling that ProClarity generates rubbish code. I am sure some careful planning of a report or generating your own MDX is more efficent than using ProClarity's total functions.
I was perhaps offereing a work around for the problem seeing as there wasn't a fix.
Mark AKA Matt
|||Hello Matt. I will try your solution.
The problem with your approach is that your own MDX will be changed to ProClarity MDX as soon as a user start to change your report. Your solution will probably work well with SSRS2005 or if you publish static ProClarity reports.
I have published several complaints about ProClarity's implementation of MDX on my blog.
Regards
Thomas Ivarsson
|||Matt,
Thank you for your comments. Our company has been struggling with the Totals for several years and just keep hoping it gets fixed. I notice it uses the Aggregate function. Maybe SUM would be better? I will keep working on it, but Thomas has a good point that you can't start analyzing the view. If it is a static view on the dashboard I suppose custom MDX would work.
Linda
Thomas,
I took off the 3rd dimension (Opportunity) and the subtotals worked. Here is my MDX:
WITH MEMBER [Company].[Company].[All Companies].[ Subtotal]
AS ' AGGREGATE( EXISTING
{ DESCENDANTS( [Company].[Company].[All Companies], [Company].[Company].[Company] ) }) ',
SOLVE_ORDER = 1000
SELECT NON EMPTY { [Next 3 Months Anticipated Close] } ON COLUMNS ,
NON EMPTY { { { { [Stage].[Stage].&[10 - Confirmed], [Stage].[Stage].&[20 - Qualified], [Stage].[Stage].&[30 - Proposed] } } * { { [Company].[Company].[All Companies].[ Subtotal] }, { DESCENDANTS( [Company].[Company].[All Companies], [Company].[Company].[Company] ) } } } } ON ROWS
FROM [Daily Pipeline Snapshot]
WHERE ( [Measures].[Opportunity Value], [Sales Rep].[Sales Rep].[All Sales Rep], [Opportunity Status].[Opportunity Status].[Op Status].&[Open], [Probability].[Probability Description].[All Probability] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
However, now, I don't know what to try next since I really need that last dimension. Is there a possible solution? I would really like to send you my cube and let you experiment with it. Is that an option?
Linda
|||Hi,
Still a little confused by what you are both saying, as I can produce something identical to what you are suggesting in ProClarity and still slice and dice it afterwards. So that wouldn't imply it was static.
e,g, You code looks something like:
Code Snippet
WITH MEMBER [Campaign].[Campaign Region].[All Campaigns].[ Subtotal]
AS
' AGGREGATE( EXISTING { [Campaign].[Campaign Region].[All Campaigns].CHILDREN }) ',
SOLVE_ORDER = 1000
SELECT { [Measures].[Charge Out] } ON COLUMNS ,
NON EMPTY { { [Campaign].[Regular Campaign Flag].&[0],
[Campaign].[Regular Campaign Flag].&[1.] } *
{ { [Campaign].[Campaign Region].[All Campaigns].CHILDREN },
{ [Campaign].[Campaign Region].[All Campaigns].[ Subtotal] } } } ON ROWS
FROM [Self Service Prototype 3]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
mine looks something like
Code Snippet
SELECT { [Measures].[Charge Out] } ON COLUMNS ,
NON EMPTY { { [Campaign].[Regular Campaign Flag].&[0],
[Campaign].[Regular Campaign Flag].&[1.] } *
{ { [Campaign].[Campaign Region].[All Campaigns]},
{ [Campaign].[Campaign Region].[All Campaigns].children } } } ON ROWS
FROM [Self Service Prototype 3]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
They both produce the same results and I can slice it later on. I didn't write the code; I used ProClarity to generate it, so there is no reformatting. Which I know is an issue if you start having drop down slicers or usign subcubes. The performance hit is less as well with mine, on a cold cache the top one ran 13secs lower one 3 secs.
The only difference is I have an "All level" at the top and you have a "sub total" at the bottom.
The only down side to it as far as i can see it is if you don't include all the members of the dimension then the "All level" doesn't work.
And no matter what you code to fix the issue, as soon as you drill into something ProClarity will revert it back to using its way of MDX. So you could have a fancy quick query on the first page but as soon as you drill into a level, you still going to lose your code and perhaps incur a performance hit. Which I found out when I drilled into mine, ProClarity reverted it back to using subtotals.
You could always try caching the results (common ones) that way when your uses run their queries they hit the cache first. I believe chris webb has a blog on cache warming.
Matt
|||Hello Linda! This one
Code Snippet
{ [Next 3 Months Anticipated Close] } on columns is that a calculated member, calculated measure, named set or dimension member?You can send me the cube but the problem is that we cannot change ProClarity's MDX behaviour. Have you tried the same view/query in Excel 2007? I think you can download a trial version only to see if there is a difference between these clients.
Regards
Thomas Ivarsson
|||Thomas,
[Next 3 Months Anticipated Close] is a named set.
STRTOSET(
"[Anticipated Close Date].[Hierarchy].[Month].&["
+ Format(Now(), "yyyy") + Format(Now(), "MM") + "].Item(0):
[Anticipated Close Date].[Hierarchy].[Month].&["
+ Format(Now(), "yyyy") + Format(Now(), "MM") + "].Item(0).Lead(2)")
Where can I send the cube? I would like you to look at it and see if you get the same behavior I do.
Can Excel 2007 read cubes?
Linda
|||Linda! Excel2007 supports all SSAS2005 features except writeback. Try that option first and see if it helps.
It can reveal if it is ProClarity's MDX that is the problem.
How large is the cube? If it is large you may have to place it on an FTP-site.
If you look at my profile you can see my email.
STRTOSET is not good for performance. Is that a dynamic,running month, set?
Regards
Thomas Ivarsson
Regards
Thomas Ivarsson
|||Thomas,
We have Excel2007. I will install it and try it.
The data warehouse is 10,500 KB.
Yes, it is a dynamic running month set. What can I use instead of STRTOSET?
Thanks,
Linda
|||Linda! I have an example here: http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!352.entryThere are many other ways to solve this.
I think you can send the cube directly.
Regards
Thomas Ivarssonsql
No comments:
Post a Comment