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 ID||Call Count||FAQ key|
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?