[ mssql ] query OLAP cube from MSSQL Management Studio

1. connect > analysis service

Image

2.

– browse cube

– slide and dice

Image

NOTE that multidimensional expression (MDX) can be used to query

For example,

test_mdx

SELECT
{ [Measures].[Extended Amount] }
ON COLUMNS,
{ [Due Date].[Fiscal Quarter - Month].[Fiscal Quarter].&[1].&[7].&[20050713],
[Due Date].[Fiscal Quarter - Month].[Fiscal Quarter].&[1].&[7].&[20050714],
[Due Date].[Fiscal Quarter - Month].[Fiscal Quarter].&[1].&[7].&[20050715] }
ON ROWS
FROM "InternetSales"

[ mssql ] first OLAP cube

create cube

1. Choose FaceTable, and bring the related table using FK

Image

2. Get suggested measure group, Using Existing Table, Image

3. Create Measure, left the measure all selectedImage

4. select all dimensions

Image

NOTE : Physical table fields shown in picture below

Image

NOTICE :

– From Measure, there is `Count` Measures added

– From Dimensions,

Image

5. Create Date Hierarchy, DimDate always have a hierarchyImage

6. Build > Deploy cube

Browse cube

7. Browse a cube, add measure

Image

8. Slice and dice

Image

ref : developerfusion