MDX Query Optimization












1















I have a pivot table generated in Excel. I need to use the same MDX query which is used in Excel, I have extracted the same query from Excel which is like this -



SELECT {[Measures].[OQ],[Measures].[RQ],[Measures].[SQ],[Measures].[SRQ]} 

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , 

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

Hierarchize({DrilldownLevel({[PO].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}),

Hierarchize({DrilldownLevel({[PO].[Priority].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Container].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[PO].[Name].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Num].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[PO].[Warehouse].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Status].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Factor].[All]},,,INCLUDE_CALC_MEMBERS)}))

ON ROWS  

FROM [Purchases] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


This query returns about 3 lac records in excel, when I run this query in SSMS it gives error like "Server: The operation has been cancelled due to memory pressure.".



I am new to MDX, above query uses many CrossJoins, is there any way to optimize this query?



Thanks and Regards,



Amit Thakkar






mdx







share|improve this question























  • Could you please add pivot table output to your question?

    – George
    Feb 2 '15 at 18:58
















1















I have a pivot table generated in Excel. I need to use the same MDX query which is used in Excel, I have extracted the same query from Excel which is like this -



SELECT {[Measures].[OQ],[Measures].[RQ],[Measures].[SQ],[Measures].[SRQ]} 

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , 

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

Hierarchize({DrilldownLevel({[PO].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}),

Hierarchize({DrilldownLevel({[PO].[Priority].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Container].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[PO].[Name].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Num].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[PO].[Warehouse].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Status].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Factor].[All]},,,INCLUDE_CALC_MEMBERS)}))

ON ROWS  

FROM [Purchases] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


This query returns about 3 lac records in excel, when I run this query in SSMS it gives error like "Server: The operation has been cancelled due to memory pressure.".



I am new to MDX, above query uses many CrossJoins, is there any way to optimize this query?



Thanks and Regards,



Amit Thakkar






mdx







share|improve this question























  • Could you please add pivot table output to your question?

    – George
    Feb 2 '15 at 18:58














1












1








1








I have a pivot table generated in Excel. I need to use the same MDX query which is used in Excel, I have extracted the same query from Excel which is like this -



SELECT {[Measures].[OQ],[Measures].[RQ],[Measures].[SQ],[Measures].[SRQ]} 

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , 

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

Hierarchize({DrilldownLevel({[PO].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}),

Hierarchize({DrilldownLevel({[PO].[Priority].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Container].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[PO].[Name].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Num].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[PO].[Warehouse].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Status].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Factor].[All]},,,INCLUDE_CALC_MEMBERS)}))

ON ROWS  

FROM [Purchases] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


This query returns about 3 lac records in excel, when I run this query in SSMS it gives error like "Server: The operation has been cancelled due to memory pressure.".



I am new to MDX, above query uses many CrossJoins, is there any way to optimize this query?



Thanks and Regards,



Amit Thakkar






mdx







share|improve this question














I have a pivot table generated in Excel. I need to use the same MDX query which is used in Excel, I have extracted the same query from Excel which is like this -



SELECT {[Measures].[OQ],[Measures].[RQ],[Measures].[SQ],[Measures].[SRQ]} 

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , 

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

CrossJoin(

Hierarchize({DrilldownLevel({[PO].[Date].[All]},,,INCLUDE_CALC_MEMBERS)}),

Hierarchize({DrilldownLevel({[PO].[Priority].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Container].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[PO].[Name].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Num].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[PO].[Warehouse].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Status].[All]},,,INCLUDE_CALC_MEMBERS)})),

Hierarchize({DrilldownLevel({[POL].[Factor].[All]},,,INCLUDE_CALC_MEMBERS)}))

ON ROWS  

FROM [Purchases] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


This query returns about 3 lac records in excel, when I run this query in SSMS it gives error like "Server: The operation has been cancelled due to memory pressure.".



I am new to MDX, above query uses many CrossJoins, is there any way to optimize this query?



Thanks and Regards,



Amit Thakkar






mdx




mdx






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jun 12 '13 at 9:44









Amit ThakkarAmit Thakkar

33626




33626













  • Could you please add pivot table output to your question?

    – George
    Feb 2 '15 at 18:58



















  • Could you please add pivot table output to your question?

    – George
    Feb 2 '15 at 18:58

















Could you please add pivot table output to your question?

– George
Feb 2 '15 at 18:58





Could you please add pivot table output to your question?

– George
Feb 2 '15 at 18:58












1 Answer
1






active

oldest

votes


















0














I think its result maybe unusable almost for statistic reason
because of returning many rows.
but for optimization I suggest using
NONEMPTY
front of your crossjoin






share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f17062349%2fmdx-query-optimization%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    I think its result maybe unusable almost for statistic reason
    because of returning many rows.
    but for optimization I suggest using
    NONEMPTY
    front of your crossjoin






    share|improve this answer




























      0














      I think its result maybe unusable almost for statistic reason
      because of returning many rows.
      but for optimization I suggest using
      NONEMPTY
      front of your crossjoin






      share|improve this answer


























        0












        0








        0







        I think its result maybe unusable almost for statistic reason
        because of returning many rows.
        but for optimization I suggest using
        NONEMPTY
        front of your crossjoin






        share|improve this answer













        I think its result maybe unusable almost for statistic reason
        because of returning many rows.
        but for optimization I suggest using
        NONEMPTY
        front of your crossjoin







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 24 '18 at 15:56









        adramazanyadramazany

        194112




        194112
































            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f17062349%2fmdx-query-optimization%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            Costa Masnaga

            Image
            .mw-parser-output .avviso .mbox-text-div>div,.mw-parser-output .avviso .mbox-text-full-div>div{font-size:90%}.mw-parser-output .avviso .mbox-image div{width:52px}.mw-parser-output .avviso .mbox-text-full-div .hide-when-compact{display:block} Questa voce o sezione sull'argomento Lombardia non cita le fonti necessarie o quelle presenti sono insufficienti . Puoi migliorare questa voce aggiungendo citazioni da fonti attendibili secondo le linee guida sull'uso delle fonti. Segui i suggerimenti del progetto di riferimento. Costa Masnaga comune Localizzazione Stato   Italia Regione Lombardia Provincia Lecco Amministrazione Sindaco Sabina Panzeri (Al centro il cittadino) dall'08/06/2009 Territorio Coordinate 45°46′N 9°17′E  /  45.766667°N 9.283333°E 45.766667; 9.283333  ( Costa Masnaga ) Coordinate: 45°46′N 9°17′E  /  45.766667°N 9.283333°E 45.766667; 9.283333  ( Costa Masnaga ) Altitudine 318 m s.l.m...
            Read more

            Fotorealismo

            Image
            Guerrino Boatto, Venice's St. Barnaba Church and canal Il fotorealismo è un genere di pittura basato sull'uso di una o più fotografie dalle quali l'artista prende le informazioni necessarie da utilizzare poi nel processo della creazione dell'opera, in genere ad olio o ad acrilico, al fine di avere un aspetto finale il più simile possibile alla fotografia. Indice 1 Storia 2 Lista dei fotorealisti 3 Note 4 Voci correlate 5 Altri progetti Storia | Il fotorealismo è un movimento che nasce negli Stati Uniti d'America alla fine degli anni sessanta [1] sulla scia della Pop Art [2] [3] [4] , e insieme condividono il carattere reazionario verso la travolgente ascesa dei media, che alla metà del ventesimo secolo si trasforma in un fenomeno di massa talmente imponente da minacciare il valore dell'immagine nell'arte. [5] [6] Ne consegue così che, colto il gesto d'inizio che fu della Pop Art, i fotorealisti aprono ad un ...
            Read more

            Sidney Franklin

            Image
            Questa voce sull'argomento registi statunitensi è solo un abbozzo . Contribuisci a migliorarla secondo le convenzioni di Wikipedia. Sidney Franklin, foto di Carl Van Vechten Oscar alla memoria Irving G. Thalberg 1943 Sidney Franklin , all'anagrafe Sidney Arnold Franklin , (San Francisco, 21 marzo 1893 – Santa Monica, 18 maggio 1972), è stato un regista, produttore cinematografico e attore statunitense. Il 20 giugno 1963, si sposò con l'attrice australiana Enid Bennett. Il matrimonio durò fino alla morte dell'attrice, il 14 maggio 1969 [1] Indice 1 Filmografia 1.1 Regista 1.2 Aiuto regia (parziale) 1.3 Produttore 1.4 Attore (parziale) 2 Note 3 Altri progetti 4 Collegamenti esterni Filmografia | Regista | The Baby , co-regia Chester M. Franklin (1915) The Rivals , co-regia Chester M. Franklin (1915) Little Dick's First Case , co-regia Chester M. Franklin (1915) Her Filmland Hero , co-...
            Read more