Calls Incoming / Oiutgoing and Missed in one Fact table?

Question

Calls Incoming / Oiutgoing and Missed in one Fact table?

MattieG72

SSChasing Mays

Points: 646
More actions
June 1, 2017 at 2:44 pm

#402123

Hi All
Still planning my DW and I want to ask this question. I work in a "call centre" let's say where we record incoming calls, outgoing calls and missed calls all in separate tables
but with very similar data (start / end time etc.) and maybe one or two extra fields for each type of call.
I'd like to be able to store all of this data in one fact table and have an identifier (dimension) as to the call type.
I would use the original 3 tables primary keys (incall, outcall, missedcall) as a compound key (including dimension keys) and I think that would work well.
Any thoughts or glaring omissions on this principle?
Thanks
Mattie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 1

Yes. A "fact table" is a duplication of data that must be separately maintained. It would probably be better to create what is known as a "Partitioned View" and reference that as if it were a single table.

As a bit of a sidebar, most phone systems record all calls in a single table to begin with. Why has it been split to 3 to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63487 More actions · Answer 2

Couldn't you model it like this?

CREATE TABLE CallsFact ( SurrogateCallKey INT IDENTITY , CallerID INT , CallType VARCHAR(8) , StartTime DATETIME , EndTime DATETIME , OriginalPK INT );

Then you calculate CallDuration = DATEDIFF(minute, StartTime, EndTime)
Rob Collie modeled this in his book on DAX... he creates a simple data warehouse for it. The files are available at http://www.powerpivotpro.com (I want to say it's Chapter 17).

MattieG72 SSChasing Mays Points: 646 More actions · Answer 3

pietlinden - Thursday, June 1, 2017 7:28 PM
Couldn't you model it like this?
CREATE TABLE CallsFact ( SurrogateCallKey INT IDENTITY , CallerID INT , CallType VARCHAR(8) , StartTime DATETIME , EndTime DATETIME , OriginalPK INT );
Then you calculate CallDuration = DATEDIFF(minute, StartTime, EndTime)
Rob Collie modeled this in his book on DAX... he creates a simple data warehouse for it. The files are available at http://www.powerpivotpro.com (I want to say it's Chapter 17).

Awesome, thank you.
Mattie