none
Management Studio browse option

    Question

  • Hi all,

     

    I'm VERY new to SQL Server management Studio so this is a basic question.  I have tried searching the forum but all the 'browse' questions are related to browsing/finding a database to connect to in the application.

     

    I'm in the application and connected to  my database.  Then under Databases > dbname > Cubes, I right-clicked on my cube name and select Browse from the list.  This lets me drag in the prompts I want to use in my query and the fields I want to display on my report into the display panel.  I am able to get the correct (EXACT) figures that I need.

     

    Now that I know I can get the data from the cube, I then try to actually create the query and enter the same prompts.  BUT the data that is pulled from the cube is different!!  I need to look at the code that the 'browse' option used to see how they coded it to get the data but I can't find a way to look at it.

     

    Am I missing something?  Was a setting set incorrectly when Management Studio was installed?  How do I SEE the MDX code behind the 'browse' option. 

     

    PLEASE, this is driving me crazy.  Why would they show you that it can pull the correct data and then not show you how it is done?  There MUST be a way to see the code…

     

    Thanks in advance,
    Friday, April 08, 2011 12:54 PM

Answers

  • Hi Tina,

    of course not, sorry my mistake during copy paste.

     

    SELECT
    {
         [Measures].[Carryover @9-30],
         [Measures].[Budget Authority],
         [Measures].[Task Budget],
         [Measures].[Obligations],
         [Measures].[ITD Work in Progress],
         [Measures].[ITD Budget Authority],
         [Measures].[ITD Budget],
         [Measures].[ITD Unallocated],
         [Measures].[ITD Commitments],
         [Measures].[ITD Uncommitted]
    } ON COLUMNS,
    NON EMPTY 
    {
       { [IAA].[DEPT Code].[All] },
       AddCalculatedMembers([IAA].[DEPT Code].[DEPT Code].MEMBERS)
    }
    DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME 
    ON ROWS
    FROM [XYZ FDM]
    CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
    

    Regards,

    Hrvoje

     

    • Marked as answer by UsingSP Friday, April 08, 2011 6:50 PM
    Friday, April 08, 2011 2:44 PM

All replies

  • Hello,

    AFAIK you can get the MDX statement inside the browser itself, but you can use the "SQL Profiler" to fetch all MDX statement the browser is sending to the cube. You will see, that are a lot's of statement you one calculation; creating sub cubes and so on.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Friday, April 08, 2011 1:09 PM
  • Olaf,

    Thanks for the quick reply.  As I said, I am new to Management Studio... where do I find the "SQL Profiler" and how would I create sub-cubes in my MDX code?

    I appreciate your assistance in this,

     

    Friday, April 08, 2011 1:14 PM
  • You can start the profiler directly from SSMS: Menu "Tools" => "SQL Server Profiler".
    Or from start menu => Microsoft SQL Server <version> => "Performance tools" => SQL Server Profiler.

    See MDX CREATE SUBCUBE statement in MSDN, when you fetch MDX statements with the profiler, you will see them often.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Friday, April 08, 2011 1:25 PM
  • Hi,

    MDX captured using SQL Profiler will only show you that it is of no use and that is not really the way to go. Excel and the browser component in SSMS have a special way of generating mdx queries using session scoped sets and this will be of no help especially if you are not familiar with mdx (I suppose if you were then you could write the query yourself).

    On the other hand SSMS cube browser was not designed as a reporting application so there are very limited amount of features normally supported in a true olap client.

    Why do you need the mdx after all? If the idea is to learn mdx then there are better ways as there are olap clients that generate much simpler and human readable mdx. If on the other hand you are going to use this for some reporting application then most of those have a visual query designer.

    Try downloading CubePlayer (www.softpro.hr) as it will allow you to see the generated mdx and supports most ssas features.

    Regards,

    Hrvoje Piasevoli

    Friday, April 08, 2011 1:41 PM
  • Olaf,

    FANTASTIC!  I found out what code was created.  It was:

    SELECT
     NON EMPTY [{C92A0C7C-9D0C-48CD-825E-07F013543E47}Pivot10Axis1Set0]
     DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
     {
      [Measures].[Carryover @9-30],
      [Measures].[Budget Authority],
      [Measures].[Task Budget],
      [Measures].[Obligations],
      [Measures].[ITD Work in Progress],
      [Measures].[ITD Budget Authority],
      [Measures].[ITD Budget],
      [Measures].[ITD Unallocated],
      [Measures].[ITD Commitments],
      [Measures].[ITD Uncommitted]
     }
     ON ROWS
     FROM [Volpe FDM]
    CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

    BUT, when I copy that code into Visual Studio to try to get the same results, I get an error:

    Query preparation failed.
    Additional Information:
    Query (2,12) The dimension '{C92A0C7C-9D0C-48CD-825E-07F013543E47}Pivot10Axis1Set0]' was
    not found inthe cube when the string,
    [{C92A0C7C-9D0C-48CD-825E-07F013543E47}Pivot10Axis1Set0], was parsed (Microsoft SQL
    Server 2008 Analysis Services)

    Again, I'm a newbie, what does that mean and how do I fix it so it will run in Visual Studio?

    Tina

     

    Friday, April 08, 2011 1:42 PM
  • Hi,

    This was my point exactly and while one could reconstruct the query from captured statements it is not that simple.

    Regards,

    Hrvoje Piasevoli

    Friday, April 08, 2011 1:45 PM
  • Hrvoje Piasevoli,

    I take exception to your condescending statement:

    …this will be of no help especially if you are not familiar with mdx (I suppose if you were then you could write the query yourself).

    I am familiar with MDX and have written many queries.  But I have not been able to write ONE query against the cube that will pull Inception-to-Date information AND Current-Year information AND Current-Month information – ALL AT THE SAME TIME. I need it all in ONE query to place the information in a table in a report I am building.

     

    At least Olaf gave me an answer to my question of "Where do I look for the code?"

     

    Friday, April 08, 2011 1:54 PM
  • Hello Tina,

    Hrvoje is right, tools like create various statements and sometimes it' confusing. But as I think for the first step it's good to see how such tools performs the queries.

    In the profiler trace have a look at the statements before, there should be someting like "CREATE SESSION SET". You could eigher execute the MDX statement one by one (batch isn't possible!) or you combine them. Example, first the Create Session Set + Select :

    CREATE SESSION

         SET [Adventure Works].[{90EDF9B2-3017-49D7-90FA-C1FEC54909A2}Pivot4Axis1Set0] AS

         '

              {

                    { [Customer].[Customer].[All Customers] },

                    AddCalculatedMembers([Customer].[Customer].[Customer].MEMBERS)

              }

         '

     

    SELECT

         NON EMPTY [{90EDF9B2-3017-49D7-90FA-C1FEC54909A2}Pivot4Axis1Set0]

         DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,

         {

              [Measures].[Customer Count]

         }

         ON ROWS

         FROM [Adventure Works]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

    To combine them you have to create a named set with the same alias without the cube name =>

    WITH SET [{90EDF9B2-3017-49D7-90FA-C1FEC54909A2}Pivot4Axis1Set0] AS

         '

              {

                    { [Customer].[Customer].[All Customers] },

                    AddCalculatedMembers([Customer].[Customer].[Customer].MEMBERS)

              }

         '

    SELECT

         NON EMPTY [{90EDF9B2-3017-49D7-90FA-C1FEC54909A2}Pivot4Axis1Set0]

         DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,

         {

              [Measures].[Customer Count]

         }

         ON ROWS

         FROM [Adventure Works]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

     

     

     


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Friday, April 08, 2011 1:58 PM
  • Hey, my apologies if it sounded offending - didn't meant it like that at all but I accept it was badly worded .

    Can you describe the structure or perhaps provide a snapshot of your screen with comments regarding dimensions, hierarchies, and attribute structures. Don't forget to mention what you have put in filter area and all the conditions you might have added.

    The other option is to capture the full trace of mdx statements.

    Regards,

    Hrvoje Piasevoli

    Friday, April 08, 2011 2:08 PM
  • Olaf, Hrvoje,

    I attempted to build a query and here is the code:

     

    CREATE SESSION

          SET [XYZ FDM].[{C92A0C7C-9D0C-48CD-825E-07F013543E47}Pivot10Axis1Set0] AS

          '

                      {

                                  { [IAA].[DEPT Code].[All] },

                                  AddCalculatedMembers([IAA].[DEPT Code].[DEPT Code].MEMBERS)

                      }

          '

    SELECT

          NON EMPTY [{C92A0C7C-9D0C-48CD-825E-07F013543E47}Pivot10Axis1Set0]

          DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,

          {

                      [Measures].[Carryover @9-30],

                      [Measures].[Budget Authority],

                      [Measures].[Task Budget],

                      [Measures].[Obligations],

                      [Measures].[ITD Work in Progress],

                      [Measures].[ITD Budget Authority],

                      [Measures].[ITD Budget],

                      [Measures].[ITD Unallocated],

                      [Measures].[ITD Commitments],

                      [Measures].[ITD Uncommitted]

          }

          ON ROWS

          FROM [XYZ FDM]

    CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

     

    The current-month information I am trying to retrieve is Carryover @9-30

    The current-year information is Budget Authority, Task Budget and Obligations

    The remaining fields are Inception-to-Date.

     

    When I attempt to run this query I get the error:

    Query (9, 1) Parser: The syntax for 'SELECT' is incorrect. (msmgdsrv)

     

    Again, thanks to both of you for your assistance.  I now have something to work on and try to understand.

    Tina

    Friday, April 08, 2011 2:28 PM
  • Hi Tina,

    Does this get you anywhere? Any statements before that like create subcube?

    SELECT
    {
         [Measures].[Carryover @9-30],
         [Measures].[Budget Authority],
         [Measures].[Task Budget],
         [Measures].[Obligations],
         [Measures].[ITD Work in Progress],
         [Measures].[ITD Budget Authority],
         [Measures].[ITD Budget],
         [Measures].[ITD Unallocated],
         [Measures].[ITD Commitments],
         [Measures].[ITD Uncommitted]
    } ON COLUMNS,
    NON EMPTY 
    {
       { [IAA].[DEPT Code].[All] },
       AddCalculatedMembers([IAA].[DEPT Code].[DEPT Code].MEMBERS)
    }
    DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
    ON ROWS
    FROM [XYZ FDM]
    CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
    
    Hrvoje

    Friday, April 08, 2011 2:33 PM
  • Hrvoje,

    I copied your code into Visual Studio and tried to execute it.  The error now reads:

    Query (20,1 ) Parser: The syntax for 'ON' is incorrect (msmgdsrv)

    Can you have 'ON COLUMNS,' followed directly by 'ON ROWS' ?  I've never tried that before.

    Tina

    Friday, April 08, 2011 2:39 PM
  • Hi Tina,

    of course not, sorry my mistake during copy paste.

     

    SELECT
    {
         [Measures].[Carryover @9-30],
         [Measures].[Budget Authority],
         [Measures].[Task Budget],
         [Measures].[Obligations],
         [Measures].[ITD Work in Progress],
         [Measures].[ITD Budget Authority],
         [Measures].[ITD Budget],
         [Measures].[ITD Unallocated],
         [Measures].[ITD Commitments],
         [Measures].[ITD Uncommitted]
    } ON COLUMNS,
    NON EMPTY 
    {
       { [IAA].[DEPT Code].[All] },
       AddCalculatedMembers([IAA].[DEPT Code].[DEPT Code].MEMBERS)
    }
    DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME 
    ON ROWS
    FROM [XYZ FDM]
    CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
    

    Regards,

    Hrvoje

     

    • Marked as answer by UsingSP Friday, April 08, 2011 6:50 PM
    Friday, April 08, 2011 2:44 PM