Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010

    Question Unanswered: Best Aggregate Fact Table Design, sparse rows?

    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
    Join Date
    Nov 2015
    1) It depends on how/where/what calculates the total as to whether is a particularly bad idea. It's probably bad if the total is calculated outside of the database because it creates an unnecessary dependency on code external to the database. From a performance standpoint it makes very little difference. You can always leave the total out of the table and add a view on top of the table which computes the total if that makes the REST call easier.
    2) Perhaps use 0 for city and state id rather than null as this gives you greater indexing options later if your table becomes large.

Tags for this Thread

Posting Permissions

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