Feeds

Hands on with MDX

Dimension and measures (yes, but can it handle cubic furlongs?)

Secure remote control for conventional and virtual desktops

Following our introduction to MDX (to be found here) this follow-up article is a get-you-started guide to using this powerful language to manipulate multi-dimensional data.

The basics

Relational databases store data in two-dimensional tables, a familiar concept that mimics grids of data on paper. Multi-dimensional data is fundamentally different and is stored as dimensions and measures. Dimensions describe the data: examples are Products (the items we sell), Location (where sales take place) and Time. Measures are usually numeric, like Units Sold, Sales and Budget.

We can see the principle of multi-dimensional data in a very simple two-dimensional structure. Here there are two descriptive dimensions – Products in the rows and Time in the columns. There is one numerical measure, Units Sold.

Units Sold

Jan Feb Mar Apr
Apples 125 45 78 169
Figs 74 53 89 39
Lemons 20 21 25 14
Pears 220 74 103 156

But, unlike tables in a relational database, we aren’t limited to two dimensions. Add a third – Location - and we get a 3-D cube of data.

As before the rows show Products and the columns show Time. The slices that give the cube depth show the additional Location dimension. The values held at the intersections of all three dimensions are the Units Sold. (For simplicity only four values are shown). The green cell shows the number of lemons sold in Chicago in April.

Dimensions contain members and here each dimension has four: Chicago, London, Paris and New York are the four members in the Location dimension.

A multi-dimensional data structure can have many more dimensions, and this is where any visualisation technique breaks down. We humans live in a 3-D world and imagining further dimensions is hard: however, the cube analogy is a good starting point for understanding the principle behind multi-dimensional structures. (Not entirely coincidentally, multi-dimensional OLAP structures are commonly called cubes).

Hierarchies, levels and aggregations

Dimensions are often hierarchical which enables them to hold data at different levels. A Time dimension is almost invariably hierarchical and typical levels might be years, quarters and months.

Hierarchies usually have an All level at the top to give a grand total of all data, with levels below. In this case the Year level contains members for 2006 and 2007, each of these is divided into four quarters and each quarter into three months. (Not all the months are shown.) This hierarchy lets us look at the Units Sold measure for each time period: all fig sales in a particular year, quarter or month.

During the cube-building process, data for the lowest level, called the leaf level (monthly values here but could be weekly, daily, whatever) is combined to give the higher values, quarters combine into years, and years into All. These calculated values are called aggregations.

Beginner's guide to SSL certificates

More from The Register

next story
Microsoft to bake Skype into IE, without plugins
Redmond thinks the Object Real-Time Communications API for WebRTC is ready to roll
Mozilla: Spidermonkey ATE Apple's JavaScriptCore, THRASHED Google V8
Moz man claims the win on rivals' own benchmarks
Microsoft promises Windows 10 will mean two-factor auth for all
Sneak peek at security features Redmond's baking into new OS
FTDI yanks chip-bricking driver from Windows Update, vows to fight on
Next driver to battle fake chips with 'non-invasive' methods
DEATH by PowerPoint: Microsoft warns of 0-day attack hidden in slides
Might put out patch in update, might chuck it out sooner
Ubuntu 14.10 tries pulling a Steve Ballmer on cloudy offerings
Oi, Windows, centOS and openSUSE – behave, we're all friends here
Apple's OS X Yosemite slurps UNSAVED docs into iCloud
Docs, email contacts... shhhlooop, up it goes
Was ist das? Eine neue Suse Linux Enterprise? Ausgezeichnet!
Version 12 first major-number Suse release since 2009
prev story

Whitepapers

Cloud and hybrid-cloud data protection for VMware
Learn how quick and easy it is to configure backups and perform restores for VMware environments.
Getting started with customer-focused identity management
Learn why identity is a fundamental requirement to digital growth, and how without it there is no way to identify and engage customers in a meaningful way.
Reg Reader Research: SaaS based Email and Office Productivity Tools
Read this Reg reader report which provides advice and guidance for SMBs towards the use of SaaS based email and Office productivity tools.
Intelligent flash storage arrays
Tegile Intelligent Storage Arrays with IntelliFlash helps IT boost storage utilization and effciency while delivering unmatched storage savings and performance.
The next step in data security
With recent increased privacy concerns and computers becoming more powerful, the chance of hackers being able to crack smaller-sized RSA keys increases.