OLAP technology is distinguished by its ability to process datasets quickly even when they are huge as all public libraries in your country combined. It is possible in large part due to a specific OLAP data structure as a cube. It consists of:
- measures and its groups;
Today we’ll take a closer look at measures and dimensions and figure out what they are and how they impact on the way the information is processed.
What are dimensions
The main difference between a dimension and a measure is that the first one forms the data structure, and the second one is responsible for providing users with numeric values used when creating reports.
A dimension takes the highest level in the OLAP datacube structure. A dimension is the metadata element, which describes the core economic indicators of enterprise’s activity, for example, products, customers, sales region, and period of time etc. Dimensions help in data grouping and filtering on various grounds. Then this information can be detailed into other hierarchies, and that is how you can obtain an in-depth business data analysis. For example, dimension ‘Country’ is detailed to “Region”, then - to “Town” etc.
How does it work in practice? Imagine that your co-worker or boss needs a simple summary of graphic cards sales for 2017. Due to OLAP application an analyst can detail the report and obtain the information for the more narrow duration of sales. Also it can be useful to know how the beginning of sales of new cheap and revolutionary powerful model affects the sales of traditional graphic cards by well-known brands. Thanks to this information the analyst can track market trends promptly and correct company’s business development strategy if needed.
Precisely dimensions, interacting with measures, make it possible to create necessary planar and voluminous data slices in reporting. While using Ranet OLAP, you can also make a graphic chart. Like report creation it will take a few minutes.
All dimensions in Ranet OLAP are displayed in alphabetical order in Metadata Tree. To view all dimension hierarchies a user should click left mouse button on the icon of the required element.
Do you know, that up to 94% of manually created reports contain numeric and factual mistakes? You can avoid inaccuracies in reports just by starting using Ranet OLAP. It not only helps in making excellent reports but also enables you to spend less time on routing actions.
What are measures
By definition, each cube must have at least one measure. In practice, one cube contains several, sometimes even hundreds of measures.
In comparison to dimensions, measures don’t submit to hierarchies. Most often, a measure is a numeric value by which the dimension is detailed or aggregated. It specifies a certain dimension and answers the “How much…?” question. For example, information about totaled bike sales is set out in one of measures.
Namely measure is what BI apps users are mostly looking for because numeric values form the basis of reports. The absence of hierarchies doesn’t prevent quick values calculations. You can use aggregation and granularity options, such as: Sum, Min, Max, Count etc. For making more complex calculations you can use MDX. It is a set of expressions, which helps create a compound formula for obtaining analytical data. MDX in Ranet OLAP works in two modes: the first is for users with no technical background, the second is for advanced users, for instance, programmers.
Measures in the cube are clustered according to basic fact tables. In other words, facts contain numeric values for data warehouse, and measure groups contain measures for OLAP cube in the same way. Measure groups can be arranged in the following ways:
- Measures of the same granularity level are grouped into one set.
- Measures from one fact table can be qualified as a measure group.
- In certain contexts measures can proceed from several fact tables.
A measure group has a list of partitions, which contain isolated sets of information, and aggregations. An aggregation is a ready-to-use dataset. In this case values have been previously calculated for each of measure groups. It is done to increase queries execution productivity. Measure grouping itself helps rationalize processing of users’ queries.
Measure Group as an object contains the following information: the name of the group, storage mode, and processing mode.
Follow the link and begin your introduction to manipulating the cube data using Adventure Works demo databases.