Common query statements for Mondrian

Common query statements for Mondrian

Data Warehouse: 192.168.20.230/olap

Logical Model: Ntsdw.xml

The logical model is built on the Data Warehouse, and all the MDX statement against the logical model (Ntsdw.xml) queries, all operations are performed on the cube, dimension, hierarchy, level, and member in the logical model. So understanding this logical model is the first and most important step.

all operations are for members (members) the query

1. Query the cube for total energy consumption

Select {[Measures]. Members} on COLUMNS from [NTSDW]

or select{[measures].[ Nts_sample]} on 0 from [NTSDW]

execution process: to [NTSDW] of the Cube is found in the name [Nts_sample] of the measures ( It's Jiang is a member of the final query ) , and because of this measure ( members ) The Aggregation method aggregator= "Sum" so the value of the query is the overlay of all the member data on an axis.

2. query The energy consumption value of the year ( The Members here are the corresponding measures in the Metrics table [nts_sample])

Select {[Measures]. Members} on COLUMNS from [Ntsdw] where [time]. [2015]

3. Check The energy consumption value of Jiangsu area for year

Select {[Measures]. Members} on 0,{[region].[ Jiangsu ]} on 1 from [Ntsdw]where [Time]. []

4. search for the total energy consumption of lighting in Jiangsu area

Select {[measures].[ Nts_sample]} on 0,{[region]} on 1,{[allclass].[ Lighting ]} on 3 from [Ntsdw]where [Time]. []

5. Search for the annual energy consumption of the company lighting in Jiangsu area

Select {[measures].[ Nts_sample]} on 0,{[region].[ Jiangsu ]} on 1,{[class].[ Lighting ]} on 3,{[org].[ Day Retrospective ]} on 4 from [Ntsdw]where [Time]. []

6. 1 months 1 days in Jiangsu area energy consumption Total ( levels below the dimension must be nested at level 1 1 )

Select {[Measures]. Members} on COLUMNS from [Ntsdw]where [Time]. [2015]. [1]. [1]

7. Check The energy consumption of the year 1 months to 5 months

Select {[measures].[ Nts_sample]} on0,{[time].[ 2015]. [1]:[time]. [2015]. [5]} on the ROWS from [NTSDW]

8. Check The energy consumption values for the year 1 months and 6 months

Select {[Measures]. Members} on0,{[time].[ 2015]. [1],[time]. [2015]. [6]} on the ROWS from [NTSDW]

Members The statistics of the members are carried out according to the level and level.

9. statistics of energy consumption by energy category ( An array is printed out )

Select {[Class]. Members} on 0 from [NTSDW]

Statistics of energy consumption by time-level ( Here is an array of all time energy consumption values by level, printed output )

Select {[Time.default]. Members} on 0 from [NTSDW]

One . statistics on energy consumption by month ( Here is an array of the energy consumption values for each month by level, printed output )

Select {[Time month]. Members} on 0 from [NTSDW]

statistics on energy consumption by region ( also by Level )

Select {[Region District]. Members} on 0 from [NTSDW]

Statistics of year, days retrospective research and development Center, lighting in Nanjing Each district consumes energy

Select {[Region building]. Members} on 0,{[org].[ Day Retrospective ]. [ Research Center ]} on 1,{[class].[ Lighting ]} on 2 from [Ntsdw]where [Time]. []

A few points to pay special attention to:

(1). The hierarchy is below the dimension, most of it is one, but there are multiple levels. such as: The Time dimension under the date and year of the day and the level of the week. Level is below the level, you can have multiple, such as year, month and day of the year, three levels. the presence of layers and levels makes it possible to drill up and down.

(2). [ Time.default] members, [time.defult]. Members; [Timemonth] members [time month]. Members; hierarchies and levels and members closely related to (

and [TIME] is a limitation on the dimension, such as [TIME]. [2014]. [1], it will limit the time of the query, the root is to query the limit of the axis.

(3). the axis limits can be used On {axis} syntax to assign dimensions to axes (Axis, plural Axes) , a query can have more than one axis. With on 0,on 1,on 2,on3 ... and other Expressions , The first five axes can also use aliases columns,rows,pages,chapters,sections represents

the axis must be from 0 Start , and continuous , cannot be skipped. The following is not possible.

Select {[Region building]. Members} on 0,{[class].[ Lighting ]} on 2 from [NTSDW]

The same dimension, hierarchy cannot appear in two axes at the same time, otherwise the validation will not pass, as the following notation is not possible.

Select {[Region building]. Members} on 0 from [Ntsdw]where [region]. [ Jiangsu ]. [ Nanjing ]

Common query statements for Mondrian