Embed Size (px)
Citation preview
Was ist MDX
«SQL» für multidimensionale Datenbanken
Sehr kurze Abfragen mit relativ komplexen Ergebnissen
YTD
Rolling Average
Net Performance
Beispiel: SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Transaction Count]),
([Measures].[Reseller Order Count])
} ON COLUMNS,
TopCount(
{[Product].[Subcategory].[Subcategory].Members},
5,
([Measures].[Reseller Sales Amount])
) +
{([Product].[Subcategory].[All Products])} ON ROWS
FROM [Step-by-Step]
;
SQL Day 2012 2
Ergebnis
/*
Reseller Sales Amount Reseller Transaction Count Reseller Order Count
Road Bikes $29,358,206.96 12,850 1,460
Mountain Bikes $26,492,684.38 7,487 1,215
Touring Bikes $10,451,490.22 4,463 478
Mountain Frames $4,713,672.15 4,476 860
Road Frames $3,849,853.34 4,708 1,013
All Products $80,450,596.98 60,855 3,796
Liegt immer als Pivot vor
SQL Day 2012 3
CUBE: EINFÜHRUNG
SQL Day 2012 4
Data Infrastructure & BI Platform
Analysis Services
Reporting Services
Master Data Services
Integration Services
Data Mining
Data Warehousing
Der Microsoft BI Solution Stack
Business Productivity Infrastructure
Dashboards & Scorecards
Excel Services
Web based forms & workflow
Collaboration
Search
Content Management
LOB data integration
PowerPivot for SharePoint
Delivered through a Familiar Interface
Self-Service access & insight
Data exploration & analysis
Predictive analysis
Data visualization
Contextual visualization
PowerPivot for Excel 2010
OLTP vs. Cube
OLTP
Optimiert für Write
Normalisierung
NICHT optimiert für Reporting
Cube
De-Normalisiert
Optimiert für Reporting
SQL Day 2012 8
SQL Day 2012 9
Tuple
SQL
Wenn ich nichts auswähle, kommen alle Datensätze (Rows)
MDX
Wenn ich nichts auswähle, kommt das default Measure als Summe
Die Schnittmenge von den Cube-Kanten nennt man Tuple
Der Cube ist gefüllt mit Measures (Zahlen)
SQL Day 2012 10
SQL Day 2012 11
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Transaction Count]),
([Measures].[Reseller Order Count])
} ON COLUMNS,
TopCount(
{[Product].[Subcategory].[Subcategory].Members},
5,
([Measures].[Reseller Sales Amount])
) +
{([Product].[Subcategory].[All Products])} ON
ROWS
FROM [Step-by-Step]
SQL Day 2012 12
SELECT
{
([Measures].[Reseller Sales Amount]),
([Measures].[Reseller Transaction Count]),
([Measures].[Reseller Order Count])
} ON COLUMNS,
TopCount(
{[Product].[Subcategory].[Subcategory].Members},
5,
([Measures].[Reseller Sales Amount])
) +
{([Product].[Subcategory].[All Products])} ON
ROWS
FROM [Step-by-Step]
DEMO: CUBE
SQL Day 2012 13
MDX SCHRITT FÜR SCHRITT
SQL Day 2012 14
Wir möchten gerne dies:
/*
EnglishProductName TotalSalesAmount
Mountain-200 Black, 38 1327957.4077
Mountain-200 Black, 42 1139429.4872
Mountain-200 Silver, 38 1136622.4928
Mountain-200 Black, 46 1011074.3685
Mountain-200 Black, 38 NULL
Mountain-200 Silver, 42 1011486.1762
Touring-1000 Blue, 60 835290.1559
Road-350-W Yellow, 48 897217.9635
Mountain-200 Silver, 46 1029170.7639
Road-350-W Yellow, 40 840970.6467
*/
SQL Day 2012 15
Variante SQL:
USE MdxStepByStep
SELECT
m.EnglishProductName, o.TotalSalesAmount
FROM dbo.DimProduct m
INNER JOIN ( -- TOP 10 PRODUCTS OF 2003
SELECT TOP 10
a.ProductKey, SUM(a.SalesAmount) AS TotalSalesAmount
FROM (
SELECT
x.productkey, x.salesamount
FROM dbo.FactInternetSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2003
UNION ALL
SELECT
x.productkey, x.salesamount
FROM dbo.FactResellerSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2003
) a
GROUP BY a.ProductKey
ORDER BY TotalSalesAmount DESC
) n
ON m.ProductKey=n.ProductKey
LEFT OUTER JOIN ( --PRODUCT SALES IN 2004
SELECT
a.ProductKey, SUM(a.SalesAmount) AS TotalSalesAmount
FROM (
SELECT
x.productkey, x.salesamount
FROM dbo.FactInternetSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2004
UNION ALL
SELECT
x.productkey, x.salesamount
FROM dbo.FactResellerSales x
INNER JOIN dbo.DimDate y
ON x.OrderDateKey=y.DateKey
WHERE y.CalendarYear=2004
) a
GROUP BY a.ProductKey
) o
ON m.ProductKey=o.productkey
ORDER BY n.TotalSalesAmount DESC
SQL Day 2012 16
Variante MDX:
WITH
MEMBER [Measures].[Total Sales Amount] AS
([Measures].[Internet Sales Amount]) + ([Measures].[Reseller Sales
Amount])
SET [Top 10 Products of 2003] AS
TOPCOUNT(
{[Product].[Product].[Product].Members},
10,
([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY
2003])
)
SELECT
{([Measures].[Total Sales Amount])} ON COLUMNS,
{[Top 10 Products of 2003]} ON ROWS
FROM [Step-by-Step]
WHERE ([Date].[Calendar Year].[CY 2004])
;
SQL Day 2012 17
DEMO
SQL Day 2012 18
Funktionen & With
1. WITH
2. MEMBER [Measures].[Total Sales Amount] AS
3. [Measures].[Internet Sales Amount]+[Measures].[Reseller Sales Amount]
4. SET [Top 10 Products of 2003] AS
5. TOPCOUNT( [Product].[Product].[Product].Members, 10, ([Measures].[Total Sales Amount], [Date].[Calendar Year].[CY 2003]))
6. SELECT
7. {([Measures].[Total Sales Amount])} ON COLUMNS,
8. {[Top 10 Products of 2003]} ON ROWS
9. FROM [Step-by-Step]
10.WHERE [Date].[Calendar Year].&[2004]
SQL Day 2012 19
Where
Schränkt den Cube ein (Slicing)
Verändert damit die Resultate
SQL Day 2012 20
WHERE: DEMO
SQL Day 2012 21
MDX Anwendungsbereiche
Reporting Services
Excel
PerformancePoint
Eigene Apps ...
SSIS
SQL Day 2012 22
FRAGEN?
SQL Day 2012 23
