US and Worldwide: +1 (866) 660-7555
Results 1 to 3 of 3

Thread: Measures based on Dimension Column

  1. #1
    Join Date
    Apr 2008
    Posts
    2,447

    Default Measures based on Dimension Column

    Ok, I'm feeling REALLY n00b-ish here, because I can't seem to think my way out of this.

    Assume a fact table (With Degenerate Dimensions):
    Order# Placed Shipped PaidBy Client
    1 2014-05-01 2014-05-03 Cash A
    2 2014-05-01 2014-05-07 MC B
    3 2014-05-02 2014-05-03 Visa A
    4 2014-05-03 2014-05-09 Cash C

    And that you want to be able to have output:
    Date OrdersPlaced OrdersShipped
    2014-05-01 2 0
    2014-05-02 1 0
    2014-05-03 1 2
    etc.

    But also:
    Client
    A B C
    OrdersPlaced 2 1 1
    OrdersShipped 2 1 1

    And also:
    Client
    Method A B C
    Cash 1 1
    Visa 1
    MC 1

    How do I structure my schema?

    I thought about converting it to a snowflake, but then I'd end up with two fact tables - Payments and OrdersByTime

    Am I missing something really simple, or do we actually have to do that restructure, and then use 2 Cubes & a Virtual?
    Last edited by gutlez; 05-28-2014 at 06:02 PM.

  2. #2
    Join Date
    Jan 2013
    Posts
    448

    Default

    It seems like you're getting in trouble trying to use two separate dates a single attribute. What if you used a finer grain, and captured placed / shipped events as separate fact rows? Use a single Date field, with a new attribute with values (Placed|Shipped).

  3. #3
    Join Date
    Apr 2008
    Posts
    2,447

    Default

    Yeah,

    That's exactly the issue I'm running into.
    I think I could convert the data into something along the lines of
    Order# Status Date
    1 Placed 2014-05-01
    1 Shipped 2014-05-03

    But I'm struggling with making this table really wide to attach the payment type, and customer as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •