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 ID | Call Count | FAQ key
|
|---|
| ABC | 1 | 123 |
| DEF | 1 | 456 |
|
|
|
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
|
|---|
| 123 | 1 | Store Location FAQ |
| 456 | 2 | Store Location FAQ |
| 456 | 3 | Store 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
