0 Replies Latest reply: Feb 12, 2013 9:14 AM by Steve Taschereau RSS

    Seeking best practice for joining (multiple key?) tables.

      First let me apologize in advance for my inaccurate terminology, I'm not a DB person.

       

      That said, here's what I'm trying to accomplish and I would appreciate some experienced best-practice feedback.

       

      I have 2 tables, a "call fact" table that records phone calls and an FAQ table that records a list of automated FAQs that were played to the caller.

       

      The tables look something like this:

       

      Call Fact:

       

      Call IDCall CountFAQ key
      ABC1123
      DEF1456



       

      FAQ Table (I believe the ID is auto-generated, thus allowing a single call above to link to multiple FAQs)

       

      FAQ Key
      ID
      FAQ Name
      1231Store Location FAQ
      4562Store Location FAQ
      4563Store Hours FAQ

       

      What I ultimately want to be able to do is create a chart which displays total number of calls for each distinct FAQ Name.

       

      Right now I'm using a "straight forward" join of the two tables, that seems to be okay. However I am having a little trouble with the

      chart which makes me think maybe there's a more appropriate way to join the tables. The "trouble" I'm having is that I created a

      chart with a dimension of [FAQ Name], and expected that I could just use =sum([Call Count]) as the expression. However if I

      want the numbers to come out correctly, I have to use =count(DISTINCT [Call ID]) which, I believe, is not as efficient as sum([Call Count])

       

       

      So question 1 is what is the best way to join these tables in my data model?

      And question 2 is, what is the best dimension/expression for me to chart the desired metric?

       

      Thanks,

      Steve