MDXqueries example
MDX Query Language is used to retrieve information stored in OLAP Cube
created in various technologies like Microsoft SQL Server Analysis
Services(SSAS), Oracle, Tera data, etc. Key difference between MDX and
T-SQL is MDX Query build Multidimensional View of the data, where T-SQL
builds Relational View. SQL Query designed to handle only two dimension
while processing tabular data .
1.
General :
1. Top Product Revenue
|
select [Measures].[Cout] on columns,
non empty
TOPCOUNT( [Product].[Model Name].members,3, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
2. Top month Revenue
|
select [Measures].[Cout] on columns,
non empty
TOPCOUNT([Dim Temps].[Month].members ,3, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
3. Top year Revenue
|
select [Measures].[Cout] on columns,
non empty
TOPCOUNT([Dim Temps].[Year].members ,3, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
Specific :
4. Revenue byRegion
|
select [Measures].[Cout] on columns,
--non empty
[Region].[Rejion].members on rows
from [sales_esp] ;
|  |
5. Revenue by Country
|
select [Measures].[Cout] on columns,
--non empty
[Region].[Pays].members on rows
from [sales_esp] ;
|  |
6. Revenue By Gouvernment
|
select [Measures].[Cout] on columns,
--non empty
[Region].[Gouvernorat].members on rows
from [sales_esp] ;
|  |
7. Revenue By Gender
|
select [Measures].[Cout] on columns,
--non empty
[Dim Customer].[Sexe].members on rows
from [sales_esp] ;
|  |
Top 5 Revenue by:
8. Customers
|
select [Measures].[Cout] on columns,
non empty
TOPCOUNT([Dim Customer].[Customer Name].members ,5, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
9. Enterprise
|
select [Measures].[Cout] on columns,
non empty
TOPCOUNT([Enterprise].[Enterprise Id].members ,5, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
10. Supplier
|
select [Measures].[Cout] on columns,
non empty
TOPCOUNT([Dim Supplier].[Supplier Name].members ,5, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
11. Category Product
|
select [Measures].[Cout] on columns,
--non empty
TOPCOUNT([Category].[Product Category Key].members ,5, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
12. Region
|
select [Measures].[Cout] on columns,
--non empty
TOPCOUNT([Region].[Rejion].members ,5, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
13. Country
|
select [Measures].[Cout] on columns,
--non empty
TOPCOUNT([Region].[Pays].members ,5, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
14. Mounth
|
select [Measures].[Cout] on columns,
--non empty
TOPCOUNT([Dim Temps].[Month].members ,5, [Measures].[Cout]) on rows
from [sales_esp] ;
|  |
Varriation :
15. Tendance de vente
|
select [Measures].[Cout] on columns,
non empty
Order(
({[Dim Temps].[Year].members},
{[Dim Temps].[Month].members}),[Measures].[Cout],BASC
) on rows
from [sales_esp] ;
|  |
16. Product Revenue by Mounth
|
select [Measures].[Cout] on columns,
non empty
({[Product].[Model Name].members},
{[Dim Temps].[Month].members}) on rows
from [sales_esp] ;
|  |
17. Revenue by Gender and Category
|
select [Measures].[Cout] on columns,
non empty
({[Dim Customer].[Sexe].members},
{[Sub Category].[Product Sub Category Key].members}) on rows
from [sales_esp] ;
|  |
Commentaires
Enregistrer un commentaire