CODEWITHSUNDEEP
data-modelingdata-warehousebusiness-intelligencedimensional-modeling
Prefect73
Prefect73

Reputation: 341

BI multi-dimensional modeling: How to turn measures into dimensions?

so here´s what i plan to do: Let´s say we have a typical sales fact table with typical columns like:

Date, SalesRep, Product, Region, Units, Amount

Now, simple enough, I can sum over the Amount and roll it up against the Region like this:

Region      Sum of Revenue
------      --------------
North       56,234 $
South       48,788 $
East        38,787 $
West        15,334 $

But what if I want to have it another way, like this:

Revenue range    Region
-------------    ------
>50,000          North
>25,000          South
                 East
>10,000          West

That would mean to treat quantitative facts as a dimension and vice versa, right? But how can i transform measures into dimensions?

Any push into the right direction would be welcome.

Upvotes: 1

Views: 977

Answers (1)

Marek Grzenkowicz
Marek Grzenkowicz

Reputation: 17343

Dynamic value banding:

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/dynamic-value-banding/

A dynamic value banding report is organized as a series of report row headers that define a progressive set of varying-sized ranges of a target numeric fact. For instance, a common value banding report in a bank has many rows with labels such as “Balance from 0 to $10,” “Balance from $10.01 to $25,” and so on.

This kind of report is dynamic because the specific row headers are defined at query time, not during the ETL processing. The row definitions can be implemented in a small value banding dimension table that is joined via greater-than/less-than joins to the fact table, or the definitions can exist only in an SQL CASE statement.

The value banding dimension approach is probably higher performing, especially in a columnar database, because the CASE statement approach involves an almost unconstrained relation scan of the fact table.

Banding dimension table:

ID | RANGE_SET    | RANGE_NAME | RANGE_MIN    | RANGE_MAX
---|--------------|------------|--------------|---------------
1  | THREE_RANGES | >50,000    |  50,000.01 $ | 9,999,999.00 $
2  | THREE_RANGES | >25,000    |  25,000.01 $ |    50,000.00 $
3  | THREE_RANGES | >10,000    |  10,000.01 $ |    25,000.00 $
10 | BIG_SMALL    | Small      |       0.00 $ |   100,000.00 $
11 | BIG_SMALL    | Big        | 100.000.01 $ | 9,999,999.00 $

The column RANGE_SET allows to store a few sets of ranges in the same dimension table and is used to group the defined ranges.

Aggregated fact table:

If you are dealing with a large fact table and the dynamic approach is too resource intensive, you can load data into an aggregated fact table and use the sum of revenue to fetch an appropriate banding key.

REGION | BANDING_ID | SUM_OF_REVENUE
-------|------------|---------------
North  | 1          |       56,234 $
South  | 2          |       48,788 $
East   | 2          |       38,787 $
West   | 3          |       15,334 $

Upvotes: 1

Related Questions