Putting everything together in one single fact table

View previous topic View next topic Go down

Putting everything together in one single fact table

Post  revdpoel on Wed Aug 06, 2014 3:45 am

Hello

From a source system we are getting monthly the next information. I am simplifying the information for the example

office number
number of transactions week 1
number of transactions week 2
number of transactions week 3
number of transactions week 4
number of transactions between 000 and 100 euro per month
number of transactions between 101 and 500 euro per month
number of transactions > 500 per month

The information is stored in one file.
When banknumber has the value -2 it means that the information is on organization level. In other words the aggregation of all offices.

Now they made one fact table A, which looks like this

month-key
week-key
office-key
number of transactions week
number of transactions between 000 and 100 euro per month
number of transactions between 101 and 500 euro per month
number of transactions > 500 per month

When the information is about the month (week 1, 2,3 4) week-key will have the value -2 (Not Applicable) and the week-numbers will be empty
When the information is about the week (week 1, 2,3 4) the month-numbers will be empty
When the information is about the organization (week 1, 2,3 4) office-key will have the value -2 (Not Applicable)

In my opinion it is better to have 4 fact tables:
1. by week, office
2. by month, office
1. by week, organization
2. by month, organization

I find it hard to explain why the design of fact table A isn't such a good idea. Can somebody help explaining??

Thanks

revdpoel

Posts : 24
Join date : 2010-06-11

View user profile

Back to top Go down

Re: Putting everything together in one single fact table

Post  nick_white on Wed Aug 06, 2014 9:05 am

Hi,
does each week map to one, and only one, month?

If so then create a Week Dimension with a grain of week and where each week record also contains the relevant month attributes (this is just like a standard date dimension but aggregated up to the week level).
Then drop the month-key from your fact table and just use the week-key.

Fundamentally, the issue is that the grain of the table hasn't been declared and then kept to, if I have understood correctly what has been done. The grain of the fact table is either week or month and all records in that fact table must be expressed in terms of that grain - you can't mix weekly and monthly records in the same fact table.

If you want to create a table for month records (for performance reasons; your week fact table will answer all your month-based queries) then create a month dimension and aggregate your weekly fact records up to the month level and insert them into your month fact table

Regards,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Putting everything together in one single fact table

Post  revdpoel on Wed Aug 06, 2014 12:27 pm

Hai Nick

What you say is: make a weekly fact table with weekly facts and aggregate it to a monthly fact table. Clear.

My colleague says: Why bother?? I can put everything in one fact table. And when it concerns weekly numbers I will point the month-key to a 'Not Applicable' key. And when it concerns monthly numbers I will point the week-key to a 'Not Applicable' key.

I agree completely with you. But can you explain to me why not mixing up weekly with monthly records? What problems can I or the business get?

Thanks

revdpoel

Posts : 24
Join date : 2010-06-11

View user profile

Back to top Go down

Re: Putting everything together in one single fact table

Post  TheNJDevil on Wed Aug 06, 2014 4:23 pm

If the point is to provide users with any kind of self-service EVER or if your colleague ever decides to work elsewhere, then this nonstandard data model he is suggesting will only provide 2 things. Confusion and then someone having extra work to remodel and develop it into a more standard way of modeling the data.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Putting everything together in one single fact table

Post  nick_white on Wed Aug 06, 2014 5:01 pm

The short answer is why bother putting in the month key? It's actually more work to put it in than to follow the Kimball methodology.To illustrate this using cut-down tables...

Your Week Dimension (WEEK_DIM) has attributes of WEEK_KEY, YEAR_MONTH, YEAR_WEEK
Your fact table (TXN_FACT) has columns of WEEK-KEY and NUM_TRANS.

To find the number of transactions in a week:
SELECT a.YEAR_WEEK, SUM(b.NUM_TRANS)
FROM TXN_FACT b, WEEK_DIM a
WHERE a.WEEK_KEY = b.WEEK_KEY
GROUP BY a.YEAR_WEEK

To find the number of transactions in a month:
SELECT a.YEAR_MONTH, SUM(b.NUM_TRANS)
FROM TXN_FACT b, WEEK_DIM a
WHERE a.WEEK_KEY = b.WEEK_KEY
GROUP BY a.YEAR_MONTH

i.e. the same data model supports multiple types of queries
BTW Aggregates are only created for performance reasons, they don't provide additional functionality. There is no need to create a fact table aggregated at the monthly level as the weekly one provides the monthly summaries as well - as the examples above illustrate. If you had a much more complicated fact table and millions of records (and reports were frequently run grouping records by month) you might pre-aggregate the data into a monthly fact table to boost performance of monthly summarised queries.

The long answer to your question is that this is the Kimball methodology - your company either designs its data warehouse in accordance with the methodology (or the Inmon methodology or whatever other methodology you want to follow) or it doesn't, it's entirely up to you.
But not designing it in accordance with a standard methodology is a bit like designing a transactional system where the database is not in 3NF - there's nothing stopping you doing it but you'd have to be pretty confident in your abilities to ignore the last 50 years of industry best practice!

I guess the one "escape clause" to this is if you have read Kimball's book(s), fully understand his methodology, have experience of implementing it a number of times and then make a conscious decision not to follow it in a specific area of your design because your particular set of circumstances mean a different approach makes more sense then that's OK. But you would have fully analysed the situation and understood the downsides as well as the upsides of deviating from the methodology before making the design decision.
I have a reasonable level of experience in this area and I have never come across a situation where I have had to deviate from Kimball's methodology (in a non-trivial area of design).

Hope this helps when you discuss with your colleagues?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Putting everything together in one single fact table

Post  revdpoel on Thu Aug 07, 2014 1:52 am

thank you all

revdpoel

Posts : 24
Join date : 2010-06-11

View user profile

Back to top Go down

Re: Putting everything together in one single fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum