Special

Introducing the “Welcome to Xojo” Bundle!

New to Xojo and looking for guidance? We've put together a terrific bundle to welcome you! Xojo Bundle

This bundle includes six back issues of the magazine -- all of year 15 in printed book and digital formats -- plus a one-year subscription so you'll be learning all about Xojo for the next year. It's the perfect way to get started programming with Xojo. And you save as much as $35 over the non-bundle price!

This offer is only available for a limited time as supplies are limited, so hurry today and order this special bundle before the offer goes away!

Article Preview


Buy Now

Issue 16.5 ('Spreadsheet Databases')
Instant purchase and download via GumRoad!

COLUMN

Ragged Hierarchies in Data Marts

How to deal with hierarchies in a data mart

Issue: 16.5 (September/October 2018)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a data architect for a major fashion retail brand. But in his 23 years of IT experience, he has been everything from a PC Technician, iSeries System Administrator, iSeries Programmer, Sr. Technical Lead, Data Modeler, Data Architect, Oracle DBA, BI Consultant and Solution Architect. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 8,648
Starting Page Number: 82
Article Number: 16507
Related Link(s): None

Excerpt of article text...

In times past we have discussed hierarchies. Mostly this was in the context of how to deal with hierarchies in OLTP databases and the accompanying SQL. In this month's column we are going to touch on them again, but in the context of a data mart.

Generally speaking, you have two kinds of tables in a data mart: Facts and Dimensions. Facts are usually numeric facts. For example, you may have a FACT_SALES table that has ORDER_AMOUNT, TOTAL_ITEMS_ORDERED, SHIPPING_LOCATION_COUNT, etc.

A Dimension is where the columns that would usually be part of the WHERE clause of SQL statement would be found. For example, for a DIM_STORE table you would have columns like: STORE_NUMBER, OPENING_DATE, STORE_TYPE, DOOR_COUNT, SQUARE_FEET, etc.

There is a third kind of table that will sometimes exist in data marts and that is a Bridge table. Bridge tables are exactly what they sound like: they bridge two other tables together in order to resolve a many-to-many relationship. We will discuss examples of this shortly.

Hierarchies in a data mart will end up in a dimension table. Ralph Kimball in his book The Data Warehouse Toolkit (3rd ed., p.57) identifies the following kinds of dimensional hierarchies:

  • Fixed Depth Positional Hierarchies

...End of Excerpt. Please purchase the magazine to read the full article.