1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2015
    Rep Power

    Question Best practices for aggregate fact table

    I'm trying to construct an agg table to capture phone call data and group by state, city and time but also want just general measures by month. I'm thinking to have this:

    month | city_id | state_id | total_calls_inbound | total_calls_outbound | total_calls

    2015-01 12 2 54 2 56
    2015-01 10 4 147 15 162
    2015-01 null null 201 17 218


    and a dimension table to easily convert city, state into their string versions and also provide other attributes (e.g. GPS coordinates).

    My questions are:
    1. I'm including 'total_calls' in the schema even thought it could easily be calculated from inbound + outbound. I did this for simplicity in a REST call, is that a bad idea?
    2. I'm adding a 'null' row to show all the calls for a given month regardless of city or state, again to simplify the client side. It adds a row and is somewhat sparse but preferrable by the developer. Acceptable practice?

  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    1. not really

    2. yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo