Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Performance issue with MDX query

  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Default Performance issue with MDX query

    I'm trying to get top 5 records from MySQL database based on some filters using below MDX query and cube definition.But, this query takes more time for execution and this works fine when we have less no.of FILE_NUM's in where condition. Please suggest how to rewrite this query to gain performance.
    MDX Query:
    SELECT {[Measures].[BBLSOIL_TOTAL], [Measures].[MCF_PROD_TOTAL], [Measures].[AVG.DAYS]} ON COLUMNS, {TOPCOUNT(FILTER([WELL_ID].[WellIds].MEMBERS, [WELL_ID].CurrentMember > 0.0), 5.0, [Measures].[BBLSOIL_TOTAL])} ON ROWS FROM [MON_KPI_CHARTS] WHERE CrossJoin([FILE_NUM].[1],[FILE_NUM].[10],[FILE_NUM].[18],[FILE_NUM].[47],[FILE_NUM].[52],[FILE_NUM].[105],[FILE_NUM].[126],[FILE_NUM].[392],[FILE_NUM].[588],[FILE_NUM].[656],[FILE_NUM].[995],[FILE_NUM].[1005],[FILE_NUM].[1010],[FILE_NUM].[1061],[FILE_NUM].[1128],[FILE_NUM].[1137],[FILE_NUM].[1138],[FILE_NUM].[1337],[FILE_NUM].[1340],[FILE_NUM].[1410],[FILE_NUM].[1438],[FILE_NUM].[1503],[FILE_NUM].[1628],[FILE_NUM].[1698],[FILE_NUM].[1801],[FILE_NUM].[1808],[FILE_NUM].[1843],[FILE_NUM].[1876],[FILE_NUM].[1885],[FILE_NUM].[2017],[FILE_NUM].[2051],[FILE_NUM].[2053],[FILE_NUM].[2134],[FILE_NUM].[2929],[FILE_NUM].[2930],[FILE_NUM].[2931],[FILE_NUM].[2946],[FILE_NUM].[2979],[FILE_NUM].[3039],[FILE_NUM].[3080],[FILE_NUM].[3087],[FILE_NUM].[3124],[FILE_NUM].[3125],[FILE_NUM].[3165],[FILE_NUM].[3166],[FILE_NUM].[3237],[FILE_NUM].[3421],[FILE_NUM].[3445],[FILE_NUM].[3485],[FILE_NUM].[3493],[FILE_NUM].[3501],[FILE_NUM].[3552],[FILE_NUM].[3557],[FILE_NUM].[3622],[FILE_NUM].[3795],[FILE_NUM].[3812],[FILE_NUM].[3824],[FILE_NUM].[3837],[FILE_NUM].[3858],[FILE_NUM].[3884],[FILE_NUM].[3952],[FILE_NUM].[3963],[FILE_NUM].[3984],[FILE_NUM].[3995],[FILE_NUM].[4021],[FILE_NUM].[4030],[FILE_NUM].[4097],[FILE_NUM].[4117],[FILE_NUM].[4142],[FILE_NUM].[4145],[FILE_NUM].[4153],[FILE_NUM].[4155],[FILE_NUM].[4159],[FILE_NUM].[4161],[FILE_NUM].[4190],[FILE_NUM].[4209],[FILE_NUM].[4216],[FILE_NUM].[4223],[FILE_NUM].[4251],[FILE_NUM].[4255],[FILE_NUM].[4303],[FILE_NUM].[4313],[FILE_NUM].[4315],[FILE_NUM].[4329],[FILE_NUM].[4343],[FILE_NUM].[4346],[FILE_NUM].[4356],[FILE_NUM].[4366],[FILE_NUM].[4372],[FILE_NUM].[4400],[FILE_NUM].[4401],[FILE_NUM].[4409],[FILE_NUM].[4422],[FILE_NUM].[4443],[FILE_NUM].[4484],[FILE_NUM].[4501],[FILE_NUM].[4539],[FILE_NUM].[4569],[FILE_NUM].[4630],[FILE_NUM].[4638],[FILE_NUM].[4639],[FILE_NUM].[4658],[FILE_NUM].[4686],[FILE_NUM].[4698],[FILE_NUM].[4699],[FILE_NUM].[4768],[FILE_NUM].[4775],[FILE_NUM].[4794],[FILE_NUM].[4799],[FILE_NUM].[4803],[FILE_NUM].[4805],[FILE_NUM].[4835],[FILE_NUM].[4891],[FILE_NUM].[4923],[FILE_NUM].[4925],[FILE_NUM].[4929],[FILE_NUM].[4950],[FILE_NUM].[4956],[FILE_NUM].[4961],[FILE_NUM].[4978],[FILE_NUM].[4987],[FILE_NUM].[4990],[FILE_NUM].[4992],[FILE_NUM].[4996],[FILE_NUM].[5020],[FILE_NUM].[5025],[FILE_NUM].[5026],[FILE_NUM].[5048],[FILE_NUM].[5057],[FILE_NUM].[5058],[FILE_NUM].[5067],[FILE_NUM].[5074],[FILE_NUM].[5075],[FILE_NUM].[5077],[FILE_NUM].[5079],[FILE_NUM].[5080],[FILE_NUM].[5090],[FILE_NUM].[5095],[FILE_NUM].[5096],[FILE_NUM].[5098],[FILE_NUM].[5103],[FILE_NUM].[5105],[FILE_NUM].[5139],[FILE_NUM].[5149],[FILE_NUM].[5154],[FILE_NUM].[5158],[FILE_NUM].[5165],[FILE_NUM].[5180],[FILE_NUM].[5198],[FILE_NUM].[5199],[FILE_NUM].[5207],[FILE_NUM].[5215],[FILE_NUM].[5219],[FILE_NUM].[5223],[FILE_NUM].[5236],[FILE_NUM].[5242],[FILE_NUM].[5275],[FILE_NUM].[5300],[FILE_NUM].[5304],[FILE_NUM].[5313],[FILE_NUM].[5321],[FILE_NUM].[5356],[FILE_NUM].[5368],[FILE_NUM].[5389],[FILE_NUM].[5401],[FILE_NUM].[5444],[FILE_NUM].[5457],[FILE_NUM].[5467],[FILE_NUM].[5468],[FILE_NUM].[5498],[FILE_NUM].[5519],[FILE_NUM].[5520],[FILE_NUM].[5531],[FILE_NUM].[5539],[FILE_NUM].[5542],[FILE_NUM].[5563],[FILE_NUM].[5578],[FILE_NUM].[5707],[FILE_NUM].[5723],[FILE_NUM].[5761],[FILE_NUM].[5785],[FILE_NUM].[5795],[FILE_NUM].[5809],[FILE_NUM].[5830],[FILE_NUM].[5860],[FILE_NUM].[5903],[FILE_NUM].[5923],[FILE_NUM].[5962],[FILE_NUM].[5966],[FILE_NUM].[5970],[FILE_NUM].[5996],[FILE_NUM].[6005],[FILE_NUM].[6006],[FILE_NUM].[6008],[FILE_NUM].[6012],[FILE_NUM].[6023],[FILE_NUM].[6032],[FILE_NUM].[6041],[FILE_NUM].[6043],[FILE_NUM].[6073],[FILE_NUM].[6100],[FILE_NUM].[6150],[FILE_NUM].[6201],[FILE_NUM].[6223],[FILE_NUM].[6271],[FILE_NUM].[6295],[FILE_NUM].[6314],[FILE_NUM].[6404],[FILE_NUM].[6440],[FILE_NUM].[6459],[FILE_NUM].[6482],[FILE_NUM].[6486],[FILE_NUM].[6502],[FILE_NUM].[6540],[FILE_NUM].[6588],[FILE_NUM].[6610],[FILE_NUM].[6637],[FILE_NUM].[6653],[FILE_NUM].[6664],[FILE_NUM].[6725],[FILE_NUM].[6819],[FILE_NUM].[6871],[FILE_NUM].[6932],[FILE_NUM].[6934],[FILE_NUM].[6978],[FILE_NUM].[7009],[FILE_NUM].[7042],[FILE_NUM].[7043],[FILE_NUM].[7055],[FILE_NUM].[7493],[FILE_NUM].[7547],[FILE_NUM].[7554],[FILE_NUM].[7612],[FILE_NUM].[7624],[FILE_NUM].[7638],[FILE_NUM].[7646],[FILE_NUM].[7671],[FILE_NUM].[7693],[FILE_NUM].[7695],[FILE_NUM].[7696],[FILE_NUM].[7697],[FILE_NUM].[7698],[FILE_NUM].[7708],[FILE_NUM].[7710],[FILE_NUM].[7711],[FILE_NUM].[7862],[FILE_NUM].[7910],[FILE_NUM].[7927],[FILE_NUM].[7960],[FILE_NUM].[7962],[FILE_NUM].[8009],[FILE_NUM].[8033],[FILE_NUM].[8056],[FILE_NUM].[8057],[FILE_NUM].[8104],[FILE_NUM].[8109],[FILE_NUM].[8170],[FILE_NUM].[8177],[FILE_NUM].[8181],[FILE_NUM].[8211],[FILE_NUM].[8323],[FILE_NUM].[8376],[FILE_NUM].[8412],[FILE_NUM].[8475],[FILE_NUM].[8541],[FILE_NUM].[8547],[FILE_NUM].[8578],[FILE_NUM].[8654],[FILE_NUM].[8691],[FILE_NUM].[8697],[FILE_NUM].[8699],[FILE_NUM].[8749],[FILE_NUM].[8763],[FILE_NUM].[8790],[FILE_NUM].[8840],[FILE_NUM].[8870],[FILE_NUM].[8939],[FILE_NUM].[9036],[FILE_NUM].[9077],[FILE_NUM].[9094],[FILE_NUM].[9107],[FILE_NUM].[9149],[FILE_NUM].[9150],[FILE_NUM].[9293],[FILE_NUM].[9429],[FILE_NUM].[9499],[FILE_NUM].[9550],[FILE_NUM].[9571],[FILE_NUM].[9579],[FILE_NUM].[9752],[FILE_NUM].[9910],[FILE_NUM].[9953],[FILE_NUM].[10270],[FILE_NUM].[10271],[FILE_NUM].[10272],[FILE_NUM].[10273],[FILE_NUM].[10274],[FILE_NUM].[10275],[FILE_NUM].[10309],[FILE_NUM].[10326],[FILE_NUM].[10403],[FILE_NUM].[10408],[FILE_NUM].[10471],[FILE_NUM].[10491],[FILE_NUM].[10496],[FILE_NUM].[10505],[FILE_NUM].[10551],[FILE_NUM].[10572],[FILE_NUM].[10601],[FILE_NUM].[10615],[FILE_NUM].[10679],[FILE_NUM].[10725],[FILE_NUM].[10778],[FILE_NUM].[10819],[FILE_NUM].[11002],[FILE_NUM].[11042],[FILE_NUM].[11055],[FILE_NUM].[11076],[FILE_NUM].[11095],[FILE_NUM].[11112],[FILE_NUM].[11213],[FILE_NUM].[11249],[FILE_NUM].[11308],[FILE_NUM].[11311],[FILE_NUM].[11486],[FILE_NUM].[11555],[FILE_NUM].[11606],[FILE_NUM].[11661],[FILE_NUM].[11666],[FILE_NUM].[11669],[FILE_NUM].[11701],[FILE_NUM].[11808],[FILE_NUM].[11829],[FILE_NUM].[11834],[FILE_NUM].[11849],[FILE_NUM].[11868],[FILE_NUM].[12009],[FILE_NUM].[12116],[FILE_NUM].[12127],[FILE_NUM].[12190],[FILE_NUM].[12191],[FILE_NUM].[12219],[FILE_NUM].[12220],[FILE_NUM].[12241],[FILE_NUM].[12264],[FILE_NUM].[12266],[FILE_NUM].[12277],[FILE_NUM].[12294],[FILE_NUM].[12303],[FILE_NUM].[12333],[FILE_NUM].[12365],[FILE_NUM].[12393],[FILE_NUM].[12412],[FILE_NUM].[12421],[FILE_NUM].[12429],[FILE_NUM].[12436],[FILE_NUM].[12451],[FILE_NUM].[12458],[FILE_NUM].[12462],[FILE_NUM].[12474],[FILE_NUM].[12475],[FILE_NUM].[12489],[FILE_NUM].[12490],[FILE_NUM].[12491],[FILE_NUM].[12512],[FILE_NUM].[12523],[FILE_NUM].[12529],[FILE_NUM].[12530],[FILE_NUM].[12544],[FILE_NUM].[12580],[FILE_NUM].[12581],[FILE_NUM].[12585],[FILE_NUM].[12606],[FILE_NUM].[12610],[FILE_NUM].[12688],[FILE_NUM].[12690],[FILE_NUM].[12694],[FILE_NUM].[12702],[FILE_NUM].[12703],[FILE_NUM].[12710],[FILE_NUM].[12711],[FILE_NUM].[12721],[FILE_NUM].[12722],[FILE_NUM].[12729],[FILE_NUM].[12735],[FILE_NUM].[12742],[FILE_NUM].[12797],[FILE_NUM].[12860],[FILE_NUM].[12877],[FILE_NUM].[12878],[FILE_NUM].[12911],[FILE_NUM].[12966],[FILE_NUM].[13063],[FILE_NUM].[13115],[FILE_NUM].[13153],[FILE_NUM].[13168],[FILE_NUM].[13213],[FILE_NUM].[13236],[FILE_NUM].[13246],[FILE_NUM].[13247],[FILE_NUM].[13250],[FILE_NUM].[13268],[FILE_NUM].[13291],[FILE_NUM].[13293],[FILE_NUM].[13294],[FILE_NUM].[13303],[FILE_NUM].[13320],[FILE_NUM].[13328],[FILE_NUM].[13340],[FILE_NUM].[13341],[FILE_NUM].[13346],[FILE_NUM].[13349],[FILE_NUM].[13356],[FILE_NUM].[13370],[FILE_NUM].[13371],[FILE_NUM].[13381],[FILE_NUM].[13437],[FILE_NUM].[13445],[FILE_NUM].[13449],[FILE_NUM].[13456],[FILE_NUM].[13490],[FILE_NUM].[13491],[FILE_NUM].[13492],[FILE_NUM].[13495],[FILE_NUM].[13523],[FILE_NUM].[13528],[FILE_NUM].[13544],[FILE_NUM].[13580],[FILE_NUM].[13610],[FILE_NUM].[13627],[FILE_NUM].[13691],[FILE_NUM].[13701],[FILE_NUM].[13702],[FILE_NUM].[13803],[FILE_NUM].[13844],[FILE_NUM].[13936]}, [DATE_VALUE.DATE_VALUE_HIR].[2016-01-01 00:00:00.0])

    Cube: Please find the attachment

    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2016
    Posts
    1

    Default

    Hello All, I am also facing the same problem with mdx query performance.Following is my mdx query.


    WITH SET [KRI] AS
    'Order(Union({[Metric].[All Metrics]}, [Metric].[Metric Name].Members), [Measures].[Breached Employee], DESC)'
    MEMBER [Measures].[S_POPULATION] AS
    '([Measures].[Population] / Count([Metric].[ID].Members))'
    MEMBER [Measures].[COUNTRY_PERCENTAGE] AS
    '((100.0 * [Measures].[Breached Employee]) / [Measures].[S_POPULATION])',
    format_String = "##"
    MEMBER [Measures].[METRIC_PERCENTAGE] AS
    '[Measures].[Percentage]',
    format_String = "##.##%"
    SELECT NON EMPTY
    { [Measures].[Breached Employee], [Measures].[COUNTRY_PERCENTAGE] } ON COLUMNS,
    NON EMPTY Crossjoin ( [Country].[Country Name].Members, [KRI] ) ON ROWS
    FROM [METRIC_BREACHED_EMPLOYEE_V2]
    WHERE NonEmptyCrossjoin (
    {
    NonEmptyCrossjoin (
    {
    { [Region].[Americas].[3], [Region].[Asia].[1], [Region].[Europe].[2], [Region].[Pacific].[5] }
    },
    {
    { [BusinessLine].[GM Equity].[101], [BusinessLine].[Institutional Client Group].[102], [BusinessLine].[Research].[105], [BusinessLine].[Global Liquidity Management].[121], [BusinessLine].[EM Debt].[123], [BusinessLine].[RMBS].[124], [BusinessLine].[German Large Corps].[126], [BusinessLine].[CPM].[128], [BusinessLine].[Global Foreign Exchange].[133], [BusinessLine].[Corporate Finance Central].[135], [BusinessLine].
    [Listed Derivatives & Clearing].[136], [BusinessLine].[ECM].[137], [BusinessLine].[GC Legacy].[143], [BusinessLine].[RMBS Legacy].[144], [BusinessLine].[CI - Non-core].[147], [BusinessLine].[Structuring].[175], [BusinessLine].[EPT Legacy].[177], [BusinessLine].[Special Commodities Group].[186], [BusinessLine].[NCOU Central].[210], [BusinessLine].[LDCM].[226], [BusinessLine].[GM Central].[326], [BusinessLine].[Core Rates].[327], [BusinessLine].[Debt Strats].[328], [BusinessLine].[FIC APAC].[329], [BusinessLine].[Global Credit Trading].[330], [BusinessLine].[GM Debt Management].[331], [BusinessLine].[GM Non-Strategic].[332], [BusinessLine].[CPSG Lending].[333], [BusinessLine].[DCM Debt Syndicate].[334], [BusinessLine].[Financing & Solutions Group].[335], [BusinessLine].[IBC & CBC].[336], [BusinessLine].[M&A and other Advisory].[337], [BusinessLine].[Treasury].[338], [BusinessLine].[GTB Central].[339], [BusinessLine].[GTB Management Projects].[340], [BusinessLine].[GTB Instituional Cash].[341], [BusinessLine].[GTB Securities Services].[342], [BusinessLine].[GTB Trade Finance & CM Corporates].[343], [BusinessLine].[CIB Central].[344], [BusinessLine].[AM Central Functions].[345], [BusinessLine].[AM Abbey Life].[346], [BusinessLine].[AM CTA].[347], [BusinessLine].[CIB Non-core Wholesale Assets].[348], [BusinessLine].[CIB Non-core Operating Assets].[349], [BusinessLine].[AM Alternative Fund Solutions].[350], [BusinessLine].[AM Passive].[351], [BusinessLine].[AM Solutions & Trading Group].[352], [BusinessLine].[AM Active].[353], [BusinessLine].[AM Alternatives Real Assets].[354], [BusinessLine].[AM Global Client Group].[355], [BusinessLine].[WM Central].[366], [BusinessLine].[WM Clients].[367], [BusinessLine].[WM Global Coverage].[386] }
    }

    )
    },
    {

    NonEmptyCrossjoin (
    NonEmptyCrossjoin (
    { [Report].[OaFI-Summary v1].[106550] },
    { [DBC].[All Types] }
    ),
    {
    ( [Time].[2016].[201601] : [Time].[2016].[201606] )
    }
    )
    }

    )
    This query is taking almost 5 to 6 min,because of that connection time out is happening.Could anyone please give me the solution to improve the performance of this query.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.