Identifying fact grain

View previous topic View next topic Go down

Identifying fact grain

Post  sr123 on Mon Mar 05, 2012 6:38 pm

I'm trying to build a dimensional model for a not-so transactional system and am having trouble identifying fact grain. The source system is sort of an asset management system with entities like servers, location (Data center locations), network gear, product (an internal company system that is hosted on 1 of these servers), business group within the organization that uses these products etc,. It seemed to me that I needed to have more than 1 fact table, mainly because some of these dimensions are pretty disparate, they cannot be lined up on the same record (as foreign keys in the fact table). But going by this approach is going to end up in at least 4 (or more) fact tables! And more importantly I cannot identify the grain, so really my fact tables are in place in order for the end users to be able to run queries joining some of these tables..Is this approach right? Any thoughts and help is highly appreciated!

Thanks.

sr123

Posts : 10
Join date : 2012-03-05

View user profile

Back to top Go down

Re: Identifying fact grain

Post  BoxesAndLines on Tue Mar 06, 2012 10:47 am

What are you measuring?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Identifying fact grain

Post  sr123 on Tue Mar 06, 2012 12:57 pm

All that the business wants from this model is to be able to report on counts, number of servers against a given location, number of products on a server etc,. That and they definitely want historical data, because they really miss having the capability to compare counts between last month and the current month and so on.

sr123

Posts : 10
Join date : 2012-03-05

View user profile

Back to top Go down

Re: Identifying fact grain

Post  BoxesAndLines on Tue Mar 06, 2012 1:13 pm

So your grain would be server and product. Location is simply a dimension. Since you want trending, a daily/weekly/monthly snapshot is in order.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Identifying fact grain

Post  sr123 on Tue Mar 06, 2012 2:30 pm

Thank you! I was thinking on those lines too, but there are quite a few of those dimensions and I am almost ending up with 4-5 fact tables going that route (combining 3-4 logically related dimensions into a fact, with these dimensional keys sometimes repeating across facts) not to mention that there's not a lot of difference between my dimensions and facts conceptually, other than the fact that I don't have to have all dimensional attributes in the fact tables.

sr123

Posts : 10
Join date : 2012-03-05

View user profile

Back to top Go down

Re: Identifying fact grain

Post  sr123 on Tue Mar 06, 2012 2:34 pm

You mean server and product as 2 different fact tables right?

sr123

Posts : 10
Join date : 2012-03-05

View user profile

Back to top Go down

Re: Identifying fact grain

Post  BoxesAndLines on Tue Mar 06, 2012 2:47 pm

No. Why would I want two fact tables? Put it all in one.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Identifying fact grain

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