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


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


      FAQ Key
      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?