Reclassification queries in a geographical data warehouse

Francisco J. Moreno¹, Jaime Echeverri², Fernando Arango¹

¹ School of Systems, University National of Colombia. Carrera 80 Nº 65-223.

² Universidad de Medellín. Carrera 87 Nº 30-65. Medellin, Antioquia, Colombia. Phone: (094) 425-5376.

fjmoreno@unal.edu.co, farango@unal.edu.co, jaecheverri@udem.edu.co

Abstract

A data warehouse is a specialized database designed to support decision-making and is usually modeled using a multidimensional view of data. A multidimensional model includes dimensions that are composed of levels. The levels of a dimension are organized in a hierarchy, e.g., salespersons are grouped into stores. Throughout its lifespan a member (instance) of a level can be associated with several members of a higher level of the hierarchy, e.g., the salespersons can rotate between the stores. This succession of associations enables the formulation of queries such as: “How much did a salesperson sell in his n-th season (stay) in the store X?” In this paper, we enrich this type of query, known as season queries, with spatial features. This enhancement enables the formulation of queries such as: “How much did a salesperson sell in his n-th season in a given geographic region?” (A spatial query window that contains a set of stores.) In order to facilitate their formulation, we propose and incorporate an operator into a multidimensional query language to demonstrate their feasibility of implementation.

Key words: Temporal data warehouses, spatial data warehouses, OLAP, members’ reclassification, season queries.

Consultas de temporada espacial en un modelo multidimensional

Una bodega de datos es una base de datos especialmente diseñada para soportar la toma de decisiones y es usualmente modelada en forma multidimensional. Un modelo multidimensional posee dimensiones las cuales se componen de niveles. Los niveles de una dimensión se organizan jerárquicamente, e.g., los vendedores se agrupan en tiendas. A través de su existencia un miembro (instancia) de un nivel se puede asociar con varios miembros pertenecientes a un nivel superior en la jerarquía, e.g., los vendedores pueden rotar entre las tiendas. Esta sucesión de asociaciones posibilita la formulación de consultas como: “Cuánto vendió un vendedor en su enésima temporada (estadía) en la tienda X?” En este artículo, se enriquece este tipo de consultas, conocidas como consultas de temporadas, con aspectos espaciales. Esta mejora posibilita la formulación de consultas como: “Cuánto vendió un vendedor en su enésima temporada (estadía) en una región geográfica” (Una región espacial que cubre un conjunto de tiendas.) Para facilitar su formulación, se propone e incorpora un operador en un lenguaje de consulta multidimensional para demostrar la viabilidad de su implementación.

Palabras clave: Bodegas de datos temporales, bodegas de datos espaciales, OLAP, reclasificación de miembros, consultas de temporadas.

Recibido el 26 de Agosto de 2009

En forma revisada el 4 de Octubre de 2010

1. Introduction

A data warehouse [1-2] is a specialized database designed to support decision-making and is usually modeled using a multidimensional view of data. Although there are several multidimensional models [3-11]; they share a set of key concepts such as dimension, hierarchy, level, fact, and measure, among others. 

A dimension is associated with a subject of analysis, called fact. For example, SALESPERSON, TIME, PRODUCT, and CUSTOMER dimensions can be associated with a sale. A multidimensional collection of data arranged in this way is commonly referred to as a data cube [12] (to be referred to hereinafter simply as cube.) 

A dimension represents a business perspective to analyze the facts and it is composed of a non-empty set of levels. For example, in Figure 1 Salesperson, Store, City, and State are levels of the SALESPERSON dimension. A level in turn has attributes [11], which provide supplementary information about the level. For example, Name and Salary are attributes of the Salesperson level. For simplicity, we do not show attributes of levels in Figure 1.

The levels of a dimension are structured as a hierarchy according to the analysis needs [13]. The hierarchical relationship between the levels captures their full containment [9]. For example, in our SALESPERSON dimension, a salesperson is fully contained in a store, a store is fully contained in a city, and a city is fully contained in a state. 

A member (instance) of a level can be associated with several members of a higher hierarchical level throughout its lifespan, i.e., a member can be reclassified, e.g., a salesperson can rotate between the stores, a product can change its category. These reclassifications originate the concept of season [14]. Informally, a season is a maximum interval during which a member of a level is associated with a member of a higher level. For example, suppose a salesperson Sp₁ is associated with the store St₁ from 2009-01-01 to 2009-03-15. Note that throughout his lifespan a salesperson can experience several (disjoint) seasons in the same store. Thus, the ordering of the seasons between two members originates the notion of the n-th season, e.g., the first season of Sp₁ in St₁, the second season of Sp₁ in St₁, the first season of Sp₁ in St₂, and so on. 

The seasons can originate queries such as: “How much did Sp₁ sell in his n-th season in St₁?” These types of queries are called season queries [14]. However, in [14] season queries that involve spatial features are not supported, e.g., how much did Sp₁ sell in his n-th season in region R₁? (Where R₁ is a spatial query window that contains a set of stores.) In this paper, we propose an operator to support this type of query, i.e., spatial season queries. To the best of our knowledge, there is no language or operator that allows one to formulate spatial season queries in a concise and simple way. 

Although over the last years both spatial and temporal data warehouses have been an active field of research [15-16], the notion of spatial season queries is not present in any work we have found in the literature. The works closest to ours are the following. In [17] the authors focus on solving queries such as: obtain the total sales of all stores that are inside a given region (a spatial query window); however, they do not deal with members’ reclassifications. Shekhar [18] proposes an operator that supports spatial aggregation in the context of a spatial multidimensional database; however, this work also does not deal with members’ reclassifications. Other works [8], [19-22] deal with reclassifications but they do not consider spatial features or season queries. 

This paper is organized as follows: in Section 2 we present a motivating example. In Section 3 we propose an operator to support spatial season queries and in Section 4 we give examples. Finally, in Section 5 we present conclusions and outline future work. 

2. Motivating example 

Consider a consortium with stores in the cities of a country. The country is divided territorially into states, which group the cities. One of the most important subjects of analysis for the consortium are the sales of products, since from their behavior may raise strategies for production, distribution, purchasing, inventory management, marketing, among others. The products are classified into categories, e.g., cosmetics, meat and dairy products, and the customers are classified by gender and age groups. 

A multidimensional model for representing this scenario is shown in Figure 1. We use notations from Malinowski [15] based on the entity-relationship graphical notations. Note that, since the cardinality of every level (rectangles) participating in a fact relationship (grey diamond) is zero-to-many (crowfoot connector), such cardinalities are omitted. Note also that Store, City, and State are spatial levels. A spatial level is a level that the application needs to keep its spatial characteristics [15]. This is captured by its geometry represented using spatial types [23], such as Point and Region. In addition, the symbol between two spatial levels, see Figure 1, represents the topological relationship inside [23-24], e.g., a store is inside a city and a city is inside a state. 

The salespersons of the consortium tend to rotate between the stores in periods of days. The rotation is due to factors such as salesperson’s experience, skills, greater number of people in certain stores at certain times, distribution and launch of products, management of replacements due to vacation, permissions, sick leaves of the salespersons. Thus, a salesperson associated with store St₁, may go on training, and later be associated with store St₂ and then return to store St₁. The temporal association between salespersons and stores is shown in Figure 1 by means of µ = day [22] (temporal unit to trace their assignments.) 

Consider Figure 2 where the dashed region R₁ represents the set of stores in the western region of a country and consider the query: obtain the total sales made by salesperson Sp₁ in his first season in the stores in the western region of the country. To answer this query, according to Figure 2, the sales made by Sp₁ in his first and second seasons in St₁ and in his first season in St₂, St₃, and St₄ should be considered. Note that the sales made by Sp₁ corresponding to his second season in St₁ are included in the result because he has not left R₁. Thus, while Sp₁ rotates between the stores of R₁ without leaving this region, his sales will be part of the total requested. Eventually, when Sp₁ leaves R₁ and then returns to some store in that region, he begins his second season in R₁ (in Figure 2, when Sp₁ returns to St₃ from St₆.) Note that more specialized queries can be formulated, e.g., obtain the total sales of cosmetics made to middle-aged women by Sp₁ in his first season in the western stores.

Similar queries to the previous ones can be applied in other fields. In the military field, where the military units perform missions at strategic sites, the following query can be formulated: In its third season when the Red Unit performed missions in sites within the southern region, did the number of casualties decrease compared to the previous two seasons? In the fishing field where the boats are regularly assigned to certain fishing spots, the following query may be formulated: What was the total salmon catch by all the boats in their first three seasons in the Polar region (where the Polar region contains a set of specific fishing spots)? In the next section we present an operator to facilitate the formulation of this type of query. 

3. The Spatial_Season operator 

In order to design an operator to facilitate the formulation of the queries outlined in Section 2, we identify the arguments required by such an operator. Consider Figure 3 and the query: obtain the total sales made by salesperson Sp₁ in his first season in region R₂. Assume that the SALESPERSON dimension is formed as shown in Figure 1.

Let Q be one of the sales made by Sp₁ in his first season in St₁. Q contributes to the total requested since St₁ is inside R₂. Assume now that the SALESPERSON dimension is formed as shown in Figure 4 and that the Q sale was made by Sp₁ when he lived in neighborhood N₁, see Figure 5. Thus, the Q sale is characterized, from the geographic point-of-view, by store St₁ that is inside R₂ (and therefore it contributes to the total requested) and by the neighborhood N₁ which is outside R₂ (and therefore it does not contribute to the total requested.) Therefore, the statement of the query should be clarified to avoid this ambiguity.

On the other hand, note that it is possible that in a moment in time, a salesperson lives in a neighborhood of a city different from the city of the store where he works, i.e., in time t the city associated with a salesperson along the path that goes by the Store level might be different from the city associated with the salesperson along the path that goes by the Neighborhood level. Thereby, Salesperson.Store.City and Salesperson. Neighborhood.City represent different geographic contexts. The first refers to the city where the salesperson works (store) and the second refers to the city where he lives (neighborhood). Thus, a sale might be referred to two cities: the city of the store where the sale was made and the city where the salesperson that made the sale lives. 

In order to facilitate the formulation of this type of query, we define a Spatial_Season operator. Our operator receives as arguments: i) a spatial query window, ii) a geographic context, c) a list of aggregates, where an aggregate is an aggregate function applied to a measure, and iv) a cube. Our operator returns a cube as well. For example, consider a cube corresponding to the schema of Figure 1, the region R₂ of Figure 3 and the geographic context Salesperson.Store. For each salesperson in Sales his seasons in R₂ are calculated. Each season has its start and end time (SStart and SEnd attributes) and its corresponding order number (SNumber attribute). The facts are grouped into their respective seasons along with the aggregates requested. 

For example, assume that the facts (D₅, Prod₁, Sp₁, Cust₁, 10, 50$), (D₂₈, Prod₁, Sp₁, Cust₁, 15, 75$), (D₁₉, Prod₂, Sp₁, Cust₁, 8, 80$), and (D₃₅, Prod₂, Sp₁, Cust₁, 5, 50$) are the only ones that are part of the first season of salesperson Sp₁ in region R₂, a season that takes place between day D₁ and day D₄₀. When applying the Spatial_Season operator with the aggregate list {SUM(Sale_value)} the operator generates two facts: (S₁, Prod₁, Sp₁, Cust₁, 125$) and (S₁, Prod₂, Sp₁, Cust₁, 130$). This indicates that the salesperson Sp₁ sold in his first season (S₁) in region R₂ to customer Cust₁, 125$ of the product Prod₁ and 130$ of the product Prod₂. 

We define the following syntax for the operator Spatial_Season: Spatial_Season_{SQW, GC, AL}(C) = C’, where: i) SQW (Spatial Query Window): is the spatial query window, e.g., region R₁ in Figure 2 and region R₂ in Figure 3, ii) GC (Geographic Context): is a path expression that indicates the geographic context. The expression is formed by the level names separated by dots, it starts with the bottom level of one dimension and ends with a spatial level of the same dimension, e.g., Salesperson.Store and Salesperson.Store. City are valid geographic contexts, iii) AL (Aggregate List): is a list of elements af(m) where af is an aggregate function such as SUM, MAX, COUNT and m is a measure, e.g., {SUM (Sale_value), MAX(Units_sold)}. Each af(m) generates a measure with name afm, e.g., the previous list generates the names SUMSale_value and MAXUnits_ sold, iv) C (Cube): is the cube on which the Spatial_Season operador is applied, and v) C’: is the resulting cube. 

Note that our operator takes a cube (C) as an argument and returns a new cube (C’), thus facilitating its integration into a multidimensional query language, and enabling the composition of queries and the integration of their results, see Section 4. 

The corresponding schema for the resulting cube C’ is generated as follows: i) we preserve all the dimensions of the schema of the original cube (C) except the TIME dimension, ii) the TIME dimension is replaced by a SEASON dimension with a homonymous level with attributes SStart, SEnd, and SNumber, and iii) the measures are generated from the aggregate list as explained in iii) in the previous list. 

Figure 6 outlines the original and the resulting schema generated by Spatial_Season and in Table 1 we outline an algorithm to generate the resulting cube C’. We describe how C is transformed into C’; however, in an actual implementation C should remain intact.

Consider, e.g., the facts of Table 2 corresponding to the schema of Figure 1 and region R₁ of Figure 2. Suppose the first season of Sp₁ in stores that are inside R₁ is [D₁, D₂₂₅]. The results of the operation Spatial_Season_{R1, Salesperson.Store, {SUM(Sale_value)}}(Sales) are shown in Table 3.

The results of Table 3 show, e.g., that Sp₁ sold during his first season in R₁, that elapsed between day D₁ and day D₂₂₅, a total of $ (70 + … + 50) of Prod₂ to Cust₂. 

4. Spatial season queries examples 

In order to access the attributes of levels, we propose the notation LevelName@Attribute Name, e.g., Season@SNumber, Salesperson@ Salary; since Datta’s language lacks this feature.

5. Conclusions and future work

As future work, we plan to incorporate our operator in a multidimensional query language such as MDX [26], a language which in recent years has become a de facto standard to query multidimensional data. However, in principle there are two drawbacks that ought to be considered: first MDX has no spatial features and second MDX does not support temporal relationships between levels.

On the other hand, the temporality that exists between two levels can generate a complex data type: a trajectory. For example, a salesperson rotation between the stores defines a trajectory. We believe that the management of a trajectory as a first-class concept in a data warehouse can similarly generate interesting queries, e.g., to analyze the performance of the salespersons that have followed similar trajectories, where the notion of similarity of trajectories should be defined. For example, two salesperson trajectories could be considered similar if they have in common at least 75% of stores visited. The works [27-30] are points of departure for these issues.

Finally, we plan to experiment with real data in several domains and analyze the results in order to discover trends that may be associated with spatial seasons.

References

1. Inmon W. H.: “Building the Data Warehouse”, Wiley, New York, 2005.        [ Links ]

2. Kimball R., Ross M., Thornthwaite W., Mundy J., and Becker B.: “The Data Warehouse Lifecycle Toolkit”, Wiley, New York, 2008.        [ Links ]

3. Agrawal R., Gupta A., and Sarawagi S.: “Modeling multidimensional databases”. 13^th ICDE’97, Birmingham (1997) 232-243.        [ Links ]

4. Gyssens M., and Lakshmanan L.: “A foundation for multi-dimensional databases”. 23rd VLDB’97, Athens (1997) 106-115.        [ Links ]

5. Vassiliadis P.: “Modeling multidimensional databases, cubes and cube operations”. 10^th SSDBM, Capri (1998) 53-62.        [ Links ]

6. Golfarelli M., and Rizzi S.: “A methodological framework for data warehouse design”. 1st DOLAP, Washington D.C. (1998) 3-9.        [ Links ]

7. Lehner W., Albrecht J., and Wedekind H.: “Normal forms for multidimensional databases”, 10^th SSDBM’98, Capri (1998) 63-72.        [ Links ]

8. Pedersen T. B., Jensen C. S., and Dyreson C. E.: “A foundation for capturing and querying complex multidimensional data”. Information Systems, Vol. 26, No. 5 (2001) 383-423.        [ Links ]

9. Jensen C. S., Kligys A., Pedersen T. B., and Timko I.: “Multidimensional data modeling for location-based services”. 10^th GIS 2002, McLean (2002) 55-61.        [ Links ]

10. Timko I., Dyreson C. E., and Pedersen T. B.: “Probabilistic Data Modeling and Querying for Location-based Data Warehouses”. 17^th SSDBM, Santa Barbara (2005) 273-282.        [ Links ]

11. Kumar N., Gangopadhyay A., Bapna S., Karabatis G. and Chen Z.: “Measuring interestingness of discovered skewed patterns in data cubes”. Decision Support Systems, Vol. 46, No. 1 (2008), 429-439.        [ Links ]

12. Jarke M., Lenzerini M., Vassiliou Y., and Vassiliadis P.: “Fundamentals of Data Warehouses”, Springer, New York, 2003.        [ Links ]

13. Torlone R.: Conceptual multidimensional models. In: M. Rafanelli (ed), Multidimensional Databases: Problems and Solutions. Idea Group, USA(2003), 69-90.        [ Links ]

14. Moreno F., Arango F., and Fileto R.: “Season queries on a temporal multidimensional model”. 11^th IM2, Valencia (2009) to appear.        [ Links ]

15. Malinowski E., Zimányi E.: “Advanced Data Warehouse Design: from Conventional to Spatial and Temporal Applications”, Springer, New York, 2008.        [ Links ]

16. Golfarelli M. and Rizzi S.: “A survey on temporal data warehousing”. International Journal of Data Warehousing and Mining, Vol. 5, No. 1 (2009), 1-17.        [ Links ]

17. Rao F., Zhang L., Yu X., Li Y. and Chen Y.: “Spatial hierarchy and OLAP-favored search in spatial data warehouse”. 6^th DOLAP, New Orleans (2003), 48-55.        [ Links ]

18. Shekhar S., Lu C. T., Tan X. and Chawla S.: Map cube: a visualization tool for spatial data warehouses. In: H. J. Miller, J. Han (eds), Geographic Data Mining and Knowledge Discovery. Taylor and Francis, USA(2001), 73-108.        [ Links ]

19. Chamoni P., Stock S.: “Temporal structures in data warehousing”. 1st DaWaK, Florence(1999) 353-358.        [ Links ]

20. Mendelzon A., and Vaisman A.: “Temporal queries in OLAP”. 26^th VLDB, Cairo (2000) 242-253.        [ Links ]

21. Malinowski E., Zimányi E.: “A conceptual solution for representing time in data warehouse dimensions”. 3^rd APCCM 2006, Hobart (2006) 45-54.        [ Links ]

22. Moreno F., Arango F., and Fileto R.: “A multigranular temporal multidimensional model”. 1st miproBIS, Opatija (2009) 1-6.        [ Links ]

23. Parent C., Spaccapietra S., and Zimányi E.: “Spatio-temporal conceptual models: data structures + space + time”. 7^th ACM-GIS, Kansas (1999) 26-33.        [ Links ]

24. Schneider M.: “Computing the topological relationship of complex regions”. 15^th DEXA, Zaragoza (2004) 844-853.        [ Links ]

25. Datta A. and Thomas H.: “The cube data model: a conceptual model and algebra for on-line analytical processing in data warehouses”. Decision Support Systems, Vol. 27, No.3 (1999), 289-301.        [ Links ]

26. Whitehorn M., Zare R. and Pasumansky M.: “Fast Track to MDX”, Springer, New York, 2006.        [ Links ]

27. Brakatsoulas S., Pfoser D. and Tryfona N.: “Modeling, storing, and mining moving object databases”. 8^th IDEAS, Coimbra (2004) 68-77.        [ Links ]

28. Orlando S., Orsini R., Raffaeta A. and Roncato A.: “Trajectory data warehouses: design and implementation issues”. Journal of Computing Science and Engineering, Vol. 1, No. 2 (2007), 211-232.        [ Links ]

29. Marketos G., Frentzos E., Ntoutsi I, Pelekis N., Raffaetà A. and Theodoridis Y.: “Building real world trajectory warehouses”. 7^th MobiDE’08, Vancouver (2008) 1-8.        [ Links ]

30. Spaccapietra S., Parent C., Damiani M. L., Fernandes de Macêdo J. A., Porto F. and Vangenot C.: “A conceptual view on trajectories”. Data & Knowledge Engineering, Vol. 65, No. 1 (2008), 126-146.        [ Links ] ]]> 1 2005 2 2008 3 1997 Birmingham 232-243 4 1997 Athens 106-115 5 1998 Capri 53-62 6 1998 Washington D.C 3-9 7 1998 Capri 63-72 8 2001 26 5 5 383-423 9 2002 McLean 55-61 10 2005 Santa Barbara 273-282 11 2008 46 1 1 429-439 12 2003 13 2003 69-90 14 2009 Valencia 15 2008 16 2009 5 1 1 1-17 17 2003 New Orleans 48-55 18 2001 73-108 19 1999 Florence 353-358 20 2000 Cairo 242-253 21 2006 Hobart 45-54 22 2009 Opatija 1-6 23 1999 Kansas 26-33 24 2004 Zaragoza 844-853 25 1999 27 3 3 289-301 26 2006 27 2004 Coimbra 68-77 28 2007 1 2 2 211-232 29 2008 Vancouver 1-8 30 2008 65 1 1 126-146