Hi,

I am trying to set up an orders datamart, which would be fed from our ERP
daily. Requirement is to know at any given time what are the bookings, what
is the backlog and how much has been billed. I am trying to create a
dimensional model. My structure is as follows.


Table 1 Order: OrdID, CustID, EmployeeID, Country

Table 2 Products: ProdID, ProdLine

Table 3 Customers CustID, CustName

Table 5 Employees: Employee ID, EmployeeName

Table 5 Fact Table: Ord ID, ProdID, CustID,EmployeeID, Order Date, Invoice
Date, OrderQty, InvoicedQty, BacklogQty,Cost/Unit, Selling PricePer Unit

My problem is that for the orders that are unshipped would have a null date.
I was thinking of having two fact tables one for Booking and Backlog and
another one for Billing to avoid the null date problem or Is there a way to
do this with one fact table.

Any help would be greatly appreciated.

Thanks

Ather

It depends of your OLAP server Microsoft, Oracle, Cognos, Hyperion....
Not all products around support more than one fact table.

I have set up a quite similar system on MSAS using the standard
edition of
Analysis Services for a client. In that case I would have prefered two
fact tables, but as the Standard Editon of SQL server MSAS does not
support more
than one partition I had to merge the order table and the billing
table into on fact table.

In order to separate the data I created a new dimension
"BillingStatus" with one member representing "billed" and another
"order" as there is of course
different dates on the invoice and the delivery date.

Re
BT





"AM" <am (AT) nowhere (DOT) com> wrote