Multidimensional data model can be a bit counterintuitive at the beginning but you will love the flexibility and power it gives you when you get used to it. People unfamiliar with OLAP mostly do the same mistakes, when trying to work with Egeria. Here are the steps to avoid them.
The first thing that comes to mind when people start thinking on the subject they'd like to model in Egeria are quantities they would like to track.
A lemonade store owner keeps track of how much lemonade is sold, how much did he earn, prices of goods he sells and so on.
A project manager keeps track of the hours and money spent, hourly rates of his team members as well as budget of his projects.
These quantities are called measures. These numbers will be stored in the cells of the hypersheets. These are NOT the dimensions! Make a list of the measures you'd like to track in the first step. Here are some examples:
The second step is to understand the (lowest) granularity level for each of the quantities. In our examples:
These things that describe our measures are our dimensions (or axis). Every dimension consists of dimension items (or axis elements). The dimension 'month' consists of 12 months. The product dimension consists of products (SKUs) which the company sells. Some dimensions are hierarchical (like point of sales grouped by region or employees grouped by departments for example). Some complex and large dimensions can also be split into several smaller ones (for example time is mostly split into subdimensions like year, month and day).
Now make groups of measures based on their dimensionality (set of dimensions we identified in the step 2). These groups are the hypersheets. In the example above the measures are already grouped. We have 2 hypersheets (sales and prices) for 'Lemonade' and 3 hypersheets (spent effort, hourly rates and budget) for 'project management'.
Hypersheets with more than one measure will need an extra dimension to identify which measure is meant. We can call it something like 'account', 'position' or 'KPI'. This dimension will consists of one element for each measure we have in a group. Sometimes you will also want some additional elements in the 'account' dimension to store interim result for the formulas.