Menu
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:
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?
241
A fact table or a fact entity is a table or entity in a star or snowflake schema that stores measures that measure the business, such as sales, cost of goods, or profit. Fact tables and entities aggregate measures , or the numerical data of a business.
A multidimensional data model is organized around a central theme, for example, sales. This theme is represented by a fact table. Facts are numerical measures. The fact table contains the names of the facts or measures of the related dimensional tables.
Additionally, any table in a dimensional database that has a composite key must be a fact table. This means that every table in a dimensional database that expresses a many-to-many relationship is a fact table. Therefore a dimension table can also be a fact table for a separate star schema.
The main difference between fact table or reality table and the Dimension table is that dimension table contains attributes on that measures are taken actually table. 1. Fact table contains the measuring of the attributes of a dimension table.
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")
95
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
