CSCI312 Big Data Management

Intuitive design of a data cube from a functional specification of operational database A train company has an operational database with information about the daily train trips between the cities located in the same or different countries. The company would like to implement a data warehouse that can be used to implement the following applications. (i) find the total number of kilometers made by trains in a given year, departing from the stations locating in a given country and arriving at the stations located in a given country.

(ii) find the total duration of international trips in a given year, that is, trips departing from a station located in a country and arriving at a station located in another country,

(iii) find the total number of trips that departed from or arrived at a given city in a given month of a given year '

(iv) find and average duration of train trips in a given country in a given year,

(v) for all trips in a given year, find an average number of passengers on a trip.

(vi) find an average number of passengers all trips between two given city

(vii) find total number of trips per each driver. (viii) find the total number of trips that used a given train type in a given year.

(1) Use the specifications of applications listed above to find a data cube, that should be implemented by the train company to create a data warehouse. In your specification of a data cube, list the names of dimensions, hierarchies, and measures.

(2) Pick any three dimensions from a data cube found in the previous step and at least 4 values in each dimension and one measure to draw a sample three-dimensional data cube in a perspective view similar to a view included in a presentation 09 Data Warehouse Concepts, slide 6.