Reporting at all levels master detail

Hello,

I have a purchase order headers, lines, and distributions table. There are cases where a header doesn't have a line and/or distribution but the users want to report on all pos regardless missing data.

Example:

PO# Line# description project amt
1234 1 test1 3Afds 10
1234 2 test2 3Afds 20
1234 3 test3 null 0 <-- no dist line
1235 null null null 0 <--no line or dist

Should I create one fact table that includes all rows at the header, line, and distribution and just put null and zeros where not applicable? Thanks!

No.

You would typically have two facts (lines and distributions) and put header information into various dimensions. You carry those dimensions in both facts. Each fact would also have additional dimensions specific to that fact.

Thank you! But what if the users really want to view the data this way:

PO# Line# description project amt
1234 1 test1 3Afds 10
1234 2 test2 3Afds 20
1234 3 test3 null 0 <-- no dist line
1235 null null null 0 <--no line or dist

queenie680 wrote:Thank you! But what if the users really want to view the data this way:

PO#  Line# description project amt
1234 1       test1         3Afds    10
1234 2       test2         3Afds    20
1234 3       test3         null       0    <-- no dist line
1235 null    null           null       0   <--no line or dist

Queeenie680,

Can't you use left outer join with dimension on left and fact on right , it will make sure you get all the things from dimension.
I am not sure if I understood you questions but this is what I think

You mean I would join two facts the line and distribution with an outer join to the header dimension?

The user needs to distplay all rows from header to line to distribution in one listing like the one I typed above. The problem is sometimes there are headers with no lines and lines with no distributions etc. but they still want to see it.

queenie680 wrote:You mean I would join two facts the line and distribution with an outer join to the header dimension?

The user needs to distplay all rows from header to line to distribution in one listing like the one I typed above. The problem is sometimes there are headers with no lines and lines with no distributions etc. but they still want to see it.

Header - > dimension
Line - > Fact
Distribution - > Fact

What I am saying is this

( Dimension left join with Line ) Left join with Distribution.

I am not talking about fact to fact join.

Lines and distributions must be in different fact tables because they are at different levels of detail. How the user sees it is a matter of the query. You can construct a query to present the data any way you want.

Thank you. You mean:

LINE(fact) -->HEADER(dim)<---DIST(fact)

Join the two facts through the dimension? Would this cause performance issues? Would the performance be better just to stuff everything into one face table?

queenie680 wrote:Thank you. You mean:

LINE(fact) -->HEADER(dim)<---DIST(fact)

Join the two facts through the dimension? Would this cause performance issues? Would the performance be better just to stuff everything into one face table?

If you need all the data from dimension which I believe you do then DIM table has to be left most and then you add FACT on right one by one.
You can meet reporting requirement this way.

Hi sorry I guess I'm not understanding the joining part.

HEADER_DIM
-------------
header_key
po_number

LINE_FACT
--------------
line_key
header_key
line_number
description

DISTRIBUTION_FACT
-----------------------
dist_key
header_key
project_number
amount

--output needs to be this
PO#  Line# description project amt
1234 1       test1         3Afds    10
1234 2       test2         3Afds    20
1234 3       test3         null       0    <-- no dist line
1235 null    null           null       0   <--no line or dist

Are you saying the join should be:
SELECT po_number, line_number, description, project_number, amount
from header_dim
LEFT JOIN LINE_FACT
ON header_dim.header_key=LINE_FACT.header_key
LEFT JOIN DISTRIBUTION_FACT
ON header_dim.header_key=DISTRIBUTION_FACT.header_key ? is this right? what should be the join here? Im confused sorry!

In most cases there would not be a header dim, but rather a collection of dimensions to cover what is carried in the header (date, buyer, department, etc...). Collectively these dimensions would be much smaller than a header dim. In such a model the PO number itself is carried as a degenerate dimension in the fact table(s). Also, if all you have is a po number, there would not be a dimension table at all, just the degenerate column in the fact.

ok got the dimension part. I'm still not understanding how I tie the two fact table together to get all rows?

Summarize the two fact tables to the same level of granularity and join or union the two sets. Granularity can be represented by dimension keys and/or attributes.

But, if you get your dimensions right on the two tables, you may not need to combine them at all. For example, why would there not be a line dimension in the distribution fact? And if there was, do you really need the line fact to do a distribution query?

The thing is, your model in more relational then dimensional. A distribution is not subordinate to a line. In a dimensional model each fact table stands alone and are considered peers with a M:M relationship between them. Line would be a dimension (probably degenerate) of distribution.

Modeled right, your output should be a simple query against the distribution facts.

ok I think I got it now. Thank you!