PDA

View Full Version : Re : [Mondrian] Mondrian performance



Pranav.Matekar@zycus.com
03-04-2007, 09:26 AM
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
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian