Order headers and lines

Hi,

I am trying to design a data mart that will server as a data source for an Analysis cube. I would like to know the best practice for handling order headers and order lines. Do I need 2 fact tables in the data mart, and does there need to be a foreign key relationship between them.

The header table will contain information like whether a delivery was made on time, which depot delivered the order, how many times the delivery was attempted, total distribution charge, whereas the line table will contain product related information with quantities ordered / delivered, and the cost per line

Thanks

Depends on the analysis that needs to be supported, both by the data mart and the cube. For example, the FactInternetSales and FactResellerSales tables in the sample Adventure Works DW database are at the line level. But from your description, it sounde like there are relevant measures at both the header and line item level, so 2 fact tables (with 2 mesure groups) may be preferable. The detail table would then have a foreign key for the parent header table.

In terms of dimensions, those which directly relate to the detail measure group can be configured with a "many-many" relation to the header measure group (using the detail measure group and header fact dimension as intermediates). And those dimensions which directly relate to the header measure group can be configured as "referenced" (via the header fact dimension) for the detail measure group.

This article argues for a single detail-level fact table, but the AS 2005 modelling features may mitigate some of these issues:

Managing Your Parents

Be mindful of reporting needs when designing parent and child fact tables

By Ralph Kimball

...

Oracle tricks

Hi there,

We have a BI application that uses SSAS 2005 cubes. This application needs to work with either an Oracle 9i or SQL Server 2000 data mart. Unfortunately, when connecting to Oracle, the Data Source Views in the SSAS project start complaining because of the way Oracle references tables (Schema.TableName etc., compared to SQL which is just TableName).

Are there any tricks to getting a single SSAS 2005 project to work against either an Oracle 9i data mart or a SQL 2000 data mart?

We would really like to avoid having to create and maintain two SSAS projects.

Thanks,

--Phil

I have not tried this, but if you could set up a "dbo" schema in oracle it might work as SQL2000 has a "default schema" of dbo, so dbo.<tablename> should work in both.

Oracle Provider of OLEDB - Unable to process cubes

Hi all,

Our data mart resides in Oracle 9i.

I can connect the data source views fine, and explore the data in those views.

Even in the cubes themselves, I can explore the data.

But when I go to process the cubes, I get the error ...

"Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'RTM Data Mart', Name of 'RTM Data Mart'.

Anyone know why?

The provider is the Native OLE DB provider from Oracle.

Thanks,

--Phil

Check your impersonation settings, it should be 'use the service account'|||

That did the trick - thanks.

Interesting that the Data Source Views are happy enough with a fixed identity, but processing cubes wants the service account.

But I am happy to have made progress - thanks again.