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 table design

Hello,

I have an oracle table from which rows are retrieved every 10 mins. Some other application puts rows into the same table. There is a timestamp field and I am planning to use that inorder to retrieve new rows. I am concerned about transactions. There might be a point where that application inserts a row when I try to retrieve and so, I will miss that row no matter I check the timestamp. Can somebody throw some light on this?

Also, when I retrieve rows and process, there might be a situation where the processing is faulty and therefore, that row has to be fetched again sometime.

I am thinking of using a new table. Please suggest some ideas regarding my design.

P.S
I am very interested in knowing such scenarios and I would like to know how to deal with such situations at enterprise level ( I wonder what would be the design in time critical applications where multiple applications put and retrieve rows on a single table using timestamps).Don't add a new table to your schema. It will only make things more complicated then they should be.

I suggest the following :

1. Add a column to your table, say : process_timestamp date not null default to_date('01.01.1901','DD.MM.YYYY'). In fact, you can assign any default value you want, but don't use NULL.

2. Your program that retrieves the rows should only read those having process_timestamp = to_date('01.01.1901','DD.MM.YYYY')

3. If the execution succeeds, update process_timestamp to sysdate. If the execution fails, leave it unchanged.

Doing so,
1. You don't have to change anything to the programs that are inserting rows into your table. The default value (to column process_timestamp) will be assigned automatically.

2. You don't have to worry about rows that are inserted while you are processing. You will pick them up later on.

3. On failure, you will be able to reprocess the rows as many times as necessary.|||Many thanks for the reply!

Oracle Stored Procedure Design limits with MSRS

What are the Oracle Stored Procedure Design limits with MSRS'
I have a complex SP that is using temp table in Oracle.
Frist deleting the data and doing two insert queriess from select queries to
gather and transform data and place in the temp table.
Then a final select query on the temp table to return the data in the oracle
reference cursor.
This works fine in Oracle and tools like PL/SQL Developer and Crystal Report
and Enterprise.
But in the VS.NET IDE I get nothing but an empty result set back in the MSRS
report writing.
No error messages etc...
Quite puzzling...
Does anyone know why this is happening?
Is this a design issue related to a problem iwth the way Oracle and the MS
Data Provider works'
Is there a work around?
HELP'?
ThanksWhen I tried it, I was never able to make PL/SQL package calls directly from
RS. Straight T-SQL code works fine, but calling packages never returned any
result sets for me. I tried both the MS and ORA data providers.
The workaround I used was to redirect the user to an .aspx page, then make
the package call using ADO.NET. When the package is done processing, the
page then redirects to the final report which then renders in the browser.
James
"Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in message
news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> What are the Oracle Stored Procedure Design limits with MSRS'
> I have a complex SP that is using temp table in Oracle.
> Frist deleting the data and doing two insert queriess from select queries
> to
> gather and transform data and place in the temp table.
> Then a final select query on the temp table to return the data in the
> oracle
> reference cursor.
> This works fine in Oracle and tools like PL/SQL Developer and Crystal
> Report
> and Enterprise.
> But in the VS.NET IDE I get nothing but an empty result set back in the
> MSRS
> report writing.|||I don't use Oracle, so might be talking out of my arse!
But (or Butt if I am!), could you not use a query on a MS SQL server
with the Oracle server as a linked server, or use an 'openrowset' query
again on an MS SQL Server querying the Oracle server. I think I'm right
in saying that this latter option would do all the processing on the
Oracle server (and hopefully use all it's functionality) just returning
the dataset.
I have my suspicion though it all boils down to the quality of the
OLEDB/ODBC provider used. And most non-MS based systems have a vested
interest in providing poor interconnectivity with MS.
Chris
James Counihan wrote:
> When I tried it, I was never able to make PL/SQL package calls
> directly from RS. Straight T-SQL code works fine, but calling
> packages never returned any result sets for me. I tried both the MS
> and ORA data providers.
> The workaround I used was to redirect the user to an .aspx page, then
> make the package call using ADO.NET. When the package is done
> processing, the page then redirects to the final report which then
> renders in the browser.
> James
> "Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in
> message news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> > What are the Oracle Stored Procedure Design limits with MSRS'
> >
> > I have a complex SP that is using temp table in Oracle.
> >
> > Frist deleting the data and doing two insert queriess from select
> > queries to
> > gather and transform data and place in the temp table.
> >
> > Then a final select query on the temp table to return the data in
> > the oracle
> > reference cursor.
> >
> > This works fine in Oracle and tools like PL/SQL Developer and
> > Crystal Report
> > and Enterprise.
> >
> > But in the VS.NET IDE I get nothing but an empty result set back in
> > the MSRS
> > report writing.|||I know that it is possible to use Oracle Stored procedures. I have not done
it myself but I have seen people work through issues and be successfull.
Have you searched the newsgroup for any posting that refers to Oracle.
One suggestion I do have is to use the generic query window, this makes sure
that you are using the managed provider. Also, start out without parameters
and get that working. I know some people had trouble with some parameters to
Oracle Stored Procedures but they got that to work eventually. That is why I
suggest starting out with no parameters and then go forward from there.
There are certain design limits you have to follow for it to work but if you
do that you should be able to do this. Temp tables should be OK.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in message
news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> What are the Oracle Stored Procedure Design limits with MSRS'
> I have a complex SP that is using temp table in Oracle.
> Frist deleting the data and doing two insert queriess from select queries
to
> gather and transform data and place in the temp table.
> Then a final select query on the temp table to return the data in the
oracle
> reference cursor.
> This works fine in Oracle and tools like PL/SQL Developer and Crystal
Report
> and Enterprise.
> But in the VS.NET IDE I get nothing but an empty result set back in the
MSRS
> report writing.
> No error messages etc...
> Quite puzzling...
> Does anyone know why this is happening?
> Is this a design issue related to a problem iwth the way Oracle and the MS
> Data Provider works'
> Is there a work around?
> HELP'?
> Thanks
>