Online Analytical Processing (OLAP) promises an interface to analyse Linked Data containing statistics going beyond other interaction paradigms such as follow-your-nose browsers, faceted-search interfaces and query builders. As a new way to interact with statistical Linked Data we define comon OLAP operations on data cubes modelled in RDF and show how a nested set of OLAP operations lead to an OLAP query. Then, we show how to transform an OLAP query to a SPARQL query which generates all required facts from the data cube. Both metadata and OLAP queries are issued directly on a triple store; therefore, if the RDF is modified or updated, changes are propagated directly to OLAP clients.
Linked Data provides easy access to large amounts of interesting statistics from
many organizations for information integration and decision support, including
financial information from institutions such as the UK government3 and the
U.S. Securities and Exchange Commission.4 However, interaction paradigms for
Linked Data such as follow-your-nose browsers, faceted-search interfaces, and
query builders [
In this paper, we assume that statistical Linked Data has been modelled
using QB and focus on efficiently executing OLAP queries on statistical Linked
Data. In previous work [
The current work presents a new way to interact with statistical Linked Data: – We define comon OLAP operations on data cubes as Linked Data reusing
QB and show how a nested set of OLAP operations lead to an OLAP query. – We show how to transform an OLAP query to a SPARQL query which generates all required facts from the data cube.
In the remainder of the paper, we first present a motivational scenario from the financial domain in Section 2. As a prerequisite for our contribution, in 5 http://www.w3.org/TR/2012/WD-vocab-data-cube-20120405/ 6 http://wiki.planet-data.eu/web/Datasets Section 3, we formally define a multidimensional model of data cubes based on QB. Then, in Section 4, we introduce OLAP operations on data cubes and present a direct mapping of OLAP to SPARQL queries. We apply this mapping in a small experiment in Section 5 and discuss some lessons learned in Section 6. In Section 7, we describe related work, after which, in Section 5, we conclude and describe future research. 2
In this section we describe a scenario of analysing Linked Data containing financial information. The Edgar Linked Data Wrapper7 provides access to XBRL filings8 as Linked Data reusing QB. Those filings disclose balance sheets of a large number of US organizations, for instance that RAYONIER INC had a sales revenue net of 377,515,000 USD from 2010-07-01 to 2010-09-30.9
Using LDSpider, we crawled Linked Data from the Edgar wrapper and stored a data cube SecCubeGrossProfitMargin into an Open Virtuoso triple store. The data cube contains single disclosures from financial companies such as RAYONIER INC. Each disclosure either discloses cost of goods sold (CostOfGoodsSold) or sales revenue net (Sales) as measures. The two measures have the unit USD and an aggregation function that returns the number of disclosures, or - if only one - the actual number. Any disclosure is fully dependent on the following dimensions: the disclosing company (Issuer), the date a disclosure started (Dtstart) and ended (Dtend) to be valid, and additional segment information (Segment).
In our scenario, a business analyst wants to compare the number of
disclosures of cost of goods sold for two companies. He requests a pivot table with
issuers RAYONIER INC and WEYERHAEUSER CO on the columns, and the
possible periods for which disclosures are valid on the rows, and in the cells
showing the number of disclosed cost of goods sold, or - if only one - the actual
number. Figure 2 shows the needed pivot table.
7 http://edgarwrap.ontologycentral.com/
8
http://www.xbrl.org/Specification/XBRL-RECOMMENDATION-2003-1231+Corrected-Errata-2008-07-02.htm
9
http://edgarwrap.ontologycentral.com/archive/52827/0001193125-10238973#ds
In this section, as a precondition for OLAP queries on Linked Data, we formally
define the notion of data cubes in terms of QB. The definition is based on the
multidimensional models of Go´mez et al. [
Given a triple store with statistical Linked Data, we use basic SPARQL triple patterns on the store to help us defining sets of multidimensional elements. Given a multidimensional element x iri(x) ∈ (I ∪ B) returns its IRI or blank node: Member defines the set of members as M ember = {?x ∈ (I∪B)|(?x a skos:Concept )}. Let V = 2Member, V ∈ V, ROLLU P M EM BER ⊆ M ember × M ember, rollupmember(V ) = {(v1, v2) ∈ V ×V |(iri(v1) skos:broader iri(v2)∨ iri(v2) skos:narrower iri(v1))} Level defines the set of levels as Level = {(?x, V, rolluplevel(V )) ∈ (I ∪ B) × V × ROLLU P LEV EL|(?x a skosclass:ClassificationLevel ∧∀v ∈ V (iri(v) skos:member ?x)}. Let L = 2Level, L ∈ L, ROLLU P LEV EL ⊆ Level × Level, rolluplevel(L) = {(l1, l2) ∈ L × L|(iri(l1) skosclass:depth x) ∧ (iri(l2) skosclass:depth y) ∧ x ≤ y))} Hierarchy defines the set of hierarchies as Hierarchy = {(?x, L, rolluplevel(L)) ∈ (I ∪B)×L×ROLLU P LEV EL|(?x a skos:ConceptScheme )∧∀l ∈ L(iri(l) skos:inScheme ?x)}. Let H = 2Hierarchy. 10 http://www.olap4j.org/ 11 http://www.w3.org/2004/02/skos/ 12 http://www.w3.org/2011/gld/wiki/ISO_Extensions_to_SKOS Dimension defines the set of dimensions as Dimension = {(?x, H) ∈ (I ∪ B) × H|(?x a qb:DimensionProperty ) ∧ ∀h ∈ H(?x qb:codeList iri(h))}. Let D = 2Dimension.
Measure defines the set of measures as M easure = {(?x, aggr) ∈ (I ∪ B) × {U DF }|(?x a qb:MeasureProperty )} with U DF a default aggregation function since QB so far does not provide a standard way to represent typical aggregation functions such as SUM, AVG and COUNT: if only one value is given, the value itself else the number of values is returned. Conceptually, measures are treated as members of a dimension-hierarchy-level combination labelled “Measures”. Let M = 2Measure.
DataCubeSchema defines the set of data cube schemas as {(?x, D, M ) ∈ (I ∪ B)×D ×M|(?x a qb:DataStructureDefinition ∧∀d ∈ D(?x qb:componentProperty ?comp∧?comp qb:dimensionProperty iri(d))∧∀m ∈ M (?x qb:componentProperty ?comp∧?comp qb:measureProperty iri(m)))}.
Fact defines the set of possible statistical facts as F act = {(?x, ?c0, . . . , ?ci, ?e0, . . . , ?ej ) ∈ (I ∪ B) × (I ∪ B ∪ L) × . . . × (I ∪ B ∪ L) × L × . . . × L|(?x a qb:Observation )∧(?x?d0c0∧?d0 a qb:DimensionProperty )∧. . .∧(?x?dici∧?di a qb:DimensionProperty ) ∧ (?x?m0e0∧?m0 a qb:MeasureProperty ) ∧ . . . ∧ (?x?mj ej ∧?mj a qb:MeasureProperty )}. Let F = 2F act.
DataCube defines the set of data cubes as DataCube = {(cs, F ) ∈ DataCubeSchema × F |cs = (?x, D, M ) ∧ D = {D0, . . . , D|D|} ∧ M = {m0, . . . , m|M|} ∧ ∀c ∈ F (c = (?obs, c0, . . . , c|D|, e0, . . . , e|M|) : (?obs qb:dataSet ?ds∧?ds qb:structure ?x) ∧ ∀Di ∈ D(?xiri(Di)ci ∧ iri(Di) qb:codeList ?h∧?l skos:inSchme ?h∧?v skos:member ?l ∧ (?v skos:notation ?ci∨?v skos:exactMatch ?ci)) ∧ ∀mi ∈ M (?xiri(mi)ei ∨ ei = null).
We distinguish metadata queries and OLAP queries on data cubes. Whereas
metadata queries return multidimensional objects such as the cube schema, the
dimensions, and the measures, OLAP queries return facts directly contained in
or derived from (e.g., by aggregation of several facts) the data cube.
Definition 1. According to Gray et al. [
Subqueries and aggregation functions in SPARQL 1.1 make easily possible
the concept of Gray et al. [
In this section we show how to issue OLAP queries on a multidimensional model.
We define common OLAP operations on single data cubes [
Note, this paper focuses on direct querying of single data cubes, the integration of several data cubes through Drill-Across or set-oriented operations such as union, intersection, difference is out-of-scope. Multiple datasets can already be queried together if they are covered by one qb:DataStructureDefinition.
Each OLAP operation has as input and output a data cube. Therefore,
operations can be nested. A nested set of OLAP operations lead to an OLAP query.
For interpreting a set of OLAP operations as an OLAP query and evaluate it
using a SPARQL query on QB, we adopt the notion of subcube queries [
As examples, we describe three distinguishable subcube queries: – A full-cube query returns exactly the tuples from a DataCubeInstance and inquires all dimensions: (?, ?, ?, ..., m0, ..., m|M|). – A point query asks for a data cube comprising one specific instance tuple: (a0, a1, ..., a|D|, m0, ..., m|M|) with ai a member of a dimension Di. – A fully-aggregated query asks for the measures aggregated over all dimensions: (ALL, ALL, . . . , ALL, m0, . . . , m|M|).
In the following we describe how to evaluate each OLAP operation in terms of this query model and how a nested set of OLAP operations results in one specific OLAP subcube query. We assume that names and schemas of input and output data cubes are implicitly given and we focus on the data cube facts which will be queried for. An input data cube is represented as a full-cube query (?, ?, ?, ..., m0, ..., m|M|).
Projection is defined as P rojection : DataCube × M easure → DataCube and removes a measure from the input cube and allows to query only for specific measures. We evaluate P rojection by removing a measure from the subcube query tuple.
Slice is defined as Slice : DataCube × Dimension → DataCube and removes a dimension from the input cube and aggregates over the members of a dimension. We evaluate Slice by setting the tuple element of that dimension to ALL.
Dice is defined as Dice : DataCube × Dimension × V → DataCube and allows to filter for and aggregate over certain dimension members. We evaluate Dice by setting the tuple element of that dimension to this particular member or set of members and aggregate over the set. Note, dice is not a selection operation but a combined filter and slice operation.
Roll-Up is defined as Roll−U p : DataCube×Dimension×Level → DataCube and allows to create a cube that contains instance data on a higher aggregation level. We evaluate roll-up by replacing the inquired members of a dimension with members of a higher level. Note, we do not define Drill − Down, since it can be seen as an inverse operation to Roll − U p.
As an example, consider an OLAP query on our SecCubeGrossProfitMargin cube for the cost of goods sold (CostOfGoodsSold) for each issuer and each date until when each disclosure is valid (dtend), filtering by disclosures from two specific segments. A nested set of OLAP operations that queries the requested facts can be composed as follows. In all our queries, we use prefixes to make URIs more readable: S l i c e (
Dice (
P r o j e c t i o n ( e d g a r : Se cCu b e G r oss P r of it M ar gi n , e d g a r : CostOfGoodsSold ) , e d g a r : segment , { e d g a r : segmentAHealthCareInsuranceCompany , e d g a r : s e g m e n t A R e s i d e n t i a l R e a l E s t a t e D e v e l o p e r M e m b e r } ) , ( ? , ∗ , ? , { e d g a r : segmentAHealthCareInsuranceCompany , e d g a r : s e g m e n t A R e s i d e n t i a l R e a l E s t a t e D e v e l o p e r M e m b e r } , e d g a r : CostOfGoodsSold )
Next, we describe how to evaluate such an OLAP query using a SPARQL query on QB. Since OLAP hierarchies add considerable complexity to QB and since a Roll − U p has a similar effect to a Slice operation, in this paper, we assume data cubes with only one hierarchy and level per dimension. A subcube query Q = (q0, . . . , q|D|, m0, ..., m|M|) can be translated into a SPARQL query using the following steps: 1. We initialise the SPARQL query using the URI of the data cube. We query for all instance data from the data cube, i.e., observations linking to datasets which link to the data structure definition. 2. For each selected measure, we incorporate it in the SPARQL query by selecting additional variables for each measure and by aggregating them using the aggregation function of that measure, using OPTIONAL patterns in case we query for several measures. 3. For each inquired dimension, we query for all the instances of skos:Concept in a level of a hierarchy of the dimension and for the represented values used (from members either linked via skos:notation or skos:exactMatch) in the observations. We query for the observations showing property-value pairs for each of these variables. To display inquired dimensions in the result and correctly aggregating the measures, we group by each dimension variable. 4. For each fixed dimension, we filter for those observations that exhibit for each dimension one of the listed members.
We transform our example from above to the following SPARQL query. Note, “UDF” represents the standard aggregation function from our scenario: s e l e c t ?dimMem0 ?dimMem1 UDF( ? measureValues0 ) where { ? obs qb : d a t a S e t ? ds . ? ds qb : s t r u c t u r e e d g a r : S e c C u b e G r o s s P r o f i t M a r g i n . ? obs e d g a r : i s s u e r ? v a l u e s 0 .
?dimMem0 s k o s : member ? l e v e l 0 . ? l e v e l 0 s k o s : inScheme ? h i e r a r c h y 0 . e d g a r : i s s u e r qb : c o d e L i s t ? h i e r a r c h y 0 .
?dimMem0 s k o s : exactMatch ? v a l u e s 0 . ? obs e d g a r : dtend ? v a l u e s 1 .
?dimMem1 s k o s : member ? l e v e l 1 . ? l e v e l 1 s k o s : inScheme ? h i e r a r c h y 1 . e d g a r : dtend qb : c o d e L i s t ? h i e r a r c h y 1 .
?dimMem1 s k o s : n o t a t i o n ? v a l u e s 1 . ? obs e d g a r : segment ? v a l u e s 2 .
? sli c er M em0 s k o s : member ? l e v e l 2 . ? l e v e l 2 s k o s : inScheme ? h i e r a r c h y 2 . e d g a r : segment qb : c o d e L i s t ? h i e r a r c h y 2 . ? sli c er M em0 s k o s : exactMatch ? v a l u e s 2 . F i l t e r ( ? sli c erMe m0 = e d g a r : segmentAHealthCareInsuranceCompany OR ? sl i cerM e m0 = e d g a r :
s e g m e n t A R e s i d e n t i a l R e a l E s t a t e D e v e l o p e r M e m b e r ) ? obs e g a r : CostOfGoodsSold ? measureValue0 . } group by ?dimMem0 ?dimMem1 5
In this section we demonstrate in a small experiment the applicability of our OLAP-to-SPARQL mapping to our scenario from the financial domain. The SecCubeGrossProfitMargin cube contains 17,448 disclosures that either disclose cost of goods sold or sales revenue net. The values of the measures fully depend on one of 625 different issuers, the date a disclosure started (27 members of dimension Dtstart) and ended (20 members of Dtend) to be valid, and additional information (21,227 members of Segment). The two measures have the unit USD and an aggregation function that returns the number of disclosures, or - if only one - the actual number. If fully materialised according to Definition 1, the cube contains 626 · 28 · 21 · 21, 228 = 7, 813, 772, 064 facts. To compute all of its facts, 24 = 16 SPARQL subqueries would be needed.
OLAP interface allow users to interactively combine OLAP operations into an
expression of an OLAP query language. Results of the query shall be visualised
using a pivot table, a compact format to display multidimensional data [
In order to answer the OLAP question of our scenario, we created the following MDX query. Multidimensional elements are described there using URIs.13 For an introduction to MDX, see its website.14 A more detailed description of how to transform an MDX query into an OLAP query due to space constraints we leave for future work when we evaluate our OLAP-to-SPARQL mapping more thoroughly. 13 Note URIs need to be translated to an MDX-compliant format that does not use reserved MDX-specific characters. 14 http://msdn.microsoft.com/en-us/library/aa216770%28v=sql.80%29.aspx SELECT { edgar : cik1417907idConcept , edgar : c i k 1 0 6 5 3 5 i d C o n c e p t } ON
COLUMNS, C r o s s J o i n ( edgar : d t s t a r t R o o t L e v e l . Members , edgar : dtendRootLevel . Members ) ON ROWS FROM [ edgar : SecCubeGrossProfitMargin ] WHERE { edgar : CostOfGoodsSold }
A nested set of OLAP operations to compose our OLAP query is as follows: S l i c e ( P r o j e c t i o n ( edgar : SecCubeGrossProfitMargin , edgar : CostOfGoodsSold ) , edgar : segment )
This query can then be represented as a subcube query with dimensions Issuer, Dtstart, Dtend, Segment: (?, ?, ?, ∗, CostOf GoodsSold). The resulting SPARQL query is as follows: s e l e c t ?dimMem0 ?dimMem1 ?dimMem2 count ( xsd : decimal ( ? measureValue0 ) ) sum( xsd : decimal ( ? measureValue0 ) ) where { ? obs qb : da t aS et ? ds . ? ds qb : s t r u c t u r e edgar : SecCubeGrossProfitMargin . ? obs edgar : i s s u e r ? v a l u e s 0 .
?dimMem0 s k o s : member ? l e v e l 0 . ? l e v e l 0 s k o s : inScheme ? h i e r a r c h y 0 . edgar : i s s u e r qb : c o d e L i s t ? h i e r a r c h y 0 .
?dimMem0 s k o s : exactMatch ? v a l u e s 0 . ? obs edgar : d t s t a r t ? v a l u e s 1 .
?dimMem1 s k o s : member ? l e v e l 1 . ? l e v e l 1 s k o s : inScheme ? h i e r a r c h y 1 . edgar : d t s t a r t qb : c o d e L i s t ? h i e r a r c h y 1 .
?dimMem1 s k o s : n o t a t i o n ? v a l u e s 1 . ? obs edgar : dtend ? v a l u e s 2 .
?dimMem2 s k o s : member ? l e v e l 2 . ? l e v e l 2 s k o s : inScheme ? h i e r a r c h y 2 . edgar : dtend qb : c o d e L i s t ? h i e r a r c h y 2 .
?dimMem2 s k o s : n o t a t i o n ? v a l u e s 2 . ? obs edgar : CostOfGoodsSold ? measureValue0 . } group by ?dimMem0 ?dimMem1 ?dimMem2
The aggregation function used is a non-standard one, therefore, we had to compute the SUM and COUNT for the measure. We run the query after a reboot of the triple store. The query took 18sec and returned 58 facts to be filled into the requested pivot table. The number of 7, 813, 772, 064 potential facts in the cube does not have a strong influence on the query since the cube is very sparse, for instance, the triple store contains observations only for a fraction of segment members.
Data from the data cube is queried on demand, and no materialization is done.
We correctly aggregate data on one specific granularity, defined by the
mentioned inquired and fixed dimensions. Dimensions that are not mentioned will
be automatically handled as having an ALL value [
Filling the pivot table with measure values from the SPARQL result requires matching of the member values for each fact for the following reasons: first, if the data cube is sparse, i.e., not for every possible combination of members a value is given, then, for non-occurring combinations the SPARQL query does not return a value; second, all member combinations of inquired dimensions are calculated, even though only specific combinations might be selected, as in the case of the two issuers in the OLAP query of our scenario. Indexing of either the pivot table or the SPARQL result table may allow faster population of the pivot table.
In summary, though our OLAP algebra to SPARQL mapping may not result
in the most efficient SPARQL query and require additional efforts for populating
the pivot table, it correctly computes all required facts from the data cube
without the need for explicitly introducing the non-relational ALL member or
using sub-queries [
Kobilarov and Dickinson [
OLAP query processing in general has long been a topic of research [
Specific approaches regarding OLAP on Linked Data seem to have
concentrated so far on multidimensional modelling from ontologies [
In this work we use the graph-based RDF data model for querying and storing of multidimensional data reusing QB. Here, both schema information and actual data is accessed using Linked Data principles and managed using an RDF store. Our approach focuses on OLAP queries that can be composed by common OLAP operations and can be represented as a subcube query. Our mapping allows translating OLAP queries into one SPARQL query to be run on the RDF without storage of intermediate results. In our small experiment the produced SPARQL query showed sufficiently fast, but queries are expected to become insufficient for larger datasets. Since no materialization is done, only few extra space is required for a hashmap to fill the pivot table with the SPARQL result set, and updates to the RDF are propagated directly to OLAP clients. Although there may be more efficient querying approaches such as special indexing and caching, to the best of our knowledge, this is the first work on computing and querying of data cubes represented in RDF. 8
We have presented an approach to interact with statistical Linked Data using common Online Analytical Processing operations of “overview first, zoom and filter, then details-on-demand”. For that, we define common OLAP operations on single data cubes in RDF reusing the RDF Data Cube vocabulary, map nested sets of OLAP operations to OLAP subcube queries, and evaluate those OLAP queries using SPARQL. Both metadata and OLAP queries are issued directly on a triple store; therefore, if the RDF is modified or updated, changes are propagated directly to OLAP clients. Though, our OLAP-to-SPARQL mapping may not result in the most efficient SPARQL query and require additional effort in populating resulting pivot tables, we correctly calculate requested facts of a data cube without the need for explicitly introducing the non-relational ALL member or using subqueries.
Future work may be conducted in three areas: 1) extending our current approach with OLAP hierarchies and Drill-Across queries; 2) implementing an OLAP engine to more thoroughly evaluate our current approach and to investigate more efficient OLAP query execution plans; 3) investigating possible OLAP clients that map OLAP operations to intuitive user interactions.
This work was supported by the German Ministry of Education and Research (BMBF) within the SMART project (Ref. 02WM0800) and the European Community’s Seventh Framework Programme FP7/2007-2013 (PlanetData, Grant 257641).