Grain of measure in fact table

View previous topic View next topic Go down

Grain of measure in fact table

Post  milazzo.vincenzo on Tue Sep 16, 2014 12:26 pm

Hi,

I have a doubt to ask.
In the book "The Data Warehouse Toolkit" following is wrote: "One of the core tenets of dimensional modeling is that all the measurement rows in a fact table must be at the same grain".

Oracle says following: "it is not necessary for the Measures to be all in the same grain or dimensionality, for example Sales Forecast is by Qtr, Customer, Market & Employee whereas the Revenue Forecast is by Qtr, All Customers, By region and by Brand.. "

Then the"Sales Forecast" measure isn't at the day granularity (for example) like my fact table but Qtr.
I have to create another fact table for "Sales Forecast" and any other measure which exists only for certain granularity and some dimensions?

Thanks.

milazzo.vincenzo

Posts : 4
Join date : 2014-09-16

View user profile

Back to top Go down

Re: Grain of measure in fact table

Post  nick_white on Tue Sep 16, 2014 1:24 pm

Hi - not sure where the Oracle quote comes from but if you can let me know I'll have a look at the context in which they make this statement.

However, Kimball is correct. You have to "declare the grain" of each fact table and then stick to it for all measures in that fact table. Any measures that have a different grain have to go in different fact tables. If you don't stick to this then your design will start to fall apart as when you start grouping by different dimensional attributes the aggregation for some measures wont be correct.

Regards,

nick_white

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

View user profile

Back to top Go down

Re: Grain of measure in fact table

Post  ngalemmo on Tue Sep 16, 2014 1:59 pm

A proper model would implement sales forecast and revenue forecast as two separate facts. You would combine the two by summarizing sales forecast to the same grain as the revenue forecast.

While you sometimes see semi-additive measures in a fact, they still follow the grain, an example would be a month-end account balance fact.

When measures do not follow the grain of the fact the fact table itself becomes difficult to use. You would need to apply functions to the off-grain values to make the usable in a query.

Oracle is incorrect.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Grain of measure in fact table

Post  BoxesAndLines on Tue Sep 16, 2014 2:56 pm

ngalemmo wrote:...

Oracle is incorrect.

Amazing the incompetence that is still around after all these years.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Grain of measure in fact table

Post  milazzo.vincenzo on Tue Sep 16, 2014 3:22 pm

I do not want to judge a brand such as Oracle (at least for now )
But in fact, through their BI product, namely with the administrator tool, you can model the measurements for a given level of the hierarchy. Tomorrow I'll try to share the entire presentation ppt.

Thanks 4 reply

milazzo.vincenzo

Posts : 4
Join date : 2014-09-16

View user profile

Back to top Go down

Re: Grain of measure in fact table

Post  ngalemmo on Tue Sep 16, 2014 3:49 pm

Yeah, that's all fine and good. The issue is not the layers of software that a vendor can provide to simplify the use of such a structure, but rather from a dimensional modeling point of view; should such a structure be allowed to exist? You can put anything you want into a table, but don't call it dimensional modeling.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Grain of measure in fact table

Post  milazzo.vincenzo on Wed Sep 17, 2014 2:37 am

Hi all,
I'm posting some screenshot. the last picture is that of interest:

http://i39.servimg.com/u/f39/19/00/10/31/immagi12.png

http://i39.servimg.com/u/f39/19/00/10/31/immagi13.png

http://i39.servimg.com/u/f39/19/00/10/31/immagi14.png

http://i39.servimg.com/u/f39/19/00/10/31/immagi15.png

milazzo.vincenzo

Posts : 4
Join date : 2014-09-16

View user profile

Back to top Go down

Re: Grain of measure in fact table

Post  ngalemmo on Wed Sep 17, 2014 3:50 am

OK, but the last slide does not imply all of the measures are in the same fact table. The diagram is a bus matrix. Each row represents a different fact table. What they are showing is consistent with standard dimensional modeling practice.

What is confusing is when hierarchies are involved. For example, the revenue forecast and customer. It is possible you may have a single revenue forecast fact with rows at the segment level and others at the customer level, but is can make for some odd results. If there is a forecast for a segment as well as some customers in the segment, what would happen if you used a hierarchy bridge to sum by segment? It would include the sum of the segment and all customers in the segment that had forecasts. The numbers may or may not be correct. It is quite possible it may be implemented with two facts. They simply do not go into the actual data model.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Grain of measure in fact table

Post  milazzo.vincenzo on Wed Sep 17, 2014 7:54 am

thanks for reply.

milazzo.vincenzo

Posts : 4
Join date : 2014-09-16

View user profile

Back to top Go down

Re: Grain of measure in 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