Grain of measure in fact table

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.

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,

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.

ngalemmo wrote:...

Oracle is incorrect.

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

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

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.

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

https://i.servimg.com/u/f39/19/00/10/31/immagi12.png

https://i.servimg.com/u/f39/19/00/10/31/immagi13.png

https://i.servimg.com/u/f39/19/00/10/31/immagi14.png

https://i.servimg.com/u/f39/19/00/10/31/immagi15.png

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.

thanks for reply.