select data from olap MDX query

Question

Please help to understand how MDX query works. I have connected to cube using excel and construct a mdx query. In short finally I need to get the table like this:

                        12.01.2015
        +-------+-------+-------+-----+-------+------               
        | 00:00 | 01:00 | 02:00 | ... | 23:00 | TOTAL   
--------+-------+-------+-------+-----+-------+------
Ivan    | null  |   3   |  null | ... |   12  |  38
Pert    |   3   |   8   |  null | ... |  null | 125
Sidor   |

We see the Date, Time (hour), FIO (Ivan, Petr etc) and values. The cube has dimensions: Dim Date, Dim Hour, Dim Users I trying to get MDX query from excel but I cant understand how to modify it to get result I need. Here is the request (formatted):

SELECT NON EMPTY 
CrossJoin(
    Hierarchize(
        DrilldownMember(
        {
            {
                DrilldownMember(
                    {
                        {
                            DrilldownLevel
                                (
                                    {[Dim Date].[Даты YMD].[All]}
                                )
                        }
                    }, 
                    {[Dim Date].[Даты YMD].[Year Name].&[2015]}
                )
            }
        }, 
        {[Dim Date].[Даты YMD].[Year Name].&[2015].&[5]}
    )
), 
Hierarchize(
    {
        DrilldownLevel(
            {[Dim Hour].[Hour Key].[All]}
        )
    }
)) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,
    [Dim Date].[Даты YMD].[Date Key].[Month Name],[Dim Date].[Даты YMD].[Date Key].[Year Name] ON COLUMNS , 
    NON EMPTY 
    Hierarchize(
        {
            DrilldownLevel(
                {[Dim Users].[FIO].[All]}
            )
        }
    ) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM 
(
    SELECT ({[Dim Date].[Даты YMD].[Year Name].&[2015].&[5]}
) 
    ON COLUMNS 
    FROM [Dwh Sorting]) 
    WHERE ([Measures].[Fact Table Count]) CELL PROPERTIES VALUE, 
    FORMAT_STRING, 
    LANGUAGE, 
    BACK_COLOR, 
    FORE_COLOR, 
    FONT_FLAGS

It gets data for May 2015. My goal is to get a long table (with a lot of columns) like I showed. With one or more months.

He isn't getting any error. He wants data in a different format. — SouravA
@user2598575 - Say you have three months. How does the data need to look like then? — SouravA
@Sourav_Agasti do you know anything about cube design? I don't. Just some basic knowledge could answer this one: stackoverflow.com/questions/30324119/… — whytheq

whytheq whytheq · Accepted Answer · 2015-05-19T16:52:55

Excel code that is autogenerated adds a lot of extra "bits".

Getting rid of the extra bits I'm guessing at something like the below. This script should give you lots of columns - assuming data for every hour in 2015 you should end up with 24 columns for every day in 2015!

The reason I say "guessing" as we do not know the hiearchical structure of your cube:

SELECT 
  NON EMPTY 
      Descendants
      (
        [Dim Date].[Даты YMD].[Year Name].&[2015]
       ,[Dim Date].[Даты YMD].[Date] //<<this assumes there is a level in Dim date called Date
      )
    * 
      {[Dim Hour].[Hour Key].MEMBERS} ON COLUMNS
 ,NON EMPTY 
    [Dim Users].[FIO].MEMBERS ON ROWS
FROM [Dwh Sorting]
WHERE 
  [Measures].[Fact Table Count];

If you need just the hours for a range of dates then try the : operator. You will not need to apply the Descendants function in this case:

SELECT 
  NON EMPTY 
      (
        [Dim Date].[Даты YMD].[Date Key].[2014-01-02]
      : 
        [Dim Date].[Даты YMD].[Date Key].[2015-02-10]
      )
    * 
      {[Dim Hour].[Hour Key].MEMBERS} ON COLUMNS
 ,NON EMPTY 
    [Dim Users].[FIO].MEMBERS ON ROWS
FROM [Dwh Sorting]
WHERE 
  [Measures].[Fact Table Count];

select data from olap MDX query

1 Answers