Mondrian e JPivot

Mondrian e JPivot
Mondrian
●
●
Relational OLAP (ROLAP) Server
Scritto in Java e licenza Open Source Eclipse
Public License
●
Supporto a query MDX, OLAP4J, XMLA
●
Connessione JDBC, semplice caching
www.robertomarchetto.com
JPivot
●
●
●
●
www.robertomarchetto.com
Interfaccia web per le
analisi OLAP
Accede ai dati
usando Mondrian o
sorgenti XMLA
Molto diffuso,
completo nelle
funzionalità
Richiede una
formazione iniziale
all'utente finale
Datawarehouse
www.robertomarchetto.com
Risultato finale
www.robertomarchetto.com
Definizione dello schema
www.robertomarchetto.com
Mondrian schema
www.robertomarchetto.com
Caricamento in Pentaho
●
Definire una password di importazione su
\pentaho-solutions\system\publisher_config.xml
www.robertomarchetto.com
Operazioni comuni in JPivot
www.robertomarchetto.com
JPivot as querying tool
www.robertomarchetto.com
MDX
●
●
●
Multidimensional Extension (MDX) è il
linguaggio standard per le query su database
OLAP
Simile all'SQL ma con funzionalità specifiche
per la navigazione tra dimensioni e il calcolo di
indici statistici
SELECT
{[Measures].[Store Sales]} ON COLUMNS
{[Date].[2002], [Date].[2003]} ON ROWS
FROM Sales
WHERE ([Store].[USA].[CA])
www.robertomarchetto.com
I top 5 clienti
●
SELECT Measure.MEMBERS ON COLUMNS,
TOPCOUNT({[Store].[Store City].MEMBERS},
12, MEASURES.[Sales Count]) ON ROWS
FROM [Sales]
www.robertomarchetto.com
Members, CurrentMember
SELECT Measures.MEMBERS ON COLUMNS,
[Store].MEMBERS ON ROWS
FROM [Sales]
WITH SET [Quarter1] AS 'GENERATE([Time].
[Year].MEMBERS,
{[Time].CURRENTMEMBER.FIRSTCHILD})' SELECT
[Quarter1] ON COLUMNS, [Store].[Store
Name].MEMBERS ON ROWS
FROM [Sales]
WHERE (Measures.[Profit])
www.robertomarchetto.com
Children, Descendants
SELECT {GENERATE({[Store].[CA], [Store].
[WA]},
DESCENDANTS([Store].CURRENTMEMBER, [Store
Name]))} ON COLUMNS,
[Promotions].[All Promotions].CHILDREN ON
ROWS
FROM [Sales]
WHERE (Measures.[Unit Sales])
www.robertomarchetto.com
Filter
SELECT
NON EMPTY {[Store Type].[Store
Type].MEMBERS} ON COLUMNS,
FILTER({[Store].[Store City].MEMBERS},
(Measures.[Unit Sales],
[Time].1997])>25000) ON ROWS
FROM [Sales]
WHERE (Measures.[Profit], [Time].[Year].
[1997])
www.robertomarchetto.com
IIF
WITH MEMBER Measures.[Profit Growth] AS
'IIF(ISEMPTY([Time].PREVMEMBER), 1,
(Measures.[Profit])/(Measures.[Profit],
[Time].PREVMEMBER))', FORMAT_STRING =
'#.00%'
SELECT {Measures.[Profit], Measures.
[Profit Growth]} ON COLUMNS,
{DESCENDANTS([Time].[1997],[Month])} ON
ROWS
FROM [Sales]
www.robertomarchetto.com
Format
WITH MEMBER Measures.[Profit Growth] AS
'(Measures.[Profit]) - (Measures.[Profit],
[Time].PREVMEMBER)', FORMAT_STRING =
'###,###.00'
SELECT {Measures.[Profit], Measures.
[Profit Growth]} ON COLUMNS
{DESCENDANTS([Time].[1997], [Month])} ON
ROWS
FROM [Sales]
www.robertomarchetto.com
Gestire indici complessi
WITH MEMBER [Time].[Average Sales] AS
'AVG(DESCENDANTS([Time].[1997], [Time].
[Month]))'
MEMBER [Time].[Average Count] AS
'COUNT(DESCENDANTS([Time].[1997],
[Time].[Month]),EXCLUDEEMPTY)'
SELECT {[Time].[1997], [Time].[Average
Sales], [Time].[Average Count]} ON
COLUMNS,
[Product].[Brand Name].MEMBERS ON ROWS
FROM [Sales]
www.robertomarchetto.com