Data Model Question - Fat or Thin Fact table?

[mjamesking]

Hello,

Am designing a data model and am torn between two approaches for the fact table. Am wondering if anyone has any advice or preferences (or even an alternate suggestion).

Essentially I want to measure the balance of a client account on a daily basis, and also look at the number and value of orders by type (deposits, withdrawls) made each day.

Is my best approach to have:

1. a single row per account per day in the fact table, with a number of different measures on each row

e.g.

Date	Account	Balance	Deposit #	Deposit Value	Withdrawal #	Withdrawal Value
03/07/2016	1	$100	2	$5	1	$10
03/07/2016	2	$300	3	$23	2	$5

or

2. Multiple fact rows per account each day, containing the result for each particular measure

e.g.

Date	Account	Fact Type	Fact Value
03/07/2016	1	Balance	100
03/07/2016	1	Deposit #	2
03/07/2016	1	Deposit Value	5
03/07/2016	1	Withdrawal #	1
03/07/2016	1	Withdrawal Value	10

Bearing in mind the number of facts per account per day could grow and grow?

Any thoughts?

[oknotsen]

Go with option 1. Will be a lot easier for your front-end developers to get the right numbers to show without having to work with unneeded complex expressions.

Next to that, the way that Qlik handles the data I think option 1 will be better for performance (though I doubt you will notice much different the first 10 million rows).

[mjamesking]

Thanks Onno

and if I wanted to also track certain Account 'events' such as the date an account was opened or closed would it be valid to concatenate these event facts to the table, leaving most other columns blank?

e.g.

Date	Account	Balance	Deposit #	Deposit Value	Withdrawal #	Withdrawal Value	Account Opened	Account Closed
01/07/2016	1						1
02/07/2016	1	100	2	$100	0	$0
03/07/2016	1	0	0	$0	1	$100		1

again, there may be a good few more of these event based facts

Cheers

[oknotsen]

Don't you already have that information by the minimum date and the maximum date an account has facts for?

If not, you might just want to model this information in on a separate fact table. Having a central LinkTable with your MasterCalendar connecting two facts with your dimensions.

[mjamesking]

For open and close, yes, but I do have a number of other account based events that are not as obvious and can occur at any time between an account coming into existence and an account being closed - for example, interactions, account application dates.

So - on that basis, go with the event facts in a separate table with an entry per event and an event type?

[johnw]

I would, yes. An accounts table for facts about the account. An events table for the events that occur on that account. Technically open and close are events that you could store on the events table, but if they only occur once in the life of any account (if reopened it is reopened with a different number), then I would likely just store them on the accounts table. But if you can close and reopen the same account number, then I'd probably keep them on the events table.

I'm not clear why deposit # and deposit value are considered separate events. Those seem like two facts about a single event, a deposit, in which case I'd have a single row on the events table. And I suppose my fields would be [Number] and [Value] since the event type tells you what the number and value mean.

Hard to say without really knowing your data and reporting requirements well, but those are my initial thoughts.

[marcus_sommer]

It's a very interesting question then I use both types of fact-tables in different applications and have never checked which type would be perform better. I didn't want to play with my own applications which I couldn't share here and therefore created a small example-application with a variable record-number on randomly created values.

By each run of the script the data will be change and the model is very simple - only the fact-table, no dimension-tables - and it won't cover every possibility and each aspect and both types are included in this example - simply commented the other part out. To get a tendency it should be enough.

I have had run the script with 10 M records and checked the filesizes and the open-times for the pivots (within the document properties) and the results are quite clear. The fat-table need 98 MB and the thin-table 264 MB by the filesize (factor from about 2.5) and the opening-times differ from 312 to 1560 milliseconds (factor from about 5).

I think with other and more complex datamodels the differences could be a bit lower or higher but the tendency that the fat-table performs better will be remain and therefore the suggestion from Onno is right.

- Marcus

[marcus_sommer]

It's a very interesting question then I use both types of fact-tables in different applications and have never checked which type would be perform better. I didn't want to play with my own applications which I couldn't share here and therefore created a small example-application with a variable record-number on randomly created values.

By each run of the script the data will be change and the model is very simple - only the fact-table, no dimension-tables - and it won't cover every possibility and each aspect and both types are included in this example - simply commented the other part out. To get a tendency it should be enough.

I have had run the script with 10 M records and checked the filesizes and the open-times for the pivots (within the document properties) and the results are quite clear. The fat-table need 98 MB and the thin-table 264 MB by the filesize (factor from about 2.5) and the opening-times differ from 312 to 1560 milliseconds (factor from about 5).

I think with other and more complex datamodels the differences could be a bit lower or higher but the tendency that the fat-table performs better will be remain and therefore the suggestion from Onno is right.

- Marcus

[marcus_sommer]

It's a very interesting question then I use both types of fact-tables in different applications and have never checked which type would be perform better. I didn't want to play with my own applications which I couldn't share here and therefore created a small example-application with a variable record-number on randomly created values.

By each run of the script the data will be change and the model is very simple - only the fact-table, no dimension-tables - and it won't cover every possibility and each aspect and both types are included in this example - simply commented the other part out. To get a tendency it should be enough.

I have had run the script with 10 M records and checked the filesizes and the open-times for the pivots (within the document properties) and the results are quite clear. The fat-table need 98 MB and the thin-table 264 MB by the filesize (factor from about 2.5) and the opening-times differ from 312 to 1560 milliseconds (factor from about 5).

I think with other and more complex datamodels the differences could be a bit lower or higher but the tendency that the fat-table performs better will be remain and therefore the suggestion from Onno is right.

- Marcus

[marcus_sommer]

It's a very interesting question then I use both types of fact-tables in different applications and have never checked which type would be perform better. I didn't want to play with my own applications which I couldn't share here and therefore created a small example-application with a variable record-number on randomly created values.

By each run of the script the data will be change and the model is very simple - only the fact-table, no dimension-tables - and it won't cover every possibility and each aspect and both types are included in this example - simply commented the other part out. To get a tendency it should be enough.

I have had run the script with 10 M records and checked the filesizes and the open-times for the pivots (within the document properties) and the results are quite clear. The fat-table need 98 MB and the thin-table 264 MB by the filesize (factor from about 2.5) and the opening-times differ from 312 to 1560 milliseconds (factor from about 5).

I think with other and more complex datamodels the differences could be a bit lower or higher but the tendency that the fat-table performs better will be remain and therefore the suggestion from Onno is right.

- Marcus

[mjamesking]

Thanks for everyone's input.

To clarify a little about the nature of the data and the required methods of viewing.

1. I have some data that is known only once per day, for example the Account Balance. It isn't as simple as a running total of deposits minus withdrawals as it fluctuates with the share price of holdings.

2. I also have some data that I want to track that may occur more than once per day, or not at all. For instance a deposit or a withdrawal. I want to be able to track the value of these as well as the volume of them. Each of these transactions/ events also has a 'payment method' which I want to be able to break these things down by.

3. As well as these deposits and withdrawal transactions I also have some other account related things I want to count and track, for instance the number of contacts made to client services about a particular account, and break these contacts down by the 'nature' of the contact.

The data volumes are not huge currently, but I do want this to be scalable.

Having thought some more, and read all of you posts.. .then thought again, i'm leaning to the thinner fact table, because of the amount of different types of thing that I need to count.

So a dimension table for each account including all the account detail I require.

A single, thinner (realistically a mid-size) fact table containing a 'Fact Type' column and a fact Fact Type specific columns that would be redundant for other fact types (such as payment method, contact nature but keep these to a minimum).

Anyone disagree? (brave question to ask I know )

[mjamesking]

Thanks for everyone's input.

To clarify a little about the nature of the data and the required methods of viewing.

1. I have some data that is known only once per day, for example the Account Balance. It isn't as simple as a running total of deposits minus withdrawals as it fluctuates with the share price of holdings.

2. I also have some data that I want to track that may occur more than once per day, or not at all. For instance a deposit or a withdrawal. I want to be able to track the value of these as well as the volume of them. Each of these transactions/ events also has a 'payment method' which I want to be able to break these things down by.

3. As well as these deposits and withdrawal transactions I also have some other account related things I want to count and track, for instance the number of contacts made to client services about a particular account, and break these contacts down by the 'nature' of the contact.

The data volumes are not huge currently, but I do want this to be scalable.

Having thought some more, and read all of you posts.. .then thought again, i'm leaning to the thinner fact table, because of the amount of different types of thing that I need to count.

So a dimension table for each account including all the account detail I require.

A single, thinner (realistically a mid-size) fact table containing a 'Fact Type' column and a fact Fact Type specific columns that would be redundant for other fact types (such as payment method, contact nature but keep these to a minimum).

Anyone disagree? (brave question to ask I know )

[johnw]

I think I agree. I might potentially keep contacts (which don't involve money, I gather) and transactions (anything that does involve money) separate. But I'd probably combine them since they're both activities involving the account. I might have both a generic [Value] field and redundant fields like [Account Balance], [Deposit Amount], and [Withdrawal Amount]. But I might well stick with the single [Value] field and use set analysis to look at specific fact types.

[johnw]

I think I agree. I might potentially keep contacts (which don't involve money, I gather) and transactions (anything that does involve money) separate. But I'd probably combine them since they're both activities involving the account. I might have both a generic [Value] field and redundant fields like [Account Balance], [Deposit Amount], and [Withdrawal Amount]. But I might well stick with the single [Value] field and use set analysis to look at specific fact types.