[Mondrian] Mondrian performance

Pranav.Matekar at zycus.com Pranav.Matekar at zycus.com
Sun Mar 4 08:16:00 EST 2007


hi

thanks for ur immediate response.

tried query is autogenerated

WITH MEMBER [MEASURES].[PERCENTAGE_OF_TOTAL_SPEND] AS 
'(([MEASURES].[SPEND_INVOICE_LINE_AMT] / (12167731.16357 * 1000)) * 100)' 
MEMBER [MEASURES].[PERCENTAGE_OF_COUNT_OF_SUPPLIER] AS '((COUNT 
(DISTINCT({ Descendants([SUPPLIER_DIM].[All 
SUPPLIER_DIM],[SUPPLIER_DIM].Levels(1))}),EXCLUDEEMPTY) / 38170) * 100)' 
MEMBER [MEASURES].[PERCENTAGE_OF_COUNT_OF_TRANSACTION] AS 
'(([MEASURES].[NO_TRANSACTION] / 2822489) * 100)' 
MEMBER [MEASURES].[COUNT_OF_BU] AS 'COUNT (DISTINCT({ 
Descendants([BU_DIM].[All BU_DIM],[BU_DIM].Levels(1))}),EXCLUDEEMPTY)' 
MEMBER [MEASURES].[COUNT_OF_SUPPLIER] AS 'COUNT (DISTINCT({ 
Descendants([SUPPLIER_DIM].[All 
SUPPLIER_DIM],[SUPPLIER_DIM].Levels(1))}),EXCLUDEEMPTY)' 
MEMBER [MEASURES].[COUNT_OF_UNSPSC] AS 'COUNT (DISTINCT({ 
Descendants([UNSPSC_DIM].[All 
UNSPSC_DIM],[UNSPSC_DIM].Levels(1))}),EXCLUDEEMPTY)' 
MEMBER [MEASURES].[DESC_OF_UNSPSC] AS 'COUNT (DISTINCT({ 
Descendants([UNSPSC_DIM].CurrentMember,[UNSPSC_DIM].Levels(4))}),INCLUDEEMPTY)' 

MEMBER [MEASURES].[RATIO_SUPPLIER_TO_CATEGORY] AS '(COUNT (DISTINCT({ 
Descendants([SUPPLIER_DIM].[All 
SUPPLIER_DIM],[SUPPLIER_DIM].Levels(1))}),EXCLUDEEMPTY) / COUNT 
(DISTINCT({ 
Descendants([UNSPSC_DIM].CurrentMember,[UNSPSC_DIM].Levels(4))}),INCLUDEEMPTY))' 

SET [DerivedSet1] AS '{ [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[622011],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[221200],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[153100],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[229200],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[200971],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[622110],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[NOT_KNOWN],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[221100],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[164100],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[620110],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[621410],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[225100],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[169200],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[621704],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[185138],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[225200],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[621610],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[621010],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[610310],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[620260],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[620613],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[192191],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[169100],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[222100],  [OBJECT_ACCOUNT_DIM].[All 
OBJECT_ACCOUNT_DIM].[152610]}' 
SET [DerivedSet2] AS '{ [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00000],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00001],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00020],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00085],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00065],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00150],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00023],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00120],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[NOT_KNOWN],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00069],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00153],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00121],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00078],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00152],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00063],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00101],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00003],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00093],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00028],  [DOCUMENT_COMPANY_DIM].[All 
DOCUMENT_COMPANY_DIM].[00024]}' 
SET [DerivedSet3] AS '{ [BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[HW], 
[BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[836103], 
[BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[2010],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[836039],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[20050222],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[LG],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[831199],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[BS],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[20010184],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[SF],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[836067],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[CO],  [BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[SY], 
[BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[NOT_KNOWN], 
[BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[1],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[836070],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[20030216],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[ABML],  [BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[RI], 
 [BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[20040249], 
[BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[831196], 
[BRANCH_PLANT_DIM].[All BRANCH_PLANT_DIM].[TO],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[BX],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[837482],  [BRANCH_PLANT_DIM].[All 
BRANCH_PLANT_DIM].[CTS]}' 
SET [DerivedSet4] AS '{ [SOURCE_DIM].[All SOURCE_DIM].[SPEND NON K TYPE 
FEB07],  [SOURCE_DIM].[All SOURCE_DIM].[PCARD],  [SOURCE_DIM].[All 
SOURCE_DIM].[SPEND K TYPE],  [SOURCE_DIM].[All 
SOURCE_DIM].[AMEX_SPEND_FEB07],  [SOURCE_DIM].[All SOURCE_DIM].[NON PO]}' 
SET [DerivedSet5] AS '{ [UNSPSC_DIM].[All UNSPSC_DIM].[Resin and Rosin and 
Rubber and Foam and Film and Elastomeric Materials],  [UNSPSC_DIM].[All 
UNSPSC_DIM].[Industrial Production and Manufacturing Services], 
[UNSPSC_DIM].[All UNSPSC_DIM].[Financial and Insurance Services], 
[UNSPSC_DIM].[All UNSPSC_DIM].[Organizations and Clubs], [UNSPSC_DIM].[All 
UNSPSC_DIM].[Engineering and Research and Technology Based Services], 
[UNSPSC_DIM].[All UNSPSC_DIM].[Healthcare Services],  [UNSPSC_DIM].[All 
UNSPSC_DIM].[Office Equipment and Accessories and Supplies], 
[UNSPSC_DIM].[All UNSPSC_DIM].[Information Technology Broadcasting and 
Telecommunications],  [UNSPSC_DIM].[All UNSPSC_DIM].[Public Utilities and 
Public Sector Related Services],  [UNSPSC_DIM].[All UNSPSC_DIM].[Politics 
and Civic Affairs Services],  [UNSPSC_DIM].[All UNSPSC_DIM].[Management 
and Business Professionals and Administrative Services], [UNSPSC_DIM].[All 
UNSPSC_DIM].[Medical Equipment and Accessories and Supplies], 
[UNSPSC_DIM].[All UNSPSC_DIM].[National Defense and Public Order and 
Security and Safety Services],  [UNSPSC_DIM].[All UNSPSC_DIM].[Industrial 
Cleaning Services],  [UNSPSC_DIM].[All UNSPSC_DIM].[Travel and Food and 
Lodging and Entertainment Services],  [UNSPSC_DIM].[All 
UNSPSC_DIM].[Transportation and Storage and Mail Services], 
[UNSPSC_DIM].[All UNSPSC_DIM].[Material Handling and Conditioning and 
Storage Machinery and their Accessories and Supplies],  [UNSPSC_DIM].[All 
UNSPSC_DIM].[Industrial Manufacturing and Processing Machinery and 
Accessories],  [UNSPSC_DIM].[All UNSPSC_DIM].[Education and Training 
Services],  [UNSPSC_DIM].[All UNSPSC_DIM].[Laboratory and Measuring and 
Observing and Testing Equipment],  [UNSPSC_DIM].[All 
UNSPSC_DIM].[Editorial and Design and Graphic and Fine Art Services], 
[UNSPSC_DIM].[All UNSPSC_DIM].[Building and Construction and Maintenance 
Services],  [UNSPSC_DIM].[All UNSPSC_DIM].[Chemicals including Bio 
Chemicals and Gas Materials],  [UNSPSC_DIM].[All UNSPSC_DIM].[NOT_KNOWN], 
[UNSPSC_DIM].[All UNSPSC_DIM].[Drugs and Pharmaceutical Products]}' 
MEMBER [BRANCH_PLANT_DIM].[DerivedMember6] AS 'SUM ([DerivedSet3])' 
MEMBER [DOCUMENT_COMPANY_DIM].[DerivedMember7] AS 'SUM ([DerivedSet2])' 
MEMBER [OBJECT_ACCOUNT_DIM].[DerivedMember8] AS 'SUM ([DerivedSet1])' 
MEMBER [SOURCE_DIM].[DerivedMember9] AS 'SUM ([DerivedSet4])' 
SET [DerivedSet10] AS '{ Filter ( { TOPCOUNT ([DerivedSet5], 10, ( 
[BRANCH_PLANT_DIM].[DerivedMember6], 
[DOCUMENT_COMPANY_DIM].[DerivedMember7], 
[OBJECT_ACCOUNT_DIM].[DerivedMember8],  [SOURCE_DIM].[DerivedMember9], 
[MEASURES].[SPEND_INVOICE_LINE_AMT]) )}, ( 
[BRANCH_PLANT_DIM].[DerivedMember6], 
[DOCUMENT_COMPANY_DIM].[DerivedMember7], 
[OBJECT_ACCOUNT_DIM].[DerivedMember8],  [SOURCE_DIM].[DerivedMember9], 
[MEASURES].[SPEND_INVOICE_LINE_AMT])> 0 )}' 
SET [DerivedSet11] AS '{ [SUPPLIER_DIM].LEVELS(1).MEMBERS}' 
MEMBER [BRANCH_PLANT_DIM].[DerivedMember12] AS 'SUM ([DerivedSet3])' 
MEMBER [DOCUMENT_COMPANY_DIM].[DerivedMember13] AS 'SUM ([DerivedSet2])' 
MEMBER [OBJECT_ACCOUNT_DIM].[DerivedMember14] AS 'SUM ([DerivedSet1])' 
MEMBER [SOURCE_DIM].[DerivedMember15] AS 'SUM ([DerivedSet4])' 
MEMBER [UNSPSC_DIM].[DerivedMember16] AS 'SUM ([DerivedSet10])' 
MEMBER [BRANCH_PLANT_DIM].[DerivedMember17] AS 'SUM ([DerivedSet3])' 
MEMBER [DOCUMENT_COMPANY_DIM].[DerivedMember18] AS 'SUM ([DerivedSet2])' 
MEMBER [SUPPLIER_DIM].[DerivedMember19] AS 'SUM ([DerivedSet11])' 
MEMBER [OBJECT_ACCOUNT_DIM].[DerivedMember20] AS 'SUM ([DerivedSet1])' 
MEMBER [SOURCE_DIM].[DerivedMember21] AS 'SUM ([DerivedSet4])' 
SELECT 
NON EMPTY { [MEASURES].[SPEND_INVOICE_LINE_AMT], 
[MEASURES].[PERCENTAGE_OF_TOTAL_SPEND]} ON COLUMNS , 
NON EMPTY { GENERATE ( { ORDER ( { Filter ( { [DerivedSet10]}, ( 
[BRANCH_PLANT_DIM].[DerivedMember17], 
[DOCUMENT_COMPANY_DIM].[DerivedMember18], 
[OBJECT_ACCOUNT_DIM].[DerivedMember20],  [SOURCE_DIM].[DerivedMember21])> 
0 )}, ( [SUPPLIER_DIM].[DerivedMember19], 
[MEASURES].[SPEND_INVOICE_LINE_AMT]),BDESC )}, { Filter ( { TOPCOUNT ({ 
CROSSJOIN ( { [UNSPSC_DIM].CurrentMember}, { Filter ( [DerivedSet11], ( 
[UNSPSC_DIM].CurrentMember)> 0 )} )}, 3, ( 
[BRANCH_PLANT_DIM].[DerivedMember12], 
[DOCUMENT_COMPANY_DIM].[DerivedMember13], 
[OBJECT_ACCOUNT_DIM].[DerivedMember14],  [SOURCE_DIM].[DerivedMember15], 
[MEASURES].[SPEND_INVOICE_LINE_AMT]) )}, ( 
[BRANCH_PLANT_DIM].[DerivedMember12], 
[DOCUMENT_COMPANY_DIM].[DerivedMember13], 
[OBJECT_ACCOUNT_DIM].[DerivedMember14],  [SOURCE_DIM].[DerivedMember15], 
[MEASURES].[SPEND_INVOICE_LINE_AMT])> 0 )} )} ON ROWS 
FROM [PRANAV] WHERE ( [BRANCH_PLANT_DIM].[DerivedMember17], 
[DOCUMENT_COMPANY_DIM].[DerivedMember18], 
[OBJECT_ACCOUNT_DIM].[DerivedMember20],  [SOURCE_DIM].[DerivedMember21])



thank you.


Pranav
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070304/2328e524/attachment.html 


More information about the Mondrian mailing list