Is facts table in dimensional model is actually a table of events?

Question

I'm new to dimensional data modeling and wondering how I can apply it to something that doesn't look like a sales report.

Let's say I have a web messenger. It tracks user's device, browser type and location.

Now, my colleagues from business department want to be able to tell:

• how many messenger chats occurred in Chrome last month?
• how many messenger chats happened in North America on mobile device last year?
• chats rate per day during last week (with ability to filter by browser, device and location)

So for me it looks like I want to measure chats rates, what should a facts table for that look like?

Also, should browser and device live in one or separate dimensions? I can't imagine ETL process that will build such a table.

In my current understanding schema should look like this:

It seems that every time chat created I should add it to chat_facts table, which for me looks like saving a table with events that we will count later by aggregation. Is that a correct approach to facts table?

Livelong

Answer 1

Fact tables can contain transactions, events, balances, snapshots, processes. There are even "factless" fact tables.

In your case, recording chats as events is perfectly reasonable. Besides message count, each chat record might also contain other facts such as size, duration, number of participants, etc.

Dim Details makes no sense. Instead, create dim "Device", and dim "Browser".

One thing your model is missing is dim "Date", for analytics related to dates: calendar table

(I would also rename "created" in your fact table into something more descriptive, such as "Creation Date")

Orpington

Thank you so much for the answer! – Livelong