Search code examples
ssasmdxolapolap-cubetabular

Convert 3D cube to Tables(Rows x Columns)

I was just wondering is there possible ways to display the data from 3d cube in a tabular format withoug slicing through MDX Query-

Suppose, I have a cube with 3 dimensions - Class(A,B,C,D..), Vendor(V1,V2,V3), Period(2010,2011) and the measure would be SaleValue..

Now, I would like to view the records like this..

**Class| Vendor|Period|SaleValue**

A | V1  | 2010  |  987
A | V2  | 2011  |  654
A | V3  | 2010  |  214
A | V1  | 2011  |  5643
A | V2  | 2010  |  698
A | V3  | 2011  |  212

B | V1  | 2010  |  224
B | V2  | 2011  |  668
B | V3  | 2010  |  741
B | V1  | 2011  |  3216
B | V2  | 2010  |  953
B | V3  | 2011  |  2114

C | V1  | 2010  |  159
C | V2  | 2011  |  852
C | V3  | 2010  |  369
C | V1  | 2011  |  147
C | V2  | 2010  |  123
C | V3  | 2011  |  654

Is this possible to implement using MDX queries ? If so, please tel me any of sample queries..

Thanks in advance..

Solution

  • You can write an Mdx query that will give something really close to a tabular format. For instance:

    SELECT {[Measures].[SaleValue]} ON COLUMNS,
NON EMPTY [Class].Members * [Vendor].Members * [Period].Members ON ROWS
FROM cube

    The result will look like this:

                   |  SaleValue
(A, V1, 2010)  |  987
(A, V2, 2011)  |  654
(A, V3, 2010)  |  214
...

    If you do not want to have sub totals, you can replace [Class].Members with lowest_level_of_Class.Members or use the leaves function.