exist-development Mailing List for eXist-db (Page 77)

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

*> Can you contribute a use case (or several)? I did make some progress, but
need more cases to cover bigger area.*

Gladly!  I am very interested in trying to understand how the world of
documents (XML), tables, graphs (RDF) and OLAP work together.  Something
like a "grand unified theory of data".  I think that eXist could be extended
to include all four models, but the other models will never be flexible
enough to include XML.  I also think that XQuery extensions and modules
might include the functions of both SQL, SPARQL and MDX.

There are a few core concepts we must try to understand how the work with
faceted search is opening the door to new thinking.  The key is to integrate
with existing open-source concepts so new open source developers will
quickly understand what we are talking about.

One of the first things to understand is the concepts of "categorical data"
and "measurement data".  Categorical data is where you divide all the items
in any data set into discrete non-overlapping and potential hierarchical
categories.  Open source tools like Mondrian already have had very specific
XML tags for configuring dimensions and measures.

Measurement data is data that has can be "summed or averaged" such as
integers, decimal numbers or financial amounts.

First, if people have not used OLAP before I would get familiar with putting
sample data sets with both categories (dimensions) and measures (vales and
amounts) into a spreadsheet and then start using the "pivot table" features
to create simple sums and totals.  For using under 10,000 records most
modern computers can recalculate all the totals in under 1 second.  But this
process gets your brain started to think about the concepts of data
categories and measures.  I have training materials on this if you are
interested.  Doing a Google of "Pivot Table Tutorial" is a good start.

The most common and "typical" use case for OLAP cubes comes from the retail
sales sector.

Say you have 10,000,000 sales transactions items that look like this:

<sales-transaction>
   <item-sku>1234567</item-sku>
   <datetime>2010-10-11T8:00.23</datetime>
   <store-id>12345</store-id>
   <shelf-height type="meters">1.2</shelf-height>
   <promotion-code>spring-sale</promotion-code>
   <price-paid currency="USD">1.23</price-paid>
</sales-transaction>

The first step you want to "enrich" the data to include all the descriptions
about the item (color, size, weight) from your product catalog.  These
extended attributes can be placed into a much larger XML file will all the
extended attributes of an item such as its color, size, weight and create
reports such as "How many people purchased a red item any stores in when
they are moved from the top to the middle shelf and what was the average
price paid?"

The key is to put the data into "fact" tables with many categories and
pre-count the number of items in each category.

If you have 10,000,000 items it can take a very long time to continually
re-calculate the number of items in each sub-category (red hats under
$10.00)

Calculating the number of items in each sub-category can be done with
triggers.  Each time new records are added to "fact tables" you increment
pre-computed data sets (called aggregate caches) that store the counts of
items in each category.  The more categories you have the larger the
aggregates grow in disk space but the faster the sums are calculated.  Most
systems allow you to tune aggregate caches based on the most frequent
queries.

There are many other use cases from inventory management, order management,
task and project management, and workflow but I will try to document these
in other places.  If anyone wants to volunteer an semi-public eXist server I
can load a use-case XRX application.

I think that Anne's initial work on faceted search is starting us to all
think about the issues of storing pre-computed counts of items in
collections.  Although Anne's focus was faceted search, the approach of
extending the configuration file was right on.  And it made me ask - could
we come up with some XML file format that would store generalized aggregate
information used in both faceted search and in analytical reporting?

BI and OLAP are trillion dollar market segments that all depend on the
ability of an information system to pre-compute sums.  The concepts of
aggregates is also very general.  If we designed this correctly eXist could
be the only system in the world that would BI functions using simple XQuery
extensions.  Very, very exciting!

Here are a few other references that might help:

There are XML standards for defining dimensions and measure that are part of
the Common Warehouse Metamodel CWM standard here:
http://en.wikipedia.org/wiki/Common_Warehouse_Metamodel

OLAP Overview
http://mondrian.pentaho.com/documentation/olap.php

MDX Tutorial using Mondrian
http://www.cse.unsw.edu.au/~cs9318/10s1/lect/*MDX*.pdf

Mondrian Architecture
http://mondrian.pentaho.com/documentation/architecture.php

Aggregate Tables
http://mondrian.pentaho.com/documentation/aggregate_tables.php

This note got a bit long.  I will try to re-post it on a blog.

I hope this helps!

- Dan

On Wed, Oct 13, 2010 at 1:25 PM, Dmitriy Shabanov <sha...@gm...>wrote:

> Hi Dan,
>
> On Tue, Oct 12, 2010 at 1:49 AM, Dan McCreary <dan...@gm...>wrote:
>
>> It is very interesting to see that pre-computed counts of item
>> categories might be included in a native XML database!  My initial
>> reason for wanting to use eXist was to track metadata related to OLAP
>> cubes!
>>
>
>  Can you contribute a use case (or several)? I did make some progress, but
> need more cases to cover bigger area.
>
> --
> Dmitriy Shabanov
>

-- 
Dan McCreary
Semantic Solutions Architect
office: (952) 931-9198
cell: (612) 986-1552

2009	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct (22)	Nov (85)	Dec (20)
2010	Jan (47)	Feb (127)	Mar (268)	Apr (78)	May (47)	Jun (38)	Jul (131)	Aug (221)	Sep (187)	Oct (54)	Nov (111)	Dec (84)
2011	Jan (152)	Feb (106)	Mar (94)	Apr (90)	May (53)	Jun (20)	Jul (24)	Aug (37)	Sep (32)	Oct (70)	Nov (22)	Dec (15)
2012	Jan (33)	Feb (110)	Mar (24)	Apr (1)	May (11)	Jun (8)	Jul (12)	Aug (37)	Sep (39)	Oct (81)	Nov (38)	Dec (50)
2013	Jan (23)	Feb (53)	Mar (23)	Apr (5)	May (19)	Jun (16)	Jul (16)	Aug (9)	Sep (21)	Oct (1)	Nov (2)	Dec (8)
2014	Jan (16)	Feb (6)	Mar (27)	Apr (1)	May (10)	Jun (1)	Jul (4)	Aug (10)	Sep (19)	Oct (22)	Nov (4)	Dec (6)
2015	Jan (3)	Feb (6)	Mar (9)	Apr	May (11)	Jun (23)	Jul (14)	Aug (10)	Sep (10)	Oct (9)	Nov (18)	Dec (4)
2016	Jan (5)	Feb (5)	Mar	Apr (2)	May (15)	Jun (2)	Jul (8)	Aug (2)	Sep (6)	Oct	Nov	Dec
2017	Jan (2)	Feb (12)	Mar (22)	Apr (6)	May	Jun	Jul (1)	Aug (1)	Sep (5)	Oct (2)	Nov	Dec
2018	Jan (2)	Feb	Mar	Apr	May	Jun (5)	Jul (3)	Aug	Sep (7)	Oct (19)	Nov	Dec
2021	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct (1)	Nov	Dec
2022	Jan	Feb	Mar	Apr	May	Jun (3)	Jul	Aug	Sep	Oct	Nov	Dec

exist-development Mailing List for eXist-db (Page 77)

eXist-db is a feature rich Open Source native XML database

exist-development — eXist Developer's List